Well, it looks as if the problem is that pattern count only counts for a specific rocord not for all the records in the table.
What i did to fix this was create a new field in "Leave Records_LS" call " block 1" and put the pattern count in there [PatternCount ( Leave Records_LS::Time Blocks Leave Falls in ; "1")]
Then in the Leave sheet for "Allocated block 1" i have the formula [sum(block1)]
This seems to do the trick
I wounder however is their a better, more efficent, cleaner way i could of done this? rather than adding fields and using formulas? I can't see how i owuld do it with a relaiton ship.
I'm all ears for ideas.
Unless it is an aggregate function like , sum, count, average etc. All functions that have a reference to a related table in their parameters will refer only to the fields of the first related record from that table, not all the related records.
PatternCount ( List ( RelatedTable::Field ) ; "searchtext" )
can be used to check for the existance of "searchtext" within Field in any of the related records from RelatedTable.
you may want to use:
ValueCount ( FilterValues ( List ( RelatedTable::Field ) ; "searchtext" ) )
Filtervalues will return "searchtext" if the text in Field = "searchtext" instead of contains searchtext. ValueCount or IsEmpty can be used then to produce a value you can use in a case or IF function (or If script step) to produce the result you want if one of the related records has the specified text.
I tried: ValueCount ( FilterValues ( List ( Leave Records_LS::Time Blocks Leave Falls in ) ;"1") ) and it works grea thanks! BUT
BUT now i need to find a way to NOT count records where "1" is checked and the "Type of Leave Requested" field = "Annual" and the "Approval status field" does not equal "approved"
For something like that, I suggest setting up a calculation field in the related table such as:
( ( Time Blocks Leave Falls in ≠ 1 ) Or
( Approval Status ≠ "approved ) AND
( Type of Leave Requested = "Annual" )
Select number as the return type.
Check the results of this expression. It should return 1 (True) for all the records you want to count and 0 (false) for those you don't want to count.
You can then define a summary field as the "total of" this field in the Leave Records table or you can define a calculation field with Sum ( ) in the parent table to total up all records were this is the case. With the summary field, you can place it directly on a layout based on your parent table and it will report the total of all related records where this calcualtion returns a 1.