Solved: Multiple Selection Reference Field

David2 · 10-05-2020 03:18 AM

Hi,

Is it possible to have a reference field that allows multiple select?

For example, in my tasks table, I have a field for assigned to, which links to a staff table, and want to be able to select multiple staff.

Possible? Had a quick look around the editor and on the forums and couldn’t find much.

Lucinda_Mason

You have to save the app after selecting EnumList and setting the type to Ref, then you can set the base type table.

First set the Type to EnumList, then set Base Type to Ref. Then SAVE!

It will give an error because you couldn’t set the table before but now you can. See Referenced Table Name. It’s been like this as long as I’ve been using AppSheet. I don’t understand why they make it a two step process.

View solution in original post

Steve

David2

I’ve had a look through this but was unable to find the exact answer I was after, I found a sample app below, which does what I need, allows for an Enumlist where multiple entries from a referenced table can be selected by using a EnumList Type, Base type set to Ref and a section called Base Type Details where the referenced table is selected. However, I was unable to replicate this in my app, the Base Type Details is not visible in my view.

Lucinda_Mason

You have to save the app after selecting EnumList and setting the type to Ref, then you can set the base type table.

First set the Type to EnumList, then set Base Type to Ref. Then SAVE!

It will give an error because you couldn’t set the table before but now you can. See Referenced Table Name. It’s been like this as long as I’ve been using AppSheet. I don’t understand why they make it a two step process.

Adml

Hi @Lucinda_Mason,

Thank you for your solution.

Once that relationship is established using EnumList and Ref as the base how would go about (de)referencing it given the result for each record is a cell containing multiple related key values as text.

Using the Tasks to multiple Staff example from David2 how would you display related tasks for a given staff member? A virtual column on the Staff table with a select() against matching values in the column or something else?

RefRows complains about column types and I’m wondering how well supported this column / relationship type is within AppSheet.

Steve

Pranav_Subraman

Hi @Lucinda_Mason,

Thank you for giving a detailed view on the problem. As @Adam_Lawton is asking, I’m also having the same issue. How should we display related tasks for a given staff member?

I tried the “SELECT(Tasks[Member], IN([ID], [_THISROW].[Related Tasks]))” under App Formula for column Related Tasks as per the reference of @Steve in List Dereference article:

I have 3 Tasks:

But the Related Tasks shows 0:

Kindly guide me on this please.

Steve

Is the Related Tasks column a normal column, or a virtual column? It should be a virtual column.

pratik3p

Lovely, was looking for exact same thing. thanks a ton

ElChapo615

Bless you David for asking and bless you Lucinda for the answer. 🙏

David2

Thank you so much @Lucinda_Mason! That’s extremely helpful, I’ll try this tonight. Never considered saving the app to see if it allowed for me to complete this process. Thank you.

David2

I am having one issue with this method. In my tasks view, I have three columns that reference my staff table, and these are ‘task by’ ‘assigned to’ and ‘completed by’.

I have attached images of each, for some reason. As I progress through the fields, I seem to loose an option each time. Can anyone suggest why this might be?

Arivel

Hi @David2

I stumbled against the same issue.
Solved by adding a “Valid if” rule in the data validity section like that:
=ref_table[column_name]

I think appsheet need some rules on what information it will display from the refs.

An example in the link below:

Ben_Systems

Thanks that help me too

in a visiting app to add how much people will be send to visit a sick guy in hospital by an organization

and it save only the id but shows the label from the ( CONTACTS[Full Info])

and It works Great

Thanks Again

rbocazl

Thanks, you saved my life

Josephine

I notice on a few things in Appsheet, that you can’t complete a whole section in one ‘save’ - this example being perfect. We choose a ref base, save (knowing we’ll get an error), so that we can then see the next box needed to save without an error.
Are there any plans to remove these additional steps?

David2

Apologies I haven’t responded to the previous responses, after continuously hitting walls with Appsheet I switched over to Ninox Database, far better implementation and much easier to work with.