Reference without duplicates from dropdown

[Djigi]

Hi Everyone,

I’m not sure if this is the best way to approach this, but let me try to explain the situation:

productid, productname, productseasoning
1 , cashew , salted
2 , cashew , smokehouse
3 , almonds , salted
4 , almonds , smokehouse

This list is bigger, but in this example I would get 4 choices if I reference to productid.

What I would like is to have a choice between cashew or almonds and than another choice between salted or smokehouse. By using valid_if I do get the choices, but that does not work as a reference. Is there a way to reference to a certain product id without having to pick between a list of of “duplicates”, but instead having two narrowed down choices?

[Aurelien]

Hi @Djigi

Can you tell us what you tried so far ? Did you use FILTER() ?

[Djigi]

Hi @Aurelien,

Initially I made a reference to the products without using the productsseasoning, and the seasoning was picked by using enum. It gave me the correct values in the other table. In a previous version I used valid_if which also gave the correct values in the other table, but no reference.
I would like to have a specific reference with the specific productseasoning as well.

I did not use Filter() yet and I’m trying to read up and figure out if this is the solution and how to use it correctly.

[Aurelien]

The main advantage with FILTER is that it gives a lists of Ref values.
So, that’s really easier to deal with Valid_If expression when using it.

Let us know if you get trouble into dealing with it !
Also, that could help if you provide screenshots of your table structures.

See you

[Steve]

help.appsheet.com

Dependent Dropdown

How to make a dropdown with choices that depend on the value selected in another dropdown.

[Djigi]

Thanks Steve, I’ve used it like in that Article (video), but I’m missing the reference afterwards.

[Djigi]

I think I understand what you mean and I’ve made it work, but without reference.

With Valid_if for Product ingredient and Product seasoning the second choice is specific to the answer of the first.

With FILTER(“Production Products”, AND([Product ingredient] = [_THISROW].[Product ingredient], [Product seasoning] = [_THISROW].[Product seasoning])) I get the exact key (Production id) because Product ingredient and Product seasoning are specified, but it is not a reference. Is there a way to make this connection?

Please let me know if I have not provided all required information.

[Aurelien]

Hi @Djigi

In your first picture, something grabs my attention.

Product Id must be of type Ref, then at clicking on the black pen you choose the source table Production Products.
Then, the formula you have set must be removed from the “app formula” field, and set in the “valid_if” field.

Can you let us know if that works ?

Cheers

[Djigi]

This did the trick. Thanks for the help again @Aurelien!

So the solution is to only use valid_if fields and to reference Product id:

Product id:
FILTER(“Production Products”, AND([Product ingredient] = [_THISROW].[Product ingredient], [Product seasoning] = [_THISROW].[Product seasoning]))

Product ingredient:
Production Products[Product ingredient]

Product seasoning:
Production Products[Product seasoning]

I’m still learning, so if there are any pointers to what can be improved. Please let me know

[Djigi]

@Aurelien has helped me with the solution. Thank you very much! My question is if I should put my post with the details as a solution for people who have the same problem in the future. He is the one that deserves the credit for sure though. What is best?

[Aurelien]

Hi @Djigi

You are very welcome, thank you.
I must say that, indirectly, everyone in the community is a part of your solution, as we all help each other at learning and sharing knowledge.
(this is way too wise for me… )

More seriously, the most relevant thing to do, from my point of view, is to set the solution where it will be easier for future searchers to find a solution.
So, feel free to mark yours as solution !

[Djigi]

@Aurelien So profound. I think this, as well, is the solution.

[Djigi]

I’m sorry, but I’ve cheered a bit too early. I do have what I would like, but one more thing would make it more amazing.

Is it possible to make the app define the reference just by picking the Product ingredient and the Product seasoning? Right now I still need to make an additional choice out of one option due to the previous solution. If this could be possible that would make the app perfect. It does not automatically pick the fourth id (as in picture) if is not set to “not show”.

Excuse me for the nitpicking, but I will probably require this for the rest of the app as well

[Aurelien · Accepted solution]

Hi @Djigi

Not sure what you need, but this is probably barrier language for me.

1. you need to adjust the dropdown in the product ID:
   This is what you made yet, so in the Valid_If expression you probably have:

FILTER(“Production Products”, 
  AND(
    [Product ingredient] = [_THISROW].[Product ingredient], 
    [Product seasoning] = [_THISROW].[Product seasoning]
  )
)

2. you need the app to pick in the dropdown without needing the user to enter the field.
   Here, you should keep the previous setting, and set an additional one: you need to wrap the previous expression with an ANY(), in the Initial Value field.
   So, that will be:

ANY(
  FILTER(“Production Products”, 
    AND(
      [Product ingredient] = [_THISROW].[Product ingredient], 
      [Product seasoning] = [_THISROW].[Product seasoning]
    )
  )
)

Let us know if that works for you

[Djigi]

No language barrier it seems. Now credit goes to the correct solution Amazing @Aurelien this is exactly what I needed.