Doing math on the sum of values from 3 sections in a sorted layout

[ken_s2007]

I'm not well versed in summary layout parts and reports, but am trying to learn.

I have a (personal) database to track investments (stocks, mutual funds, bonds, etc.). I have fields such as: number of shares, price, and value (technically: shares * price). Each record has three checkboxes: buy, sell, and current holding (previous (historical) records do not have 'current' checked).

After establishing a found set (of a particular investment), I have a script that constrains the found set to buys, sells, and current record(s) (plural, because the same investment could be held in multiple accounts). (All historical records (except buys and sells) are thus not in the found set.)

I have a script, triggered on layout enter, that sorts by buy, sell, and current. (Buys and sells are executed trades.)

I have a sub summary part, and in it, a summary field based on value.

The layout displays the records sorted as I want, and the summary field displays the correct total for each of the three groupings of records.

What I want is to get the value of each of those summary fields, so that I can do a calculation: (Sells + current holding) - buys = profit / loss.

Of course using the single summary field doesn't work.

I could run a script to find the buy records, set a variable based on the summary field value, find the sell records, set a variable on the summary field, and so on. Then have fields with those variables (plus a calculation field) in the trailing grand summary.

I could also create separate fields for buy (shares, price, and value), sell, and current, but I'd prefer not to.

I thought I'd ask, because I might be missing a simpler method. thanks

Edited July 31, 2019 by ken_s2007

[Steve Martino]

Look at GetSummary function

https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fgetsummary.html

[ken_s2007]

That looks very promising. I will check it out. Thanks!

[comment]

10 hours ago, ken_s2007 said:

I might be missing a simpler method.

If I understand correctly your description (which is not at all certain) then no, I don't think you're missing anything. You can use GetSummary() to get the sub-summary value within a sorted group.There is no way (that I know of) for a calculation to reach out to another sorted group and pluck its sub-summary value. 

Ostensibly, you could define a calculation field using (only) the GetSummary() function, and then use GetNthRecord() to get the sub-summary value of another group. But you would have to know how many records away the other group is - which is not likely when there are more than 2 groups.

If all the records in the three groups have a common parent record in another table, you could do the calculation there, using 3 relationships. But then you would lose the ability to report on a found set of only some of the related records.

 

10 hours ago, ken_s2007 said:

I could run a script to find the buy records, set a variable based on the summary field value, find the sell records, set a variable on the summary field, and so on.

Actually, here there is a simpler way: use the method known as Fast Summaries to collect the sub-summary values of the groups in your existing found set.

 

10 hours ago, ken_s2007 said:

I could also create separate fields for buy (shares, price, and value), sell, and current

Or a single calculation field that would split the values into 3 separate repetitions that can be summarized individually.

 

Edited August 1, 2019 by comment

[comment]

Do note however: in a found set sorted by Category, where category can be be either A or B or C, a calculation field =

sTotalValue - 2 * GetSummary ( sTotalValue ; Category )

when placed in a trailing grand summary part, will return the equivalent of:

sub-summary value of A + sub-summary value of B - sub-summary value of C

This is because the calculation will be evaluated from the context of the last record in the found set, which belongs to category C.