Create picklist column sourcing the same table

[Andras]

Hi there,

I'm trying to create a Picklist that is sourced from the same Table to store relationship mappings between clients. I'm not concerned about bidirectional mappings for now, I can create an automation for that.

The Picklist works great within a Table, I can even customize the display text.

And choosing from the dropdown, it creates rowlinks as expected.

However, the difficulty comes when I try to use this in an App. In a New Client form I add a Data Cell for the Relationship column, but if I Format Selection as Auto then it does not become a Picklist even though that column is one:

If I format the Data Cell as a Picklist then I can't choose which column to reference. Even though I use a customized display text in the Picklist inside the Table, it does not show in the Data Cell that way and shows the first column instead:

I tried setting the initial value to =Table1[Relationship] which makes it show #VALUE! when nothing is selected:

But the items in the dropdown show with the custom formatting I created:

Yet, it still shows the Client ID after making a selection:

Admittedly, it stores the value correctly as a rowlink in the Table but it's a confusing user experience. Additionally, with the last method I sometimes get an error message that it could not load the next page and I have to manually refresh but it still inserts the data to the table.

I'm thinking about the following options:

• Create a new column to be the first column containing a formula to concatenate the First Name + Last Name cells and that would display instead of the Client ID.
• Store all clients in a separate table a second time just for the purposes of using in the Picklist but that's tedious because that second table will need to be updated when a new client is added in the main table.

Any other ideas how could I improve this?

Regards,
Andras

[Andras]

I also had to make a slight change to the automation for the Submit button at the bottom of this form to ensure it does not store #VALUE! literally in the spreadsheet if a selection is not made.

The Data Cell in my example is called DataCell8 so I added the following in the Take data from: section:
=IF(ISERROR($[DataCell8]),"",$[DataCell8])
and write to:
[Relationship]

It would be nice if I could make the field not show the #VALUE! at all.

[aj]

Hi @Andras, try setting the data source of the Block that this data cell is in to the table name (e.g. Table1)? For example:

Once you have that, then set the source of the data cell object to =Table1[Relationship], make it Editable, and keep the format as Auto.

The result should be something like this:

[Andras]

Hi @aj thanks for the suggestion. I tried doing that, set the Block source to Table1 and the Data Cell source to =Table1[Relationship] but the first value of the picklist is already pre-selected in this case, both with Shared and Variable source type. That seems to be the case for you as well based on the screenshots. I want to let the user not choose a relationship, i.e. it can be empty.

[aj]

Hi @Andras, I see what you are saying now. Your 1st option above related to creating a new/left column with a concatenation may be a viable solution then. For example:

Clients table

Relationships table

Picklist in builder

[Andras]

Thanks @aj , I will give that a try later. Right now the relationship is not a major component in the client table, so having that as a first column would be a bit weird.

Could you please help submit a feature request internally based on this topic to have the possibility to specify the column when choosing Picklist for the format?

[aj]

Hi @Andras, yes I can pass the word on. This has been brought up before and the product folks are aware. I'll +1 it though.

Keep those ideas and questions coming in!