FileMaker Aging Receivables using ExecuteSQL

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/