Jump to content

Expanding a date range


This topic is 5707 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5707 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.