Set Value Action updates 1 of 2 expected fields but they have similar requirements

[Daisy_Ramirez]

I have a workflow on the “Products” table configured to run a “Run action on rows” action to reference the correct data in the same “Products” table and then run a set value action on that data.

On change, the system would use the barcode in field “Second Barcode” of the existing record in the Products table (RECORD B), search for that barcode in the “Product Barcode” field of the same table (say for example it finds it in RECORD A) and assign the “Weight” field data from RECORD B to the “Weight” field of RECORD A.

Works Great!

With this action running fine, I chose to update another field in the same way but it fails each time even though it has almost the same exact configuration. Here is the action:

The set value for Scale Weight posts as expected for the correct record. There are no validations on the field.

The set value for Second Barcode does not post:

• There were validations on the Second Barcode field so I removed them for testing but it still doesn’t post.
• The data type of the Product Barcode field and the Second Barcode field is exactly the same
• It is an editable field

What could I be missing here? Any help would be tremendous. Thanks so much!

[Marc_Dillon]

There’s no way that your expression for Second Barcode would ever return a value that is different from what is currently there.

Assume the current value of [Second Barcode] is “A”. It will look for the value “A” in the [Product Barcode] column in Products Table, and then return the [Product Barcode] value, which of course is “A”, because that’s what you just matched.

This is a completely useless and meaningless expression as it is now. What are you wanting to happen with it?

[Daisy_Ramirez]

Here’s the table for reference:

When user updates the Second Barcode field for record having Product Barcode = 777777777 with 995665444, I’d like the system to find 995665444 in the same table under field “Product Barcode” and assign the Scale Weight from record Product Barcode 777777777 to the Scale Weight of Product Barcode 995665444. And also update the Second Barcode field for record having Product Barcode 995665444 with 777777777.

I hope this makes sense. Thanks Marc

[Marc_Dillon]

I would think that both of your expressions are incorrect then.

You’re using a reference Action, from the 777… record, to run the “set values” Action, from the 995… record, correct? Your expressions should be:

LOOKUP( [_THISROW].[Product Barcode] , Products , Second Barcode , x )

Where “x” is the returned value. In your case, it would be Scale Weight for the first, and Product Barcode for the second.

Unless I’m still not understanding…

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Koichi_Tsuji]

Not fully catching up with this thread, but I do believe you are not able to get the expected result by action.

I m not sure how you are triggering the action to change the value of the target column by data change action, however, due to AppSheet current native behaviror, your action is not going to end up with the expected result.

Let me briefly explain the reason.

First of all, im assuming you are firing data change action by “form save” event. Meaning, when the user change the value in second barcode field and the save the form, your subsequent action to change value in other fiield will be fired.

This is one of the area where I need the improvement from the Appsheet, but so that your lookup expression to get the proper result, you need to wait for the sync to be done.
When the data change action to calculate the lookup expression result, currently, your action is leaning on the old state of the app (the data before the data change in secon barcode field is changed).

This is super tricky subject, I m not able to well explain why and how it happens, but this is what it is.

My sugestion is you do not employ the action to change the value in the target fields through the action, just push the same expression to initial value constrains, and set up reset on edit to be fired when your trigering fields value is changed. This should be only workaround to achieve your goal.

[Koichi_Tsuji]

Lookup expression is kinda of expression dependent on the other table (incl table where the data change is to occur). Think about the VC expression where we have same type of expression. To get the VC is fully refreshed by the event of data change to affect the value in VC, we need to wait for Sync to be completed. (if VC calculation is not dependent on table calculation, but only dependent on the row level, this is not an issue)

We are not firing the action with VC calcuation on your case, but the same story is going to apply.

When your user change the value in second barcode value, then immidiately your action to change values in other fields is going to be fired, before your app finishes a sync. Then your action is not way to get the most latest value to push to the target field, so resulted in the un-desired results.

Sorry, i m not good at explaining this tricky subject, but again this is what it is.

[Koichi_Tsuji]

I have been claiming to AppSheet team quite a few times, that we need to have client (device) basis user variable holding feature is essential to have. What I mean we need to have the ability to hold the one time user based variable without needing a sync and consume such variable for bits and pieces. If we could get this feature, your problem is going to be solved, same for me.

[Koichi_Tsuji]

Honestly, this is more important feature for Appsheet rather than Automation/BOT stuffs.

@Steve

@praveen

[Koichi_Tsuji]

I m completely not sure why Appsheet team are spending resource for Automation stuffs, rather than this feature (holding client based variable)…

yes, I m just one of the user of Appshet out of millions, but this is my humble opinion.

I m not so keen on Automation / BOT stuffs, as majorities of my clients requirement were filled by legacy Workflow.

Holding client side variable is lacking completely. (Usersettings function of appsheet is not helping to solve this puzzle.)

[Marc_Dillon]

I don’t really know what this 5-post rant was all about, but @Daisy_Ramirez if my last suggestion doesn’t work for you, you can solve your situation by using the new INPUT() expression, like this:

Using INPUT() to update another table using a value from this row Tips & Tricks 💡

This link no longer works. Here’s a replacement link: https://www.appsheet.com/samples/Inputfuction-230844?appGuidString=502cc394-2717-4a69-8246-5fc26a1aa... Unfortunately, this platform doesn’t not allow posts to be edited after a certain amount of time so I can’t go back and fix it. Here’s a link to a “tip” I wrote about how to make a direct link that works as the platform is currently configured:

[Daisy_Ramirez]

Thanks Marc and Tsuji!

Marc I did try the scenario below with no luck but will read into the Input() possibility.

Tsuji I actually do follow your scenarios above as I too have run into this. There are key changes I’d like to see run locally before the sync.

Will post back if I’ve found a solution.

Thanks again!