Computing data with two tables

Ask for Help
1

Hello,
This may or may not have been addressed, but after extensive searching through the community, I can not find anything that helps me with my issue.
I have two tables. On one table, I have an inventory list. I have a transaction log on the second table that records the weight or quantity being pulled out or put back into storage.
My app has a form that updates the transaction log as users retrieve items from storage or return them to storage. I want the transaction log columns to update the applicable qty or weight columns on the inventory column.
Im am really struggling with this and can not seem to figure this out. Any advice would be appreciated.

Screenshot (9)
Screenshot (9)1627×805 48.7 KB

Screenshot (42)
Screenshot (42)1612×870 43.9 KB

2

So you are looking for a running total, yes?

2 Likes
3

yes i am looking for a running total. Honestly, thank you, this video is the closest i have gotten. but I still can not figure out how to link the values of the two table together to perform calculations

4

Can you show us what you have set up so far after watching Darren’s video?

1 Like
5

I apologize Its been a min since I have had the chance to respond. I have been away and haven’t been able to follow up until now.

I have not gotten any further on this project even with Darren’s video. I tried a couple of times but Darren’s video isn’t referencing another table. I have a separate table I have to reference to update the totals. table 2 is a separate transaction log that gets updated as inventory is pulled or added to some storage lockers. employees update the transaction log by means of a form in the app. Table one is separate from the transaction log. The transaction log is a history of activity for audit purposes. Table one is a running inventory of the lockers so that alerts can be sent in regards to expiration dates and stock quantities. Darren’s video is great but I couldn’t get his video to apply across two tables.

6

So table 1 showcases “live” numbers, right? I would structure it like this.

  • In table 2, have only 1 column for Qty, and 1 column for Weight. If it’s in, keep it positive, else log it as negative for out.
  • On submission of transaction log, add an action that increment the Qty column in table 1 by “Qty” in table 2, and Weight column in table 1 by “Weight” in table 2. The positive/negative signs handle themselves. You increment those through the “Nomenclature” relation.
7

Thanks. I’ll give this a try and get back to you guys soon!