Aging receivables is a special cross tab report. See below for sample photo.
Rows are companies, and the columns are the sum of invoices and payments in specific date ranges and the total.
For example there a 5 columns:
- total balance
- balance from today to today plus 30 days
- balance from today plus 31 days to today plus 60 days
- balance from today plus 61 days to today plus 90 days
- from beginning to today
For this example, there are two tables – Company and Invoices.
The Invoices table has a “Record Type” field to indicate whether the record is an invoice or payment record.
The Invoices table has a field for payments: Amount of Payment.
The Invoices table has a field for invoices: Invoice Grand Total.
Table = Company
Custom Function
BalancePeriod ( CompanyID ; DateYoungest ; DateOldest )
Let (
[
Company = CompanyID;
Youngest = DateYoungest;
Oldest = DateOldest;
_Invoice = _FIELDREF_getField ( Invoices::Invoice Grand Total ; True );
_Payment = _FIELDREF_getField ( Invoices::Amount of Payment ; True );
_Table = _FIELDREF_getTO ( Invoices::Invoice Grand Total ; True );
_MatchField1 = _FIELDREF_getField ( Invoices::ID_Company ; True );
_MatchField2 = _FIELDREF_getField ( Invoices::Date ; True );
_sql =
“select ( sum ( ” & _Invoice & ” ) ” &
“- sum ( ” & _Payment & ” ) )” &
” from ” & _Table &
” where ” & _MatchField1 & ” = ? ” &
” and ” & _MatchField2 & ” >= ? ” &
” and ” & _MatchField2 & ” <= ? ”
];
ExecuteSQL ( _sql ; “” ; “” ; CompanyID ; Youngest; Oldest )
)
Fields
ID_Company – unique company ID
BalancePeriod_0_30, Calculation, BalancePeriod ( Invoices::ID_Company ; Get ( CurrentDate ) -30 ; Get( CurrentDate ) )
BalancePeriod_31_60, Calculation, BalancePeriod ( Invoices::ID_Company ; Get ( CurrentDate ) – 60 ; Get ( CurrentDate ) – 31 )
BalancePeriod_61_90, Calculation, BalancePeriod ( Invoices::ID_Company ; Get ( CurrentDate ) – 90 ; Get ( CurrentDate ) – 61 )
BalancePeriod_91, Calculation, BalancePeriod ( Invoices::ID_Company ; Date ( 1 ; 1 ; 2000 ) ; Get ( CurrentDate ) – 91 )
BalancePeriodTotal, Calculation, BalancePeriod ( Invoices::ID_Company ; Date ( 1 ; 1 ; 1990 ) ; Get( CurrentDate ) )
Tables = Invoices
Fields
Amount of Payment – if record type is payment – payment entered here
ID_Company – match field to company table
Date – payment or invoice date
Invoice Grand Total – total including taxes etc etc
Record Type, Value List = Invoices, Payment
Notes
https://fmforums.com/topic/83576-executesql-and-the-separation-model/
https://community.claris.com/en/s/question/0D50H00006hCtR9/how-to-pass-field-names-to-executesql-using-variables
https://www.soliantconsulting.com/blog/executesql-named-buckets/
