1 of 1 people found this helpful
Which of these things are you trying to do?
- Find the existing customer with the next highest number from the one you are one, or
- 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.
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.
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.
Thank you! It's working.