Multiple product quantities moving inventory to multiple locations

[cadyellow]

Hello! 

I've got an inventory board ("Print Products") that has hundreds of products (each with variable quantities). I add new amounts of each product regularly (in "Order Received" and "Products Received" for individual line items). When I sell items they go in "Order Sold" (with "Products Sold" for individual line items).

The problem is, I move various amounts of various products between multiple locations. Sometimes they sell at a location, but sometimes they don't and I need to move unsold inventory around between locations. I created another table "Currently At" to try to keep track of how much inventory was at each location, but I'm finding it incredibly time consuming and tedious to input each quantity one line item at a time so that I can check in/check out the different quantities of each product. I was able to write an automation so that when I put something in "Products Sold" it automatically put it in "Currently At" with the correct quantity decrement. I can make forms for receiving, sold or updating the "Currently At" table, but each of these forms still only lets you put in one item at a time. When I take products to a location, I'm often taking 200 items that are requiring quantity changes of 1-5 each for several different products (decreasing the quantity from one location, adding it to another location). Has someone figured out a better system for how to move inventory between locations and update the tables? At the very least, is there a better automation for how to update the increase and decrease at the same time? Any help would be so greatly appreciated. I'm a one-woman, fine art business and while I love tech, I'm much more comfortable with paintbrushes. Thank you! 

[TheTimeSavingCo]

I assume you have a table where each record represents the movement of a product within a location?  If so:

re: At the very least, is there a better automation for how to update the increase and decrease at the same time?

I like to solve this with a "Transfer" type record where I enter the place where it's moving from and the place where it's moving to, with the quantity.  An automation then triggers that'll create two records, one for decreasing from the place it's moving from, and one for increasing for the place it's moving to

---
re: When I take products to a location, I'm often taking 200 items that are requiring quantity changes of 1-5 each for several different products (decreasing the quantity from one location, adding it to another location)

Hmm, with reference to the system above, I think I'd have a view set up where the 200 affected items are listed with the "To", "From", and "Quantity" fields, and after I keyed in everything I would trigger an automation that would go through all the records in the view and run the actions listed above in the "Transfer" type automation for each of the records found, does that make sense?

[cadyellow · Accepted solution]

Hi Adam,

Thanks so much for your response. Adding another junction table just for movement (with to and from columns) was a really good idea and doing that let me set up automations that will update all of my quantities as things move in and out, thank you. That was the step I couldn't think of on my own. I got that all working great. 

The second part of the question with the view set up is helpful, but the problem is I'm taking different items and different amounts every time and I'm adding more products constantly. So I guess there's probably no fast work around to that, I'll just have to keep inputting exactly what I'm taking every time. But having the movement table will at least make it better. 

So this is probably as answered as it's going to be, but here's my last question. There's no better view for inputting lots of items to an order besides the grid view, correct? There's no way to add multiple items and quantities to a form/interface view, right? Is that what you were trying to tell me in the second part of your answer? 

Thanks for your help!

[TheTimeSavingCo]

Yeah, I'm most comfortable with the Grid view to do this sort of thing and do think it's the best.  There's no way to add multiple items / quantities via form/interface view at this time; you could try checking out Fillout.com for this though!

Just so we're on the same page, I'm thinking of a simplified view in the main products table (probably "Print Products"?) where we can easily set everything up and then run an automation for all the movement records to get created for us automatically, does that make sense?

[cadyellow]

Adam,

Thank you for all your help with this. I'm still unclear on what the simplified view would look like from the main table that would trigger movement records to get created for me automatically? I can create views in the main "Print Products" table where I'm looking at/analyzing current inventory, but I'm still making decisions about how many of each item I'm moving to where. So if I have 11 of something and only want to move 4 of that item, I wouldn't want an automation to take all of them (or necessarily the same products every time). I'd love for you to help me understand better what you're thinking about this step, since I'm not understanding how to visualize it. 

[TheTimeSavingCo · Accepted solution]

I've put a simplied version together here of what I meant

The automation triggers when we check "Trigger Movement", then looks for any records in the "Products" table that has a "Quantity" value (i.e. that is currently being moved)

It then uses a repeating group action to:
1. Create a record in "Movement" with the appropriate data linked to the original product
2. Clear the "Quantity", "Move to" and "Move from" fields so that the next time you run the automation they don't get added again

[cadyellow]

This is BRILLIANT!! I didn't realize this was even possible. Thank you so much for taking the time to figure this out and show me exactly what you meant. Thank you for even the clarity of making sure I knew to clear the data after the automation runs so it doesn't import again the next time. I copied your automation into my table at added the columns like you have them here. I just have two questions. 

One, I have a Locations table that's separate. Should I link the "Move To" and "Move From" columns on my main table to the Locations table? or leave them as a single select like you have in yours?

Two, there's a linked column in your table that links "Table 1" and your "Movement" table together. Which table did this originate in? Or did it show up after your automation ran?

Again, thank you SO MUCH for all your help! This is going to save me so much time! I now understand what you mean about a simplified view where I can quickly look at products and quantities, make movement decisions and then check the trigger box when I'm ready for new records to be created and everything to be updated. I'm so excited and thrilled to have a solution for this, thank you!!

[TheTimeSavingCo]

Glad I could help!

re: One, I have a Locations table that's separate. Should I link the "Move To" and "Move From" columns on my main table to the Locations table? or leave them as a single select like you have in yours?

Hmm, this depends on whether you have location specific information you'd like to store in Airtable, e.g. address, contact numbers, etc.  If you do, a new table would be great, if not, a single select field's fine.  Having it as a table instead of a single select field won't affect the functionality here so it's totally fine whatever we decide

---
re: Two, there's a linked column in your table that links "Table 1" and your "Movement" table together. Which table did this originate in? Or did it show up after your automation ran?

Ah, once we create a linked field to another table a new field will automatically get created in the linked table

e.g.:
Table A has one field: "Name"
Table B has one field: "Name"
In "Table A", we created a linked field to "Table B"
In "Table B", a linked field back to "Table A" will automatically be created

Does that make sense?

[cadyellow]

That makes sense and the whole thing is working great. Thank you so much for your help with this! Also, I've run the automation and it automatically erases it for me from my main table when it's done, is that supposed to happen? Either way, it's great and I'm so happy. Thank you again for all your help!

[TheTimeSavingCo]

Yeap, it's supposed to automatically erase it from the main table!  I was worried that we might forget to clear out those values, resulting in them being added again the next time we ran the automation