Grist: lookup value from another table

user_14781203 0
Viewed 263

Attempting to learn Grist, and don't know Python...but willing to learn. Just trying to draw the lines between Python and formulas.

I have a table that has "Items": fields named "ProductID" "collection" & "buyer" There is another table that is named "Sales": fields named "Sku"(same as ProductID) "Qty" "Cost" "Sales" "Date"

I would like to create another table, that consolidates the data into one document (since all of sales may not be in all of items, and sales has a ton of duplicates due to the date the transaction occurred.)

Something like: "Sku" "Buyer" "collection" "Qty" "Cost" "Sales" "margin"(formula to calculate) "Sales" would need to be the root table, and reference the "items" table for more information.

If my data was smaller, in excel I would:

copy skus, paste in new tab, remove duplicates, and run a sumifs. ex: if in cell B1 and sku is in a1:

=Sumifs(sales!$Qty, sales!$sku, A1)

Then I would run an index match on items in c1 for example:

=index(items!$Buyer, match(a1, Items!$ProductID, 0), 1)

grist python
1 Answers

(Very late, but answering in case it helps others.)

It sounds like the resulting table should have one record per Sku (aka ProductId). You can do it in two ways: as another view of the Items table or as a summary of the Sales table grouped by Sku.

In either case, you can pull in the sum of Qty or Cost as needed.

In case of a summary table, such sums get included automatically (more on that in https://support.getgrist.com/summary-tables/#summary-formulas).

If you base it on the Items table, you can look up relevant Sales records by adding a formula column (e.g. named sales) with formula:

Sales.lookupRecords(Sku=$ProductID)

Then you can easily add sums of Qty and Cost as columns with formulas like SUM($sales.Qty) or SUM($sales.Cost).

user_3285650
Related