call_frequency should be of type number. last_called and next_call_date should be of type date.
This calculation could be defined as a field of type calculation instead of using auto-enter. Since you are using auto-enter, did you clear the "do not replace existing value...." check box?
Hi Phil. Well, I thought I had, - but hadn't. So, it does (sort of) work now. Thank you.
The only thing is that FMP thinks that the number in "call_frequency" means 'weeks'. So it adds 7 days on for each increment. However, in this database, that works just as well for me. So I'm happy. Thanks for such a fast response.
If it's incrementing in weeks, something is wrong and you should check data and calculations. When you add 5 to a date field, you get a date 5 days later--not 5 weeks.
Well, I've just checked and the entire process really is as I orignally outlined.
The freq field is set as "number" and both the other fields are 'date'. I am simply setting auto-enter as "date1 + freq"
Can't think of anything else to check! But when I test it, it's consistently adding a full week on each one of my records for each increment.
I've created more filemaker date calculations than I can possibly count and date fields always evaluate in terms of days. There must be something else going on here...
Hi again Phil.
Well, I'm sure you're right. As I said, I'm rather new to FMP. But I'm damned if I can find out what the problem is! I've looked at all the sttings for each of the 3 fields and there's no clue at all!
Anyway, I'm happy with weeks as the default. Although it would be nice if someone else wants to throw some light as to what might be going on :)
(I'm using FMP 11, btw).
My concern here is that since we don't know why it is computing this value, we have no way to be sure that it will continue to do so correctly. You can upload a copy of your file to a share site and post a download link to it here if you would like someone else to take a look at it.
The file works perfectly, as far as I can see.
If I type 1/3/2011 (dd/mm/yyyy) into Last_Call, and 1 into Call_Frequency, it shows 2/3/2011 in Next_Reg_Call. If I change either value it correctly updates the Next_Regular_Call date.
What threw me was that the Call Frequency field is actually named 'Call-Frequency_Weeks' although I can see nowhere where a multiplier of 7 is used.
I don't really see why the field Last_Call is not just a straightforward calculation field as Phil said (though it works the way it is). Same with Next_Reg_Call, unless you want to be able to override it with a date, but then I don't understand the need for Next_Call field.
Your original posting is a bit confusing now, as there isn't any field called 'next_call_date'. There is 'next_call' and 'next_reg_call'. 'Next_Call' is simply a manually-entered date. 'next_reg_call' is updated correctly by the calculation Last_Call + Call_Frequency_Weeks (except for the field contents being days, but the field name being 'weeks'.)
Well that really is strange. I'm still getting 7 days for every increment of 1. (That's why I changed the name of the field by adding the word 'weeks'). I can live with it that way.
The 'nextcall' field is only an overriding field, in case the next call needs to be before the next reg call, It's supposed to manually entered. The actual field for the calculation is next_reg_call.
Still a mystery though. And I don't suppose the fact that I'm using Mac OS X and it's set to UK date format would make any difference.
"The 'nextcall' field is only an overriding field, in case the next call needs to be before the next reg call, It's supposed to manually entered. The actual field for the calculation is next_reg_call" - that was what I thought, but there is nowhere that the manually entered date is used in any calculation that I can see. You would need an If statement in one field or the other.
"I'm still getting 7 days for every increment of 1" - have you tried downloading the clone you posted up, creating a record, and then adding just the first date and the frequency data?
I would also have changed the Last_Call field to be a calculation, btw, not auto-entered.
I'm using Windows XP, UK format.
Tried this on a Mac running Snow Leopard. Works perfectly.
OK I've now changed LastCall and also NextRegCall to be calculation fields. (using the same formula as was in when they were auto-entry fields). I'm sure that's better protocol, - although, in my case, it seems to do the same job.
And, miracles do happen. I downloaded the cloned file as you suggested and it works perfectly as days (not weeks)! So now I'm really flummoxed. If I propogate the data from my existing file, that works too. I have no idea what the problem was, - but maybe it has something to with the fact that I originally imported the database from a DBaseV saved file (which had in itself been exported as such from Microsoft Works). Perhaps it was retaining an old formula I couldn't find.
Anyway, thanks again for all your help with that issue.