FileMaker Aging Receivables using ExecuteSQL

By

Share:

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/

Related Posts

Importance of Citizen Developers

Buy or Build? Top Tips on When to Invest in Custom Software

Optimizing FileMaker Custom Apps When Transitioning from LAN to Cloud

Filemaker 2023 Newest Updates

FileMaker, AWS CLI, BaseElements and Mac OS

FileMaker Comparison Articles

Book a Webinar

Join us at one of our 15-30 minute, free no-obligation information webinars. We’d love to see you there!