Jump to content
  • 0

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



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.

Link to comment
Share on other sites

2 answers to this question

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.






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.

Link to comment
Share on other sites

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.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...