Separating enum list referenced data

[Tom_automated]

I’ve got this detail view:

from table ‘band’. The musicians come from an EnumList from table ‘musicians’ since both tables have the column

where for the ‘band’ table 'musicians is valid if musicians[musician].

So the table view of band looks like this

listing the conductor and time. If you go to the specific row, you’ll get a detail view like picture 1.

The table view for my musicians table looks like this

When you go to a specific row of the musicians table, you’ll get something like this

I would like the musician detail view to also display the conductor and dates that the particular musician is part of. So if Ed is playing in the band with Ellen and Molly from the ‘band’ table

Then the below view with Ed’s details should also list those dates and the conductor.

I’ve tried making the ‘conductor’ column of the band table a Ref to the musician table, but nothing displays in the Related Bands inline. Also, when I try to add from the inline section, nothing shows.

I’m guessing this has to do with the Enum List, but I don’t know how to extract the related info for just one of the musicians in the list?

[WillowMobileSys]

First, what kind of object is shown in that initial image with conductor and musicians? I’ll just refer to it as OBJECT.

Make sure that your EnumList for musicians has the Base Type set as Ref and the source table set to the Musicians table. You will likely need to set the Base Type, Save and then re-enter the column to set the source table.

I believe then that AppSheet will Automatically create a reverse reference list within the Musicians table pointing to those OBJECTs that have that musician listed on them.

I am not clear on how Related Bands is part of the question.

[Tom_automated]

I had the EnumList base type as name. If I change it to Ref, the reference breaks as seen in the form

My ref column in the ‘bands’ table

Doing this causes AppSheet to auto compute the column Related Bands in the musicians table which is where I expected to see the conductor and times that the individual musician is part of.

[WillowMobileSys]

Tom_automated:

I had the EnumList base type as name. If I change it to Ref, the reference breaks as seen in the form

You will need to update the musician list to be the list of row keys instead of the names AND make sure the name column is set as the LABEL column.

If I am understanding your data structure, making the above changes should correct the Related Bands list. It’s not currently working because the Ref links are broken. In other words, when you have just the list of musician NAMES, the system doesn’t know which rows to build the reverse reference to.

[Tom_automated]

Making ‘musician’ in the musicians table both the key and label does not seem to change anything. I get basically the same result.

[WillowMobileSys]

Sorry I need to make sure I am clearly stating where these changes are to be made.

Brief explanation of Ref columns.

A Ref column type allows you to point to a “row” in another table. It requires being assigned the key value of the row. the key is stored as the column value in the sheet or database. But we don’t want to display the ugly key value in our views so AppSheet has also provided the ability to set a displayable value using the Label property. In views, where the Ref column is displayed, AppSheet will automatically replace the key value with the value of the Label column.

Ref columns have another advantage in that they allow direct access to any other column of that assigned row. E.g. Say the source table has columns “A”, “B”, “C”. A column set as type of Ref can access those other column using “Dot” notation. These would look like:

[ref column].[A] 
[ref column].[B]
[ref column].[C]

The setting of key and Label columns in a table is done regardless of how the table will be used in the app. Each table should be inspected to make sure the appropriate column is set as the key and assign a column as Label.

---

Brief explanation of EnumList with base Ref type.

The EnumList column type allows choosing of multiple rows. You can set various types of data to be shown in the dropdown list. One of these is a Ref base type.

An EnumList set with a Ref base type works very much like that described above for Ref columns. EXCEPT a user can choose more than one Ref row. The chosen row keys will be saved in the sheet as a comma separated list.

The definition of EnumList with a base type of Ref will look similar to this:

---

---

I am confused by your image showing UNIQUEID and musicians Ref column. You stated it is the musicians table. But you would not have a musicians Ref column in the Musicians table.

In a post above you showed a musicians column defined as EnumList. That is the column I am focused on trying to fix. It first needs to be correctly defined as described in the “Brief explanation of EnumList with base Ref type” above.

Then in the Musicians table:

1. make sure the correct column is set as the key. Maybe it also has a UNIQUEID column? If so then that is your key column.
2. set the column that has “Ed”, “Fidel”, “Alexis”, etc as the Label. (Name column?)

Now you need to fix the musicians EnumList selected values so that it contains the row keys and not the names. The easiest way is to use the app and go to that dropdown, clear the list, re-select the musicians and then save.

The list you are showing will still look the same but now the column will have saved the key values instead of the name text. This in turn should fix your Related Bands list.

I’d finally like to point out that there is not a lot of detail provided about how you have setup your app data. There is a big assumption that you have used the AppSheet provided features. If you have structured the data in an unconventional (i.e. unexpected way), that’s not a problem, we just may need to dig deeper with more details to resolve your issue.

[Tom_automated]

Something must not be translating in our communication. This is my band table minus a few columns.

If I leave the base type of ‘musicians’ as name, there is no break in the reference.

However, if I change the base type as you suggested to Ref, then there is a break.

I am using the ‘conductor’ column of the ‘band’ table to reference the ‘musicians’ table.

When I do this, the ‘Related bands’ column appears in the ‘musicians’ table.

I have cleared the rows in both my ‘band’ and ‘musicians’ tables. I have re-added individual musician info into the table via the app. I have rescheduled some bands via the app.

The current table view of ‘band’ table.

The current table view of ‘musicians’ table.

As you can see, there are no related bands from the ‘musicians’ view despite the individual musician being included in the ‘band’ detail view below.

When I try to add related bands via the Add button from the ‘musicians’ view, no new row is added. Something seems to sync, but there is no visual evidence of a new row being added.

This is the spreadsheet for the ‘band’ table.

This is the spread sheet for the ‘musicians’ table.

[WillowMobileSys]

Yes, there is a mis-understanding. If you have Zoom capability. I would be willing to help walk you through all of the necessary settings and resolve the issue.

Let’s take it one step at a time. The musicians column should remain as EnumList. Under the
EnumList settings, there is another property named “Base Type”. This is set to Ref. Refer to the below image as an example of how to setup the musicians EnumList column:

The tricky part is that there is a small bug where the “Referenced table name” property is not shown until after you have set the “Base Type” property to Ref and Saved.

[Tom_automated]

That fixes the musicians reference. No more yellow exclamation

But I still do not see anything in the Related bands section.

[WillowMobileSys]

I see that the Musician table is using Name as the key. That is why the broken yellow exclamation resolved without anything more. I would strongly recommend against using name. You will likely have other musicians of the same name.

For Related Bands to work, the REF_ROWS() function needs to match up by the key column. In this case the musician key. So the function in your case should look like this:

'REF_ROWS(“band”, “musicians”)`

Now that the REf link is fixed. This should work.

[Tom_automated]

WillowMobileSystems:

REF_ROWS(“band”, “musicians”)

That seems to have broke it again

[WillowMobileSys]

The error implies that you used the column name as “musician” - not plural. I believe the “bands” table had the column named as “musicians”.

Try adding the “s”.

[Tom_automated]

That was a typo that I’ve since corrected. This is my current band table

[LeventK]

@Tom_automated
[musician] column in band table is ENUMLIST type. Is Base type REF and it points out to musicians table?

[Tom_automated]

Looks like base type REF to ‘musicians’ table to me

[WillowMobileSys · Accepted solution]

@Tom_automated

Hmm, this is a manually created Related list? I just tried to re-create and get the same error with the EnumList but not with an Enum. So, I actually think now REF_ROWS() will not operate on EnumList’s.

You can still create your custom Related Bands list. You just need to use a different expression. Something like:

SELECT(Band[Key], IN([_THISROW].[musician], [musician]))

[LeventK]

@WillowMobileSystems
ENUMLIST should work with base type REF. I have many apps with REF_ROWS expression pointing out to an enumlist with base type of REF actually i.e. In-App Messaging sample app of mine.

@Tom_automated
In your musicians table change the [musician] column type from NAME to TEXT and then try again pls.

[LeventK]

I do apologize for my mis-direction, I checked my app and saw that I’m remembering it wrong.
@Tom_automated
Actually you can’t create a REF_ROWS expression in the same table that you are already referring in your ENUMLIST base type.

[WillowMobileSys]

Oh ok.

Do you agree that the SELECT() expression I suggested will work in place of the REF_ROWS()?

[LeventK]

@WillowMobileSystems
Yes it definitely will work.

[Tom_automated]

WillowMobileSystems:

SELECT(Band[Key], IN([_THISROW].[musician], [musician]))

The select statement works but now I seem to have a UX problem. I have another table (essentially the ‘conductors’ table with a bunch of other info that actually references the ‘band’ table conventionally. The bottom of the conductors detail view shows the inline view of the ‘band’ table which is what I want

The detail view of the ‘musicians’ table also show the inline view of the ‘band’ table (the select statement we just created)

The inline deck view is fine, but I need to show the ‘conductor’ not the other band members. The problem is, it’s the same inline deck view used on the conductor data table view where I do need to list the other band members (and not the conductor). How do I point AppSheet to using different deck views depending on which table view I’m using (conductor or musician)? I tried creating 2 deck views with CONTEXT("View")="Musician" & CONTEXT("View")<>"Musician" in the Show If area but that didn’t work.