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
- 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?
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:
and write to:
It would be nice if I could make the field not show the
#VALUE! at all.
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:
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
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.
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:
Picklist in builder
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?
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!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.