Getting a calculation to re-evaluate

[CKonash]

Hello, 

I have a fire dept database that has a field for "Date Of Hire", a field for "DateInactive" and then a calculation field to give the Years, Months, and Days of active service.  The calculation seems to only run when one of those two fields are modified which doesn't update the other members years of service.   

Is there another way to have the Years Of Service calculated that wouldn't allow that field to become out of sync or what looks to be stuck because the calculation wont reevaluate until one of it's formula fields is modified.  

Below is the calculation for Years of Service that I have been using. 

 

Thanks in advance for your help.  

Chris Konash

 

 

 

GetAsText(
    Year( If( DateInactive; DateInactive; Get( CurrentDate ) ) )   -
    Year( Date of Hire ) -
    If(
        If( DateInactive; DateInactive; Get( CurrentDate ) ) <
         Date(
            Month( Date of Hire );
            Day( Date of Hire );
            Year( If( DateInactive; DateInactive; Get( CurrentDate ) ) )
        );
        1;
        0
    )
) & " Years, " &
GetAsText(
    Mod(
        Month( If( DateInactive; DateInactive; Get( CurrentDate ) ) ) -
        Month( Date of Hire ) + 12 -
        If(
            Day( If( DateInactive; DateInactive; Get( CurrentDate ) ) ) <
            Day( Date of Hire );
            1;
            0
        );
        12
    )
) & " Months, " &
GetAsText(
    Day( If( DateInactive; DateInactive; Get( CurrentDate ) ) ) -
    Day( Date of Hire )   +
    If(
        Day( If( DateInactive; DateInactive; Get( CurrentDate ) ) )
         ≥ Day( Date of Hire );
        0;
        If(
            Day(
                If( DateInactive; DateInactive; Get( CurrentDate ) ) -
                Day( If( DateInactive; DateInactive; Get( CurrentDate ) ) )
            ) <
            Day( Date of Hire );
            Day( Date of Hire );
            Day(
                If( DateInactive; DateInactive; Get( CurrentDate ) ) -
                Day( If( DateInactive; DateInactive; Get( CurrentDate ) ) )
            )
        )
    )
) & " Days "

 

Edited December 22, 2017 by Lee Smith
formatted calc to make it more readable.

[rwoods]

Hi Chris

Your calculation is probably showing as 'Stored' if you look at it in the Manage->Database window. So FileMaker stores the result to make it indexable and quicker when you are performing a find or calculation based on it.

However, because you are using Get ( CurrentDate ) in your calculation that stored value becomes out of date every day. FileMaker will re-evaluate the result when one of the other fields that are referenced changes (as you pointed out), but sadly FileMaker doesn't really notice that the result of Get ( CurrentDate ) has changed!

To fix this, you need to make the result of the calculation 'unstored' (by clicking 'Options' in the Manage->database window for that field). This way FileMaker will re-evaluate the field whenever it is on screen or required for a find or whatever.

The down side of this is that it will be slower to view and work with the result of the calculation, which can become a problem if you have a lot of records.

Because you are using a 'Calculation' field-type, there is no real way to have both the performance of a stored result AND the convenience of the calculation field being always-updated.

If this were my database, I would change it to be a 'Text' field, and use the 'Auto Enter Calculated Result' option, using exactly the same calculation as you have above. Then, I would have the database run a script on startup (once per day only) that set's the field correctly for todays date using the same calculation. FileMaker would handle any intra-day updates if any of the other fields referenced changed. You then have a 'stored' field, which gives you the best performance, and the result will always be correct. Make sure to uncheck the box that says 'Do not replace existing value of field (if any)' in Manage->Database->{yourField}->Options if you try this technique.

 

Hope that helps, it's a common question, and the topic of different techniques for calculated fields is well-trodden on this forum!

[CKonash]

RWoods.  

Thanks so much for the detail and really explaining the pros and cons so well.  I have been struggling with this for a while and i never thought about a server script and changing the field type to help my problem.  I made the changes to the field type and it's displaying correctly (THANK YOU!) However I am new to the use of a LOOP script step.  When I made the script i think i put everything in that I need but if it's not too much trouble could you let me know if I have anything that is incorrect or any steps missing?   Right now I have the Date of Hire filed setting itself, is that the correct way to have the calculation reevaluate through a script?

Thanks again for all the info and taking the time to help!

 

Edited December 22, 2017 by CKonash

[rwoods]

Hello again

Yes I believe that by 'touching' the 'Date of Hire' field you will cause FileMaker to re-evaluate the calculation as update the Years of Service field.

You may want to put a 'Show All Records' in before the GTRRP [First] step, just to make sure all the records are being used.

Otherwise, looks good!

[CKonash]

Thank you very much RWoods.  I will be putting a server script on trial today.  Really appreciate the guidance.  

 

Chris