While Loop

Question

Hello everyone, 
 I need to do a expression rule which will return a unique code. 
 So my expression will need to generate a code (done already), verify if exists already in the database, if yes, the expression will need to generate another code, until it does not exists in the database. 
 I checked loop functions in Appian and does not find the While loop function, but rather functions with known iteration like "forEach", "apply". 
 Thank you in advance. 
 Nanbei

Dave Lewis · Answer

I only recommend this basic approach if your domain (the number of codes possible) is much, much larger than the number of codes generated. 
 I assume that your "codes" are pseudo-random generated values. If you're going to try to create 100,000 codes for 50,000 people, you DO NOT WANT to generate codes randomly and see if they're already in the database. Half your codes are used at the end, then it becomes how many times do you flip a coin before it finally comes up tails? Twice? Fifteen times? Though unlikely, you could theoretically need to retry several thousand times. 
 If you're trying to generate 50,000 codes for 50,000 people, the last code would only have 1 in 50,000 odds of success of working the first time, and only 1 in 50,000 odds of working the second time, or the third, or any time after that. You still have less than 20 percent chance of having found it after 10,000 attempts. 
 If you're going to go the route of generating codes at random, then trying again if you have a match, you need to make the chances of a match astronomically slim, something like 10,000 times as many possible values as there are values that are actually being used. You'll still have to be capable of withstanding a performance hit of several retries in a row. No matter how unlikely it becomes to retry 50 times before you find an unused code, it always remains possible. 
 Exact expectations placed on random numbers and performance just in general don't go together. The most inefficient sorting algorithm ever invented, which can theoretically take an infinite amount of time to process, is the most inefficient ever because it uses randomization. See Bogosort.

Robert Shankin · Answer

This seems almost wastefully expensive. 
 By that I mean this is excess, inefficient process. Don't guess and check new codes. 
 I expect there are at least 2 alternatives, (but probably more) 
 1st - https://community.appian.com/w/the-appian-playbook/846/generating-guids - you can make a guild with expressions. 
 2nd - both Oracle and SQL server will manage unique sequences for you, you should be able to pull them. 
 3rd - if you don't have the luxury of Oracle or SQL Server, you can make a "codes" table in mySQL where the PK is your new code. just make new rows in that table and use the id value as your code. It doesn't have to start at 1.

While Loop

Top Replies