I have a table of Orders, and each order has a key. That key follows this pattern PO_XXXXXX. The first order has the key PO_000001. When the system makes a new order, it must get the last key and increment it. I've tried to use several Built-in functions, but with no success.
Does anyone know the best approach to resolve this situation?
I have created a small demo for this. Below is output
1. First get the value of number from the string : TextToInteger(Substr(Var1,Index(Var1,"_",0,False,True)+1,Length(Var1)-1))
Here Var1 holds the value, which is PO_00001
2. Do increment and add padding and get back string.
"PO_"+FormatText(Temp+1,6,6,True,0)
regards
@Vikas Sharma thanks. Didn't know about the FormatText Function.
Hi,
For your purpose first you should get the digit from PO_000001. For getting the digit you can do below.
Substr("PO-00001",Index("PO-00001","-",0,False,True)+1,Length("PO-00001")-1)
You will get 00001 as a result. After that convert it to an integer or long integer. Then increment by one and build string again.
Hi Jose,
You can create a server action, which will have the key as input parameter. Using String_Split function, split the key using "_" which will provide you two values: PO and xxxxxx. Now you have to convert the second part into integer using TextToInteger.
Check the length of integer by converting back to text. Thus you will know how much extra zero's you need to add prior to the integer.
Next, you need to increment that number by 1. Convert it back to text using IntegerToText. concatenate the number of zeros to be added based on the previous step result.
Append the final text with "PO_" and you have the new key.
Note that you do not have a concurrency problem. If there's a single processing timer that does this, you're safe, but if it's a user that can trigger this, and there's multiple users that can do so, you may have a situation that all users, at the same time, request an update to the number, and all get returned the same one!
@Kilian Hekhuis I'm aware of that. For now, only one user does this operation.
Ok, good. But if there's ever more than one user, you'll need to handle it differently (typically with an entity holding the next sequence number, using a GetForUpdate entity action to fetch the current value).
The GetForUpdate blocks other processes from accessing the entity, right?
Yes, that's its purpose. If two or more processes try to access that same record, only one will be granted access and the others will block. When the one granted access is finished (i.e. the database transaction is closed), another one will be granted access, and will read the new, updated value.