Built my base a little wrong I think but I just need this to last a couple months until a permanent solution comes in. In the screen shot I have a few rows that include different quantities of the same part number. I would like to be able to combine the same parts that are separated into a single row for a summary. They are separated like this because we have separate “batches” going out for powder coating. I presume this will be in rollups but I haven’t figured it out yet. I’m trying to spend very little time on building anymore into this as we have a permanent solution being made but it’s a month out yet.
I started to play with interfaces but didn’t see an immediate solution there.
This summary is to give counts and weights for shipping the products. These ship to powder coated and then come back to us for post process work.
Hey @Colby_Waibel!
Welcome in!
This is interesting.
I’d love to explore this with you if you’re down to jump in this hole with me.
There’s a good chance that I’m missing exactly what you’re looking for, but here’s where my mind jumps to.
I created a base to play around with this use case, and here’s a preliminary, basic setup I pieced together.
We first want to think about how we should structure our data and design our (data)base.
I’m thinking about it like this:
-
You need to keep track of all your parts.
- Names
- SKU/SRAS PN(?)
- Total Inventory
- Weight of Individual Parts, etc.
-
You need to keep track of your batches.
- What’s the status of the batch?
- What parts are in the batch?
- How many of each part are in a batch?
- What’s the net weight of all the parts in the batch?
- When did it ship out? etc.
That means you probably need two tables (possibly three… but I’d like to aim to have only two if possible.)
First I created a Parts table.
This acts as both your inventory as well as your list of parts.
Since these records contain information on everything that has to do with your parts, we’ll also keep data like what crate they’re stored in, as well as things like their SKU/SRAS PN, weight, etc.
Next, I created a Batches table.
This was an interesting challenge.
There are a few reasons why this is weird that are a bit dense to explain, so I’ll summarize them at the very bottom of this post to read if you’re curious.
I created two views.
One contains a record “type” for your batches.
Here you can see that this record contains all your critical batch information.
Net weight, the total number of parts, date it shipped, etc.
The second record “type” is for the parts in a given batch.
These records are children to the batch “record type” in the same table.
To give you a peek at how it’s structured, here’s what it looks like without filters in place.
So when you have a new batch, you create a new record for the parts in the batch. You’d set the quantity, and then your inventory will update to reflect what’s currently available.
If you hop back to the parts table, you’ll see that there’s a rollup field that takes all of the batch information and then calculates your true inventory based on how many parts were in your batches.
So if you get a shipment for 100 parts but then put 15 parts in a batch, your available inventory would drop to 85 until you reset your true inventory count or received more parts, etc.
Truth be told, it would be best to leverage an automation to update your inventory counts, but for the sake of this quick prelim., I’m leaving it out.
Inventory management is kind of weird for me, so I apologize for any confusion in how I explained it.
I’m hoping this is a solid starting point.
Let me know if I’m completely missing something or if you want to keep digging into this.
