Strategic approach to my Airtable DB design

[Joel_Ford]

Hi everyone; I use different parts from different vendors to create new products that I then offer for sale online. I do not need to track sales, payments, or customers in my database. I do need to track an inventory of parts that are used to make products AND I need to track the product from listing to sold. I currently do this on a spreadsheet which has three primary tabs, data links between the tabs (for drop down selections) and several calculated fields (both logical and numerical)

I’ve tried to adapt my current spreadsheet to AirTable with little happiness. I think I need to start from scratch. I’d like to hear how you think the table designs should be approached. Here’s an example of my work flow:

I buy a number of category1 parts from category1 vendors.
I buy a number of category2 parts from category2 vendors.
I use 1 part from each category to create a new product.
A unique SKU is created by formula, CAT1-CAT2-RAND#
The per item cost of each part is summed to create a new PRODCOST field for the item.
I list that product for sale and mark it as listed.
When it sells I mark it as sold.

The two categories of parts have separate characteristics, naming, and descriptors, and very little duplication of data, so I’ve kept them separate in my current system. I suppose they could be combined, but it just means that a lot of fields in a data row get left blank. I need for the count of parts in each category to reflect current availability, a live inventory, if you will. So, I need it to be reduced by 1 or more whenever I use parts to create a new product.

This seems to pretty straightforward DB stuff. In an SQL database, I think I know how I’d set it up, but I’ll be damned if I can make sense of how to do it in Airtable. Any help will be appreciated.

[Joel_Ford]

Okay, realizing that my first query might have been asking a bit too much from even the most helpful of forum members, I dove in head first and have managed to get done almost all that I needed to. Mostly, the challenge is semantics. I do have one straightforward question left, though. Maybe this will generate a response;

How do I create Link field that will only display records that meet criteria?

I need to link a filed to another table, which is easily done, but I only want that link to display records that have a count of 1 or more in a specific numeric field in the other table. I see how to do this with a Rollup, but I need it to be a linked field.

Thanks!

[Klaus_Becker]

I tried some solutions for that, the one that seemed less complicated was to create a new version of your “other table”, a copy, but excluding the records that don’t match your criteria (you could create a filter to make that easy if there are too many records on the “other table”). with this you have a new matching table, then you can link to this one.

also, can you share your solutions to the problems you listed originally? I’m having troubles with a sales table, maybe we could help each toher more ^^

[Joel_Ford]

Klaus, Not sure that my actions qualify as “solutions” yet, but here’s what I have:

1. An ASSETS1 and ASSETS2 tables that contain the parts I use to craft products. (might merge to one table)
2. A PRODUCTS table that pulls via links from the ASSETS tables to add multiple parts to my finished PRODUCT.

Currently, I am using an IF(myfield=BLANK(),0,1) formula in a field in the PRODUCTS table that adds “1” to the field if an ASSET item is used to craft a product. Then, I use a rollup in the ASSET tables to bring that count in. Then, I use a formula to subtract that from the total number of assets in stock in the specific ASSET table. This provides me with an {#Available} field for each ASSET table.

This works for multiple assets from the same table. Eventually, I may need it to count the number of assets used from one table or another. I suspect that is just a slightly more complex formula along the lines of: IF(x,COUNT(z), else do y).

But, I still haven’t found a way to add only parts that are available (i.e. #Available > 0) to a new product. I’m baffled about why there isn’t straightforward way to manage an inventory. I consider this a basic function of any DB system. Parts in, parts out; I’m hoping I’ve just missed it and some AirTable guru is going to swoop in and sprinkle some wisdom on me…

Joel