Nested Collections and Records

@Phil_Evans I am intrigued by this suggestion but don’t really understand it. Would you be willing to elaborate a bit and explain how you would configure this in Appgyver?

Just an idea (I dont know if i understood the question right): what if you use a group by.
So you have one table, all the artists in one column and all the albums in one column. In airtable you can group them by whichever column you like in 2 or 3 clicks. In appgyver you can group them with formulas.

Thanks, @Edvin_Rushitaj. This idea looks promising, but after fiddling with the GROUP function for a bit I’m not sure it will give me what I’m after (though it’s possible I’m misunderstanding). Let’s see if I can illustrate.

I have an artist dashboard page (see image) that is pulling from the Artist table in my Airtable using “GET COLLECTION”. Tapping here navigates to an “Artist Overview” page.

The Artist Overview page defaults to a Dashboard view that is pulling all the Artist details using a “GET RECORD” request and a page parameter with the Artist ID.

From here I want to tab over to the “Albums” area to view the Artist Albums. I assumed (based on my limited knowledge so far) that I would have to revert back to a GET COLLECTION request here to access a list of records (albums) but I don’t know how to make it dynamically pull the albums associated with the current artist ID.

Does that make sense? Please assume very limited experience here as I am very novice and trying to learn as I go.

Thank you all again for your help!

If you open a new page from a page which has the selected artist, you could configure the new page to receive a “page parameter” that you could set to the selected artist ID. Thus you can have that value on the next page as well.
Read here for more:

I was talking about the dB structure. But considering the appgyver side, everything just becomes a list of songs. Use a criteria to limit it to a specific album. Producing an album list might be harder, I don´t know if there is a “select unique” function.

Just throwing ideas out there really.

What are you actually trying to build? A personal music player or a some unique spin on the current ones?

Thank you @Mihaly_Toth . I used this process to open the “Artist Overview” page. So I created the page parameter and have no problem passing the Artist ID when using a “single data record” data variable.

This allows me to access all the “Fields” associated with that artist record. The problem is the Albums are not contained in an artist field but are records themselves that are linked to a specific Artist.

So to get a list of Albums I assume I have to use the “collection of data records” variable type which does not allow you to link to any particular ID or page parameter as far as I can tell (see below)

Am I making sense? It seems the idea of “nested lists” within specific records would be a pretty simple function but perhaps I am not describing it well.

How do you “link” the albums to the artist?
I mean in the database what is the reference?
Do the albums have a field named: “artist”: artistID?
Or do you have a list of albumIDs among the artist fields?

When using the get record collection you do have filtering options in the bottom right side of your screen.
If you have a field in the albums as artistId that would be the most straightforward way, because:
In that case you could do the following:
Get record collection from the resource ‘albums’. Filter condition would be set to:

artistID is equal to pageParams.artistID

Screenshots with the important parts in red (i have a sample dataset just to show):

This is the filtering formula:

Ah… I see what you were saying now from a dB perspective. I think the reason I chose to create an Album table was so I could include fields that offered information specific to those albums.

It’s not a player I’m trying to build. More of a music rating/cataloging system that has some unique analytics built in. Weirdly the stuff I thought would be most simple is turning out to be the most complicated. :_)

A system you suggest here is definitely solvable. But the most important factor is to know how do you wish to link between the artists, albums and songs. Sorry, just updated my previous answer with some more details.

This looks very promising! The only problem here is I don’t have the Filtering, Ordering, and Paging options that I see in your screenshot.

Is there something I need to do to have those show up?

oh, is it Airtable? I missed that point.

In this case I have to dive deeper into it. Because those fields might appear only for Firebase connector. I am not sure, though…

I would still stick to a flatter structure but use 3 tables

Collection; Artist
Document: auto assigned ID
Fields: As required. (If you include number of albums then you know the ID for all albums)

Collection; Album
Document: auto assigned ID (Consider using same ID as artist appended with -01, -02 etc indicating album number
Fields: As required, including artist ID as one field (not needed if using the composite ID suggested)

Collection; Song
Document: auto assigned ID (or use composite of Artist -aa-bb where aa is artist album# and bb is track number)
Fields: As required

I believe this would make it simple enough to get at whatever data you wanted. I do something a little similar with some of my data.

1 Like

So if it is Airtable you use for your backend I suggest checking out these posts. Maybe they can help you out:

1 Like

Hey @Mihaly_Toth… the posts linked above were very helpful and I believe have pointed me to the right solution, however something is still not quite working and I was wondering if you could help me diagnose.

I was able to figure out the “filterByFormula” query parameter for Airtable which looks like this:

The specific syntax here is indicating {ArtistID} as the field to filter by and using the existing page parameter “ArtistID” as the condition. This formula is currently yielding zero results, but I don’t think the problem lies with the formula or the syntax; rather it seems it is not recognizing the Artist ID.

If I go into the data collection Test area and use the filterByFormula parameter to select for a static value like the artist name it works perfectly, and you can see the ArtistID field show up in the results:

But when I change the condition to look specifically for that ArtistID I get zero results:

Naturally, the dynamic formula isn’t going to work if it can’t even find a static value, but I can’t figure out why it’s not recognizing the ArtistID when I am pulling it right from the original array.

Any thoughts?

In your data resource artist ID seems to be a list of texts. So changing that to simple text should solve your issue.
So check if in Appgyver and Airtable the ArtistID type is the same.
I don’t know how would you use find by key or similar formulas with airtable queries. But essentially if not changing the type of the artistID to text in Airtable, but having it still as an array (list of texts), you should send the list of texts, from Appgyver app as query parameter. So maybe use:

{ArtistID}=MAP(params.ArtistID, item)

A few brackets to keep in mind:
You can see that in Airtable the
ArtistID: [
“fjndkaibflsk”
]

  • This “[]” bracket indicates a list or array.
  • The “{}” indicates an object, with its “key”: “value” pairs. But the value can even be “[]” array, or number, or true/false (boolean).
  • And lastly the “()” this is important to check in the formulas, but it is essentially the same as formulas in Microsoft Excel or any spreadsheet.

A while ago I worked with some nested objects/lists example. In nested lists you often need to add a connection from the parent to the children. Maybe this can help in some way.
https://forums.appgyver.com/t/manipulate-data-in-nested-lists-example-countries-api/5404

@Mihaly_Toth , thank you for explaining these details. It was very helpful.

After asking around the Airtable forums it seems every “linked” field will always be an array and this cannot be changed. So I need a new approach.

I am intrigued by your suggestion above but I don’t really understand the MAP function and can’t seem to find a tutorial in the Appgyver documentation. Can you explain in a little more detail how I would go about applying this formula “{ArtistID}=MAP(params.ArtistID, item)” with my set-up?

I also had another idea to create a new page parameter based on ArtistName (which is a single text string) instead of ArtistID. I created the parameter and tried to dynamically bind it to the data variable using “ArtistName=params.ArtistName” but it didn’t work either. If I just put in “ArtistName=‘The Beatles’” it works. So there is something wrong with how I am formatting the dynamic part of this I guess?

I don’t know. But I am running out of ideas.

Hi there, it’s been a while since we tried to figure this out, but I am glad you had some help from Airtable forum as well. Now I read through the whole thread again and have noticed a few things.

  1. When you go on to the artist dashboard page do you still have the page parameter artistID? What do you mean by “tabbing over” here?
  1. There is a difference in syntax when you show the results of a static search and the syntax you tried on get record collection. I assumed that we need to have the {ArtistID} in brackets. But it seems that it is not the case. So maybe try
ArtistID=STRING(params.ArtistID)

Also if you cannot use the params.ArtistID for other functions either, I would suggest going through the logic flows. Maybe there is a leftover flow that changes the value of it. Or you could try the debug preview mode to see how the values are assigned.

One more thing to try:

ArtistID=WITH_ITEM(pageVars.justaquerylist, params.ArtistID)

For this add a new page variable of your desired name which should be a “list of texts”.

Thank you for the quick reply!

I pass the the Artist ID from an Artist list page to what is effectively an Artist detail page. This works perfect as it passes along all the artist detail contained in the fields associated with the artist record. From this Artist detail page I have 3 tabs which I created using the conditional renderer container. Looks like this:

Then I tab over to Albums:

This all works just fine and I assume the page parameter containing the artist ID is active on the whole page and is not affected by the conditional renderer.

I tried inputting this as you suggested: ArtistID=STRING(params.ArtistID). It did not return any results.

I did have another thought. Previously you asked:

The answer is ‘all of the above’. I link every Album record to the the respective artist record which creates a field “ArtistID”. I also mirror that field within the Album record with another plain text field called “ArtistName” (this is why I can input ArtistName='The Beatles" in the filter and it works fine).

But the Artist link mentioned above also creates a field within the Artist record that references all the Album IDs that are linked to them. I wonder if there is some way to use this field to search for albums that only match those IDs?

Sorry to dump so much on you. Just trying to help you help me as much as possible… :_)

Well, I would try to display the page parameter in the conditional render. Like just add a text and bind its value to see if it is really there. I have not really used the conditional renderer yet, though.

So if you have a list of album ids you could in fact go with a loop in the logic which would query each album details as a Get record, but then you need to build a list of objects to be able to use it as a repeated list. And I am not sure how this would affect the performance and read-counts. But it might be worth a try.
Create a pageVars.albumDetails > list of objects with the exact same properties as the get record data variable (or in fact maybe you can create a collection of albums and then do the logic in that canvas)
Also need a pageVars.loopIndex, just to know wether to keep going or exit the loop.

Logic would be:
First make sure loopIndex is 0, so set pageVars.loopIndex to 0. Then:
If flow function (pageVars.loopIndex<COUNT(data.selectedArtist.albums).

  • true: get record where the id is the data.selectedArtist.albums[pageVars.loopIndex] → set page variable (or data variable) to the following formula WITH_UNIQUE_ITEM(pageVars.albumDetails, outputs[“Get record”].response (or something similar) → set page variable pageVars.loopIndex to pageVars.loopIndex+1 → go back to the IF function.
  • false: set pageVars.loopIndex to 0 and you should already have all the album details.