8 Replies Latest reply on Sep 18, 2015 7:06 PM by raykennedy

    Getting the count of a related field with a condition

    raykennedy

      Looking to get a count of specific field based on conditions.

       

      Simple example
      Table 1 = Contacts

      Table 2 = Phone Numbers (related table)

       

      Table of Phone numbers has a field of phoneType ("Office","Cell","Fax" etc)

       

      I would like to count the number of related records with a condition that says if ( phoneType="Cell") or something like that and than take action based on what the count is. I don't need the count of all related records for that table, just the ones that match the condition.

       

      I know I can get the Count of related records using the Count() script and/or do a summary field. This will all be done on a script trigger of on modify on the drop down when the user changes the phoneType in a portal from the layout of the Contacts.

       

      My main question or obstacle is I am not sure how to incorporate the condition in the count, I only know how to count all related records.


      Any thoughts?

        • 1. Re: Getting the count of a related field with a condition
          erolst

          There is no CountIf(); but what you can do is

           

          ValueCount ( FilterValues ( List ( PhoneNumbers::type ) ; "Cell" ) )

          • 2. Re: Getting the count of a related field with a condition
            Mike_Mitchell

            Hello, Ray. There are at least three ways to accomplish this task I can think of off the top of my head:

             

            1) Create another TO to the child table. Create a new relationship that includes the filtering field on the child side, and use a global field on the parent side. Then, using either a script or an unstored calculation, you can retrieve a count of the related records via a summary field in the child table (using a Count).

             

            2) Use ExecuteSQL ( ) to pull the value when you need it. This works fine with a Script Trigger.

             

            3) Perform a script that opens a secondary window, goes to a layout based on the child TO, finds the records corresponding to the criteria you need (probably the parent key and the filtering field), and then return the total using Get ( FoundCount ). You can just close the extra window when you're done.

             

            4) Some other method I haven't thought of yet.   

             

            HTH

             

            Mike

             

            Edit: Or just use erolst's straightforward, simple method.  

            • 3. Re: Getting the count of a related field with a condition
              erolst

              Here's another one, with zero overhead:

               

              Create a one-row portal into PhoneNumbers, filter it by

               

              PhoneNumbers::type = "Cell"

               

              and put a text object into it that reads e.g.

               

              Cell: {{FoundCount}}

              • 4. Re: Getting the count of a related field with a condition
                dtcgnet

                Love it!

                 

                Name the one-row portal. Add a pulldown global field (e.g., GlobalPhoneType) with your phone types above the one-row portal which you've set up as erolst has indicated. When you select a value in the pulldown, trigger a script that sets a global variable, something like Set Variable $$PhoneType = GlobalPhoneType. Modify the portal so that it filters using $$PhoneType = PhoneNumbers::type. Then use a Refresh Portal step.

                 

                You'll have an easy way to get the count for any phone type simply by selecting a value.

                • 5. Re: Getting the count of a related field with a condition
                  raykennedy

                  Ok, I think this should do what I need it to do, I will post it here in case someone else inquires.. Great suggestions everyone. Mike Mitchell, will be playing around with those thee options as well, still trying to get a handle on the ins and outs of the relationships.

                   

                  Erolst, your quick code worked. Here is how it is being implemented. This may or may not be the most efficient or effective way to do this but it seems to work fairly well with my basic testing. Not using exact syntax, just illustrating what it is doing.

                   

                  Script Trigger "onModify" on a drop down menu in a portal record...

                   

                  If [ phoneType = "Cell" ]

                   

                       Set Variable [$CountPhoneType; Value: ValueCount ( FilterValues ( List ( phoneType ) ; "Cell" ) )

                   

                       If [  $CountPhoneType  > 1]

                            # CREATE ERROR MESSAGE, TEMPORARY USING DIALOG BOX

                            Show Custom Dialog [ ...... ERROR MESSAGE ETC .... ]

                          

                            # Reset Field back to " " or whatever the default value would be.

                            Set Field [  phoneType ; " "  ]

                   

                        End If

                   

                  End If


                  Thanks everyone for the various options. Hope this helps someone else at some point down the road. Feel free to comment to improve it or better solutions.

                  • 6. Re: Getting the count of a related field with a condition
                    dtcgnet

                    Portal filter formula: $$PhoneType = PhoneNumbers::type.

                    Portal is named: "TypeCountPortal"

                     

                    User clicks into field near the Phone Count... The field is GlobalPhoneType. It uses the value list associated with PhoneNumbers::type.

                    User pulls to "Cell". GlobalPhoneType is now equal to "Cell"

                     

                    On modify script fires since GlobalPhoneType has been modified. The script looks like:

                     

                    Set Variable [ $$PhoneType ; Value: GlobalPhoneType ]

                    # $$ is necessary so the variable doesn't disappear when the script is done running.

                    Refresh Portal "TypeCountPortal"

                    Exit Script

                     

                    That's the script. It will work for any phone type that is in your Phone Types list. If you're not yet in FM14, use Refresh Object instead of Refresh Portal. The count will be shown in the Cell: {{FoundCount}} text that erolst mentioned. Change "Cell: " to "<<$$PhoneType>>: " and it will all happen as soon as the user selects the type of phone for which they want a count.

                    • 7. Re: Getting the count of a related field with a condition
                      erolst

                      raykennedy wrote:

                      Script Trigger "onModify" on a drop down menu in a portal record...

                       

                      That code looks like you're not interested in the actual number of cell phone entries, but trying to prevent duplicate entries of type “cell" …?!

                      • 8. Re: Getting the count of a related field with a condition
                        raykennedy

                        erolst,
                        Yes, in that particular use the count is being used to prevent duplicate input. But getting the count based on the condition will help me out in other tasks. Not sure if there is better way to do it just for that purpose but there are other areas of my database where I would want to get the count and display or create an action based on the count.