7 Replies Latest reply on Nov 17, 2008 12:57 PM by rowing190

    nth select from found set

    rowing190

      Title

      nth select from found set

      Post

      I am a new FP9 user and this site has been wonderfully helpful in solving how to constrain a found set to one contact per company. In addition to limiting a mailing to only one contact per company in order to save on mailing costs, our marketing department many times with request a nth select - a request of all corporate communications contacts but select only  8 out of 10. I have been able only to create the most rudimentary script.

       

      Go to Record/Request/Page [First]

      Loop

      Omit Multiple Records [No dialog;1]

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

      End Loop

       

      This script will choose every other record, or the found set is constrained by 50%. If the Omit Multiple Records is changed to [No dialog; 2] then the found set is constrained by 33%. If [No dialog; 3], then by 25%. 

       

      This sort of accomplishes what I want, but not really - not if I want exactly 8 out of 10 found records. Is there a function that will work in the Omit Multiple Records script step that will allow me to select a percentage of the found set. I have looked and looked and not discovered one? Or is there a completely different approach to this that I am not taking?

       

      Thank you for your help. 

       

      rowing190

        • 1. Re: nth select from found set
          TSGal

          rowing:

           

          Thank you for your post.

           

          Yes, there is a way to select the number of records.  However, in your example, you are assuming there will always be 8 our of 10 records.

           

          Sort Records [No dialog; Restore]

          Go to Record/Request/Page [First]

           

          Set Variable [$co; "" ]

          Set Variable [$counter; 1 ]

           

          Loop

             If [$co <> Company ]

                Set Variable [$co; Company ]

                Set Variable [$counter; 1]

             End If

             If [$counter > 8 ]

                Omit Record

             Else

                 Set Variable [$counter; $counter + 1]

             End If

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

          End Loop

           

          ----------------------

           

          We first sort the records by the Company name and go to the first record.

           

          Next, we create two variables.  One is to keep track of the Company as we loop through the records, and the other is count a maximum of eight records for each Company.

           

          We enter the loop and evaluate the variable $co to the current Company.  If it is different (which it will be the first time), then we initialize the variable $co to the Company field and reset the counter to 1.

           

          Next, we evaluate the current counter value.  If it is greater than 8, then we want to omit the record.  If not, then we increment the counter by 1.  Then, we go to the next record and loop to the top.  For the second record, assuming the Company name is the same, the first If () function is false and we evaluate if the counter is greater than 8.  It is not (the value is 2) so we increment one more and go to the next record and loop back.  After the eighth occurrence, we see the value of the counter is 9, so the record is omitted.  When the Company name changes, the counter variable is reinitialized and we start again.

           

          I know there is a lot to understand here, but if you want me to expand on any topic or script step, let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: nth select from found set
            rowing190
              

            Please forgive me for not being clear. In this case I simply want 8 out of 10 contacts out of a found set of say 4,048 contacts - not 8 of 10 contacts per company. You are right that there will be times when our marketing department wants 9 out of 10 records or 7 out of 10 records.

             

            I did create a counter field and populate it. I assume that this will need to be done anew for each found set. I attempted to modify the script that you provided by omitting the company name variable, but I have not quite got it to work yet. I assume that this will be a simpler script since only 8 out of every 10 contacts are needed and not 8 out of 10 contacts at each company. 

             

            Again, thank you so much for your help. 

            • 3. Re: nth select from found set
              TSGal

              rowing190:

               

              Thanks for the clarification.

               

              The script is actually easier to create and can be modified as follows:

               

              Go to Record/Request/Page [First]

               

              Set Variable [$counter; 1]

               

              Loop

                 If [$counter > 10]

                    Set Variable [$counter; 1]

                 Else

                    If [$counter > 8]

                       Omit Record 

                    End If

                 End If

                 Set Variable [$counter; $counter + 1 ]

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

              End Loop

               

              -----------------

               

              We go to the first record, initialize a counter, and enter the loop.

               

              We first check to see if $counter is greater than 10.  If so, reinitialize $counter back to 1.  If it is not greater than 10, but it is greater than 8, then we want to omit the record.  In any case, we still increment the counter and go back to the top of the loop.

               

              If you want to change this to 7 out of 10 or 9 out of 10, just change the middle step where it says:

              If [$counter > 8]

              ... to the value you want.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: nth select from found set
                blackcat795
                  

                rowing190

                 

                Not clear on exactly what you trying to achieve here! think you going the long way round!

                If you only want to send a mailing to one contact in a company would your current setup not fail if a company has no contact name?

                If your skipping contacts how do you know what you've got?

                 

                I personally would put a radio button set in the portal for contact names (called for eg mail & no-mail,set default on create to no-mail).

                You go through your customer records & set the radio button to mail for each contact to send to.

                Script a search in your contacts for your (mail)entry, (All customers & contact=mail).

                This is then your print range, if there are multiple contacts for a customer that require a mail as defined by Marketing, then they just need to go to the contact file & select mail in the radio utton for the additional contact.

                Where there is no contact name for a customer, include this in you search ie customer & Is empty contact.

                 

                HTH

                Blackcat

                • 5. Re: nth select from found set
                  rowing190
                    

                  TSGAL:

                   

                  Thank you so much for your help. I was able to implement the script that you suggested to retrieve a nth select from a found set. I also attempted to implement the script that you suggested to select an nth select per company - for example, 8 records per company. I have not been sucessful with this script. Here is the script that you suggested:

                   

                  Sort Records [No dialog; Restore}

                  Go to Record/Request/Page [First]

                   

                  Set variable [$co; ""]

                  Set variable [$counter; 1]

                   

                  Loop

                     If [$co <> Company]

                         Set Variable [$co; company]

                         Set Variable [$counter;1]

                     End if

                     If [$counter > 8]

                        Omit Record

                     Else

                        Set variable [$counter;$counter + 1]

                     End If

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

                  End Loop

                   

                   

                  I am not certain what I am doing wrong with this script. I have tried and tried and cannot get it to work. I assume that I am not messing up the first step of the loop by making it If [$co < Company or $co > Company]. Otherwise I think that I am following your script faithfully. One question: I have been repopulating the counter field after I sort by company name. Is that possibly messing things up. 

                   

                  Thanks again for all of your help. 

                   

                  rowing190

                   

                   

                   

                   

                  • 6. Re: nth select from found set
                    TSGal

                    rowing190:

                     

                    Let me clear up some confusion first...

                     

                    The script step: If [$co <> Company] is correct.  It can also be written as:

                     

                    If [$co ≠ Company] 

                     

                    That is, I'm checking to see if $co is not equal to Company.  That means, a new Company is encountered, so we need to reinitialize $co to the Company name and reset the counter back to 1.  Does that make sense?

                     

                    Also, make sure the sorting in step one is also sorting by Company.  If not, then the script would not work properly because the Company name would change all the time, so the counter would be reset to 1 each time.

                     

                    If you think you are doing this correctly, let me know what kind of results you are getting.

                     

                    TSGal

                    FileMaker, Inc. 

                     

                     

                    • 7. Re: nth select from found set
                      rowing190
                        

                      TSGAL:

                       

                      Thank you for the clarificaton and for all of your help. The script is working properly now.

                       

                      rowing190