Constructing a calendar of work days

[The Mad Jammer]

I work for a school system and our calendar for this school year is July 1st, 2004 - June 30th, 2005. The calendar itself is nothing more than a string of 366 characters. (i.e. WWSSSSSWWHSSSTWWSSSSNWWSSSSSWW etc..). My mission, if I decide to accept it, is to load these values into a file such that each character denotes a specific day/date, calculate the date based on the position of the character in the string (i.e the first character is July 1st etc), reset the calculation to handle the change to the new year at character number 184 (approximately) and to fill in what looks like a calendar object on a layout so that someone can change a work day to a non-work day if need be. (There is a hurricane moving in our dierection as I write this so it is possible that the master calendar will have to be changed next week if the hurricane actually hits and we have to close down the place for a day or two.)

In this calendar W's denote weekends, S's denote school days, N's denote non-school days (but not necissarily non-work days), T's denote teacher work days and H's denote holidays. (W's and H's are always considered non-work days.)

I can easily import the 366 character string into a text field but then comes the difficulty. How do I move each character into a corresponding repeating element in another field(or redefine my current field with 366 repeating values). Obviously a loop would be the answer but how can I set and increment a variable (loop counter) that will act as the index to the repeating field and also the start position of the character string?

Once this is done I will also need to be able to convert a date to a julian date and use the rightmost 3 positions of the Julian date as the index into the repeating field. Any Julian Date converters out there?

The upshot of the project is to calculate the number of work days between two dates.

Thanks

The Mad Jammer

P.S. I've attached the 366 byte school calendar string for reference.

SCHLCAL2.TXT

[Fitch]

You could put the repeating field on a layout and use Go to field (next) in your loop. I.e., use a counter to increment the Middle() and Position() function on the text string, but don't use the counter to deal with which repeat you're on. Just use Set Field or Insert, but don't specify the field.

Have you considered using a separate file/table for this instead, with one record for each day? That would make it pretty simple to count the work days, with no need for elaborate calculations.

You might find something helpful here or here.

[The Mad Jammer]

Thanks Fitch,

The answer was easier than I first thought. I have 3 fields on the database, RecordNumber initialized to 1 and auto-increment by 1, CalDate and Day. This is the script I wrote to generate my own calendar from July 1, 2005 to June 30, 2005.

Loop

New Record/Request

Set Field["CalDate", "Date(7,1,2004) + (RecordNumber - 1)"]

If [DayofWeek(CalDate) = 7 or DayofWeek(CalDate) = 1"]

Set Field["Day", "X"]

Else

Set Field["Day", "W"]

EndIf

Exit Loop If ["CalDate) > Date(6,29,2005)"]

End Loop

Now all I have to do is figure out how many days are marked with a "W" within a date range. The bugger is that I have to calculate this number from another database which must supply the begin and end date for the date range and store the number in the other database. Any ideas on how to accomplish that?

The Mad Jammer

And then... there was nothing

[Fitch]

In FileMaker 7, you can do this with a relationship. In FileMaker 6, you could construct calculated key fields for a relationship and ranges, but an easy way would be to script (pseudo code)

Enter Find mode

Insert calculated result ( the date range )

Set Field[ "Day", "W" ]

Perform Find

Set Field[ "theNumberInTheOtherDB", "Status (CurrentFoundCount) ]

[The Mad Jammer]

Fitch,

Sadly the script you gave me doesn't work. It calculated 935 days for a begin and end date that are the same. I'm using FMP 5.0 and I suppose that has a few more limitations than later versions. Doing this in a script is not really what I want either because something has to make the script run. Then, there's the issue of a date not being filled in. I prefer a calculation but it seems darn near impossible to get this to work.

The Mad Jammer

And Then...there was nothing

[Fitch]

The script needs to be run in the calendar file, on a layout where the date field is accessible, or else the Insert calculated result ( the date range ) step won't work. Also, "the date range" here refers to a calc like this:

DateToText( start date ) & "..." & DateToText( end date )

If a date hasn't been filled in, you can test for that at the top of the script, and either display a "You're a bonehead" message and halt the script, or deal with it by filling in the missing date based on your own business rules.

If a calc is what you really want, take a look at the free Ranges Toolbox plugin, the excellent Troi Ranges plugin, or the mind-bending Smart Ranges technique.

[The Mad Jammer]

Tom,

Thanks. A calculation is really what I want because the number of days is then used to calculate the average number of days it takes to clear these cases. As I said, the performance of the indiviudals in the group is measured using this calculation.

I think I'll skip the "You're a bonehead" message, although it is applicable at times. for this scenario, the case is entered on the day it is received but it may not be resolved until a few days later.

Thanks for the leads to the 3rd-party stuff too.

The Mad Jammer

[comment]

In case you're still interested: this is really easy. All you need to do is change your point of view (see attached).

datestring.zip

[The Mad Jammer]

comment,

I looked at your attachement and I see what you are getting at. Your assumption is that everything is happening on that layout or in that file. This is not the case. For my puposes the calendar file is a related file. There is a Main file that it is related to and the Main file has the Start date and End date fields that comprise the date range. So I need to set the date range in the calendar file using the data in the Main file and then run a script on the calendar file to calculate the number of days with a 'W' in it and populate the field in the Main file with the number of records found. Your solution doesn't address that issue.

Besides, I'd like to show the numer of days on the Main file for each record. This is why i'd like a calculation instead of a script. How can I execute a script without some user action? And what if the script is run every time the record is selected or left? thats a lot of script runs just to click past a bunch of records to get to the one you want if iot happens to be at the end of the file.

The Mad Jammer

[comment]

Again, all you need is a bit of imagination:

First, there are no scripts in my file, other than the initial setup of a new string, and the modification thereof.

Second, it is only a demo of how to handle the string.

Here you have a Main file related to the string file. In the Main file, I have duplicated the calculation that returns the type of the date. You can do the same for the range/count calculations. In fact, you can move the entire functionality of the string file into the Main file.

datesonstring.zip

[The Mad Jammer]

comment,

Your solution will take a little more time for me to digest. In the meantime I produced a solution using scripts in both files to calculate and set a variable in the main file with the number of work days. Here's how I got it to work.

In the calendar file I defined 2 global variables. One holds the date range of interest and the other is a count of the work days found.

In the Main File I created this script

Set Field["cal::gDateRange", "DateRange"]

Freeze Window

Perform Script[sub-scripts, External:"Calendarfile.fp5"]

Refresh Window[bring to front]

Set Field["Workdays", "cal::gWorkDayCount"]

In the calendar file I have this script

Enter Browse Mode[]

Show All Records

Sort[Restore,No Dialog]

Enter Find Mode[]

InsertCalculatedResult["CalDate", "gDateRange"]

InsertCalcualtedResult["CalDay", "W"]

Perform Find[]

Set Field["gWorkDayCount", "Status(CurrentFoundCount)"]

I have a button on the main file that executes the first script and populates the work day varaiable.

This is perhaps a better solution because the only time I care about the number of days is when the client prints a report calculating the average number of days from the start date to the end date. So when they print the report I can run the scripts to reset the work days for all the records and then print the report based on the new numbers.

However, I would like to delve into your solution a bit more deeply and try to really figure out how to implement that into the application. I believe it would be faster than the solution I currently have.

Thanks again,

The Mad Jammer

[The Mad Jammer]

comment,

In your example, how did you tie a date to the position in the datestring? I don't see it.

The Mad Jammer

[comment]

AnyDate - StringStartDate + 1

will return the position of the character in the string associated with AnyDate.

Then, using the Middle() function, you can refer to that character.

Or perhaps I don't understand your question.

[The Mad Jammer]

What I mean is how does one know that 7/1/2004 is mapped to position 1 in datestring? I don't see where this relationship is defined. There must be somewhere that links stringStartDate with the position it refers to in datestring. Am I clear or do I need to explain myself betterer? This is the only thing I don't get about it.

You are correct that I could use the datestring as a global in my application and then generate calculated work days using the date fields I already have defined for begin and end dates. And I like the field at the bottom that changes the day type. That would have to be in the application for my users to mark holidays that they get to take off.

The Mad Jammer

[comment]

I am still not sure I understand your difficulty.

No relationship is required to read the string. All it takes is simple counting:

First you count how many days have elapsed since the StringStartDate; then you count the same number of characters from the beginning of the string, and there's your corresponding character. It's so simple you could do that without a computer - especially the second part.

Example:

Here's a string that begins on January 1:

"abcdefghijklmnoprstuvz"

Question: what is January 15?

Let's count: "abcde(5)fghij(10)klmno(15)..."

Answer: January 15 = "o".

Now take the formula

AnyDate - StringStartDate + 1:

January 15 - January 1 + 1 = 15

and

Middle("abcdefghijklmnoprstuvz", 15, 1) = "o"

I hope that makes it clear.

[The Mad Jammer]

comment,

I understand your logic now. I came to me on the ride home last night. I realized that stringStartDate was the anchor of the logic and that it would always be the first position of the calendar because thats what the calendar is promulgated on. So it all makes much more sense now. The only real issue is that several of the calculated fields used in your solution will be propogated throughout all the records in this file, which is fairly sizable. But it beats the heck out of the scripting I did.

To be sure I learned alot from both the scripting and developing a clever way to build what is essentially an array and then ranging over the individual element of the array. Thanks for taking the time to show me this solution. Its really quite good.

Have a beer on me!

The Mad Jammer

[comment]

Good. I told you all you needed was to look at it from another angle - and that's often the most difficult thing to do.

I wouldn't worry about the calculations speedwise. FMP is very fast with text manipulations. But perhaps you should make the results permanent by using auto-enter, or even a script, so that future changes in the string will not affect finished records. Depends on how you intend to work in your Main file, I think.

And thanks for the beer!