Avatar of Blair Phifer
Blair Phifer
 asked on

How to identify the last record in a sorted list?

I have a table of records that I am sorting to do totals and place those totals in a second table. (The records will not change, so keeping totals is OK.)


In order to get the totals the way I want to, I sort the table and then run thru it in a script.  At the end of one of the sorting types, I do some cumulative totals that I have been keeping thru the type. At the end of the list of records, I need to do the totals, but the difference between the last and next to last will not trigger the IF statement that does the totals. When I run the script, everything works, except for the totals of the last group.


Is there a way to identify the last record of a sorted list? I know how to get the last record number, but I want to cover myself in case the last record of the table is not the last record in the sorted version.


ETA: I have a 'temporary' solution where I do the totals outside of the loop at the end of the script that does all of the other work. This catches the data from the last run thru the loop.


If [ not window.exists ( "Ceded Data for Totals" ) ] 

New Window [ Style: Card ; Name: "Ceded Data for Totals" ; Using layout: “T27_CededData for Totals” (T27_CededData) ; Top: 0 ; Left: 0 ] 

End If

Go to Record/Request/Page [ First ]

Set Variable [ $LineItemID ; Value: T27_CededData::Ceded_LineItemID ]
Set Variable [ $CededLineItemGroupID ; Value: $LineItemID ] 
Set Variable [ $PreviousReinsuranceType ; Value: 1 ] 
Set Variable [ $ReinsuranceID ; Value: T27_CededData::ReinsuranceTypeID ] 
Set Field [ T27_CededData::__MagicKey ; "" ] 

Loop

Set Variable [ $LineItemID ; Value: T27_CededData::Ceded_LineItemID ] 
Set Variable [ $PeriodID ; Value: T27_CededData::PeriodID ] 

If [ $LineItemID ≠ $CededLineItemGroupID ] 

Set Field [ T27_CededData::__MagicKey ; "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PolicyYearID ; $PreviousPolicyYearID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PeriodID ; $PreviousPeriodID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::ReinsuranceID ; $PreviousReinsuranceID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::LineItemID ; $PreviousLineItemID ] 

If [ $CededLineGroupTotal = "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "No" ] 
Else
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "Yes" ] 
End If

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Total ; $CededLineItemGroupTotal ] 

Commit Records/Requests [ With dialog: On ] 

If [ $PeriodID ≠ $PreviousPeriodID ] 

Set Field [ T27_CededData::__MagicKey ; "" ] 

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PolicyYearID ; $PreviousPolicyYearID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PeriodID ; $PreviousPeriodID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::ReinsuranceID ; 4 ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::LineItemID ; 1 ] 

If [ $CededLineGroupTotal = "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "No" ] 
Else
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "Yes" ] 
End If

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Total ; $TotalCededPremium ] 
Commit Records/Requests [ With dialog: On ] 
Set Variable [ $TotalCededPremium ; Value: "" ] 
Set Field [ T27_CededData::__MagicKey ; "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PolicyYearID ; $PreviousPolicyYearID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PeriodID ; $PreviousPeriodID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::ReinsuranceID ; 4 ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::LineItemID ; 2 ] 

If [ $CededLineGroupTotal = "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "No" ] 
Else
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "Yes" ] 
End If

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Total ; $TotalCommissionExpense ] 

Commit Records/Requests [ With dialog: On ] 

Set Variable [ $TotalCommissionExpense ; Value: "" ] 

End If

Set Variable [ $CededLineItemGroupID ; Value: $LineItemID ] 
Set Variable [ $CededLineItemGroupTotal ; Value: "" ] 

End If

Set Variable [ $ReinsuranceID ; Value: T27_CededData::ReinsuranceTypeID ] 
Set Variable [ $PolicyYearID ; Value: T27_CededData::PolicyYearID ] 
Set Variable [ $PeriodID ; Value: T27_CededData::PeriodID ] 
Set Variable [ $Amount ; Value: T27_CededData::Amount ] 
Set Variable [ $CededLineItemGroupTotal ; Value: $CededLineItemGroupTotal + $Amount ] 

If [ $LineItemID = 1 ] 

Set Variable [ $TotalCededPremium ; Value: $TotalCededPremium + $Amount ] 

End If

If [ $LineItemID = 2 ] 

Set Variable [ $TotalCommissionExpense ; Value: $TotalCommissionExpense +  $Amount ] 

End If

Set Variable [ $PreviousPolicyYearID ; Value: $PolicyYearID ] 
Set Variable [ $PreviousPeriodID ; Value: $PeriodID ] 
Set Variable [ $PreviousReinsuranceID ; Value: $ReinsuranceID ] 
Set Variable [ $PreviousLineItemID ; Value: $LineItemID ] 
Go to Record/Request/Page [ Next ; Exit after last: On ]

End Loop

Set Field [ T27_CededData::__MagicKey ; "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PolicyYearID ; $PreviousPolicyYearID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PeriodID ; $PreviousPeriodID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::ReinsuranceID ; 4 ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::LineItemID ; 1 ] 

If [ $CededLineGroupTotal = "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "No" ] 
Else
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "Yes" ] 
End If

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Total ; $TotalCededPremium ] 

Commit Records/Requests [ With dialog: On ] 

Set Variable [ $TotalCededPremium ; Value: "" ] 
Set Field [ T27_CededData::__MagicKey ; "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PolicyYearID ; $PreviousPolicyYearID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::PeriodID ; $PreviousPeriodID ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::ReinsuranceID ; 4 ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::LineItemID ; 2 ] 

If [ $CededLineGroupTotal = "" ] 
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "No" ] 
Else
Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Actual ; "Yes" ] 
End If

Set Field [ T27f_cededdata_CEDEDTOTALS||id|CREATE::Total ; $TotalCommissionExpense ] 

Commit Records/Requests [ With dialog: On ] 

Close Window [ Name: "Ceded Data for Totals" ; Current file ] 

Open in new window

FileMaker Pro* Script Development

Avatar of undefined
Last Comment
Will Loving

8/22/2022 - Mon
Will Loving

You can include an IF statement along the lines of:

If [ Get(RecordNumber) = Get(FoundCount)
<Get the totals...>
End If

But I also think that there are probably easier ways to do what you're doing using either Sub-Summary Totals or using the GetSummary() function.
Blair Phifer

ASKER
Will,

Thanks for the quick response. My problem is that I will be doing more work in the DB with the totals than with the individual records that make them up. A new set of data will be entered once annually (maybe quarterly in later versions).

Each period contains 52 records that are then grouped into 18 totals (It's a weird system ;) ). Once we have the totals, we will be working with the totals as the records for each period, rather than the individual records. This caused me to go away from subsummaries since I will not be working solely with list views and portals. I will be using them in calculations with other related tables and putting them in things like pivot reports.

Does that make any sense?
ASKER CERTIFIED SOLUTION
Will Loving

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23