Jon1

Calculating related records

Discussion created by Jon1 on Jul 13, 2018
Latest reply on Jul 20, 2018 by philmodjunk

Hello,

 

I have an application where you can generate a purchase order to buy rolls of paper. Each purchase order can have more than one type/size of paper. The line items are a portal to the line items table. An example of the form is below.

We order the paper in pounds and the supplier sends as many rolls of that type/size paper required to get as close as possible to the desired number of pounds ordered. It will never match exactly.

  The form I made to associate the PO with the rolls received is below. The number of rolls, weight and price are accurate. Since there will always be a difference between the weight ordered versus the weight received – I want to show the variance. Where there is only one related record (PO Line Item), the variance calculates correctly. When there is more than one related record (PO Line Item), the variance on the first line item calculates correctly as shown below.

 

The second related record (PO Line Item) however does not calculate correctly. See below.

 

 

The -8,435 variance is because the 17,565 weight received is subtracting from the order quantity of the first line item – in this case it is being subtracted from the 26,000 of the first line item rather than the 18,000 of the second line item.

 

My calculation is as follows.

GR_OW_InventoryItems::GR_OW_s_OriginalLBS - GR_OW_POLineItems::GR_OW_POQuantityToOrder

The GR_OW_InventoryItems::GR_OW_s_OriginalLBS is a summary total field that adds up the weight of each of the rolls received.

 

I am filtering the portal using GR_OW_POLineItems::GR_OW_POStockCode = GR_OW_InventoryItems::GR_OW_StockCodeNative.

 

I am using the same portal filter for the number of rolls received, the weight of the rolls and the price shown on the right side of the form – and all is well. This allows me to show only the rolls that have a matching code to the PO Line Item to show on that form. I followed @nicolai’s answer on Count of Records in a Filtered Portal.

How can I make it so that the 26,486 is subtracted from the 26,000 ordered and displayed properly (as it is now) and then have the second related record of 17,565 pounds received subtracted from the 18,000-quantity ordered?

 

The relationship graph is below.

 

 

I appreciate all help.

Thank you

Outcomes