Populate field with values related to List

[Pavel1]

Hello community, need your help, please.

What I need to achieve:

For each new record created in the form linked to the table 1, a user should be able to select one or multiple values from Column A in the table 2. This I was able to achieve with EnumList and suggested values Table2[Column A].

Now, based on the selected values from Table2 [Column A], the app should automatically pick related values in the column B in the same table 2 and populate another field in the form.

Many thanks for any ideas!

[Rafael_ANEIC-PY]

Hello @Pavel, welcome to the Appsheet community !

Is the [Column A] of your “Table2” the label for that table? if that’s the case you could switch to a EnumList with base type REF for your form linked to the table 1, that would save the ID for each row selected instead of just whatever name is in your [Column A].

Once you have a list of ID’s, you just need to filter Table2[Column B] using that list, for that you could use something like:

SELECT(Table2[Column B],CONTAINS([_THISROW].[EnumList Column],[ID Column]))

I’d rather use ID’s to search on your Table2 because names and values can be repeated, but ID’s should never repeat.

[Steve]

Rafael_ANEIC-PY:

CONTAINS([_THISROW].[EnumList Column],[ID Column])

Should be:

IN([ID Column], [_THISROW].[EnumList Column])

See also:

help.appsheet.com

CONTAINS()

Does text contain fragment?

help.appsheet.com

IN()

Is item in list?

[Rafael_ANEIC-PY]

Thanks Steve, i tend to mix those two when dealing with lists hehe

When dealing with EnumList —> IN(), since it is an EnumLIST

For partial lookups for search functions using text —> CONTAINS()

Is that correct?

[Steve]

Rafael_ANEIC-PY:

Is that correct?

Yep!

[Pavel1]

Thank you Rafael and Steve! Appreciate it very much

Not yet working for me, but I believe I didn’t write syntax correct.

Here is how it looks now. I need to populate field called Customer Ref with selected values from Column PO Customer in table StatusApp, where values are equal to the values in the EnumList in the form.

Select(StatusApp[Po Customer],IN(StatusApp[PO nr.],[_thisrow].[PO number]))

PO number is a EnumList with suggested values = StatusApp[Po nr.]

Here is a snapshot of columns in table StatusApp
(in next message - can add only one media)

I’ve tried to do the EnumList based on references from StatusApp but it didn’t work out for some reason (there were no values available, empty). So now I’m back to EnumList based on Text and Suggested values
(in next message)

[Steve · Accepted solution]

Pavel:

Select(StatusApp[Po Customer],IN(StatusApp[PO nr.],[_thisrow].[PO number]))

Should be:

Select(StatusApp[Po Customer],IN([PO nr.],[_thisrow].[PO number]))

[Pavel1]

[Pavel1]

[Pavel1]

Thank you very much Steve! Last quick question: any chance I get only unique values and remove duplicates?

[Steve]

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[dionvk]

Hi Steve,

im quite new on this appsheet, and i tried use your partial search, and i tried to make a more smooth ui on input text and the result, but with my brain limitation i still failed to do so. so far i can only go with combine current search and current result so it have position on top and bottom, but it very ugly

i want to make it to show like this, but with my brain limitation i dont know how to make it, can you please help me to show the way?

this the example i want to make, it have more compact and more easy to look into