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:
LOOKUP(MAXROW(“Quote Header”,“Quote Date”,[Prospect]=[_THISROW].[prospect_id]),“Quote Header”,“quote_id”,“vir_depot_name”)
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?