Trouble populating 2 fields in one table based on ...

Daisy_Ramirez · 10-31-2019 10:03 AM

Google Sheets = Database

When a quote is entered,
1.user can select a Prospect or use the New option and input contact info
2.screen reverts to the quote
3.user inputs the location (depot) they need for the quote
4.user selects the product and enters other relevant data
5.user presses save

I have two virtual columns on the Prospects table in order to capture their preferred depot and size of the product quote when the quote has been completed with the following formulas:

vir_max_quote_depot
LOOKUP(MAXROW(“Quote Header”,“Quote Date”,[Prospect]=[_THISROW].[prospect_id]),“Quote Header”,“quote_id”,“vir_depot_name”)

vir_max_container_size
LOOKUP(LOOKUP(MAXROW(“Quote Header”,“Quote Date”,[Prospect]=[_THISROW].[prospect_id]),“Quote Detail”,“quote_id”,“Product”),“Product”,“Product_ID”,“Size”)

I need the data from two formulas to be stored in the database but it appears I’m running into a timing problem as the fields are not being populated with the results.

I then created an action to set the stage of the quote to equal “In Process” on Quote save and then created workflow to set the fields to the values of these formulas. It works when I enter the a sample quote but doesn’t seem to work for any other user.

Is there another way, I can get these values to populate the 2 key fields in the Prospect table from data in the Quote Header and Quote Detail tables?

Steve

FILTER(), LOOKUP(), MAXROW(), MINROW(), and SELECT() can only see rows that are already saved; they cannot see the row(s) in the form.

Steve

I can’t wrap my head around your schema. Can you provide a simpler description of the problem?

Daisy_Ramirez

Hi Steve, I’ll disregard what I’ve already tried and ask the general question:
Is there a way to get a field in a row in table 1 (Prospects) to be populated from data in a related row from table 2 (Quote Header) ?

Steve

Thank you for the simpler question.

In general, yes, but only if the related row in table 2 exists already, before the row in table 1 is added or edited.

If in the process of adding a row to table 1 you add a row to table 2, you cannot reference the new row in table 2 from the new row in table 1 until both rows are saved. This appears to be what you’ve described:

So I do not believe what you’ve described will work.

The reason it won’t work is because the new Quote Header row being created has not yet been saved, and is therefore not yet in the database. The LOOKUP() and MAXROW() functions used in the vir_max_quote_depot and vir_max_container_size columns examine the database. But because the new values of the rows being edited in the form have not yet been saved, those new values aren’t in the database to be seen.

Daisy_Ramirez

Ok yes. Is there a way to somehow delay the workflow?

*Upon Save of the Quote Header/Prospect entry, I have an action that sets the stage of the Quote to “In Process”
*I then have a workflow that triggers only when records are marked In Process
*The workflow updates my fields with the appropriate data successfully - sometimes - so we’re back to that timing problem.

Is there a way to somehow delay the workflow to allow the records to save correctly before I attempt to reference the data?

WillowMobileSys

Some recent knowledge I gained concerning the order of processing in relation to Action updates and Workflow processing that may be helpful here. I don’t know if it applies to your situation since I don’t know your processing details - but maybe it helps

When updates are sent to the server for processing, any triggered Workflows will run as soon as the update that triggers them is applied.

So if you have 4 updates, the second is setting stage to “In Process”, as soon as that second update is applied on the server the associated Workflow will run. And while I don’t have confirmation, it has been my experience from several testing attempts, that the additional updates will WAIT until the Workflow processing is done.

Now, for the tricky part. Let’s say the 3rd of the 4 updates is a change to a row in Table Z. But the Workflow just triggered ALSO changes that same row in Table Z. When update #3 is applied, it will OVERWRITE the changes made by the Workflow.

Why?

The update from the device was made without any knowledge of the Workflow running. The server simply applies that #3 update as it should - last update wins. This is no different than two users changing the same row at the same time - last update wins.

What to look out for

There are a few ways to help mitigate this potential issue of order dependence:

Look for ways to perform as many of the updates in the UI as possible through Actions. This has the added benefit of updates being seen by the current user ASAP!
In a series of UI updates, if any trigger a Workflow, try to ensure that update is the last one. (e.g. a Grouped set of actions). If you find that a Workflow must run before some Action results are applied, then this is a very strong indicator that you should re-structure the processing flow as you will likely run into the overwriting issues described above.
If its a Workflow that runs several Action steps (even Actions that call other Actions), look to break up that processing into isolated Workflows that do not depend on each other. You can have several Workflows tripped by the same criteria.

I hope this helps at least a little!

Daisy_Ramirez

Great info! Thanks John

Steve

I would recommend making vir_max_quote_depot and vir_max_container_size normal (not virtual) columns and setting their values from a workflow.

Daisy_Ramirez

Yes, I have that setup but its not posting the data to the database.

Steve

I think it’s time you engage support@appsheet.com on this.

Daisy_Ramirez

Thanks so much for all your help Steve. I have an email into support and hope to hear from them this morning.

I’ll post any solutions

Trouble populating 2 fields in one table based on data from 2 other tables