You can do it like this:
- All the below should be done in your Test Stockbook table
- Add enough rows in the table to cover the total number of unique parts
- Start by creating the 3 columns described in the 2nd use case in this post.
- The above should result in a column that is numbered starting at 0.
- Now add a Lookup column. This column should target the Part Name column in the Test OrderBook table.
- Next, you need a Unique Elements column. This one should target the previous column.
- Now, a single value column. This one should take “From Start → Row Index” from the Unique Elements column.
- If you’ve done everything correctly so far, you should now have a column that contains a list of unique Part Names. You can now use this column to create a multiple relation back to your Test OrderBook table, matching the Part Names in each table.
- To get the rest of the values:
– In Stock: not enough information to say. Where is the starting stock level stored?
– Total Purchased: use a rollup → count via the multiple relation
– Amt Purchased: use a rollup → sum → Purchase Price via the multiple relation
– Total Sold/Amt Sold: not sure which columns in the Test OrderBook table should be used for those?