Hello, I am going around in circles on this one. Any help is much appreciated.
I’ve built app to manage a series of music events.
I have the following tables:
Users (Name, email, role [could be Band rep or event coordinator], phone, Band Name, event Name, …). This is for band reps, who will enter data for their band, and event coordinators, who will access this data.
Musicians (Name, instrument, requirements, Band name, event name, stage plot). This is where I store the band members for each band.
SetList (song title, artist, band name, event). Each band will enter their songs
Event List (event name + some info)
The event coordinator will have access to an event details page, where the coordinator will see a table with the bands participating to the event. The bands will be displayed in the order of play.
I created a Band_Event data table where I save Band name, Order of Play, Event Name, plus a few computed columns: Relationship_Band-rep, Lookup_Band_rep, Lookup_Band_rep_phone, Lookup_stage_plot
I set up the Battle details page (visibility is Role = event coordinator).
Source: Band_Event
Components: text: User/event name (which displays the event that the event coordinator is assigned to) - All good here.
Collection Table: Source Band_Event. Columns: Order of Play, Band name (filtered by event). Table sorted by Order of Play. - All good here.
Now we get to the sticky bit.
When the coordinator taps on a table row with a band name, a band details page opens. In this page, the coordinator will see:
The band name - OK
*** The band rep name and a button to call them - PROBLEM I can select Lookup_Band_rep but it does not return anything, even if I see it correctly on the data table**
A table with the band members, their instrument and requirements (source: Musicians) - OK
rel_band_rep
Relate to items where the value in: Band_name
Matches the value in: Users>Band_name [match multiple NOT ticked]
Now, I’ve been trying different things and I got something that seems to work (but I cannot figure out why, which makes me nervous).
I created a relation column Rel_Band_musos
Relate to items where the value in: Band_name
Matches the value in: Musos>Band_name [match multiple ticked]
Then a Lookup_Band_musos with relation column: Rel_Band_Musos > Name
which returns multiple names
Then a Single value column, which takes the first value of the Lookup_Band_musos column
I can display this one and the values are correct.
It seems that the issue is the relationship table (Rel_band_musos instead of rel_band_rep), but cannot figure out why, so I call it luck and it could be that it displays the correct values by chance.
Yes, it is.
When you configure a relation with “Match multiple” selected, any subsequent lookups that target that relation will always return an array (even if there is only a single match), and arrays cannot be used in any component that expects a string value - such as a text component.
Your single value approach works, because the single value column returns, well, a single value.
But the easier fix is to just use a single relation in the first place (Match multiple not checked).
Thanks for explaining. A couple of things. Please tell me where I am wrong and thanks for your patience.
The relation rel_band_rep looks at the Users data table and returns a one to one relationship. Then the Lookup Lookup_band_rep retrieves the band rep name and returns an (of a single value). If I feed this to the text component, it does not show because it is an array. However the Template type should convert it to a string. Still it does not work.
Also I can’t use a Single value as the rel_band_rep cannot be selected. I thought I could use it to convert a single value array to a text.
The relation rel_band_musos looks at the Musicians data table and returns an array (multiple values according to the number of musicians). Then the Lookup Lookup_band_musos retrieves the musicians names (multi value array). Then I use Single value (First) which takes the first value of the array and returns it as text. Which means, that it returns the band rep name only if the band rep entered his/her name first.
So I’ve been lucky, but it is not a correct solution.
rel_band_rep has Match multiple NOT selected. It’s a 1-1 relationship and it points to the Users table.
Why can’t I do a Lookup and then covert to text with Template or Single and use that?
Firstly, here is something to understand. Any time you see a value inside a lookup column in the data editor that appears inside a “bubble”, then it means the values in that column are an array.
That doesn’t make sense. A lookup through a single relation won’t return an array, unless the target column is already an array. Question: When you look at the values for “band rep name” in your Users table, do they appear to be inside a “bubble”?
Why is that a multiple relation if you only want one value?
By the way, in terms of what you have now, you could skip the lookup column and just target the single value column directly at the relation. But again, why not a single relation?
I’ll admit, I am finding it a bit difficult to follow, and your earlier screen shots weren’t very helpful because I couldn’t tell which table is which, and there was too much redaction to be able to determine which values were arrays and which weren’t.
If you’re still stuck with this, would you mind giving me temporary access to your team? I feel that if I could see all this in front of me, I could help solve your problems in seconds.
