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

Date script or calc (from-thru)


Bigray

Recommended Posts

Hi.

 

I’m a 2-month newb to FMP 6.0v4 and learning on the fly. I’m looking for a script or calc for sorting records by date. Preferably the user would enter a start date and finish date(From & Thru @ runtime). All my efforts have been unsuccessful thus far. So I'm praying to the FMP gods frown.gif for guidance. Any Help would be great.

 

Thanks,

Ray.

Link to comment
Share on other sites

Hi Ray, welcome to the Cafe' smile.gif

 

If you wish to find a certain group of records within a User-defined date span and, if you wish to sort those records by date, you will need a script. Here's one way:

 

Create two global (date) fields called gFrom and gThru. Create a Layout called Find Date on which you have placed these two global fields - allow FM to create the field labels for you also.

 

Then perform a manual sort on your date field by selecting Records > Sort. Find your date field on the left and 'Move' it to the right. Select whether ascending or decending (as you prefer). Let's call your date field DateField. Then immediately create the following script:

 

Set Error Capture [On]

Set Field [gFrom, TextToDate("")]

Set Field [gThru, TextToDate("")]

Go to Layout [Find Date]

Loop

Show Message ["Please enter Start and End dates."]

Pause/Resume Script []

If [isEmpty(gFrom) or IsEmpty(gThru)]

Show Message ["You left one of the date fields blank. Do you want to try again?" (OK, Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

If [not IsValid(gFrom) or not IsValid(gThru)]

Show Message ["One of the dates entered is an invalid date format. Do you want to try again?" (OK/Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

Exit Loop If [not IsEmpty(gFrom) and not IsEmpty(gThru) and IsValid(gFrom) and IsValid(gThru)]

End Loop

Go To Layout [layout with your date field to search]

Enter Find Mode [] ...uncheck Restore Find Requests & Pause

Insert Calculated Result [DateField, DateToText(gFrom) & "..." & DateToText(gThru)]

Perform Find [Replace Found Set]

If [not Status(CurrentFoundCount)]

Show Message ["There were no records found."]

Go to Layout [Your Original Layout]

Exit Script

End If

Sort [No Dialog] ... uncheck Restore Sort Order

Go to Layout

     

    A couple of pointers:

     

    1) Each script step is listed in the script box - just scroll down the left side until you find what you need. Double-click to insert your selection into your script. If you mess up and insert a line in the wrong place, select the line then point at the line arrows until you get a cross-bar and (holding down your left mouse button) drag the line up or down.

     

    2) When you exit this script, you will be asked whether to Keep or Replace your Find and Sort. Select REPLACE. FM will then store the sort you created (at the beginning) within your script. If you ever re-enter this script and, as leaving, select KEEP instead - or any sorts you've done in the meantime would replace your pre-defined sort for this script. Been there - done it! :rolleyes:

     

    3) While defining this script, if you need to select a field, point at it (from the selection box) and double-click to select it.

     

    4) Once your script is perfect, add the following line at the top: Allow User Abort [Off]. This will prevent Users from hitting [esc] and stopping your script and/or stranding them somewhere you don't want them. Remember to KEEP your sort. But don't add Allow User Abort beforehand. If you have a problem in your script, you might find yourself in a perpetual loop with no way out. I've been there too! laugh.gif

     

    Don't let the length intimidate you Ray. This should (hopefully) provide a safe and effective find and sort for your dates but still allow for Users entering an invalid date format. It's easy if you print this script out and then just add each line in your script as listed.

     

    If you need further help, just ask us. There are many wonderful people here willing to assist you. smile.gif

     

    LaRetta

Link to comment
Share on other sites

Hello,

 

I've tried this code, and printed it out and double triple checked and when I'm done, it is giving me an error code of: Missing End If / End Loop....

 

I even started from scratch a few times, and can't seem to get it right...Getting very frustrated..... mad.gif

 

When I hit the Ok on the pop up window it takes me to: Show message "One of the dates entered is an invalid date format. Do you want to try again? I for the live of me can't get this one... Someone please help me.....

Link to comment
Share on other sites

Hi Laretta, could you explain why you need to include a loop in this find script? I just use the following steps:

 

Set Error Capture [On]

Allow User Abort [Off]

Go to Layout [Find Date]

Show Message ["Please enter Start and End dates."]

Pause/Resume Script []

If [isEmpty(gFrom) or IsEmpty(gThru)]

Show Message ["You left one of the date fields blank. Do you want to try again?" (OK, Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

If [not IsValid(gFrom) or not IsValid(gThru)]

Show Message ["One of the dates entered is an invalid date format. Do you want to try again?" (OK/Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

Go To Layout [layout with your date field to search]

Enter Find Mode [] ...uncheck Restore Find Requests & Pause

Insert Calculated Result [DateField, DateToText(gFrom) & "..." & DateToText(gThru)]

Perform Find [Replace Found Set]

If [not Status(CurrentFoundCount)]

Show Message ["There were no records found."]

Go to Layout [Your Original Layout]

Exit Script

End If

Sort [No Dialog] ... uncheck Restore Sort Order

Go to Layout

     

    For me this script works fine laugh.gif

     

    Onno

Link to comment
Share on other sites

And, Onno, it looks like if the dates are not valid and the user clicks 'Ok' in your script, the invalid dates are still used for the find. That could hurt.

 

This is a good example for when a loop is very useful. You can ensure all criteria is met before proceeding, especially in a situation where multiple fields are validated and users have the ability to change fields that were validated previously in a script, which could produce some very unexpected and nasty results otherwise.

Link to comment
Share on other sites

Here is another version of the same thing, minus the bells and whistles:

 


Allow User Abort [Off]

Set Error Capture [On]

#

# Check for invalid date entries that would cause errors

If [

(IsEmpty(From) and IsEmpty(Through)) or

not IsValid(From) or

not IsValid(Through)

]

Show Message["Invalid Date Range. At least one date must be provided, and must be in a valid date format."]

Exit Script

End If

#

# Find records matching the date range...

Enter Find Mode[]

Insert Calculated Result [select, "DateField", "DateToText(From) & "..." & DateToText(Through)"]

Perform Find [Replace Found Set]

#

# Sort the found records...

Sort [Restore, No dialog]

 


 

Why use a loop?

The "Loop" that LaRetta used makes her script "modal", or in other words, forces the user to address the current situation before proceeding to another section of the application. The loop allows you to keep the user in the script until certain condition have been satisfied. As long as the script is still running, the user can't switch to other layouts, can't switch to other files, and can't perform other scripts, and can't even close the file (althouth they can still close the application). Depending on the situation, this can be a very useful tool. BTW, LaRetta's script is missing a couple of steps, which is why BigRay kept getting an error. I'd also re-order the "Exit Loop" statement. Here is a modified version of LaRetta's script:

Be warned! I have not tested the version below!

 


Set Error Capture [On]

#

# Initialize the From and Thru fields

Set Field [gFrom, TextToDate("")]

Set Field [gThru, TextToDate("")]

Go to Layout [Find Date]

#

# Loop through the validation steps until the user finally passes the validation, or cancels the action

Loop

Show Message ["Please enter Start and End dates."]

Pause/Resume Script []

 

Exit Loop If [not IsEmpty(gFrom) and not IsEmpty(gThru) and IsValid(gFrom) and IsValid(gThru)]

 

If [isEmpty(gFrom) or IsEmpty(gThru)]

Show Message ["You left one of the date fields blank. Do you want to try again?" (OK, Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

Else

If [not IsValid(gFrom) or not IsValid(gThru)]

Show Message ["One of the dates entered is an invalid date format. Do you want to try again?" (OK/Cancel)]

If [status(CurrentMessageChoice) = 2]

Go to Layout [Your Original Layout]

Exit Script

End If

End If

End Loop

#

# Perform the find

Go To Layout [layout with your date field to search]

Enter Find Mode [] ...uncheck Restore Find Requests & Pause

Insert Calculated Result [DateField, DateToText(gFrom) & "..." & DateToText(gThru)]

Perform Find [Replace Found Set]

#

# Check if any records were found or not

If [not Status(CurrentFoundCount)]

Show Message ["There were no records found."]

Go to Layout [Your Original Layout]

Exit Script

End If

#

# Sort the records and return to the regular layout

Sort [No Dialog] ... uncheck Restore Sort Order

Go to Layout

    Link to comment
    Share on other sites

    See Brian Kennedy's "The Works"

     

    Records are viewed in Portals , So See portal sorts , then see calculations for the sort fields.

    Also scripts are attached to round circle graphics.

     

    VERY COOL System , but a lot of work and too much to try to explain.

    Link to comment
    Share on other sites

    Hi xena1967! Hi Everyone! smile.gif

     

    Well, I left off two End Ifs and should have added a Show All Records if no records were found. I am truly sorry for your frustration. I try to be very careful with these things.

     

    I have used this looping script frequently, except I have never incorporated the IsValid portion – only the IsEmpty test. Recently on another thread, Ray (CobaltSky) had also included a nifty IsValid test. I wanted to provide the best solution so at the last minute (trusting his judgment over mine) I manually adjusted my normal script to include the IsValid test. In reviewing his posted script, he also left off a few End Ifs like I did. He also includes Beeps which I have never used (but I think are quite nifty), and tests for Status(CurrentError) = 401 and I tested for not Status(CurrentFoundCount). I believe that, if one has Set Error Capture [On], it is proper to capture the error in that manner, as Ray did.

     

    Here is the corrected script. And I still believe that taking the time to come up with a User-proof Find on globals is worth the effort. Once we get it right, it will always work. So here it is...

     

    Omissions are in bold.

     

    Set Error Capture [On]

    Set Field [gFrom, TextToDate("")]

    Set Field [gThru, TextToDate("")]

    Go to Layout [Find Date]

    Loop

    Show Message ["Please enter Start and End dates."]

    Pause/Resume Script []

    If [isEmpty(gFrom) or IsEmpty(gThru)]

    Show Message ["You left one of the date fields blank. Do you want to try again?" (OK, Cancel)]

    If [status(CurrentMessageChoice) = 2]

    Go to Layout [Your Original Layout]

    Exit Script

    End If

    End If

    If [not IsValid(gFrom) or not IsValid(gThru)]

    Show Message ["One of the dates entered is an invalid date format. Do you want to try again?" (OK/Cancel)]

    If [status(CurrentMessageChoice) = 2]

    Go to Layout [Your Original Layout]

    Exit Script

    End If

    End If

    Exit Loop If [not IsEmpty(gFrom) and not IsEmpty(gThru) and IsValid(gFrom) and IsValid(gThru)]

    End Loop

    Go To Layout [layout with your date field to search]

    Enter Find Mode [] ...uncheck Restore Find Requests & Pause

    Insert Calculated Result [DateField, DateToText(gFrom) & "..." & DateToText(gThru)]

    Perform Find [Replace Found Set]

    If [not Status(CurrentFoundCount)]

    Show Message ["There were no records found."]

    Go to Layout [Your Original Layout]

    Show All Records

    Exit Script

    End If

    Sort [No Dialog] ... uncheck Restore Sort Order

    Go to Layout

       

      It would sure be easier to spot errors in these scripts during the final review if they properly displayed the indents. Errors jump out at you when it’s displayed indented. Again, sorry for my omissions. :rolleyes:

       

      LaRetta

    Link to comment
    Share on other sites

    Hi LaRetta, you can use & nbsp; (minus the space) to force invisible space(s) and emulate an indent.

     

    If [not Status(CurrentFoundCount)]

    Show Message ["No records found."]

    End If

     

    Just a short example. Thought you might like to know this little 'trick'. wink.gif

    Link to comment
    Share on other sites

    Thanks Mariano for the clear explanation of the use of a loop in a validation script laugh.gif . I apologize for just having copied and pasted LaRettas script in my post, leaving out the loop, and thus adding to the confusion frown.gif .

    Link to comment
    Share on other sites

    Hi LaRetta, & everyone. laugh.gif

     

    Sorry for not responding sooner. I just wanted to thank you for helping a newb. I've read several responses and wonder how some can get anything done. Sometimes u have to use some artistic license and not expect someone else to do your work. :rolleyes:

     

    I used your code snipet as a template and let the script builder create the script for me. I to noticed several inconsistencies but I let script builder do its thing and I'm very happy with the results. smile.gif Thanks again, your help was great.

     

    Ray. tongue.gif

    Link to comment
    Share on other sites



    ×
    ×
    • Create New...

    Important Information

    Terms of Use