Calculate the days between current date & another

[zeddicus]

I am new to scripting and I am trying to create a script that will return all records that have an expiry date less than 28 days away from the current date.

I have tried half a dozen different ways with no success.

It is the last script I need to creat for this current project.

Can anyone please help before I throw my computer off a very high cliff.

[Søren Dyhr]

Try to see if you can make your script look like this:

Enter Find Mode [ ]

Insert Text [ Untitled::expiDate; ">" ] [ Select ]

Insert Calculated Result [ Untitled::expiDate; Get ( CurrentDate ) - 28 ]

Perform Find [ ]

Where "Untitled::expiDate" is the name of the field in your solution ...It's important to see that only the first line is made with the "Select entire content" checked.

--sd

[zeddicus]

Thanks for that but when I try to put the line

Insert Calculated Result [ Untitled::expiDate; Get ( CurrentDate ) - 28 ]

in to Scriptmaker I keep getting the text "; Get ( CurrentDate ) - 28 ]" highlighted and an error message saying "An operator is expected here."

[LaRetta]

In Soren's example all that should be in the calc box is: Get(CurrentDate) - 28.

I'm surprised that Soren didn't put it all in Insert Calculated Result[]; however, it is even simpler than this. You don't need ExpiryDate field on the layout (which is required for both Insert Calculated Result & Insert Text). And it can be done in one step (instead of two) with Set Field[]:

Enter Find Mode [ ]

Set Field [ ExpiryDate ; ">" & Get(CurrentDate) - 28

Perform Find [ ]

Only enter the part in bold in the calc box. But this will also give you ExpiryDates greater than current date and it doesn't sound like that's what you want. If you only want to find records 28 days prior, and up to the current date, use:

Get(CurrentDate) - 28 & "..." & Get(CurrentDate)

Vs. 7 really helped us out here by now accepting calculations directly in a date field using Set Field[].

LaRetta

[zeddicus]

Thanks everybody so far, it is starting to make sense now, but I still can't get it to work.

I've set up record where "Volunteer contract expiry date" is before the 28 day period, in the 28 day period and after the 28 day period in question.

The Datafile is called WAAC Volunteer Database

Some of the relevant fields are

- Last Name (Text)

- First Name (Text)

- Volunteer Contract Expiry Date (Date)

- Expire (Number)

Here are two scripts I've tried.

***********************

Allow User Abort [Off]

Set Error Capture [On]

Go to Layout ["VOLUNTEER CONTRACT EXPIRY"]

[Prints Last Name, First Name and Volunteer contract expiry date]

Print Setup [ Orientation: Portrait; Paper Size: 8.26" x 11.69" ]

[Restore]

Enter Find Mode [ ]

[Pause]

Set Field [WAAC Volunteer Database::Volunteer Contract Expiry Date ≤ (Get(CurrentDate) + 28)

Perform Find [ ]

Sort Records [specified Sort Order: WAAC Volunteer Database::Last Name; ascending WAAC Volunteer Database::First Name; ascending]

[Restore; No dialog]

Adjust Window

[Maximize]

Pause/Resume Script [indefinitely]

Enter Browse Mode

[Pause]

Go to Layout["REPORTS"]

*******************

and (supposed to convert to a number)

*******************

Allow User Abort [Off]

Set Error Capture [On]

Go to Layout ["VOLUNTEER CONTRACT EXPIRY"]

[Prints Last Name, First Name and Volunteer contract expiry date]

Print Setup [ Orientation: Portrait; Paper Size: 8.26" x 11.69" ]

[Restore]

Enter Find Mode [ ]

[Pause]

Set Field [DayOfYear(WAAC Volunteer Database::Volunteer Contract Expiry Date) ≤ (DayOfYear(Get(CurrentDate) + 28))

Perform Find [ ]

Sort Records [specified Sort Order: WAAC Volunteer Database::Last Name; ascending WAAC Volunteer Database::First Name; ascending]

[Restore; No dialog]

Adjust Window

[Maximize]

Pause/Resume Script [indefinitely]

Enter Browse Mode

[Pause]

Go to Layout["REPORTS"]

************************

What am I doing wrong?

Thanks in anticipation!!!

RK

[LaRetta]

I'm having a bit of trouble following the logic of your script. But if you have Enter Find Mode [ Pause ] remove the pause (a checkbox).

This part:

Set Field [WAAC Volunteer Database::Volunteer Contract Expiry Date ≤ (Get(CurrentDate) + 28)

Is NOT correct. This part "&#8804" I assume is the greater than or equal sign? No matter - it's in the wrong place. It needs to be on the right of that semi-colon - which makes me think you are putting your field name in the calculation box instead of the Specify Field box.

When you view that script line looking at ScriptMaker, it should look like:

Set Field [ WAAC Volunteer Database::Volunteer Contract Expiry Date ; ">" & Get(CurrentDate) + 28 ]

But again, I'm unsure what you are trying to find by which calculation.

Your must specify Volunteer Contract Expiry Date field via the button ABOVE the Calculated Result button. You may need to post your file.

And it wouldn't hurt for you to explain your relationship. Is your datafile a separate file or a related table or what? What are your tables related on?

LaRetta

[zeddicus]

Hi LaRetta,

You're having trouble following the logic!! Hee hee

I am starting to think I should throw it out and get an exercise book!!

I'll try making your changes and let you know.

Thanks for taking the time.

[LaRetta]

Hang in there, RK. I remember my very first script ... a looping one. I didn't understand what End If was for ... it kept saying I was being illegal. I wanted to smack the silly thing. So I just kept adding End If's until it finally shut up.

Of course the script didn't work. I had loops within loops within loops ... Before you know it - if your computer stays in the window, you'll be whipping through scripts without a blink.

I'm pulling an all-nighter so if you need this by tomorrow, just ask (post back). We'll walk you through it so you can finish your project. I know what deadlines are like too.

LaRetta

[zeddicus]

When I enter

Set Field [ WAAC Volunteer Database::Volunteer Contract Expiry Date ; ">" & Get(CurrentDate) + 28 ]

I keep getting an error message saying "An operator is expected here" for everything from the semi colon after "Volunteer Contract Expiry Date".

Regards

[LaRetta]

"I keep getting an error message saying "An operator is expected here" for everything from the semi colon after "Volunteer Contract Expiry Date"."

Volunteer Contract Expiry Date should NOT be in the calculation box - nor should the semi-colon!!

Select that file name BEFORE it opens the calculation box - in your Script Maker dialog where you insert the Set Field [ ] step. Look to your right and Specify Field. That's where you choose Volunteer Contract Expiry Date field - not in the calculation box itself.

THEN ... click the calculation box and type exactly this:

">" & Get(CurrentDate) + 28

In fact, copy it onto clipboard from the above. If this doesn't work, you'll need to post your file.

LaRetta

[Søren Dyhr]

Get(CurrentDate) - 28 & "..." & Get(CurrentDate)

Vs. 7 really helped us out here by now accepting calculations directly in a date field using Set Field[].

But the prerequisite is a genuine knowledge of the operators hierarchy when the typecasting takes place or establishing the facts by tiresom trial and error ...so the question is whats best, an inductive approach or a deductive ditto to the learningcurve??? Laretta we had the distinction up before, between learning a matter by heart sometimes as a religious gesture or recognizing the inner mechanics by dedicated reasoning:

http://www.fmforums.com/threads/showflat...true#Post153116

...another text that also covers the process of making the chain data->information->knowledge is this:

http://web.cba.neu.edu/~mzack/articles/kmarch/kmarch.htm

--sd

[LaRetta]

Soren, I have no idea what you just said.

If you are suggesting I parrot appropriate responses but don't understand the principles behind them, you are certainly incorrect. And I will not get into philosophical debate on whether Set Field [] is better or worse than Insert Calculated Result [] and Insert Text [] (althought it certainly is).

The subject of the post was 'between current date and another.' And his first post repeated this request. That requires a date span in my book. Do you use a different book?

LaRetta

[LaRetta]

RK, somewhere along the line you changed the calculation to + 28 instead of -28. This will make a big difference in your find.

And you didn't clarify exactly what dates you wish to find. This might change your calculation needs. But, at this point, I'd just like to see you get the calc box correct - we can fine-tune your find once you have that part nailed down.

But remember that, if you don't have a test record with ExpiryDate for mid-August, you may not find any records. Or change your calculation to -28 instead as was originally suggested. And if you have an FM Manual (a real, physical one), the section on Finding may help you as well.

So ... is your computer still in one piece?

[Søren Dyhr]

If you are suggesting I parrot appropriate responses but don't understand the principles behind

No not at all, but you're throwing someone out in the deep end of the pool, to suggest a typecast at such an early state of a membership - well he/she might be a seasoned C++ programmer who knows all of typecasts and are fully aware of associative and commutative laws implementations in programmed invironments ...but the odd's for guessing correct at this early state are...:

I will not get into philosophical debate on whether Set Field [] is better or worse than Insert Calculated Result []

To me does it seem correct to hint in the direction of the datatypes are going to be involved, thats why I suggested the scripting made 2 lines i ...instead of say bringing in multible key selfjoin relations the way I probably would have approached the matter...

Who benefits from being thrown at solutions that might bring the correct answer, where chances for understanding is the wildcard. Tuition principles like madrassas have a flipside that the student might turn into Taleban, overemphasising gesture and pledging over reasoning.

--sd

[LaRetta]

Well, Soren, I'm just an old country girl but common sense still applies even here in the boonies.

Your suggestion:

Enter Find Mode [ ]

Insert Text [ Untitled::expiDate; ">" ] [ Select ]

Insert Calculated Result [ Untitled::expiDate; Get ( CurrentDate ) - 28 ]

Perform Find [ ]

Where "Untitled::expiDate" is the name of the field in your solution ...It's important to see that only the first line is made with the "Select entire content" checked.

And you didn't mention that the field must be on the layout.

My suggestion:

Enter Find Mode [ ]

Set Field [ ExpiryDate ; ">" & Get(CurrentDate) - 28

Perform Find [ ]

He needs C++ to understand mine and not yours? Nada. It is just as simple to learn the right way as the wrong way, Soren. He has a date field and I gave him an easy date search. I will not allow this thread to be hijacked by discussions between you & I. I am here to assist him and that's exactly what I am doing. End of discussion.

[zeddicus]

Play nicely you too!! Hee hee Feel free to assume I know nothing!!

OK. This is where I've got to.

I'm trying a different tack.

I've managed to establish a field (called Expire)that has a calculated value that is the difference between the two dates.

eg 5/7/2005 and 23/7/2005 has a value of 18.

The values in this field with test data are 126, -5, 12, 141 & 14

But! when I do the following

Enter Find Mode []

Perform Find [specified Find request: Find Records; Criteria: Table::Expire: "< 28"]

[Restore]

It returns no records even though there are 3 that fit.

Any hints on this?

[LaRetta]

Your calculation should be number. Remove the Enter Find Mode []

Your script would be simple:

Perform Find [ Restore ]

Find request would be: <28

Quotes won't work.

I think you're ready to rock 'n roll.

[Søren Dyhr]

Quotes won't work.

...because they forces a typecast thru

--sd

[zeddicus]

AAAAAGGGGGHHHHH!!

Even with that change it still doesn't work.

Here is the full script.

Allow User ABort [Off]

Show All Records (Just to ensure that all records are avilable to the find)

Go to Layout ["Expiry Summary"] (Name and contract expiry date)

Enter Find Mode []

Perform Find [specified Find request: Find Records; Criteria: Table::Expire: < 28]

[Restore]

Sort Records [specified Sort Order: Table::Name; ascending

[Restore; No dialog]

Adjust Window

[Maximize]

Pause/Resume Script [indefinitely]

Enter Browse Mode

[LaRetta]

You ever heard the term 'beating a dead horse, Soren?'

Quotes won't work because you can't find 'less than 28' on text. If the field were text, it would find 16, 144 & 22 - because they all start (first two characters) with less than 28 and none of your theory or attempts to contrive would make any difference whatsoever.

zeddicus, you left the Enter Find Mode []. You are not listening. I'm done.

[zeddicus]

I'm sorry I don't understand what you mean by "You left the Enter Find Mode []

[LaRetta]

I told you to leave off Enter Find Mode [] but it is still in your script. In fact, you didn't listen to much of what I said ... and didn't answer my questions when I was attempting to assist you.

"I keep getting an error message saying "An operator is expected here" for everything from the semi colon after "Volunteer Contract Expiry Date"."

I knew exactly why you were getting this error and I told you three times exactly how to solve it - not to put the field in the calc box.

You're on your own now. I'm leaving for the office (after working all night).

[zeddicus]

Look, I'm sorry if you are getting annoyed but I don't see where I've left the "Enter Find Mode []".

I have tried everything that has been suggested.

[Søren Dyhr]

'beating a dead horse, Soren?'

Almost but isn't the verb "Flogging" originally??

http://homepages.tesco.net/~scotsnet/o.f.carter/fun/fun325.htm

--sd

[zeddicus]

OK, sorry I guess I just didn't understand what you were saying.

I'll work it ou ton my own somehow after doing a bit more research.

Thanks anyway.

[LaRetta]

I liked that, Soren!!

[Søren Dyhr]

I guess I just didn't understand what you were saying.

I made a sample of the 3 methods we came up with leaving out the stored request ...This might explain what is obvious to us, but still is somewhat a mystery to you!

Download the attached template.

--sd

methods.zip

[-Queue-]

If you do use the Insert Text and Insert Calculated Result steps (which you can just as easily combine into a single ICR step), you do not need 'select' in any portion of it. When you Enter Find Mode, there will be no data in the field, so there is nothing to 'select' anyway.

I would go with the Set Field step or relationship though. They are more reliable and less confusing, IMHO.

[zeddicus]

Hi all,

I worked my way around and got it working. Thanks for all your help!!

For some reason, although the data field (Calculation) was storing the result as a number, the Perform Find function was treating as text.

Once again, thanks for all the assistance and more importantly the patience!

[Søren Dyhr]

When you Enter Find Mode, there will be no data in the field, so there is nothing to 'select' anyway.

True, but it's set as default ...so the sloppy developer only got rid of it when was of importance - sorry!

--sd