Extracting a list of Unique IDs from a text field

[Adm_Tech]

Hi Community,

how to extract from a column only IDs of a jobs?

The column value concatenates IDs and descriptions, e.g.:

OifY0Thq: „text1”/ 101e9c50: „text2”/ bc49a497: „text3”/ 89f7bba2:”text4”

I need to extract these IDs, find them in different rows in another table and set up the dates for a specific column.

Suppose that should be possible.

Thank you!

[Steve · Accepted solution]

You might reconsider your schema. Concatenating a raw ID with an arbitrary text value seems impractical, unless the concatenation is a key value.

The easier-but-less-efficient approach would be to add a virtual column to the table containing these concatenated values, with an app formula expression like this:

INDEX(SPLIT([Concatenated Column], ": "), 1)

The resulting value of the virtual column would be the ID component of the concatenated value.

See also:

help.appsheet.com

INDEX()

One specific list item

help.appsheet.com

SPLIT()

List from text

[Adm_Tech]

Thank you very much, Steve.

I’ll apply your suggestions today.

[Adm_Tech]

And having such a list of IDs is possible to find them in another table column in different rows and set up value (date) for corresponded column in that another table?

[Steve]

Please explain a little more about what you’re trying to do. I’m confident it’s possible, but the details will determine how.

[Adm_Tech]

In the JOBS table I have the enumlist field referenced to orders from JOB ORDERS for a the product. The list shows concatenated ID + job description.
Technician must choose these orders which are closed during that JOB visit.

Very often a technicans close and mark >1 job order in 1 JOB visit, and I have several ID’s of orders in that field, separated of course.

Now what I need is:

1. to extract only IDs from that field (without descriptions)
2. then to go the JOB ORDERS and find these IDs
3. then in each of ID row to put in the END DATE column - the closing date, e.g. TODAY()

I hope this can explain better.

[Steve]

Use an action of type Data: execute an action on a set of rows that uses the expression I provided previously to identify the target rows and performs an action on each of the rows that sets the end date.

See also:

help.appsheet.com

Actions: The Essentials

Learn how to use actions to modify data and navigation in your app.

[Adm_Tech]

Thank you!

[Adm_Tech]

Hi, Steve,

the formula you suggested works in my case. The only thing is that the sync is slower but that’s acceptable for now (actually thinking if of migration with tables to MySql can improve the speed and whole).

Thank you very much!