6 Replies Latest reply on Jan 17, 2016 10:20 AM by mikebeargie

# Calculation Help!  Let function? Custom Function?

I need some advice/help for some calculations fields.

I have 3 fields...

Hours (value of 16)

HoursPerDay = Hours/DaysUntilDeadline (value of 1.45)

Then I want 35 calculation fields that populate the HoursPerDay.  Something like...

WorkloadHours Day 01 = 1.45    (HoursPerDay)

WorkloadHours Day 05 = 1.45    etc

....

That would be easy enough but I have have to EXCLUDE Saturday, Sundays, and Public Holidays (Public Holidays would be stored in another table that list the holiday date).  Each of those WorkloadHour days will correspond to a global date field (gDate 01, gDate 02, gDate03, etc).

So for example, let's assume the following dates and let's assume 1/29 is a public holiday...

gDate01 = 1/16/16 (Sat)

gDate02 = 1/17/16 (Sun)

gDate03 = 1/18/16 (Mon)

gDate04 = 1/19/16 (Tue)

gDate05 = 1/20/16 (Wed)

gDate06 = 1/21/16 (Thr)

gDate07 = 1/22/16 (Fri)

gDate08 = 1/23/16 (Sat)

gDate09 = 1/24/16 (Sun)

gDate10 = 1/25/16 (Mon)

gDate11 = 1/26/16 (Tue)  ** HOLIDAY

gDate12 = 1/27/16 (Wed)

gDate13 = 1/28/16 (Thr)

gDate14 = 1/29/16 (Fri)

gDate15 = 1/30/16 (Sat)

gDate16 = 1/31/16 (Sun)

gDate17 = 2/1/16 (Mon)

gDate19 = 2/2/16 (Tue)

gDate20 = 2/3/16 (Wed)

gDate21 = 2/4/16 (Thr)

gDate22 = 2/5/16 (Fri)

the desired result would be

etc etc

Any ideas or advice on how I can achieve this?    Thanks!

• ###### 1. Re: Calculation Help!  Let function? Custom Function?

rather than creating so many fields, why don't you just create a "dates" table that you clear and repopulate on the fly with 30 records.

To repopulate the table on demand:

Go To Layout [ dates ]

Show All Records

Delete All Records [ no dialog ]

Set Variable [ \$date ; "1/16/2016" ]

Set Variable [ \$end ; "2/5/2016" ]

Set Variable [ \$\$holidaylist ; List ( "12/25/2016" ; "7/4/2016" ; etc... ) ]

Loop

Set Variable [ \$i ; \$i + 1 ]

New Record/Request

Set Field [ dates::theDate ; \$date ]

Set Field [ dates::DaysUntilDeadline ; \$i ]

Exit Loop If [ \$date = \$end OR \$i = 31 ]  //Exit if the end date OR hard limit is reached

Set Variable [ \$date ; \$date + 1 ]

End Loop

Go To Layout [ original layout ]

Then it's just a matter of having a SINGLE calculation field for WorkLoadHours in your dates table:

Case(

DayOfWeek ( theDate ) = 1 or DayOfWeek ( theDate ) = 7 ; 0 ; // WEEKEND

DayHolidayFlag ( theDate ) = 1 ; 0 ; //HOLIDAY

Not IsEmpty ( theDate ) ; if ( DaysUntilDeadline >1 ; HoursPerDay ; 0 ) ; //WORK DAY

0 //UNKNOWN

)

I did use a custom function there ( DayHolidayFlag() ) from here: FileMaker Custom Function:DateHolidayFlag ( datefield )

It's easy enough you should understand what it does, feed it a list of dates as \$\$holidaylist, which I included in the script above.

So in summary, ditch the 30+ global date fields and just create a simple dates table. Set up some scripts to populate your holiday list, and dates table. Then setup some calculations to check for if it's a weekend, holiday, or work day.

• ###### 2. Re: Calculation Help!  Let function? Custom Function?

I agree with Mike Beargie.  Simplify by having a DATE table.

Also, you don't mention what you are doing with the dates.  Would it be helpful to have a calendar view?  If so, there are several solutions for implementing a calendar layout in FMP.

• ###### 3. Re: Calculation Help!  Let function? Custom Function?

I often create a date table in our solutions. 20 years of dates are just 7300 records and you can do wonders with such a table.

• ###### 4. Re: Calculation Help!  Let function? Custom Function?

Thanks Mike.

I'm creating all those date fields so they will appear in a portal (attached).

• ###### 5. Re: Calculation Help!  Let function? Custom Function?

I also suppose I wanted 35 calc fields so I didn't have to run a script every time data was changed....A new row created, hours amounts change frequently, etc.  Do you still recommend create a date table?

• ###### 6. Re: Calculation Help!  Let function? Custom Function?

Yes.