have a calculation field called "dur", result is number, to compute:
JOURNAL::date - durprev
change result type to date instead of number.
Thank you PhilModJunk, but I'm afraid that didn't work.
If I change the "dur" field to result as date, I just get the YYY-MM-DD (the format I have chosen with other dates), but it does not answer the question how many days are between two dates.
I tested this using a new database file where the date1 and date2 date fields are in the same table, the 'dur' number field for the calculation [date1 - date2] works fine and is consistent with the tip from FileMaker Knowledge Base. But when I do the same thing in my database where the date1 field ("durprev") is a related field, the 'dur' number does not work as intended. Why would a the related field make the difference?
Is journal::date a plain date or a calculation that returns a date? http://www.filemaker.com/help/html/func_ref3.33.10.html Says, The result of GetNthRecord() will not be updated when the record referred to by GetNthRecord() is a record other than the one in which the calculation is currently being evaluated. So if journal::date is a calculation, that calculation won't happen when you might expect it to. At least that's what I think it means. Maybe you can create a relationship that links the current record to the previous one, and then you can reference the previous date via that method.
JOURNAL::date is a plain date field that I have on the jOURNAL table, and also as a related table on the LEDGER table.
My mistake, I misread your original post.
Make sure that DurPrev is an Unstored field of type calculation or it won't correctly update.
ok, the calculation field DurPrev, result as Date, is now Unstored (it wasn't unstored before).
The Dur calculation field, result as Number, was already Unstored.
No affect on end result, DurPrev is still showing results as, for example, "734379" (this number changes depending on the date in in the date field, JOURNAL::date. I would like the result to be the number of days between dates.
OK, I had to play with the calcualtions a bit to see what should have been obvious.
Date - DurPRev
Returns that number for the first record, correct?
That's because you are subtracting zero from the date and this computes the number of days from that date to the "zero date" of 12/31/0000.
change this expresion to:
If ( DurPrev ; Date - DurPrev )
The [Date - DurPRev] formula in the LEDGER::Dur calculation field, result as number, returns the following, depending on:
- the date in the JOURNAL::date field, result as date, and
- the date in LEDGER::DurPrev field, result as date.
For example, if LEDGER shows the following last four child records of the 25 records in the found set:
JOURNAL::date Duration "Dur"
column value column value
It should show as:
JOURNAL::date Duration (in days)
column value column value
If I try the IF formula you mentioned, the Dur result is blank. Currently as shown above this field is showing the number ofdays since 01/01/0001.
Take a step back, create a field in journal that holds just the date of the previous record. If you can get that working, then try to make a field that uses it for your calculation. I think your trying to use a field that doesn't get defined until after you've referenced it. I've seen that happen in my database. Filemaker isn't foolproof in finding dependancies in calculated fields.
I understand now, based on all your input, that my formulas, in this case, should not be using related fields. Rather, a 'local' field should be used that relates to the related field. Then that local field, in turn, is used in the formulas. Seems odd that FM can't figure out using a related field in the formula, but it works anyway.
I added a new caculation field in the LEDGER table:
LEDGER::date1, result as date, and the formula as simply:
I then changed the formula in the LEDGER::DurPrev calculation field, result as date, to:
If( (Get(RecordNumber)-1) ≠ 0; GetNthRecord(LEDGER::date1;Get(RecordNumber)-1); 0)
If( (Get(RecordNumber)-1) ≠ 0; GetNthRecord(JOURNAL::date;Get(RecordNumber)-1); 0)
Finally, I changed formula in the LEDGER:Dur calculation field, result as number, to:
LEDGER::date1 - LEDGER::DurPrev
JOURNAL::date - LEDGER::DurPrev
The result in LEDGER::Dur now works as intended -- showing the number of days between the date of the current record, and the date on the previous record.
Thank you all very much indeed for the guidance and learning experience!
Phi, bucause I like to learn something new as much as possible, I opened up the file you provided. From reviewing your file, it shares a common theme that Robdownunder had mentioned, i.e., that the formulas should be using local fields, not related fields. Again, I don't know why FM can't figure out the formula result properly just because the fields in the formula are related instead of local. Anyway, good to know there's a working alternative.
Phil, I also liked your formula "If ( DurPrev ; date - DurPrev )" and I'm using that too.
Thank you Phil, for all your suggestions and putting that file together for me. Very much appreciated.
Calculations can use related fields. I couldn't do what I do with FileMaker without that being possible. The results returned by a calculation that refers to fields in related records can be very different depending on the functions used, the values in fields in the current record and the relationships I've built into my system.
Get Nth Record works differently depending on that very detail. If you use it to refer to fields in the same table occurrence, you are referring to the nth record in the current found set for that occurrence. If your refer to a field in a differnt table occurrence, you are referencing the nth record in that set of related records. (That's the equivalent to accessing data in the nth row of a portal based on the same relationship.)
It's all a matter of understanding how FileMaker works.
Ya, I figured that based on the work I have done so far in the database. GetNthRecord was the tricky part in this case, which I originally thought worked liked other functions with realted fields. Makes sense what you say. Thanks again Phil'