Jump to content

Recommended Posts

I suggest you post your date question separately, as it is unlikely that anyone else in the future will be interested in this particular combination of problems.

When you do, please clarify if it is possible for a shift to span more than 2 periods - e.g. start at 7:00 PM and end at 9:00 AM the following day.

Also, please update your profile to reflect your version and OS so that we know what you can use.


Link to post
Share on other sites
  • Newbies

Good day. I am in the board of a voluntary red-cross organization and work on the project of creating a database to summarize all relevant information into one platform. In the creation of the database I am struggling on the following issue:

One part of the database is to record the medical services the organization offers at events. On those events we work in different shifts. Displaying those shifts in a portal is no problem. Now the part where I am struggling: We distinguish between "Day-Hours" (shifts between 8.00 AM and 7.59 PM) and "Night-Hours" (shifts between 8.00 PM and 7.59 AM) for our invoicing rates. So, when a shift for example lasts from 6.00PM until 11.00PM I need Filemaker to automatically calculate how many "Day-Hours" and "Night-Hours" are within this shift - In this case the result would need to show "2" in the field for "Day-Hours" and "3" in the field for "Night-Hours". There are also shifts possible that will last until the next day, e.g. shift 1 from 2.00 PM to 6.00 PM, shift 2 from 6.00PM to 10.00 PM and shift 3 from 10.00PM to 2.00AM. 

How do I need to set up the calculation (or script), so that Filemaker automatically calculates those values?

Link to post
Share on other sites
46 minutes ago, comment said:

please clarify if it is possible for a shift to span more than 2 periods - e.g. start at 7:00 PM and end at 9:00 AM the following day.

I don't think my question has been answered. To put it more simply, can a shift last more than 12 hours?


Link to post
Share on other sites
  • Newbies

Sorry for misunderstanding. No, a shift cannot last more than 12 hours. The maximum of a shift is defined to 8 hours (guidelines and regulation of the organization)

Link to post
Share on other sites
19 minutes ago, Alan85 said:

a shift cannot last more than 12 hours.

Ah, good. Then I think you can calculate the day hours as =

Let ( [
adjEndTime = EndTime + 86400 * ( EndTime ≤ StartTime ) ;
dayStart = Time ( 8 ; 0 ; 0 ) ;
nightStart =  Time ( 20 ; 0 ; 0 ) ;

dayOverlap = Case ( 
StartTime < nightStart ; 
Max ( Min ( adjEndTime ; nightStart ) - Max ( StartTime ; dayStart ) ; 0 ) ;
Max ( Min ( adjEndTime ; nightStart + 86400 ) - Max ( StartTime ; dayStart + 86400 ) ; 0 )
] ;
dayOverlap / 3600 )

and night hours as = 

Let ( [
adjEndTime = EndTime + 86400 * ( EndTime ≤ StartTime ) ;
dayStart = Time ( 8 ; 0 ; 0 ) ;
nightStart =  Time ( 20 ; 0 ; 0 ) ;

nightOverlap = Case ( 
StartTime < dayStart ; 
Max ( Min ( adjEndTime ; dayStart ) - Max ( StartTime ; Time ( 0 ; 0 ; 0 ) ) ; 0 ) ;
Max ( Min ( adjEndTime ; dayStart + 86400 ) - Max ( StartTime ; nightStart ) ; 0 ) 
] ;
nightOverlap / 3600 )

where StartTime and EndTime are Time fields storing the start and end times of a shift. The results here are of type Number. If you want actual times instead of decimal hours, then do not divide by 3600 at the end and set the result type to Time .


  • Like 1
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By milky
      Hi All,
      Just had a look at the new Add-ons in Fm19.
      Is there a way to make the timer work in reverse.
      i.e. Start from a field (Timer::TimeLength) that a user can input, so that the specified time of say 30:00 will run down to 0:00
      Any help would be appreciated.
    • By BostonFM
      I am trying to script an import of three worksheets in the same Excel file. The 3 geographical regions worksheets are not structured the same, i.e. field name rows are not in the same place and are named differently.
      The script runs but stops to ask which worksheet to process.
      How do I create a variable/parameter in the script for it to proceed sequentially from worksheet 1-3 without stopping?
    • By SupportGuys89
      Hello Fellow FM'ers!
      We have been testing Linux and everything has been great, but we are having issues with the authentication via web direct using Windows Federated Services. We followed the steps in the available documentation "Addendum4_ADFS.pdf" and also used "ExtendOAuth_v1.pdf" to get the AD-FS rules configured as well as the necessary admin console requirements (Client Key, Shared Key etc...) We do see the following on attempting to login to our solution:

      And when we click on the AD FS Option we do receive a login prompt from our Federated Services:

      However, even with the correct credentials we still see the following error message:

      We have reviewed the logs within AD-FS and no errors are reported and the credentials are passed as expected. Within our solution and Active Directory, we also ensured the user is in the correct group to access our solution via webdirect. I will gladly provide more details into the configuration of AD-FS or FM Admin Console, but these all match the documentation provided by Claris. 
      We went a bit further and got an inhouse developer to take a look and test the flow and he was able to confirm the following:
      Our Endpoints are setup correctly and can be viewed internally @"https://INTERNALADFS/adfs/.well-known/openid-configuration" Initial request  was tested with "https://oidcdebugger.com/"  Postman was then used to test once we received a response. Decoding the jwt received with "https://jwt.ms/"
        The JWT had a unique_name, an email, and a groups array with one of the values in that group array being the group that is set up in the Filemaker solution.   With that said, we are not sure what the next step would be to test this as we appear to be seeing all the correct information in our testing yet we still are not being "authenticated" in our solution. I have also opened a ticket with FIleMaker and no luck as of yet. Any insight would be greatly appreciated!  
    • By Macastronomer
      Thank you in advance...
      We have a serialized field that has a value of FL2021-0001 and increments by +1. 
      I created a script that makes a duplicate record for reasons that don't matter here... but at this point I need to reset the serialized field to the next correct number. I do this with the Set Next Value function as such: 
      Set Next Serial Value [TableFL::SerializedField ; Max ( TableFL::SerializedField ) + 1 ]
      The problem is it sets the next value to 20210002 instead of FL2021-0002.
      Can I read this as a string and increment it or is the return always going to be a strict numeric value?
      Thanks again.
    • By Deepak Kumar
      Hello Everyone,
      I would like to check what are issues when FileMaker Pro and Server 19 are installed in Windows Server 2012 R2?
  • Who Viewed the Topic

    12 members have viewed this topic:
    24u_testers  wedgeman  ArthurLaurent  No_access  -Queue-  Will Xu  _ian  Lee Smith  Ocean West  bcooney  doughemi  EdithTruitt 
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.