Dear Airtable Community,
First of all, thank you all for your knowledge and post shares, all of the comments have been incredibly useful to newbies like me and it has helped with my work greatly!
I have a series of questions please:
Right now, I have the following:
- ‘Product’ Table: containing details of product eg. Price, cost (linked to Sub-part and Calendar table). Example: Product A, Product B, Product C
- ‘Sub-part’ Table: containing various parts of each product with its specification eg. Picture, size, vendor details, quantity of each sub-part used in the Product (linked to Product and Calendar Tables). Example: A-1, A-2, A-3, B-1, B-2, C-1, C-2, C-3, C-4, etc.
- ‘Calendar’ Table: containing purchase order details eg. Quantity of ‘Product’ ordered, color, start date, end date, etc. (linked to Product and Sub-part Tables). Example: Order2022-01, Order2022-02
Currently, I have the lookup field of ‘quantity of each sub-part used in the Product’ in the ‘Product’ table, basically to let me see the Bill of Material. Right now, the column of this lookup would look like: 1,1,2,2,3,1 meaning: Product A will need
1 pc of A-1,
1 pc of A-2,
2 pc of A-3,
2 pc of A-4,
3 pc of A-5,
1 pc of A-6
Now, back to the ‘Calendar’ Table, I would like to be able to multiply the ‘Quantity Ordered’ (field type: number) to ‘quantity of each sub-part used in the Product’ (field type: lookup) in order to see the summarized Bill of Material for this order. I’ve learned that lookup field is an array, therefore impossible to use formula with.
Is there any way I can get formula to work with this lookup field? Is there a way to convert an array into series of numbers so that I can get it to work with formulas?
Thank you so much in advance! I appreciate all of your recommendations and suggestions.
Best wishes,
Lynn
