Monthly Totals in Portal

[anthonygiuliano]

Hi, so I have two tables, parent and child.  The child records are dated amounts.  I have a layout based on the parent table, and I'd like to include a portal that shows the monthly totals from the child table.  Is there a clean way to do this?

[Wim Decorte]

The traditional way to do this is to use a calculated field on the parent table that uses the Sum() function across the parent to child relationship

[comment]

Hi, so I have two tables, parent and child.  The child records are dated amounts.  I have a layout based on the parent table, and I'd like to include a portal that shows the monthly totals from the child table.  Is there a clean way to do this?

 

Can you be more specific regarding the "monthly" part? Which months would you like to show - surely not all of them? That would become impractical over time, as the number of elapsed months grows.

[anthonygiuliano]

Can you be more specific regarding the "monthly" part? Which months would you like to show - surely not all of them? That would become impractical over time, as the number of elapsed months grows.

Well sure, the last 12 months would be fine.  But ideally there would be a portal with one record for each of the 12 prior months, and each portal row would show the sum from the child records in that month.

The traditional way to do this is to use a calculated field on the parent table that uses the Sum() function across the parent to child relationship

Sure I could use the sum function across that relationship to calculate the total, but that's not really what I'm looking for.  I could also have a list layout that shows the child records and use a summary field and sub-summary section when sorted by an EOMONTH field or something, but I'd really like to display this information in a portal.

 

I suppose I could create a child relationship for each month I want to display, with one of the criteria being that the child record's date field is in a certain month relative to today's date.  Then on the layout have 12 portals with one portal row, but that doesn't seem like the cleanest approach.

[comment]

 ideally there would be a portal with one record for each of the 12 prior months, and each portal row would show the sum from the child records in that month.

 

How many child records records do you expect to have? A very simple method would use 12 one-row portals - all based on the existing parent-child relationship - with each portal filtered to show records from one of the last 12 months and containing (the same) summary field defined in the child table as Total of Amount. However, that wouldn't work well with large amount of records.

 

To improve the above, you could base the portals on a new relationship showing only child records from the last 12 months. This would decrease the burden on the filtering portals - but they would still have to go through all the child records from the last year on every screen refresh.

 

My own preference would be to define a table of Months, with 12 permanent records, each calculating the start date of one of the last 12 months. This table would also would have a global gParentID field, populated by a script triggered OnRecordLoad of the parent layout. Thus each month record can aggregate the current parent's children for a specific month - and you can show the results in a portal to Months, based on a relationship using the x relational operator.

[LaRetta]

Hi Anthony, welcome to FMForums  :-)

 

What do these records represent - what is the purpose here?  Is it financial data such as invoicing?  If so, and you say EOM - can we assume then that you post (freeze) records at month-end?  I ask because aggregating data can become very expensive.  

 

If you freeze records from further modification at a certain point, it would be helpful to write these static totals to a monthly summary table for quick display.  In this way, their values would not have to be recalculated over and over every time you display them or switch to that layout.

I also like Comment's approach. 

[anthonygiuliano]

How many child records records do you expect to have? A very simple method would use 12 one-row portals - all based on the existing parent-child relationship - with each portal filtered to show records from one of the last 12 months and containing (the same) summary field defined in the child table as Total of Amount. However, that wouldn't work well with large amount of records.

 

To improve the above, you could base the portals on a new relationship showing only child records from the last 12 months. This would decrease the burden on the filtering portals - but they would still have to go through all the child records from the last year on every screen refresh.

 

My own preference would be to define a table of Months, with 12 permanent records, each calculating the start date of one of the last 12 months. This table would also would have a global gParentID field, populated by a script triggered OnRecordLoad of the parent layout. Thus each month record can aggregate the current parent's children for a specific month - and you can show the results in a portal to Months, based on a relationship using the x relational operator.

 

Yea, probably too many records for filtered portals to do the heavy lifting.

 

I had started playing with a "months" table, but was having trouble defining the relationships that allowed me to see the appropriate child records from the parent layout.  I might have to give that another crack based on your suggestions.  The global parentID field on the months table might have been what was missing when I tried it.

 

Thanks so much for the feedback.  I'll report back here either way.

[anthonygiuliano]

Hi Anthony, welcome to FMForums  :-)

 

What do these records represent - what is the purpose here?  Is it financial data such as invoicing?  If so, and you say EOM - can we assume then that you post (freeze) records at month-end?  I ask because aggregating data can become very expensive.  

 

If you freeze records from further modification at a certain point, it would be helpful to write these static totals to a monthly summary table for quick display.  In this way, their values would not have to be recalculated over and over every time you display them or switch to that layout.

I also like Comment's approach. 

Hi, thanks for the warm welcome  Yes, the child records represent financial transactions to be paid out to a partner represented by the parent record.  The database is not in production yet but yes I do plan on freezing records somehow once they are paid out or reported to the partner.

 

Thanks for the input, as I am concerned about performance down the line.  I plan on calculating these payments with auto-enter calculations rather than unstored calculations to help with that.

[LaRetta]

I plan on calculating these payments with auto-enter calculations rather than unstored calculations to help with that.

 

Can you explain a bit more here of what you mean?  An auto-enter calculation would be no different than a stored calculation except the auto-enter calculation would not update properly unless set to 'replace existing value'.   We just want to be sure you get the best start here.