Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculating and recording a "date completed"


eDog

Recommended Posts

I was really clipping along on a project tracking database, but now I feel so humbled.

 

I tried to record a project completion date by using this calculation:

 

Case(status="completed", DateToText(Today))

 

You non-amateur FM users (unlike me) will recognize that this records today's date and updates it every day so it looks like all my projects were completed today. Silly me. How can run a calculation just once?

 

e

Link to comment
Share on other sites

Hi eDog,

 

Change your date field to a standard date and add an auto-enter by calculation with:

 

Case(Status = "Completed", Status(CurrentDate))

 

Uncheck the box that says "Do Not Evaluate if all Referenced Fields are Empty." That should do it for you! smile.gif

 

Keep in mind this will not update for you. If you change the Status (mark it completed then change your mind), you will need to manually remove the date. If you want it to be fool-proof, you'd need to attach a script which, when Status is changed, checks the Status and takes the appropriate action (adding or deleting date).

 

Cheers,

LaRetta

Link to comment
Share on other sites

Make the marking completed action a script and attach to a button which the user will click to complete the function. Have the label on the button be a calc field (call it "butleg" for button legend) which will change with the action assigned to the button, The script will be reversible such as:

 

If(IsEmpty(CompletedField) or CompletedField = "Open",

SetField(CompletedField,Complete""),

SetField(ButLeg,"Reopen",

SetField(DateCompletedDate,Status(CurrentDate),

Else

SetField(CompletedField,"Open"),

SetField(ButLeg,"Complete",

SetField(DateCompletedDate,DateToText(""),

EndIf

EndScript

 

This will toggle the action of the button as well as the label on the button. If the record has been marked complete, clicking the button again will "uncomplete" it, remove the fate, change the button label and the legend in the field to "Open" (or whatever). If the field is empty, the button will mark it complete, add the completed date to the date field (the DateToText function will avoid the omnipresent question mark is date fields) and change the button to read "Reopen".

 

Two buttons in one! How about that?

 

Be well . . .

 

laugh.gif Tom

Link to comment
Share on other sites

LaRetta and Tom - Thanks for the advice. I am all for making it foolproof though so I want to attach a script to it. Unfortunately, Tom, I can't use the button script because I have this set up with field named current_status that is a value list of three radio buttons, "active", "on hold", and "completed". If the current_status field shows "completed" then I have another field, date_completed that records the date.

 

I feel like I ought to be able to set up a case or test or If statement that works on the idea of

 

if current_status does not equal "completed" then

clear the date_completed field

 

It seems logical that I could add the "does not equal" case to the calculation field LaRetta suggested above, but I can't figure out a command/instruction to clear or erase the date_completed field.

 

Am I off base with this logic?

 

e

Link to comment
Share on other sites

You might try the "invisible buttons" trick I often use to actually make a row of buttons appear and disappear from the layout upon demand.

 

Apparently, the date field you are using is a calculation tied to the implementation of the word "complete" in the trigger field. But because FMP is not event driven, the best you can do is use a Status function to have the date appear in that field. That means that each day that the word "complete" remains in the trigger field, the date will be auto updated in the date field. There is no way around that except to post a date into a date field through some overt action taken by the user, such as a button click. I think you should rethink the approach you are using to this entire function. You could create a "value list" which is actually a set of buttons in an appearing/disappearing field which would activate all of the end results you wish.

 

I have a file called "Magic Buttons" available on www.databasepros resources section which would show how to impletment this action.

 

Or If you send me your email address, I will mail you a copy of the file for consideration of implementing the technique into your solution.

 

Be well . . .

 

Tom laugh.gif

Link to comment
Share on other sites

Hi eDog,

 

Since you are using radio buttons and since you want the option to 'undo' it of a User clicks Completed but changes their mind, you will need three scripts - one for each value list status. Uncheck 'Allow Entry into Field' in Field Format.

 

Script Name: Active

Set Field [Current_Status, "Active"]

Set Field [DateCompleted, TextToDate("")]

 

Script Name: On Hold

Set Field [Current_Status, "On Hold"]

Set Field [DateCompleted, TextToDate("")]

 

Script Name: Completed

Set Field [Current_Status, "Completed"]

Set Field [DateCompleted, Status(CurrentDate)]

 

Draw three transparent circles and place them over each radio button. Attach the appropriate script to each circle. When clicked, the circle will highlight.

 

And please, everyone, don't correct me on the TextToDate(""). For some reason it works either way - DateToText or TextToDate - although I have no idea why and would love to understand the logic of it. :rolleyes:

 

But because it works ass-backwards also, that's the way I like to use it. :eek: laugh.gif

 

Cheers,

LaRetta

Link to comment
Share on other sites

LaRetta, TextToDate("") is correct. "" is text, and the result must be in date format to prevent a "?" from displaying in the field.

 

DateToText() would be used for concatenating a formatted date field with something else, etc. FileMaker views dates as numbers. So without it, you could end up with "731484.731483" instead of "9/25/2003...9/26/2003", if your calculation was Status(CurrentDate) & "..." & Status(CurrentDate) + 1 versus DateToText( Status(CurrentDate) ) & "..." & DateToText( Status(CurrentDate) + 1 ).

 

Exactly why DateToText("") works on a date field is beyond my logical understanding, however. Pooey. blush.gif

Link to comment
Share on other sites

Hi Queue,

 

TextToDate("") is correct. "" is text, and the result must be in date format to prevent a "?" from displaying in the field.
Exactly. Except ... when scripting, it blanks the field (no ?). It goes against logic that it should but in fact it's just because scripts override basic FM field validations including dates. :eek:

 

In addition to scripts, Custom Dialog and Value Lists also ignore date field validation!

 

Scripts override many (most, all?) field validations and it just tickles me to consider how this can be used creativly ... and when I may have added date/text/num conversions when unnecessary ... and when I should be applying my own validations. laugh.gif

 

LaRetta

Link to comment
Share on other sites

Hello eDog,

I suspect that the simplest and best solution to your problem would be to change the 'Project Completion' field from a calculation field to a text field with an Auto-entry by calculation option specified and with the option to 'Prohibit modification of value' selected.

 

An auto-entry by calculation enters only once (and doesn't update, even if the value/s it references are changed), so even using the Today function would be ok with such a set-up.

 

It's simple and water-tight and a whole lot leaner than a scripted solution. cool.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use