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

script to find records within date range


Boo
 Share

Recommended Posts

Hi,

 

Can anyone tell me what steps are needed to get records within specified date range?

 

I am not able to understand how to give the dates in the layout.. frown.gif

 

Can anyone show an example ?

 

Thank You.

Link to comment
Share on other sites

This can be done many ways depending on which version of FM you're using as well as how you've structured your db.

 

The most simple way of doing this is to just do a find & constrain:

 

Let's assume you have a date field called DATE and two global date fields called START and END which will hold the start date and end date of your range of records.

 

1) show all records

2) enter find mode

3) set your DATE field to "≥"&START

4) perform find

 

now you need to constrain the found set:

 

5) enter find mode

6) set your DATE field to "≤"&END

7) constrain found set

 

And that's it - you now have a range of records where START ≤ DATE ≥ END (BTW, in case you didn't know, the "≥" & "≤" symbols are typed by hitting either the 'greater than' or 'less than' key while holding the option key ...on a mac w/OS X, not sure on pc, but you can check keycaps to be sure)

 

Also keep in mind that this can either be scripted or just done manually with the menu options.

 

If you want other ways of doing this (I like using self-relationships to do this sometimes) without modifiying your original found set, let me know.

 

pc

Link to comment
Share on other sites

Thanks for helping me .. I'm not sure i have fully understood the steps, but this is what i could make out -

 

I have records which are to be found in a particular state and with a date range.

 

So what I did was create a layout and in that 3 fields (1 state, 2 dates fields) and a button.

I wrote a script which is executed when the button is clicked.

 

The script is something like this -

 

1) Go To Layout -this is the layout of the report i need to view.

2) Go To Record, first

3) Loop

4) If State="CA" and CreationDate ">=" START and CreationDate "

5) Go to Record, Next

6) Else

7) Omit Record

8) Exit Loop "not Status CurrentFoundCount "

9) End If

10)End Loop

11)Enter Preview Mode.

 

I am using FM 5.5, so i am not sure about the constraint found set. Is the above script and method of displaying the data

correct?

 

Also Do I have to have two layouts.. one for entering the "find" op fields and the other for the report?

 

How do I "set field to ">=" &START"?

 

Thank you.

Link to comment
Share on other sites

I'm not sure whether "constrain found set" is available in v5.5 or V6, if I missed it I would like to know how to do that frigante.

 

Based on the globals START and END and State_request you could do it in one go like this

 

enter find mode[do not restore, no dialog]
set field[date, START&"..."&END]
set field[state, State_request]
perform find[do not restore, no dialog]

smile.gif

Link to comment
Share on other sites

Heyy.. thanx for the help smile.gif I could run the state request.. but i'm still not sure as to how i should give the date range in the set field func.

 

Can you help me with that?

Link to comment
Share on other sites

You need Insert Calculated Result [datefield, DateToText(Start) & "..." & DateToText(End)] instead of the Set Field. You also need to have datefield on the current layout where the script runs or switch to a layout that contains it before performing the Insert step.

Link to comment
Share on other sites

sure. fm has a standard option for ranges, and that is to type in three dots between the low limit and the high limit of your request.

so in the set field script step, tell fm to put in "..." between the start and the end date you obtain from the globals.

 

i'm not sure what you know about functions, but "&" is the sign for concatenating strings, so

 

set field [tester, "Boo"&"kjoe"]

 

will result in "Bookjoe" in the text field tester.

 

so

 

set field [date, start&"..."&end]

 

 

will result for instance in

 

09-07-2004...10-10-2004

 

this is exactly the type of search request you need for a range search.

 

in the set field specify dialog, you can type in functions and calculations just as in define calculationfields. so all you have to do is type it out as above. I assume you have the date globals START and END, just like the text global State_request.

 

hope you get it to work

 

smile.gif

Link to comment
Share on other sites

Thank You

 

I have tried exactly as above ... but it says modify find request:(

 

1) Enter find mode

2)Set field ("Date","start&"..."&end")

3)perform find

4) Goto Layout (layout displaying the found set, if any in a report form)

5) enter preview mode

 

I am not sure where i'm doing wrong. Both start and end are global/date fields..

Link to comment
Share on other sites

queue, I never understood why set field[] and insert calculated result [] are both there, set field will do calculations nicely. The DateToText addition probably is wise.

 

boo, follow -queue-'s suggestion then and try Insert Calculated Result then confused.gif

 

smile.gif

Link to comment
Share on other sites

Originally posted by -Queue-:

[qb] You need Insert Calculated Result [datefield, DateToText(Start) & "..." & DateToText(End)] instead of the Set Field. You also need to have datefield on the current layout where the script runs or switch to a layout that contains it before performing the Insert step. [/qb]

I tried doing this and i'm getting a similar error mssg frown.gif
Link to comment
Share on other sites

Originally posted by kjoe:

[qb]

 

boo, follow -queue-'s suggestion then and try Insert Calculated Result then confused.gif

 

smile.gif [/qb]

hmm.. didn't work and am not able to understand as what i need to do confused.gif

 

Boo

Link to comment
Share on other sites

also pay attention to his second remark: that your globals are not in the layout you start the request script in.

 

i work around this by making a 'scripts' layout which is basically a layout that has all the fields in it. then start each script with a 'go to layout [scripts]' step and end with ' go to layout[original]'.

 

otherwise, i can't see what's wrong. debug the script by adding temporary halt/resume script step after each intended step and see what fm does in comparison to what you want or expect it to do.

 

smile.gif

Link to comment
Share on other sites

Set Field forces the result into a format that satisfies the field definition. You can't force a date range into a single date. Insert Calculated Result, on the other hand, allows you to put a range in as text and leave it that way, as you would if you were manually entering the range. DateToText converts the date into text so that you don't end up with a range of numbers, since date fields are internally treated as such.

Link to comment
Share on other sites

Hey - no problem..

 

First of all, there's one step you should add to your script - right after the first step:

 

1) Go To Layout

2) Show All Records

 

The 'show all records' step is important since you're not using the find feature. You have to make sure you're dealing with every record possible.

 

The rest should go like this in order:

 

3) Go To Record, first

4) Loop

5) If Statement (yours looks good)

6) Go to Record, Next - exit after last

7) Else

8) Omit Record

9) End If

10) End Loop

 

I'm assuming your 'enter preview mode' step is so that you can print out the records..

 

You don't need the exit loop step if you check the 'exit after last' option in step 6 with the 'go to next record' function. It's a little checkbox that you can check when you're on that step.

 

As for having two layouts, it's not necessary. You can have one layout with your records in the body in a row-style design (ie: a thin row with all fields side by side so that when all records are viewed as a list it looks like a spreadsheet) then you can have a header for the criteria.

 

In the header mentioned above you can put a few global fields - one for each search criteria, for example:

 

State_gl (text)

Start_gl (date)

End_gl (date)

 

I like to attach the "_gl" to the name of any global fields I create so that there's no confusion.

 

Now, in your script's IF statement, you can have:

 

If[state=State_gl and CreationDate≥Start_gl and CreationDate≥End_gl]

 

And that's about it. You'd enter a state abbreviation, start date (in date format, of course) and end date, hit your button and you're off. You could make it two layouts if you wanted to, but there's simply no need.

 

Also, you could create more global fields for other purposes such as the number of matching records you found:

 

RecordCount_gl (number)

 

Which can be displayed in the header as well. You could add this step inside your if statement in your script:

 

set[RecordCount_gl; RecordCount_gl+1]

 

Hope that helps!

 

pc

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use