We’re a service/installation company. I’m new to Grist. I have an “INVENTORY” table containing all of the items (1 item per row) that we might put on a client’s installation ESTIMATE (a different table).
- Each item (row) contains many fields for the “Quantities&Rates” (i.e. item category, our vendor, cost, markup, labor time and rate, and notes on exceptions). A COPY of this info is initially needed on our client’s ESTIMATE.
- Each item/row ALSO contains “reference info” columns (i.e. sizes, pictures, descriptions, links to more info) that are not needed on the ESTIMATE.
I want to be able to reference/look up (like using a catalogue) and COPY only all of the “Quantities&Rates” info for each INVENTORY item to a client ESTIMATE.
My issue: I don’t want to LINK the INVENTORY info to the ESTIMATE because as “Quantities&Rates” change over time in my INVENTORY, I don’t want changes on the INVENTORY list to change other past or future, finished estimates. Don’t want unique changes made to the ESTIMATE to affect the INVENTORY table’s list.
Please help. This is supposed to save me time and so far I can’t find anything different from having two spreadsheets open , popping back and forth, and copying and pasting.
shouldn´t you keep a history of your inventory?
I think Items should be a table.
But quantities, prices, etc, should be another table.
That way, you dont duplicate nor change the item itself. And you never replace prices, quantities, etc. You create new rows for different prices, quantities, etc.
And when creating an estimate for a client, you will select the product and through it you will select the price/date, etc
Anyway, you would instead of selecting from a list that would be too big, you can get the last price. That will still change the the value when the last price changes.
So I guess that after you “assemble” the estimate for the client with the current prices, etc, you could have an ACTION BUTTON widget that when clicked “saves” the estimate… I guess it’s possible then to save the value of a reference instead of the formula, which changes.
You could use a trigger formula. Each item in the estimate would reference an inventory item, but once you select one the trigger formula would pull the rates.
1 Like
I agree with @Bill_Ballou’s answer, and prepared a small example to demonstrate:
https://public.getgrist.com/85XsoNNZKFUu/Fill-Estimate-with-Item-Info/m/fork
Try entering a new line into Estimates: once you select an item, Vendor and Cost will automatically get populated with the current info from Items. If you change these fields in the Items table, the existing values in Estimates won’t change (though they will update again if you make more changes to the Item reference field, e.g. unset an item and set it again).
It’s done using trigger formulas like $Item.Cost:
1 Like
Thank you Bill and Dmitry. Can’t believe this works this way. The example was especially helpful. Perfect!
1 Like
but how do you get only the current values of an item?
Can we use a more complex trigger formula, like getting the LAST item.cost based on date?