Finding Certain Date by Comparsion

[AWS]

Hi,

I am trying to create a report that will search my entire database using something like this:

Nextservice data <= Date(Month(today),Day(today)+30,Year(Today) )

I think this will do what I want, If my NextService Date is less than todays date + 30 days from now, then I want to add that record to my found set in my search.

My problem is with the script. Heres what I've got so far

Enter Findmode[]

Insert Calculated Result[] ***

Perform Find[Replace Found Set]

Go to Layout["Next Service Date Report"]

*** I'm not sure If I should be using the Insert Calculated Result[] as I think, from what I've read is it will change the value of the field that is included in it. I would really just like to do a comparision and not save the value that is compared.

I've Tried messing around with the Insert Calculated Result step, but with no luck yet.

Thanks

Greg

[Ugo DI LUCA]

Hi,

Why not use the formula as a plain Case calc within your file.

calc = Case(your formula is true, 1, 0).

Then script a search for result = 0/1.

In addition, avoid using Today and replace it by Status(CurrentDate) making it unstored

[AWS]

Ok, I've defined a calc called IsNextServiceDate = Case(NSD <= Date( Month(Status(CurrentDate)), Day(Status(CurrentDate) + 30), Year(Status(CurrentDate)) ) , 1, 0).

I guess I don't understand how I should search properly in my script. Do I use the Insert Calc Result["IsNextServiceDate",:] or should I use another step to do this.

Thanks Ugo.

[Ugo DI LUCA]

Hi,

Let take some time here and play with this example. You'll probably think that I'm kind of a fool guy, but with this kind of calculation, the interresting input is that you use every FM's functionnality.

The easiest (not better) script could go :

Option 1 Script

Find Mode

Insert calculated result (the calc field, 1)

Perform Find.

Go to List Layout

You can also perform both finds within a same script, as you probably agree that searching a 1 could almost be as interresting as searching a 0, leading to opposite lists of records, depending on the situation.

You will need another field for that , preferably a global field, that could hold both values. When populated (1 or 0), the script would be

Option 2 script

Find Mode

Insert calculated result (the calc field, "your global field")

Perform Find.

Go to List Layout

To add even more flexibility, you could use this script as a sub-script into another script.

Imagine you want to display all the records with same result that the one you're browsing.

The script would go :

Option 3 script :

Set Field (your global field, your calc)

Perform Option 2 script

This will auto-populate the criteria field by the current result of calculation.

Now, here's another option for huge files (quicker) :

Option 4 script "Forget About Find Mode" :

Set Field (your global field, your calc OR a value)

Go to Related record (selfjoin* - show all -)

Go to List Layout

where the selfjoin is a relationship from your file to your file using "your global field" at left side and the calculation at "right side".

We'll see later if the right side (your calc) is unstored result, as it is possible that the Next Service Date could be a related record.

or even better :

Option 5 : "Forget about script"

drop the global field on layout and draw a portal using the selfjoin.

All records would be appearing at the fly in the portal.

If the calculation is a result from a related file, and you want to see the result in a portal, there are some workarounds, but I finally just don't have enough courage...

[AWS]

For some reason, I kept getting this message when trying to run my script. This happens with both Option 1 and 2 of your examples. I must be doing something wrong.

There are no Criteria in the request, type a valid request before clicking find.

Here is my script.

Enter Find Mode[]

Insert Calculated Result["IsNextServiceDate","g_Num"]

Perform Find["Replace Found Set"]

Go To Layout["Next Service Date Report"]

and how I have my fields defined.

Calc Field IsNextServiceDate = Case( NSD <= Date( Month(Status(CurrentDate)), Day(Status(CurrentDate) + 30), Year(Status(CurrentDate)) ) , 1, 0)

Global Field g_Num Options = Number

Calc Field NSD = Min(Service Date::Service Date)

Thanks Greg

[AWS]

Just wanted to leave a update, I still have the problem when running the script with the no criteria error message. but found some other realated problems with this.

First I found that my Calc Field was assigning every record with the value of one, which is not true.

I added: Not IsEmpty(NSD) and (Case( NSD <= Date( Month(Status(CurrentDate)), Day(Status(CurrentDate) + 30), Year(Status(CurrentDate)) ) ) , 1, 0)

and that then added 0 for any field that had no date at all, which, seemed to at least fix my formula.

Then in my script I removed the Insert Calculated Result step, and added a pause to the Enter Find Mode, to try it manually, and added some test fields to my layout where I was calling the script from, (IsNextService Date) and (NSD), I normally won't want these on the this layout but for testing, and ran the script, I entered a 1 for IsNextservice Date, and it went to my layout, and it displayed everything fine. So its got to be a problem with my Insert Calculated Result Step, also tried Set Field, and it also gave me the same error. Hope this helps you ugo or anyone else who might be able to find the problem with this.

Thanks Again

Greg

[AWS]

I solved my problem, not sure if it's the best way or not, but seems to work good.

I added:

Set Field ["IsNextServiceDate","1"]

New Record/Request

I removed the insert calculated result and the above added to my script seems to help. any suggestions are welcome, I've been using FM Pro for a short time, and I'm always looking for tips and advice.

Thanks for all your help

Greg

[Ugo DI LUCA]

Hi Greg,

Not sure I unsderstand what's wrong, but you wouldn't need any New Record step here.

Could you attach a sample of your file here ?