Automation: Update Table TOOL ASSIGN from another table

[desmond_lee]

Hi Guys

Anyones know how to use automation to update the table TOOL ASSIGN from the data in TOOL DATABASE when the TOOL ASSIGN form is SAVED. Below is that I have done but I dont know why it does work.

ACTIONS : ANY(
SELECT(
TOOL DATABASE[CAL CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID])), ANY(
SELECT(
TOOL DATABASE[PM CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID])), ANY(
SELECT(
TOOL DATABASE[LOAN CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID])), ANY(
SELECT(
TOOL DATABASE[STOCKTAKE CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID])), ANY(
SELECT(
TOOL DATABASE[ISSUANCE CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID])), ANY(
SELECT(
TOOL DATABASE[SCRAP CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID]))

The TOOL ASSIGN FORM saved trigger

The Source Table. Tke Key = STANDARD TOOL ID

The table TOOL ASSIGN to be updated on column, ISSUANCE CODE, LOAN CODE, SCRAP, STOCKTAKE CODE,CAL CODE, PM CODE. The KEY=ASSIGN ID

[WillowMobileSys · Accepted solution]

ANY(SELECT(TOOL DATABASE[CAL CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID]))

In bolded part above, these are row keys in two different tables.  Right?  Based on my experience, AppSheet treats these as different TYPES of the same value.  Because they are DIFFERENT types they will not match.

Try this instead:

ANY(SELECT(TOOL DATABASE[CAL CODE],TEXT([_Thisrow].[STANDARD TOOL ID]) = [STANDARD TOOL ID]))

Wrap the [_THISROW].[STANDARD TOOL ID] in the TEXT() function.  With the value as text, I believe AppSheet will try to convert it to the proper "TYPE" of table row key.  

If for some reason that still doesn't work, then wrap both values in the TEXT() function and try again.

NOTE:  This can only work because you are using the same key values in both tables.  Most App Creators use a designated auto-generated row key.  So in usual circumstances, the approach described above would not help.  In those circumstances, the INPUT function would be required and an extra action step involved.  INPUT(), for some reason, is still considered in Beta and is not available to all apps at the moment.

[WillowMobileSys · Accepted solution]

ANY(SELECT(TOOL DATABASE[CAL CODE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID]))

In bolded part above, these are row keys in two different tables.  Right?  Based on my experience, AppSheet treats these as different TYPES of the same value.  Because they are DIFFERENT types they will not match.

Try this instead:

ANY(SELECT(TOOL DATABASE[CAL CODE],TEXT([_Thisrow].[STANDARD TOOL ID]) = [STANDARD TOOL ID]))

Wrap the [_THISROW].[STANDARD TOOL ID] in the TEXT() function.  With the value as text, I believe AppSheet will try to convert it to the proper "TYPE" of table row key.  

If for some reason that still doesn't work, then wrap both values in the TEXT() function and try again.

NOTE:  This can only work because you are using the same key values in both tables.  Most App Creators use a designated auto-generated row key.  So in usual circumstances, the approach described above would not help.  In those circumstances, the INPUT function would be required and an extra action step involved.  INPUT(), for some reason, is still considered in Beta and is not available to all apps at the moment.

[desmond_lee · Accepted solution]

Hi WillowMobileSys

Thank very much for your reponse. I manage to get thi in another way. See below

Use this expression to pull data from Tool Database at the ACTION resolve what I want ANY(SELECT(TOOL DATABASE[CALIBRATION TRIGGER ALERT DATE],[_Thisrow].[STANDARD TOOL ID]=[STANDARD TOOL ID]))