Track made products, their parts/stock items & unit cost

There is an accounting or bookkeeping database in the Airtable Universe that is structured to meet your needs which may help give you some hints on how to set this up. You’re basically needing a many-to-many relationship between Accounts and Transactions, since an Account can have many transactions, and a transaction can have many accounts (especially in double-entry bookkeeping). You can also consider using the self-join relationship in a Products table. Having a field for the Product (converted to a linked field to a Products table), the Amount, and the Quantity can be used to rollup these and get a balance for each. Templates

Thanks so much for the suggestion @Ivan_Carlson I’ll have a look in more detail and post back.

Hi @Ivan_Carlson
I’ve had a look and I’m not quite sure how to map what I’m trying to do to your accounting base setup.
I need:
1)Products
2)Stock list (tracking allocated stock)
3)Stock allocated to make product
4)Total cost of stock used for given product along with more fields on the product to make notes etc.

The accounting base has Accounts, Journal, Ledger, Contacts. Which tables would best lend themselves to being modified for my purpose?

You mention using “using the self-join relationship in a Products table” I’m not sure I’m clear on the term self join?

I found this thread that looks relevant and I’ve used grouping before that could be useful here:

Thanks

In the Ledger table where the transaction line items are stored, add a Products single select field, if there is not already one. Use the Products field and the Quantity number field whenever you are recording a transaction to add to/take from a Raw Materials Inventory account, a Work-In-Progress (“WIP”) Inventory account, or a Finished Goods Inventory account. WIP is the account used to track stock allocated to make product. These three inventory accounts can be added in the Accounts table, each of which indicate in the Account Group field a group called “Inventories”. You can add a Cost of Goods Sold expense account in the Accounts table, to a “Cost of Goods Sold” Account Group. Again, be sure to use the Product field and the Quantity field whenever you are recording a transaction to decrease the Finished Goods Inventory account, and to increase the Cost of Goods Sold expense account. The Ledger table already has a Ledger Memo field to make any notes you want when recording sold product, as you mentioned.

You could add a Products table and link it to the Products field in the Ledger table. Alternatively, you could change the Products single select field in the Ledger table to a “Link to another record” field type and point to a new Products table. This will change the Products field in the Ledger table into a linked reference field, and automatically create a new Products table. A separate Products table is necessary if you want to be able to use rollups rather than groupings, and to add a Notes field, as you mentioned, to make notes for each product in the Products table.

The Products table will store all products and their component parts. The Products table can have a Quantity rollup field, and a Total Cost rollup field, each of which will sum all applicable linked records from the Ledger table. Thus, each product listed in the Products table will show its quantity on hand, and its total cost. You could create a separate view in the Products table - each view is filtered on a separate inventory account so that you can see the inventory list for Raw Materials, WIP, and for Finished Goods, for example. You could create a separate Cost of Goods Sold view in the Ledger table, filtered on the Cost of Goods Sold account, that groups by the Product field, to show total quantity sold, and cost of good sold expense, for each product sold, by making use of the summary fields.

The link you provided on the article about many-to-many relationships illustrates a self-linking relationship - like the managers/subordinates example. Similarly, in the Products table, you could add a “Link to another record” field type linking to the Products table (thus making it a self-linking relationship) to track products that have many components (a one-to-many) or many-to-many. However, as previously mentioned, while you would require a Products table to do rollups, the establishment of a self-linking setup may not be necessary, provided that the transaction affecting various products is recorded properly.

Brilliant @Ivan_Carlson :thumbs_up: Thanks so much for all of this - will go away and work on it. Cheers!