Dropdown list and presence verification in Add form

[Fabrizio1]

Hi everyone,

I'm creating a form with few fields, I'm in trouble with the first one:

the form is related to table A, the first field has to be a dropdown list (type Enum and in valid_if  I set table B's column name), required. Table B is read only 

So far it is fine but I would like to:

1. give possibility to user to add other value as well as selecting from the list: I set the option Allow other values but while testing I cannot see the item to manually edit the field
2. do the presence check: if the selected item is already present in table A, the system update the related row without creating a new entry 

any idea in how to do it? Thanks much in advance for your support!!!

[jyothis_m]

---

@Fabrizio1 wrote:

give possibility to user to add other value as well as selecting from the list: I set the option Allow other values but while testing I cannot see the item to manually edit the field

---

if you give valid_if  then only select will be possible. Instead of valid-if you can give it in suggested values

[Fabrizio1]

Thanks much @jyothis_m 

Setting suggested values it works perfectly, just one more question: I have a second field that should behave like the first one but suggesting a subset of the column, such subset is defined by first field. I tried but neither valid if or suggested values are effective, the solution works only using valid if in both fields (unfortunately not allowing new entries)

Any other tips? Thanks again much

[jyothis_m]

Can you pls tell what you want exactly as the subset. Are you looking for a dependent dropdown list Dependent drop-downs - AppSheet Help

[Fabrizio1]

correct, this is the case ( valid_if in both fields).

only thing missing is the possibility to manually insert the entry

[jyothis_m]

Can you explain what is the filter condition for the subset along with the corresponding columns and tables

[Fabrizio1]

Sure!

It is exactly like region/country example, there are 2 tables, A and B. Table B contains several columns, among them there are 2: Brand and Product, for each brand there are several product.

The form, that is related to table A, field #1 is dropdown for brand while field #2 is dropdown for product. If I set valid_if respectively to B[Brand] and B[Product] it works fine but, since B[Brand] and B[Product] do not contain any possible existing brand and product I would give possibility to the user to add a different item.

Maybe the only solution in this case is to change A[Brand] type from Enum tu Ref -> B allowing user to create new entries to table B (no longer read only). Then I do not know how to depend dropdown field #2, do you have any idea?

Do you think it is a feasible solution?

[jyothis_m]

Give me few minutes. Let me try creating a sample app.

[jyothis_m]

Pls see the sample app below and tell if this is what you want. 

Sample-2 Dependent Drop-down - AppSheet

Products table is read-only

You can Select or Add new Brand as well as Product in Order Form

[Fabrizio1]

Good but if I select brand A, in product field it suggests me A1,A2,C1,C2.

Same with B, it filters exactly with C.

It is just a matter of adjusting the formula but it should be the solution.

I'll deepen into this one later on, thanks much @jyothis_m 

[jyothis_m · Accepted solution]

Sorry @Fabrizio1 

Corrected now

[Fabrizio1]

That's perfect now! Thanks much again @jyothis_m 

[jyothis_m]

You can also try this method which will automatically update Products Table if new product is selected in Order Form. Products Table is not read-only but is updated with new products automatically

Sample-3 REF DependentDropdown - AppSheet

[Fabrizio1]

I had the same idea, in this case how can I suggest values in Product field having same PRODUCT table before (having one row for each product instead of one single row for Brand)? 

[jyothis_m]

in that case instead of dereference expression you can use

SELECT(Products[Product],[Brand]=[_ThisRow].[Brand])

[Fabrizio1]

I see, it is the suggested value in case of dereference, you inserted it in Sample-2 Dependent Drop-down - AppSheet

I meant what would be the suggested value of product field in case of referenced but with PRODUCT table having one row for each product instead of one single row for Brand. Do you think it is a feasible solution?

[jyothis_m]

---

@Fabrizio1 wrote:

but with PRODUCT table having one row for each product

---

In this case  Ref won't be a good choice i think. But still you can use the select expression in that case also. 

[jyothis_m]

---

@Fabrizio1 wrote:

do the presence check: if the selected item is already present in table A, the system update the related row without creating a new entry 

---

Since the entry is made inside a form to create a new row, it think that won't be possible from this FORM.

Maybe you can try creating a BOT that will update the columns of the existing row and finally delete the current row.