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:
- the script looks at what shift a leave record falls
- performs a find in the "Personnel" database for that shift (the shift for the date the leave request falls on)
- Counts all the records found in "Personnel" after the Find
- 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")
- 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!