Calculate form field from values in another table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have two tables, Item Master and Container Master.  The Container Master has 3 entries, 20GP, 40GP, and 40HQ.  Each of these records calculates a usable cubic feet based on user entered dimensions and usage percent.  In my Item Master table I would like to automatically calculate the number of pieces that will fit into each type of container found in the Container Master based on user entered product dimensions.  For example, let's say we are entering product number ABC123 and we calculate the cubic feet of that item to be 10.  For the Container Master record 20GP, say the usable cube is 1200.  I would like the form to be able to populate a field in the Item Master named Qty20GP = 120, (1200/10).  I would also like it to calculate the values for 40GP and 40HQ.  Any help here is appreciated.
Photo of Rick

Rick

  • 42 Points

Posted 2 years ago

  • 0
  • 1
Re:

I would like the form to be able to populate a field in the Item Master named Qty20GP = 120, (1200/10).


Make a field called [Qty20GP] and make the formula 

round(1200/[Cubic feet per unit])
Photo of Rick

Rick

  • 42 Points
Mark, thank you for the response.  I do have a fields Qty20GP, Qty40GP, and Qty40HQ.  What I can't figure out is how to grab what you have called [Cubic feet per unit] from each corresponding Container Master record.
You said that your Item Master has the cubic feet per unit calculated already for each item.  You said that product ABC123 was 10 cubic feet.  So, I'm sorry, but I do not understand your question.  Create 3 fields for the three quantities per container for the 3 std container sizes.
Photo of Rick

Rick

  • 42 Points
Sorry for the confusion Mark, let me try one more time.  Yes, Item ABC123 is calculated at 10 cubic feet.  There are 3 Container Master records, 20GP, 40GP, and 40HQ which all have unique "usable cubic feet".  On the Item Master Form, once I enter the dimensions (to calculate cubic feet) I would like to be able to pull the 20GP usable cubic feet to divide by the Item cubic feet, pull the 40GP usable cubic feet to divide by the Item cubic feet, and also the 40HQ usable cubic feet to divide by the Item cubic feet yielding the 3 formula values in the Item Master - Qty20GP, Qty40GP, and Qty40HQ.   So the link from the Item Master to the Container Master has to be done on 3 different Container records.  Does this help?
You are overcomplicating this.  There are only 3 sizes of containers in the whole world.  It is very standard. Those formuals will never change, so there is no need to pull from the container master record.  

But if you went to pull from the container master records, then you need to look at each container master record and see what their record ID#s are.  Say they are  1 2 and 3.  Then in the item master table make three formula fields called line [Link to 40HQ] and the formula would be say 3 and would calculate to 3.

Then use that field in your relationship to make the relationship back to the container master table and then lookup the container volume.  Then do that again for the two other Container masters.
Photo of Rick

Rick

  • 42 Points
Well, not really over complicating, because there is a "usable percentage" in the Container Master that effectively changes the Usable Cubic Feet, and this changes from time to time in our business ... that being said, I will certainly try your suggestion.  I had created fields to do just what you were mentioning, but I used the formula of "20GP" instead of the record ID.  I was trying to relate on Type not record ID.  I will work on this tomorrow and let you know the results.  I do appreciate your help.
OK, well that makes sense if the usable space does change from time to time.
Photo of Rick

Rick

  • 42 Points
Mark, I appreciate your help.  What I didn't realize here is that I had to create 3 relationships to the same table ... never thought about it this way.  Anyway, thank you.  I will recommend your solution.