Change Enumlist values based on Form field value

[Bob83]

Hi everyone!

We are within the classic "Orders", "Products", "OrderDetails" database.

I am in the form for inserting a new Product in the "OrderDetails" table. Each product can have one or more "Specifications" that I would like to be able to select from an enumList field present in the same form. Important: the Specifications are not the same for each Product, indeed, each Product has its own.

I then created a "Specifications" table where I associated each product with its own specifications (Text field). I then created, in the "OrderDetails" table, an enumList field referenced to the Specifications table, which I then displayed in the entry form.

I can't make the enumlist field understand that I would like it to only offer me the specifications of the Product I'm going to insert. I know that I have to insert a formula in the "Valid_If" sector of the field, but I don't know, within this formula, how to retrieve the Product ID that I'm going to insert.

Tell me if I'm wrong, but I think I've run into the usual problem where it's impossible to extrapolate a value from a form.

If you confirm that this is the case, is there the possibility of creating an action that filters the enumlist field on the values relating to the Product, to be used when we ENTER the form?

If this is also not possible, I am very afraid that the remaining solution is the inelegant one: create an action associated with the entry of the form to store in some table the ID of the Product that we are going to insert, and then filter the field enumlist based on that value.

I hope I explained myself!

Thank you all

[Tommy62]

In Ihren Erläuterungen ist nicht ersichtlich, ob es eine Referenzierung der Produkttabelle auf die Spezifikationstabelle gibt, denn mit korrekter Referenzierung können Sie einfacher auf alle Daten in Appsheet zugreifen.

Ein Grundsatz, um besser helfen zu können: Bilder sagen mehr als 1000 Worte

[Bob83]

Thanks for the quick reply, even if in German! 😄

There is no direct reference from the "Products" table to the "Specifications" table.
There is an enumList type reference in the "OrderDetails" table (which is the one that joins "Orders" and "Products") to the "Specifications" table.

This is the "OrderDetails" table which contains the reference to the "Orders" table, the reference to the "Products" table and the enumList type reference to the "Specifications" table

This is the setting of the enumList column referring to the "Specifications" table:

This is the "Specification" table:

(for example the code 212a5f02 is "Coffee", and in the table there are all its variants)

This is the form for inserting a new row into the "OrderDetails" table:

Here I would like, by clicking on the "Specifications" field, only those relating to coffee are shown to me (possibly being able to select more than one at a time).

This is the result that I would therefore like to have, for example, after insertion, in "OrderDetails" table:

The Coffee Product (212a5f02) has been added to the specific Order_ID (b0d27045), with specifications 3 and 5 ("Cappuccino" and "Basso").

Thanks again!

[Tommy62]

It should work with a filter expression in Valid If, for example FILTER("?Specification", IN([_THISROW].[?ID_Prodotto],[?Specifica])).

Since I unfortunately also have problems with filter expressions, here is another solved thread on a similar topic: I-can-t-find-a-valid-formula-for-the-amp-quot-Valid-if-amp-quot 

[Bob83]

Hi Tommy!

I tried, but it doesn't work, and unfortunately I think I know why.
When we are in an insertion form, even if it is already pre-filtered on the Product_ID that we are going to insert, the "pointer" [_THISROW].[Product_ID] does not work correctly, because no ROW physically exists yet to refer to.
It will only exist AFTER you click the SAVE button.

But I hope I'm wrong, and that there is some alternative solution...! 😊

Thanks