Forum Discussion

whitneyk's avatar
whitneyk
Qrew Member
13 days ago

Inventory Tracking

I have these tables:

  • Trailer
  • Trailer Inventory
  • Item
  • Item Usage

 

Currently my structure is set up like this:

  • Each Trailer has many Trailer Inventory records
  • Each Trailer Inventory has many Item Usage records
  • Each item has many Item Usage records
  • Each Item has many Trailer Inventory records

 

This is what I am trying to do:

Enter in an item usage record and subtract from Trailer Inventory.

When the item usage record is entered, I know the Trailer ID & The Item ID.

I need these two IDs to match to a record in the Trailer Inventory table and subtract the quantity used from the Starting Quantity in that line.

Example: In Trailer Inventory, I know that my starting quantity for Item #1 on Trailer #1 is 10. I then go enter in an item usage record and I enter that I am using Item #1 on Trailer #1, and I'm using 5 of them. I want that to go match up to the Trailer Inventory record using those two IDs, and subtract the 5 used from the original 10 that we started with.

I have a summary field on the relationship between Trailer Inventory & Item Usage that summarizes the "quantity used" from the item usage table. I was trying to add criteria to this summary field to say only summarize the records where the Trailer ID & the Item ID match on both tables, but I can't seem to get it to pull in the correct fields in the drop downs so I don't think my connections are set up properly. Can anyone help with this?

Relationships
  • MariaPeralta's avatar
    MariaPeralta
    Community Manager
    13 days ago

    Your best bet might be opening a support request given that I'd like to see your app to be able to answer this usefully. Is the Inventory record that is the mate of the added Usage record already made when they add that Usage record? One approach might be to have a Pipeline trigger when the Usage is added to then create the appropriate Inventory record to adjust the number available... but you should probably have someone look at your app and give you feedback from there.