Expanding a date range

[alextveit]

I am building a legal database, where we have loan summaries. All the calculations for giving us the totals etc work just fine. The only thing is that the lawyers want details, as well as the totals. So, the total would be outstanding amount for range 06/01/06-12/01/06 is $600.00

...should read:

06/01/06 - $100.00

07/01/06 - $100.00

etc..

Is there an easy way to do this in filemaker, or do I have to try and incorporate excel in some way?

Please let me know if I need to elaborate any more than the above. (working on this for a few weeks leads me to believe that everybody are on the same "page" as me)

Thanks!

[mr_vodka]

It sound like you are trying to run a straight summary report for a searched set of record. All you need is to find the date range of records taht you are looking for and then a summary field will give you the total of the found records.

There should be numerous examples on these forums on achieving this via script or manually, if you need to see one.

[alextveit]

I am trying to do this to an individual record, not a whole set, so unfortunately cannot use summary (I think)....

Each record has two dates (among others), and a monthly pmt amount.

Default Date

Loan End Date

Monthly Pmt

We have a calculation field that looks pretty much like this:

(Loan End Date-Default Date)*Monthly Pmt

This gives us the total amount outstanding, after the loan went into default. However, the lawyers needs a printout which will basically say:

Date - Monthly Pmt

Date - Monthly Pmt

etc

etc

..for all of the months between the loan end date, and the default date.

[comment]

You need a recursive calculation to generate this information. Since you don't have Advanced, you cannot use a custom function for this, so that leaves either a script or repeating calculation fields.

If this is strictly for display-only, I would go with the repeating fields, since it's very simple to set up (see attached). However, using your example data, I am getting a result of $700 instead of your $600 - so perhaps I am missing something.

PaymentDetailed.fp7.zip

[alextveit]

Perfect! I need to add a whole bunch of additional columns and dates, but this is exactly what I needed:) Now I can just expand this base...

And I actually have an advanced version as well, just not at home...only at work:(

Is there any other fancy version if I use advanced? Not that I really need it...would just be fun to learn:)

Thanks again!!

[comment]

Advanced lets you write custom functions, which can be recursive. It would be a bit more complex than the repeating fields*, but the entire result could be returned in a single calculation field - sort of like a 'mini-table'.

---

(*) In this case - not always.

Edited February 19, 2007 by Guest

[alextveit]

I have another little update that I was wondering if you guys could help me out with. The calculations worked great for the first part of the layout, but I am having some problems implementing the second part (late fees). I have attached a slightly altered version of the file I got.

Basically what I need, is to have the layout show the Late Fee next too the same date of the loan date on the left. (When I use a new date calculation (Default to End), it of course starts with the default date on top)

Sorry if it sounds confusing, but hopefully the attached file helps.

PaymentDetailed.zip

[comment]

I am afraid I don't quite get what you're after, but I attempted a shot in the dark. If you need something else, please attach a picture of what it should look like.

PaymentDetailed.fp7.zip

[alextveit]

You would definitely be the guy I would go shooting with in the dark then......perfect once again:)

Sorry about the confusion....trying to get something down while working on something else on the phone doesnt always work well:)