6 Replies Latest reply on Nov 2, 2011 1:36 PM by philmodjunk

    script count if help



      script count if help


       Alright, spent all day yesterday trying to figure this out. I'm using FileMaker Pro 11 (Not Advanced) Here's what's up:

      I have a database called "Personnel" where each record is an individual employee. Eeach field has information we need to know about the employee includeing their work Shift (A,B, or C each one is a different 24 hour work period)

      Their are two parts to this. The first is in a different database called "Leave" I need to have an automated count of how many people are assigned to the shift that falls on the the date that a leave request is made. This number needs to become fixed after the the date of that leave is passed but update up untill that time.

      I understand their is no CountIf function in FMP11. What I thought I would do is create a script where:

      1.  the script looks at what shift a leave record falls
      2. performs a find in the "Personnel" database for that shift (the shift for the date the leave request falls on)
      3. Counts all the records found in "Personnel" after the Find
      4. Puts that Count number into a field in the "Leave" data base called "Total Allocated" (this field would be placed in a sub summar based on the "Leave Start Date")
      5. Show all records and perform another script on the "Leave" database.

      So far my script isn't working: Show All Records --> If [Leave::Shift for Leave Start Date="A"]-->Perform Find[Restore]-->Insert Calculated Result[Select;Leave::Total Allocated; Count (Personnel::Shift)] -->Else If[Leave::Shift for Leave Start Date = "B"]-->Perform Find[Restore]-->Insert Calculated Result[Select;Leave::Total Allocated; Count (Personnel::Shift)] -->Else If[Leave::Shift for Leave Start Date = "C"]-->Perform Find[Restore]-->Insert Calculated Result[Select;Leave::Total Allocated; Count (Personnel::Shift)] -->Else --> Insert text [Select; Leave::Total Allocated; "Error???"]--> End If

      What happens is i end up with a "leave " databse showing only a poriton of the records with the number 1 in the first record in the "Allocated Total" field.

      The second part of this is that: we have a block system for requesting leave. Their are four time blocks each for a six hour period. On the leave sheet the time blocks used for the leave is entered. so what i need to do is have a formula for each block where it counts the number of times that block occures and subtracts that number from the total allocated.

      somthing like: Case(Leave::time block = "1" or "12" or "123" or "1234" then count and subtract coutn from "total allocated") etc. I know that's not a real formula but my computer is giving me problems right now and it illistrates the point. thanks to any one for their help!


        • 1. Re: script count if help

          I'm going to assume that both Leave and Personnel records identify a shift with exactly the same data. (Letters A, B, C?)

          #Script starts from Leave layout.
          Set Variable [ $Shift ; Leave::Shift]
          Go To layout [Personnel]
          Enter Find Mode [] //clear the pause check box
          Set Field [Personnel::Shift ; $Shift]
          Set Error Capture [on]
          Perform Find []
          Set Field [ Leave::gTotalAllocated ; Get ( FoundCount ) ]
          Go to Layout [original layout]

          gTotalAllocated would need to be a global field (a storage option you select in field options) in order for this to work. I am also assuming that both tables are defined in the same file.

          This script takes the shift specified in the current leave record and returns the count of Personnel with that same shift assignment, putting it ina global field so that the same total is visible from every record. If you want, you can use a non global field so that the count returned is specific to the current leave request. In that case, you'd put the count in a variable, return to your original layout and then use set field to enter the data from the variable to this field.

          The second part of this is...

          Before we factor this in, please describe the relationship between a time block and how this detail is entered in a leave request record.

          (this field would be placed in a sub summar based on the "Leave Start Date")

          I'm afraid that doesn't make much sense to me. Normally, such a 'total' field would be a summary field that totals a field in the leave table and your sub summary is then used with this summary field to compute a sub total. I don't see how a sub summary part fits in here...

          • 2. Re: script count if help

            Our Battalion Chiefs (BC)  approve leave based on a first come first serve basis. leave will be made electronic. I have  a layout called "Master Leave Sheet" that displays each record as an individual leave request with a field for the BC to check approved or not aproved. The Layout sorts records in the following way:

            1. The date Leave is requested for (asending)

            2. Weather the person requesting is Contract (regular Firefighter) or Management (an officer) [their are only 2 leave slots available for management and 14 for contract. i have a field that uses a calculation to determine if it's value should be "management" or "contract" based on the rank of the requester.]

            3. the type of leave being requested (sick, annual, etc.)

            4. weather or not the leave request has been approved or not. Only the first 14 requesters will get thier leave approved. If the BC denies one of the top 14 leave then it goes to the next person on the list. they want the top 14 who are approved at the top of the list and those denied at the bottom.

            5. The time stamp

            I have used sub summary fields to make a distinkt line between records of different dates requested so those groups are easily focused on form the list. I have a "Notes for Date" field in the sub summary b/c the BC's wanted to be able to make notes about that particular date. Since the Allocated number is reffering to the number of FF available to work on a sertain date and that is borken down to how many are avaialable in each time block i planned on putting the field in the sub summary with the notes.

            the total allocated is the number of personnel assigned to a shift, the allocated block 1 (or 2,3, etc) are the people available to work after those on leave for that time block on that date are subtracted from the total allocated.

            • 3. Re: script count if help

               Also i don't think your script will work b/c the Personnel and the Leave are seperate Databases/Tables not seperate Layouts.

              • 4. Re: script count if help

                The same method works with separate files (why make them separate?) , but you have to pass the data from file to file via script parameters instead of vairables or you add an external data source based occurrence of one of these two tables in the other file. If you use the second option, you can use the script as written and you'll find it easier to work with these two tables of information when you need to work with them in combination like this.

                That said, I don't think a find script like this the way to go here as you need different totals for different dates shown on the same report.

                It looks like a relationship between the Leaves table and the Personnel table can do the job with little or no scripting.

                But I'm still not clear on how "time blocks" work here in relationship to assigned shifts and permitted leave. Can the time block requested affect whether it's possible to grant leave to the person requesting it or is this just an additional detail describing the requested leave?


                • 5. Re: script count if help

                   we have somthing like 112 people assigned to any one shift. we must have a minimum of 86 personnel working at any given time inorder to provide our services. You can only take leave in 6 hour blocks (0700 to 1300, 1300 to 1900, 1900 to 0100, 0100 to 0700). The BC's need to know how many people will be working in each quarter inorder to determine if they have enough people in that quarter on that day or if they need to hire someone back for OT.

                  So, Total Allocated=all employees currently assigned to a particular shift (such as A shift)

                  Block 1 Allocated = Total Allocated - the number of people on some type of leave between 0700 and 1300 (which the requester marks on the sheet as "1" in the time block field. so the person could be on leave for time blocks "1", "1,2", "1,2,3" or "1,2,3,4" regardless of how many blocks of leave for that day the FF is requesting if they arn't going to be at work from 0700 to 1300 (if "1" is in any of the block options) then he needs to be subtracted form the total allocated to get the block 1 allocated.

                  • 6. Re: script count if help

                    I'm not sure how the time blocks requested is to be shown on the screen when you list leave requests. I sounds like you want a break down that reports number of people allocated for each 4th of a shift, but your records are structured as one leave request for any combination of time blocks for that one shift for one person making that request. Frankly I find myself picturing two different reports, one that lists leave requests and one that summarizes allocated personnel broken down by date, shift and time block.

                    Here's a relatonship that will count all people working a specific shift so that a leave request for that shift can use that count to help compute the number of people allocated. (This ignores the time block issue for the time being.)

                    Leaves::Shift = Personnel::Shift

                    If you define a summary field, sPersonCount as the "count of" any field that is never blank in the Personnel table, such as a serial ID field, then you can place this summary field in a layout based on the leave table and it will report the total count of people who normally work that shift. Let's also assume that you have a field in the Leaves table that is empty unless the request is approved. If the leave request is approved, some value is entered into this field. (This can easily be controlled with a single value checkbox format.) Define a summary field in Leaves, sApprovedCount that computes the count of this approval status field.

                    Then this calculation computes the Total People assigned to a shift - the total approved leave requests:

                    Personnel::sPersonCount - GetSummary ( sApprovedCount ; Shift )

                    This can be defined as a calculation field in Leaves. It has several requirements that must be met in order for it to work:

                    All the approved leave requests for a given shift must be present in the current found set.

                    The records in this found set must be sorted by Shift.

                    Wtih this calculation, you can place the field in a sub summar part sorted by shift , sort the request by date, and  then also by shift and you should see the count of available personnel for each date broken down by shift.