AnsweredAssumed Answered

script count if help

Question asked by JoshPoplawski on Nov 2, 2011
Latest reply on Nov 2, 2011 by philmodjunk

Title

script count if help

Post

 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!

 

Outcomes