Dates in find requests

[Raschelle]

I am trying to set up scripts that will omit records where the date field (RTKDateDone) is less than one year from the current date.

It would be ideal if the find could look at today's date and subtract a year (or 365 days) and find anything less than that. Is there a way to do that without making the user type in the date?

In the find requests all I can seem to do is choose the date field and put a static date in the find request in the script. I created a field which gave me the current date minus 1 year (learned that from a previous post), but you can't use fields in the find criteria.

I have many date fields that I need to create similar scripts for and was hoping for an easy way to set them up.

Thanks for any help.

Raschelle

[mz123]

Hey Raschelle,

You could set your calculation of Today's date minus one year to a global then create the following script:

Enter Find Mode (no Pause)

Set field [DateField; "<=" & DateMinusOneCalc]

Perform Find

That should do the trick!

[Raschelle]

Martha,

I think that's what I needed!! At least I did set up my calculation as global so I was on the right track.

One more question related to this. My actual script actually has five find requests and the date is the third one. I used the constrain found set to enter the others, but the date find appears first on the list. Is there a way to make it the third request?

I'm not sure if the person I'm setting up all this will care that the results are a little different based on the find order, but wanted to cover that base, if possible.

Thanks for the help!!

Raschelle

[mz123]

The results should be the same regardless of the find order, i believe.

You do your first two finds and then the following..

Enter Find Mode (no Pause)

Set field [DateField; "<=" & DateMinusOneCalc]

Constrain Found Set[]

so it should look like....

Perform Find[Restore]

Constrain Found Set [Restore]

Enter Find Mode (no Pause)

Set field [DateField; "<=" & DateMinusOneCalc]

Constrain Found Set[]

Constrain Found Set [Restore]

or something similar, is that right?

[Raschelle]

Thanks again Martha!!

That should be all I need to get these scripts up and running.

How I love FMForums to help solve problems.

Raschelle :yourock:

[comment]

You can do this directly, without the global field, and all at once:

Enter Find Mode

<< set up your find request/s >>

...

New Record/Request[]

Set field [ DateField ; Let ( today = Get ( CurrentDate ) ; "<=" & Date ( Month (today ) ; Day ( today ) ; Year ( today ) - 1 ) ) ]

Omit Record

...

Perform Find[]

[Raschelle]

That may work better than what I've been trying. I've notice two problems with my current setup:

1. The global calculation didn't show today's date in the field, it still showed yesterday's date,

2. The constrain found set also kept yesterday's date within the script. Even after running the script, it didn't change the date within the stored results (I really should have seen that one coming).

One question though...why the "omit record" step? I want to find ones older than one year, would that omit them and find instead ones within the current year? Please elaborate.

Thanks for the help!

Raschelle

[Raschelle]

Actually, nevermind, I figured it out. Since the script is in find mode, that is how you omit records in the find.

This is what I've done with the, hopefully, final script:

1. Enter find mode

2. Set field (find request 1)

3. New record/request

4. Set field (find request 2)

5. New record/request

6. Set field (find request 3: date - thx for the calculation :thanks: )

7. New record/request

8. Set field (find request 4)

9. Omit record

10. New record/request

11. Set field (find request 5)

12. Omit record

13. Perform find

I know it's a long way to go for a find, but all the requests are in the order I need and it shouldn't save the date. I'll have to test it again tomorrow, to be sure, but hopefully this will be the one that works.

Thanks to the both of you for your input and help with my issue.