4 Replies Latest reply on Nov 21, 2011 9:12 AM by philmodjunk

    PatternCount HELP

    JoshPoplawski

      Title

      PatternCount HELP

      Post

      Ok, pattern count is only counting one particular record from a table. Let me explain.

      I have a "Leave Records_LS" table where leave requests are stored. each leave request has a field (called, "Time Blocks Leave Falls in") with 4 check boxes. "1" check box to requst leave from 0700 to 1300, "2" check box for 13 to 1900, "3" check box for 19 to 0100 and "4" for 01 to 0700 then next day. 

      Options "1", "2", "3" and "4" are all set in a value list under the "time block" field.

      On a seperate layout (Called "Leave Sheet" ) I have a field "Allocated block 1". What i want is for "Allocated block 1" to give me the number of all the records where the "1" check box has been checked (keep in mind a requester may check 1 to all the check boxes 1,2,3,and 4 in any combinaiton). 

      "Leave Records_LS" and "Leave Sheet" are related by date.

      I have done: PatternCount ( Leave Records_LS::Time Blocks Leave Falls in ; "1" )

      however only the first record in the Leave Records_LS for the records that match the Leqave Sheets date is coutned. the pattern coutn doesn't aknowledge the other records at all. I dont' know what the problem is. please help. 

        • 1. Re: PatternCount HELP
          JoshPoplawski

           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.

          • 2. Re: PatternCount HELP
            philmodjunk

            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.

            • 3. Re: PatternCount HELP
              JoshPoplawski

               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"

              • 4. Re: PatternCount HELP
                philmodjunk

                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.