Seeking Advice on Syncing Airtable Bases for Seamless Project and Inventory Management

[Jeff_Hladek]

Hey All!

I need some help! I'm relatively new to Airtable and currently diving into setting up an inventory/project management system for my team. Our approach involves utilizing Airtable as a combined solution for both project and inventory management.

Here's a quick rundown: We've established a "project-specific" base to monitor products that need to be ordered, received, and delivered for each project each product has up to 750 items to keep track of. Simultaneously, we maintain an inventory base to keep track of surplus inventory and items that have been received for a project. For instance, if we need 3 units of product A for a project but have 2 in surplus, we only order 1 for that project.

Now, I'm facing a challenge. We want to mark products as received in the project base and have our inventory base automatically update when we receive products. Essentially, if we order 1 unit of product A, I'd like to mark it as received in the project base and have our inventory base reflect the increase accordingly. Additionally, it's crucial to tag that specific piece with a project identifier, indicating its commitment to a particular project.

Given that we often juggle multiple projects simultaneously (sometimes up to 5), it can become quite complex to manage committed inventory versus available stock. I'm reaching out to the community for any insights or ideas on how to achieve this seamlessly.

If you have experience with a similar setup or need more context to provide guidance, please let me know. Your expertise is highly appreciated!

Thanks in advance for your help!

[Devinder_Singh · Accepted solution]

You need data sync and automation to work together. When the data sync updates a record, you need logic or automation to figure out the increased inventory and set that value. One option is to keep an orderedUnits field in the inventory base and set it from the Airtable data sync. Then, have an automation trigger that adds this orderedUnits field to the existing inventory field.

If you're open to external solutions, Byteline has both data sync and automation that can work together to solve your use case. You can reach out to us through the chat widget on our site.

[Jeff_Hladek · Accepted solution]

Thanks, I was able to take this and adjust it a bit to make it work for our situation. I created a button in the project-specific bases that lead to a form in the Inventory base. I set up the form to prefill based on the data input in the project base when receiving. The inventory base has a records table that keeps track of the incoming forms and links it to another table. Then using other formulas and rollups, I can figure out how much total stock we have, how much is committed to a project, and how much is available for use. Now I just need to figure out how to remove all stock for a project when its time to ship it out.