4 Replies Latest reply on Aug 3, 2012 1:42 PM by cowens1

    Finding next sequential number.. but harder

    cowens1

      I have a customer list containing 20,000 customers.

      My customers are numbered from 100 to 60,000.

      This means there are many sequential customers with big gaps between their numbers.

       

      How can I write a script that will allow me to push a button to find the next customer in seqence?

       

      FYI: It may or may not make a difference, but the button resides is a different file than the customers. I have a file for my telemarketers which accesses limited data from my CustomerBase file from which to make calls.

        • 1. Re: Finding next sequential number.. but harder
          Stephen Huston

          Which of these things are you trying to do?

          1. Find the existing customer with the next highest number from the one you are one, or
          2. Find the lowest missing number in the sequence to assign to a new customer?

          In the first case, you could run a script like this:

          • Show All Records
          • Sort [on customer number -- it needs to be indexed as a number field to get the right order]
          • Go to Next Record

          The Show All records and Sort will leave you on the same record where you started, then the last step will take you to the next record in the sort order. If you need to leave the user on a found set of all records, you can add these two steps:

          • Omit Record
          • Show Omitted only

          That will isolate the new record in a found set of one.

           

          If, instead, you need to find the earliest missing number to use it later, that can be done by Showing all records, sorting them by customer number, going to the first record, and running a looping script in which you set a variable $number to = $number+1 and compare that value to the customer number. [On the first loop/record it will set itself to 1.] If they match, you go to the next record and keep looping. If they don't match, you exit the loop, and the variable value is the first missing number. This can become a SLOOOOOW script as the record count increases, but it will let you fill in your missing numbers as needed.

          1 of 1 people found this helpful
          • 2. Re: Finding next sequential number.. but harder
            cowens1

            This is great.  I tried it and it works... as long as the customer number is already in the Telemarketing table.

            I need it to find the next sequential number in the external file.

            Your assumption #1 was / is correct - I'm trying to find the existing customer with the next highest number.

            • 3. Re: Finding next sequential number.. but harder
              Stephen Huston

              You can follow the same script as provided first, but then add a Go To Related Record (GTRR) step at the end which targets the external layout of the external table, and have it Show ONLY Related Records. That should leave you on the one related in the external file's layout.

               

              You may have to establish a realtionship to the external table for the GTRR step if you do not already have a relationship defined in the local file where you are starting.

              • 4. Re: Finding next sequential number.. but harder
                cowens1

                Thank you!  It's working.