Parent and Child Tables Joined in View in Tabular Report - Need a Calculated Sum

[JohnNuttDesk]

Hi Hi,

I'm creating a quoting system for my business.  I have a parent table with 100s of parent items and a child table with 100s of child items.  Each parent item can have 1 to 30 child records so this allows my users to create a nearly unique item from millions of potential permutations.  The problem is each parent has a price and the children all have their prices.   How do I use the Aggregation in the datapage when the prices are in 2 different columns?   

One View combines the Parent table with the Child table and I'm using a Tabular Report Datapage to display all the line items for one Quote ID.  The view has a field for Parent Prices and a field for Child Prices but the view displays them each in their own field.  A quoting system should display the TOTAL price at the bottom but I can't if the prices are in 2 different columns on the Datapage.  Do I have to create Triggers to write the two tables to ONE common table where the prices are in the same column?

Thank you.

[CoopperBackpack]

Hello @JohnNuttDesk,

One of the approaches that can be used:

1) Since you use the Search option, you can make the 'Quote_ID' field required and pass the entered Quote ID  value as a parameter:

 

2) In Totals & Aggregations you can apply the custom formula that calculates the sum of prices of 2 fields for a specific Quote ID (that was passed as a parameter).

For example:

SELECT SUM(Item_parent_price) + SUM(Item_child_price) FROM quotes WHERE Quote_ID = '[@Quote_ID]'

Replace the names of the objects with your objects(fields, View, parameter) names.

 

Output:

 

Please note that when you reference a View in the SELECT statement, you need to add the _v_ prefix before the View name. 

For example, if the View name is Quotes_View, then the query syntax is:

SELECT SUM(Item_parent_price) + SUM(Item_child_price) FROM _v_Quotes_View WHERE Quote_ID = '[@Quote_ID]'

Please check if this solution works for you.

[JohnNuttDesk]

Thank you for the fast and great answer!  After reviewing your answer, I was thinking I can also just do a virtual field that is the sum of those two prices.  I could also hide those 2 fields.  Then I'll see if the normal aggregation allows me to reference the virtual field.