Separating enum list referenced data

I’ve got this detail view:
image

from table ‘band’. The musicians come from an EnumList from table ‘musicians’ since both tables have the column
image
where for the ‘band’ table 'musicians is valid if musicians[musician].

So the table view of band looks like this
image

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
image

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

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

image

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.

image

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?

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.

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

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.

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.

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

image

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.

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.
image

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

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

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.
image

The current table view of ‘musicians’ table.
image

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.

image

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.
image

This is the spreadsheet for the ‘band’ table.

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

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.

1 Like

That fixes the musicians reference. No more yellow exclamation
image

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

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.

1 Like