How to assign entries in one tab to different categories in another tab

[Julius_Kratschm]

Hi guys, might be a simple question for you, but I’m just starting with airtable.

In my database I have 1 tab called “Inventory”, where I track my stock of products and 1 tab called “Purchase Orders”, where I track purchases that employees do via a form. Both tabs are obviously connected.

In the “Purchase Orders” tab I have the purchases. Now I want that e.g. the equal purchases #33 and #35 go into the “Inventory” tab and are assigned to either the “Product Stock” or “Other Inventory” (corresponding to column “Inventory Category”):

But unfortunately in the “Inventory” tab in the column “Inventory Category” there is this product (Iseo Zylinder) displayed comma separated as “Product Stock, Other Inventory” and there is now a new inventory category called “Product Stock, Other Inventory” created (for both Rollups and Lookups):

What I want is that #33 is assigned to “Product Stock” and #35 is assigned to " Other Inventory" in the “Inventory” tab. And not with two comma seperated values in one cell in the column “Inventory Category”. Can someone help me out here?

Thanks so much.

Best Julius

[Julian_Kirkness]

Something that I’m curious about - you are entering the Inventory Category in the Purchase Orders table - is there a reason this isn’t entered in the Inventory table?

[Julius_Kratschm]

Hi. I try to explain:

The Inventory Table should be a fixed table with fixed products where nothing will be edited manually in the future. All things like stock quantity, products ordered and products sold, change automatically according to the data in the “Purchase Orders” and “Delivery Request” tables.

The same counts for the “Purchase Orders” and “Delivery Request” tables. Nothing (except the purchase status) should be edited manually here. Both tables are automatically filled by Forms by our employees.

The Inventory table should be a clean overview. I don’t want to see 10x orders of the same product here, but only a summary of the products including stock, coming from the “Purchase Orders” minus “Delivery Request” tables.

Was that somehow understandable?

[Julian_Kirkness]

Yes, I get that - but how/why is the category of the inventory item set in the Purchase orders table - what does the category field mean in this case?

[Julius_Kratschm]

Its also set via a Purchase Orders Form. It is mandatory to decide when filling out the Purchase Orders Form, whether the item belongs to the Product Stock (that can be sold immediately) or to the Other Inventory (e.g. Office Supply).

Attached is actually what I want to achieve:

[Julian_Kirkness]

OK - I understand…

I don’t think this is going to be possible in the way you hope. The grouped view can only show a record once and therefore cannot be in two groups.

I think you are going to need to have separate records for each Product / Category combination - especially as you appear to need to hold different information for each (the stock levels are different in the screenshot for example).

[Julius_Kratschm]

Julian_Kirkness:

I don’t think this is going to be possible in the way you hope. The grouped view can only show a record once and therefore cannot be in two groups.

Hm I thought it might can work as it is not exactly the same record twice, because the entries differ between “Product Stock” and “Other Inventory”

And it must be possible to see how many quantities I have in the Product Stock vs. how many quantities I have in the Other Inventory. Imagine you buy 10 iPhone X and 8 go in the Product Stock and 2 I keep myself (Other Inventory). And now a client requests 10 iPhone X and is willing to pay a lot. In this case I have to see how many I would need to take from my own inventory rather than from the stock. :confused:

[Julius_Kratschm]

@Julian_Kirkness Any other ideas? E.g. work with IF functions to search for validity in another tab: e.g. In Excel it would be something like =IF(Purchase Orders!D:D =“Product Stock”;“Product Stock”;“Other Inventory”) D:D would be column “Inventory category”.

Or maybe even working with two tabs: 1 called “Product Stock” / 1 called “Other Inventory”?

Thanks for your help anyways.

[Julian_Kirkness]

Hi @Julius_Kratschmer

You may like to try having two tables instead of the one - one for Products and one for Stock - Purchase orders would be linked to the latter. In general, each product could then have more than one stock type in the Stock table. You would then use a formula for the Name field of {Product) & " - " & {Stock Type} so that your staff could select the relevant one.

You could then use roll up and formula fields to get the various totals you need at both the Stock level and Product level.

Hope this helps?