Refine date calculation

[emncwundy]

We have a script which runs a report showing all time card entries posted for this week (for payroll purposes).

 

See the attached screen shot showing the script as it currently stands.

 

This script was created a year ago, and I have just discovered that the insert calculated result line is not only finding the current years entries, but all entries with the current week number. Hence why last years records are also being included now.

 

Can someone please help me to tweak this calculation to find only this years records for this current week.

 

I am not sure whether or not I should be inserting the following somewhere into the current script:

Year (Get (CurrentDate))

 

Thanks in anticipation!

 

timecard_script.tiff

[comment]

What is the exact formula for the cWeekNumber calculation field?

[emncwundy]

WeekOfYear ( WeekStartDate )

[comment]

LOL, it was only two and a half weeks ago that you wrote:

 

Thanks in anticipation!

 

Also, it's not a goof strategy to be laconic in your description - you will see why in a moment.

 

Now, I am confused by your answer because your script searches for records where cWeekNumber equals the WeekOfYearFiscal of the current date (actually, it searches for last week's records - hence more confusion). WeekOfYear and WeekOfYearFiscal are NOT the same thing, and you can't use them in a mix.

 

In any case, the simplest solution would be to search the WeekStartDate field directly for records whose WeekStartDate equals the starting date of the current week (or any other week of interest). For this, your script must calculate the starting day of this week. I am afraid I can't tell you exactly how to do that based on your description so far.

[emncwundy]

Thanks for your advice. The script was created with the assistance of members of fmforums because it was just a bit over my head. It has worked ok for the last 12 months because there was only one year of data. But its obviously not working now because there is more than one year of data.  I think i need to find a simpler way to find the records i want to view. I was thinking of creating two global fields - a start date field and endDate field into which i could type the range of dates I want to view. Do you think this would work in a script which ends in showing data in the report layout?

[webko]

Sure, that's a fairly common approach...

 

Script might then just concatenate the two global fields: $dateRange = Table::gDateStart & "..." & Table::gDateEnd

 

And then use that in your finds...

 

Cheers

 

Webko

[emncwundy]

Ok thanks Webko, ill give that a go. cheers.