Concatenated Drop-Down Values

[Flor_HS]

Hello all!

Thank you for simply existing! I’ve really given my best to get to where I am at with this task but is way over my head at this point. So hopefuly you guys can help!

I have my tables: Orders (Ordenes), Products (Productos), Clients (Clientes), Address (Direcciones) tables. I have an “Address Type” field (Tipo_Direccion) on my Address table.

For some clients we will have their address, from some others we won’t. While filling out the order, I am trying to show all the addresses we have for the specific client on a drop-down menu where I concatenate the address type (home+address, office+address, other+address) and show it so the person filling out the form can pick one of the three if existant addresses to ship the product to.

I created a virtual column using “Concatenate(… (Select…))” and called it from the “Valid_if” expression in the Orders.Address column, the concatenate works fine, it shows both pieces of info in my drop down, the address type + address. However when there are more than 1 address+ address type associated to one client, it shows one single line in my dropdown with all (3) address types and addresses concatenated in one field for that client.

This is how a client with 3 addresses shows:
3 address types first separated by column, then the 3 addresses concatenated all in one single list item to click on

This is the addresses table:

The virtual column:

The concatenate expression:

The column in “Orders” where I call the addresses from:

The validity field expression:

[Pratyay_Rakshit]

The V_Tipo_Direccion column’s formula has been made unnecessarily complex. What you are trying to display from the concatenation, can be achieved just by:

[Tipo_Direccion]&:&[ID Cliente]

When you are using a select expression, it essentially makes a list of values that hold true for the criterion, that’s why it showed three values.

For the valid_if, the expression is correct.

Just change the type of the column DIRECCION_ENTREGA_ORDEN to enum (if you desire one value to be selected at a time) or enum-list (if you desire multiple). When you select ‘ref’ as the type, and constrain the drop-down using a valid_if, it results in broken references (see the yellow triangle beside the selection option in the 1st photo?).

I believe this should do the trick.

[WillowMobileSys · Accepted solution]

Yes, what @Pratyay_Rakshit said, you can simplify the concatenation in the Virtual Column. To elaborate, the expression for [V_Tipo_Direccion] should be:

CONCATENATE([Tipo_Direccion],": ", [Direccion])

However, I would NOT change [Direccion_entrega_orden] to enum (sorry @Pratyay_Rakshit) . Using AppSheet’s Ref column feature is the best approach but…

There are two, actually THREE, other things to take care of.

Set the desired Label column.

The Label is what’s used as the display value for the address, in your case in the dropdown. You will want to set the Virtual Column [V_Tipo_Direccion] as the Label.

Order as Child row of Address?

You provided an image of the column [Direccion_entrega_orden] in the Orders table. Its a Ref column but you have turned on the “Is part of?” setting. This basically means that the Orders table row is a CHILD of the Address table. I do not think this is what you want.

Based on your post, its seems you are simply wanting a dropdown to select one of the three addresses to assign to the Order. If so, then turn off the “Is part of?” setting shown in the image below.

Correct the Valid_If expression

In Ref columns the Valid_If should ALWAYS return the row ID from the source table. In your case that is the column [ID Direccion]. So therefore, the Valid _If expression in the [Direccion_entrega_orden] column should be:

SELECT(Direcciones[ID Direccion], [ID Cliente]=[_THISROW].[ID Cliente])

[Flor_HS]

Thank you guys so much for your help! so it worked, I get three items now but I am not quite there yet, after I made all the changes now I get the following :

I get this when I click on the “Direccion_entrega_orden” field, it shows the Address type and the Client ID

I also get this error, already closed all browsers and re-opened:

This is how I have the Direccion_entrega_orden field:

Here is my Address (Direcciones) table:

[Flor_HS]

Also when changing “Direccion_entrega_orden” to Enum I get this message so I left it as Ref

[WillowMobileSys]

Flor_HS:

I also get this error, already closed all browsers and re-opened:

Make some small change and then re-save, the “version number” error message should go away.

To fix the dropdown, refer back to my post above. And refer the Virtual column expression and Label parts. Make those suggested changes and you should see the address show in the dropdown properly and see the yellow triangle caution symbols disappear.

[Flor_HS]

WillowMobileSystems:

SELECT(Direcciones[ID Direccion], [ID Cliente]=[_THISROW].[ID Cliente])

I worked!!! thank you so so so much!