Return-Separated Payment Schedule Attempt

[Kent Searight]

Hi all,

I've been trying to write this Custom Function for days now and I just can't seem to get it. What I would like to do is show a return-separated list of a payment schedule. In other words, if I have 4 payments to make in a 1 year period and my first payment is on 6/22/2006, I'd like my list to show:

6/22/06

10/22/06

2/22/07

6/22/07

The 3 parameters I'm using are: StartDate, Interval (# of days between payments) , NumberOfPayments.

This is what I've come up with thus far but it doesn't work. FileMaker won't accept it because it doesn't know the definition of "cfPledgeSchedule" in the Case () function.

---------

//cfPledgeSchedule ( StartDate ; Interval ; NumberOfPayments )

Case ( ValueCount ( cfPledgeSchedule ) < NumberOfPayments ;

StartDate & ¶ &

cfPledgeSchedule ( StartDate + Interval; Interval ; NumberOfPayments )

)

----------

Could someone please explain to me what's wrong with this CF and what I can do to make it work?

Thanks in advance for any help offered!

[comment]

You've explained what's wrong perfectly well - you are using an undefined variable.

If "cfPledgeSchedule" is a function, it must be called as such - with the defined parameters.

Try it this way:

PledgeSchedule ( startDate ; interval ; numberOfPayments )

Case (

numberOfPayments > 1 ; startDate & ¶ & PledgeSchedule ( startDate + interval ; interval ; numberOfPayments - 1 ) ;

startDate )

[Kent Searight]

Hi comment,

Thanks for the help. I'm looking at your example now. It might take me a while, but I'm trying to make sure I understand it 100%. I think that if I can get that one "Ah ha!" moment in studying a recursive calc, I'll be able to write them myself (that's the theory, at least)

Cheers!

[comment]

One thing to bear in mind is that a Filemaker CF can be recursive - but not iterative.

In plain terms, it means that a function can only call itself as whole - not just a part of it. Each call starts the ENTIRE function from the beginning.

What's worse, there's no index (counter), unless you create an artificial parameter (which is always confusing to the user, since it has no real meaning), or use one of the exisiting parameters as a pseudo-counter (which is not always possible).

In the above example, the numberOfPayments parameter is used as a pseudo-counter (counting down). As long as numberOfPayments > 1, we are somewhere in the middle of the process - so we need return the current result (startDate) AND continue to the next iteration. When numberOfPayments = 1, we are in the last iteration, so we only return the current result and stop.

[Søren Dyhr]

What's worse, there's no index (counter), unless you create an artificial parameter

That exactly why I do it with repeating calc'fields instead, since if it ain't a relational key is it a 1NF violation. The way I solve recuring events is like this:

Case(Extend ( NumberOfPayments ) ≥ Get ( CalculationRepetitionNumber );(Get ( CalculationRepetitionNumber )-1 ) * Extend ( Interval )+ Extend ( startDate ))

--sd

[comment]

Well, you know you are preaching to the converted here - if not to the Pope...

But it never hurts to know both techniques.

[Kent Searight]

I take it that you two feel that Søren's approach is the better of the two? Is this because each repetition holds just one piece of data versus the recursive calc approach that puts multiple values in a single field?

BTW Søren, I'm surprised to see you using a repeating field for a calculation. Having read some of your previous posts about repeating fields I've gotten the impression that you have very little use for them.

[comment]

I am sure Søren would agree that it's impossible to answer your question without knowing what you intend to use the result for.

[Kent Searight]

I'm using it to figure a payment schedule in a table called Pledges that tracks charitable pledges. Its end-uses are:

- Calculating the payment due date series

- Calculating upcoming payment reminders

- Calculating past due pledge payments

[comment]

I am not sure what all that means. If the result is a match field for a relationship, then a repeating calc with result set to Date has the advantage.

[comment]

But I am curious how the same field can serve both past due and upcoming.

[Kent Searight]

My plan is put the result in a carriage separated list in a single editable text field in the Pledges table by script upon record commital, which I'm controlling through a popup window. When a payment has been made for the lowest date, a script will strip that line out so that only remaining payment dates make up the list. The same script will also generate a new record in a table called Tasks that reminds the donor's representative to send a reminder at a predetermined time before the next payment date.

I'll use the same field as a key for showing payments overdue. To achieve this I set a global key at startup to the current date in a related table. All records that are equal to or less than the global's value AND have not already been flagged as being addressed will cause generation of a Tasks record in the same startup script. I'll also reference the field in a calc in the pledges table to display the pledges overall status.

Make sense? Do you have any suggestions on a better way of handling this?

Thanks!

-Kent

[Søren Dyhr]

put the result in a carriage separated list in a single editable text field

This disqualify actually both approaches since it is a violation of 1NF...

global's value AND have not already been flagged

Dear me!

--sd

[comment]

I think I would start by turning the individual due payments into separate records in a related table (like PledgeItems?). Actual payments made *could* be recorded as DatePaid in the same record, but I think I would prefer just adding another record to the same table - making it in effect a transactions table.

This way a script is only required to split the pledge into payments (which remain editable). Everything else is done purely by relationships. Filtering the Transaction table by Donor/Type/Date should give you any imaginable summary (and the same goes for reports, of course).

[Kent Searight]

I think I would start by turning the individual due payments into separate records in a related table (like PledgeItems?).

I like your idea. I had originally considered creating records for each pledge payment due (since it doesn't break 1NF) but I couldn't decide how to do it, i.e. which table to create the records in. The one approach that kept coming to mind was to use the table Donations, which records the transaction itself, to create new records for pending donations, but I didn't want pending donation records mixed in with completed donation records. Is this basically what you're proposing, or are you suggesting that I create a new table that just tracks pending donations.

FYI The way I have the db set up, a donation does NOT have to have a pledge record associated with it. Most donations are just one-timers with no pledge involved.

Thanks for the help!

[Kent Searight]

Dear me!

Okay, I'll bite...what's wrong with using a carriage-return separated list as a key to relate to a global key?

[comment]

I see it as a three table arrangement, but there can be variations on the theme:

Example A:

Donors - Donations (including Pledges) - Transactions

Here each donation has a minimum of two records, one in Donations and at least one in Transactions. The actual sums are only written to Transactions - the Donation record is a calculated total.

Example B:

Donors, Donations, Pledges

Here the Donations table is the transactions table, and the Pledges table is just a wrapper for several Donations. One-time donations need not have a record in Pledges.

[Kent Searight]

Example B:

Donors, Donations, Pledges

Here the Donations table is the transactions table, and the Pledges table is just a wrapper for several Donations. One-time donations need not have a record in Pledges.

That's exactly what I have now. Which table did you have in mind for holding records for pending donations? The Donations table?

[comment]

Yes, of course. The Pledges table is just a glorified calculator, to compute the individual payments and their dates (which can be done in repeating fields and imported as individual records into Donations).

[Kent Searight]

Okay, that was a dumb question.

In that case would you make the pending donation a record complete with amount, etc., and then just assign each record a value in a category field to distinguish completed transactions from pending transactions?

Edited June 23, 2006 by Guest
clarity

[Søren Dyhr]

what's wrong with using a carriage-return separated list as a key to relate to a global key?

If it was individual records, wouldn't there be a search that could be followed by a GTRR(FS)?? that could establish what donor and not which invoices was overdue?? We're speaking FM8 aren't we??

http://www.filemakerpros.com/Overdue2.zip

--sd

[comment]

What makes a donation pending? I said earlier:

Actual payments made *could* be recorded as DatePaid in the same record, but I think I would prefer just adding another record to the same table - making it in effect a transactions table.

[Kent Searight]

Ah ha! I see exactly what you're saying now.

Thanks for your help!

[Genx]

I might just be daft, but could someone please explain to me the concept of 1NF?

[Kent Searight]

First Normal Form means that you don't store more than one piece of data in any one field.

[Genx]

Ah gotcha,

I knew i'd heard it before.

Cheers Kent.