Summarizing related records by field value

[Tpaairman]

Shifting gears on summarizing related records a bit.  My file has the summary page that summarizes data from a few other tables, including the transactions table. 

I have a portal on the summary page, that shows all the transactions for that month.  Each transaction has a field showing what method of payment was used, and I have a value list with the different methods. In his case, I created a table that has the payment methods, and my value list is from that table. 

What I need to do is, on my summary page, have a portal that will have a row for each value list item, then a field that shows the sum of each transaction paid for with that payment method for that month.  But, if a new method of payment is added to the value list, then it would show up in the portal after it's added.  Obviously I can have a relation back to the payment methods table, and the portal row would show as they should, but how do I add the transaction summary?

[comment]

Can it be assumed that only one summary page will be shown at a time? IOW, the portal will be on a Form view of the summarizing table?

---
P.S. Please don't use the term "page" when you mean "record".

 

Edited May 10 by comment

[Tpaairman]

That is correct.  And I probably should clarify something.  I know that I'm calling it a summary page, but it does not have any summary reports or anything like that.  What this system is, is a bills and budget system.  So, you have a check register, bills that are due and get paid, and debts, among other things.  Each of those is on it's own table.  The summary page is a form view on it's own table, but it's showing the info from the other tables in one place.  So, a portal of all bills due that month, a portal of all debts, a summary of income, etc.  IOW, an overview for eveything in a given month.  Each record is a different month.

This issue is with the bills.  On the bills table is a field that shows what payment method the bill is scheduled to be paid with, and it uses that value list.  So back to the summary page, I have the portal showing all of the bills, but I need to also have some sort of list, like a portal, that shows each payment method, and then for each payment method, a field showing the total of the bills that month that are getting paid with that payment method.

[comment]

One way you could approach this is to define a relationship to the payment methods table using the x relational operator. Then add a global month field to this table and define a relationship to another TO of transactions matching on both month and method. This way each method can summarize its own transactions in the currently viewed month. Add a OnRecordLoad script trigger to your layout to populate the global field with the month of the currently viewed record.

Another option is to use a filtered portal - see: 
https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values

But this could be slower.

And of course the old "Ugo method" mentioned there is also still viable. See if you can find a good demo of that.

 

 

Edited May 10 by comment

[Søren Dyhr]

7 hours ago, comment said:

Another option is to use a filtered portal - see: 
https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values

But this could be slower.

In the second template in the above thread - why are you using the repetitions in the setting of the variable?:

 

 

--sd

7 hours ago, comment said:

Add a OnRecordLoad script trigger to your layout to populate the global field with the month of the currently viewed record.

 

Exelent suggestion, but how would you track changes - which event trigger deals with changes to category if you were to call a script filling the summarization in next to each first occurrence, provided I would not let self-join calc's do this task?

--sd 

Edited May 10 by Søren Dyhr

[comment]

12 minutes ago, Søren Dyhr said:

why are you using the repetitions in the setting of the variable?:

I think I accidentally left an unused script there. In some previous version there was a variable for each record so that you could browse the parent table and have the portal follow.

 

[Tpaairman]

@comment - On that link you posted, I tried one of the files that was posted there, and have recreated it in a test file.  It is summarizing things, but it still shows one portal row for each record.  I've gone back and forth, and can't find what I missed, and knowing my luck it's some stupid simple detail.  Can you see what I missed?

 

 

PMT Test.fmp12

[comment]

Well, this is my filtering expression:

Let ( [
indexValues = List ( Child::ChildID ) ;
searchValues = List ( Child::Category ) ;
index = ValueCount ( Left ( indexValues ; Position ( ¶ &  indexValues  & ¶ ; ¶ & Child::ChildID & ¶ ; 1 ; 1 ) ) ) ; 
prevValues = LeftValues ( searchValues ; index -1 )
] ;
IsEmpty ( FilterValues ( Child::Category ; prevValues ) )
)

and this is yours:

Transactions::Month = Transactions 2::Month

??

 

 

[Tpaairman]

I was going by the example posted by Mr. Vodka.

Portal_Unique_11.fmp12

I just plugged in the formula you have, and as near as I can tell, I was able to swap the names of the fields correctly, but it's still showing one portal row for each record. 

[comment]

Sorry, I can't help you with that.

 

[Søren Dyhr]

5 hours ago, Tpaairman said:

I just plugged in the formula you have, and as near as I can tell, I was able to swap the names of the fields correctly, but it's still showing one portal row for each record. 

I have custom-functionalized the filtering-calc, since it's already required twice:

 

Portal_Unique_11a.fmp12

--sd

Edited May 11 by Søren Dyhr

[Tpaairman]

I posted this at the Claris forums and got an answer, so I figured I'd post it here.

First, I had to add a field to the transactions table, I called it RECNUM.  It's a calculation that is Get (RecordID).

Then, in the portal, for the sorting, I used that field instead of the Month.

PMT Test.fmp12

[Søren Dyhr]

1 minute ago, Tpaairman said:

I used that field instead of the Month.

The date would suffice here, the calculation where you gather a summary category sorts alphabetically ...

--sd

[Søren Dyhr]

Me and other developers are not that keen on the utilization of the portalfilter, so I set out to make the same thing as done elsewhere in this thread - but now keeping the portal filtering to an absolute minimum, so It could be debated if it's required in the first place?

I'm not stating tha it's better than the previous approaches, but would like some feedback to where it both flaws as well as improve to behave?

--sd

 

Portal_Unique_11b.fmp12

[comment]

5 hours ago, Søren Dyhr said:

not that keen on the utilization of the portalfilter

That's not a valid argument that can be confirmed or refuted. 

The advantage of using a portal filter is that it eliminates the need for auxiliary TOs and relationships - thus it reduces the clutter in the relationships graph. The disadvantage is that portal filtering is evaluated "live" at every screen redraw - thus it can become slow when the number of records is large. So the decision whether to use it or not is always a balance between these two considerations and depends on the particular circumstances of each case.  

In my initial answer in this thread I mentioned two alternatives to portal filtering - and neither one of them requires adding another table.

 

[Søren Dyhr]

On 5/16/2024 at 7:34 PM, comment said:

The disadvantage is that portal filtering is evaluated "live" at every screen redraw

I've now been putting some more work into getting rid of both portal rows sorting as well as filtering ... by utilising the behaviour of ValuelistItems(""; ....   and as usual - for what it's worth? 

Ugo's method gets the TO-graph large pretty fast, and somewhat difficult to debug behaviours in later, because the naming conventions development houses tries to enforce are stretched to it's limits by using it.

--sd

Portal_Unique_11c.fmp12