Estimated Report Date

[schooltechie]

I am trying to set up estimated dates of when a Progress Report should be done for Special Education students. The reports are done approximately every 63 days, with the first report due after a "Meeting Date", and each of the other dates using the actual report date before it, but should NOT include the Christmas holiday break (Dec. 24th to Jan. 2) or summer vacation (June 15th to Sept. 1), but needs to continue uninterrupted after these dates. I have decided not to include other holidays, etc., as they are not as long in duration. At this point, I have 4 fields set up for these estimated dates, but can't figure out how to exclude the above mentioned dates. Here's what I have:

Est Prog A1 Date = Meeting Date + 63

Est Prog A2 Date = Goal 1 first progress date + 63

Est Prog A3 Date = Goal 1 second progress date + 63

Est Prog A4 Date = Goal 1 third progress date + 63

My question is - can I add something to these calculations that would omit those dates?

Thanks!

Becky

[Kundinger]

Hi Becky,

In my many years of working with educational, governmental, and business organizations... I have NOT been able to easily 'manipulate' FileMaker into managing dates the way you're requesting.

For example...

- educational organizations want only school days available for scheduling

- governmental and business organizations want days calculated based on fiscal periods

- organizations that are heavily involved in project management deal with resource availability schedules

Trying to create calculations to meet the needs of any of these situations can be daunting... to say the least.

Here is what I have found to work the best... create a separate dates file with a list of available dates... lookup todays date and add a specific number to it and display the resulting date. This is very common with school information systems (such as WinSchool, PowerSchool, MacSchool, etc.). They require the setup of a calendar before they can schedule classes.

How to do it in FMP...

Create a 'DateList' file that has ALL dates for a year (one record for each date). Have a 'mark' field that you use to indicate those dates that are available (work days). Perform a find to show only available work days. Perform a calc on these records to number them. Use a 'lookup' relation to get the date number for the 'current' day. Use a calc to add 63 days to this. Then do a 'lookup' to see what that future date will be.

I'm explaining this with the assumption that you understand FMP files, fields, relations, and lookups. If you need assistance, please contact me through this BBS or directly via e-mail. I could create a simple sample file to show you how to accomplish this.

Good Luck!

Bob Kundinger

[email protected]

[Vaughan]

Good advice Bob.

I've never set this up, but was thinking that doing it the other way -- making a file of non-work days -- might also work. Use an implementation of Smart Ranges as the relationship, based on a range of "start date + number of working days" where in Becky's case the "number of working days" would be 63. The count of related records are the non-work days that have to be added on to the final date.

I haven't done this, it's just a mental exercise at this point, but I think it might work. The trick is keeping that database of non-working days up to date.

[Kundinger]

Hi Vaughan,

Aren't mental exercises fun? That's what makes creating solutions is all about... especially in FileMaker.

Your idea of NON-working days should work as well. My experience with educational organizations is that they typically set up the number of and the specific work days before the school year begins. Governmental and business organizations typically define their non-work days for human resource management purposes.

Either way should work... as long as, (as you mentioned) the dates are kept current.

I sure hope Becky understands my suggestions. Some FMForum Viewers are knowledgeable and good at 'Mental Exercises'... some are not... I hope I didn't lose her or anyone else.

Bob Kundinger

[email protected]

[schooltechie]

Thanks Bob and Vaughan!

You have not lost me, and the wheels are spinning already. After looking at my problem "outside the box", I realized that there is the possibility of an IEP meeting taking place during summer break and I will have to check with my supervisor to see if the 63 days is in affect in this situation, also. If so, it would probably be better if using just the non-school days would work, as there would be fewer of them to set up. If I need more help, I'll be sure to check in with you both again. Thanks!

Becky

[BobWeaver]

Maybe you could find something useful in my working day calculations sample in the samples forum. It implements what Vaughan and Bob mentioned. One file has a list of non-work days and allows Calculations of the form:

NewDate = OldDate + DurationInDays

where the results are always valid workdays.

There are two versions of these sample files. The earlier one is simpler and probably sufficient for what you need to do.

Original Version

Deluxe Version