Pallet Management System

[Paras_Sood]

Hi guys

I have a simple use- case, just cannot wrap my head around the minor details

We need a system to record transactions for pallets that are coming in and out of the warehouse. ER diagram designed by me is as below:

The part that I am still not sure of is that I need to have information by supplier for the type of pallet that is present in stock. Let me take an example to explain this:

Let's say I got 10 pallets of type 1 from supplier A and 2 pallets of type 2 from supplier A. I also received 2 pallets of type 1 from supplier B and 5 pallets of type 3 from supplier B. I need to know how much of each pallet type I have in stock from each supplier.

Ideally the view I want to see is -

Supplier A:

Type 1  - 1 pallet

Type 2 - 1 pallet

Supplier B:

Type 1  - 2 pallets

Type 2 - 3 pallets

I also want to manage the inventory this way - Stock from the pallets on hand need to be managed (reduced and added) depending on the supplier and the type of pallet.

Can this be solved by adding a pallet on hand column in the supplier table?

I want this to be scalable so if they want to add more suppliers and more pallet types the view grows to include the new types.

I am thinking that maybe I should add another table in the middle as it seems like I have a many to many relationship between pallets and suppliers.

[Ramitch_Palmare]

Oh, there is no need to add a [Pallet on Hand] Column in the Supplier table..

All you have to do is use Ref function

You will have three tables

1. Pallet
2. Transactions
3. Supplier

Under Transactions Data Column, Change the Type of (1) Pallet and (2) Supplier  to (Ref) and reference it to Pallet and Supplier Tables respectively.. Appsheet will then automatically create a relative reference of those tables to each other..

You could just modify how you want it to look like under Views particularly Inline views

[Paras_Sood]

Thanks for your response! I have the tables already setup like that, but I also need to control the inventory level for each type of pallet by supplier. So if I say that I am removing 10 pallets from Supplier B of type 2, then I need to make sure that I have enough of type 2 pallets for supplier B on hand.

[Joseph_Seddik]

---

@Paras_Sood wrote:

I am thinking that maybe I should add another table in the middle as it seems like I have a many to many relationship between pallets and suppliers.

---

👏👏👍