Skip to main content
Solved

Multiple product quantities moving inventory to multiple locations

C

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! 

Best answer by cadyellow

TheTimeSavingCo wrote:

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?


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!

View original
Did this topic help you find an answer to your question?

13 replies

TheTimeSavingCo
Forum|alt.badge.img+18

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?

Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • Answer
  • June 10, 2024
TheTimeSavingCo wrote:

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?


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
Forum|alt.badge.img+18
cadyellow wrote:

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!


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?

Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 14, 2024

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
Forum|alt.badge.img+18
cadyellow wrote:

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. 


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

Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 15, 2024

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
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo
TheTimeSavingCo
Forum|alt.badge.img+18
cadyellow wrote:

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!!


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?


Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 18, 2024
TheTimeSavingCo wrote:

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?



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
Forum|alt.badge.img+18
cadyellow wrote:

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!


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

Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 20, 2024

Okay, one more thing that's kind of seperate from this issue but related... 

I want to make labels for each product that I receive. I've read there's not a great way to link airtable with Avery Label templates, but I was able to design one with the Page Designer extension that will meet my needs. I wanted to use my "Received Products" table to make labels just for exactly what new products I was adding right then, only to discover that it was printing one label per line item regardless of the quantity. 

So I've now created another table for Labels and am trying to figure out an automation so that when I add a product into my "Received" table it will automatically add it to the "Labels" table but also duplicate it as many times as the quantity says. I've read a bunch of responses on the community and they're close but not quite the right answers for me. You've been so helpful, do you know a way I could do this? 

TheTimeSavingCo
Forum|alt.badge.img+18
cadyellow wrote:

Okay, one more thing that's kind of seperate from this issue but related... 

I want to make labels for each product that I receive. I've read there's not a great way to link airtable with Avery Label templates, but I was able to design one with the Page Designer extension that will meet my needs. I wanted to use my "Received Products" table to make labels just for exactly what new products I was adding right then, only to discover that it was printing one label per line item regardless of the quantity. 

So I've now created another table for Labels and am trying to figure out an automation so that when I add a product into my "Received" table it will automatically add it to the "Labels" table but also duplicate it as many times as the quantity says. I've read a bunch of responses on the community and they're close but not quite the right answers for me. You've been so helpful, do you know a way I could do this? 


Ah, I know just the post for this!  @Dan_Montoya solved this problem here: https://community.airtable.com/t5/interface-designer/create-duplicate-records-with-interface-form/m-p/179340

Should do what you need!

Adam, Consultant @ The Time Saving Co
C
1 person likes this
  • C
    cadyellow
C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 20, 2024

This is so great, but I'm still so confused how the automation works, especially if I don't want it to erase what's in my "Received" table. It seems like with this van example they want it to come back to zero after the automation runs. But how do I get it to duplicate the records without erasing my "Received" quantities back to zero? The link to the sample book table didn't include the automation details. 

C
  • cadyellow
  • Author
  • Inspiring
  • 7 replies
  • June 20, 2024

Nevermind! I got it! I made some new columns that did the extra math and that did the trick. Thank you again!

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo

Reply


Most helpful members this week

Terms & Conditions