• 0

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

Question

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.

Recommended Posts

• 0

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.

Share on other sites

• 0

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.