Table Structure to Track Projected Inventory over Time

[leisterbrau]

I am using airtable to manage information related to a landscape design and installation business.   For most products, we purchase them as we need them and don't hold inventory.  However, for plants, we sometimes order in bulk (lots of 32 or 50), hold them in inventory and then use them as projects come up.  Plants that I order in bulk come in size a or b.

I would like to be able to visualize projected inventory at points in the future (2w, 1m, 2m) for each plant in the different sizes we can order it in.  (i.e. today I have 0 of x, but in 2w after a purchase of 30 units and sale of 20 units I will have 10)

Currently, I am using the following tables and fields to track plant inventory in and out.  The plants table is linked to sales and purchase by the SizeAIn and  SizeAOut rollups.  It can then calculate inventory by SizeAIn - SizeAOut.  I can use filters so that it excludes proposed sales/purchases or filter by date.  Is this the most efficient way to accomplish what I am looking for?  Because purchases and sales are in separate tables, every filter scenario requires 3 columns in the plants table.  Would it help if create a separate table for the distinct plant size combinations and then use those in the sales and purchase tables?

Table: Plants: SizeAIn (rollup PurchaseLineItem), SizeAOut(rollup SalesLineItem), SizeA (formula SizeAIn - SizeAOut), many fields with general plant information (i.e. height, width, moisture requirements, etc.)

Table: Sales Line Item: Plant, Sizes (SizeA, SizeB), Quantity (#), Date, Status (proposed, approved, completed)

Table: Purchase Line Item: Plant, Size (SizeA, SizeB), Quantity (#), Date, Status (proposed, approved, completed)

[TheTimeSavingCo]

re: Would it help if create a separate table for the distinct plant size combinations and then use those in the sales and purchase tables?

Hmm, so this means that in the "Plants" table, you have one field to indicate SizeA quantity, and one field to indicate SizeB quantity?  If so, yes, I think splitting them out into individual records would be great.  Would it be possible to just keep it in one table instead of creating another one?

[leisterbrau]

To clarify, the plants table is mainly general information about a plant...scientific name, common name, sun/shade, moisture, soil, cold hardiness, etc.   This is information that doesn't change with the plant pot size.  As currently configured, it has separate fields for quantity in inventory for the general plant at each size.

Within the plants table, I wouldn't want to split it up into separate records because it would duplicate the general plant information.  But maybe create a new PlantProducts table that includes the different plant and size combinations.  Then link that to the SalesLineItem and PurchaseLineItem tables.  That might make more sense...

[TheTimeSavingCo]

Ahh yeah, that scientific name etc stuff should be in its own table for sure.  Your idea sounds good then: have a "Plants" table with all the plant details, each of those records is linked to a "Size A" and "Size B" variant in the "PlantProducts" table, which is in turn linked to "SalesLineItem" and "PurchaseLineItem"