5 Replies Latest reply on Nov 7, 2008 2:41 PM by rowing190

    Selecting 1 contact per company from a found set

    rowing190

      Title

      Selecting 1 contact per company from a found set

      Post

      I am attempting to write a script that will allow me to constrain a found set of records to only 1 contact per company. Unfortunately, the script that I have composed is selecting every other record. Here is the script:

       

      Sort Records [company id]

      GoToRecord/Request/Page [First]

      Loop

      GoToRecord/Request/Page [Next;Exit after last]

      If [company id = company id]

      Omit Record

      End if

      End Loop

      Exit Script

       

       

      I am new to Filemaker Pro and writing scripts. I assume that I must be making a mistake in the "if statement". If anyone has a suggestion on how to correct my script, that would be great.

        • 1. Re: Selecting 1 contact per company from a found set
          TSGal

          rowiing190:

           

          Thank you for your post.

           

          Let's go through your post to point out what is occurring.

           

          Sorting the records and going to the first record is fine.

           

          Now, we enter the loop....

           

          We go to the next record (record 2) and check to see if the company id equals the company id.  That will always be true, so the record is omitted leaving us at the third record, and we loop back to the top of the loop, where we skip to the fourth record.  I think you see the pattern.

           

          Let me first give you a different script, and then the explanation:

           

          Sort Records [Company ID]

          Go To Record/Request/Page [First]

          Set Variable [$co; "" ]

          Loop

             If [$co <> Company ID]

                Set Variable [$co; Company ID]

                Go to Record/Request/Page [Next; Exit after last]

             Else

                Omit Record

             End If

          End Loop

           

          =======

           

          Like before, we sort the table and go to the first record.  We initialize a variable, $co, that will be used to keep track of the Company ID field.

           

          Now we enter the loop.

           

          We evaluate the $co variable with the Company ID.  If not equals (which will happen on the first record0, then we set the variable $co to the Company ID.  We do not want to omit the record, so we skip to the next record.

           

          If the $co variable does equal the Company ID, then we know we have a duplicate record.  Therefore, we omit the record which puts us automatically at the next record.

           

          The problem with this loop is that if you get to the last record and omit it, then it puts you back one record, and eventually, you will omit the last occurrence of that Customer ID.

           

          There are several ways to get around this problem, but I'd rather keep it simple for understanding purposes.

           

          Let's create an additional text field, "FIRST", and include this on the layout.

           

          The script can now be:

           

           

          Sort Records [Company ID]

          Go To Record/Request/Page [First]

          Set Variable [$co; "" ]

          Loop

             If [$co <> Company ID]

                Set Variable [$co; Company ID]

                Set Field [FIRST; "Yes"

             End If

             Go to Record/Request/Page [Next; Exit after last]

          End Loop

          Perform Find [Restore; FIRST="Yes" ]

           

          This is very similar to the previous script, but this time when we enter the loop, we see if the variable $co does not equal Company ID, and if not, we set the variable $co to Company ID, and we replace the value in the FIRST field with "Yes".  This means, this is the first occurrence of the Customer ID.  We then skip to the next record.  If the variable $co equals Company ID, then we do nothing except skip to the next record.

           

          Once all the records are evaluated, we find only those records where FIRST = "Yes".

           

          If you are going to run this script several times, then you may need to start with nothing in the field FIRST.  Therefore you would add to the beginning of the scriipt:

           

          Replace Field Contents [No dialog; FIRST; "" ]

           

          I hope this helps.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

           

          • 2. Re: Selecting 1 contact per company from a found set
            ZheWiz
              

            If there aren't too many uniques, I do this:

             

            SetVariable ($FoundCompanies ; "" )

            GoTo Record/Request/Page(First)

            Loop

            If PatternCount ( "|" & $FoundCompanies & "|" ; "|" & company id & "|" )

            SetVarialbe ($RecordNum ; Get ( RecordNumber ) )

            omit record

            Exit Loop If $RecordNum <> Get ( RecordNumber ) 

            else

            SetVariable ( $FoundCompanies ; $FoundCompanies & company id & "|" ) 

            Goto record/request/page (Next)

            end if

            end loop

             

            If you do have a "lot" of unique records then $FoundCompanies will get rather large and your method will work with a slight adjustment.  The above doesn't require a sort first, so is typically faster.

             

             

            Sort Records [company id]

            GoToRecord/Request/Page [First]

            SetVariable($CompanyID ; "" )

            Loop

            If [company id = $CompanyID]

            Omit Record //this automatically advances you to the next record.

            else

            SetVariable ( $CompanyID ; company id )

            GoToRecord/Request/Page [Next]

            End if

            End Loop

             

            I haven't run the above thru a syntax checker...so there may be small errors, but the logic is sound.

             

            Zhe Wiz


            • 3. Re: Selecting 1 contact per company from a found set
              ZheWiz
                

              Beaten to the punch. :-)

               

              Zhe Wiz

              • 4. Re: Selecting 1 contact per company from a found set
                TSGal

                Thank you, ZheWiz!  Another excellent response.

                 

                rowing190 - This should definitely get you pointed in the right direction.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Selecting 1 contact per company from a found set
                  rowing190
                    

                  Thank you so much for all of your help. I was able to get the second second script suggestion of Zhe Wiz to work perfeclty.

                   

                  rowign190