how to create Sequences in OutSystems

I have a requirement where i am generating some work flow. Each work flow has its unique Id along with a pre-fix digit inidicating the type of flow.


I have created one table for all types of work flow but for unique Id, it should be in below form:


PreFixDigit + Sequence Code (Starting from one)


so If  1= type 1

2= type 2


so unique Id for type 1 will be


100000001, 100000002, 100000003.............., 10000000N


so unique Id for type 2 will be


200000001, 200000002, 200000003.............., 20000000N


How could i generate sequence for each type?


Hi,

Do you use OutSystems BPT to implement the workflow? In that case why not use the process is and activity is that is automatically generated by OutSystems?

An alternative you can implement a sequence by creating an entity and a server action that creates a record in it and delete it. That would generate a new value for the entity id every time.

Regards,

Daniel

I am not using BPT, and as of now, i cannot move to BPT but let suppose, we move to BPT. Then based on field type of one of entity, will this process is and activity is will send me sequence based on 1.

My mean is that in same workflow, one field has value "P" then this process is or activity is should return 1 and then continue. In same flow, if field value change to "H" then if there is no previous workflow with same value then again value should start with 1 and then continue.  SO for each type of field value, sequence should have its own next val starting from 1.


I already thought about second option, but if my respective  field has 5 values, it means i will create five entities, which purpose is nothing but to get a sequence value and thus give 5 Application objects to outsystems, which have nothing to do in terms of data.


Hi Bilal,

Normally the Ids on processes, activities and entities are consider nothing more that an 'technical' primary key, with the sole purpose of uniquely identifing an object.

With your requirement to have multiple sequences, you would indeed need multiple entities to generate them.

Could you further elaborate on why need this behavouir, and why not a unique id per workflow is sufficient?

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Bilal,

Normally the Ids on processes, activities and entities are consider nothing more that an 'technical' primary key, with the sole purpose of uniquely identifing an object.

With your requirement to have multiple sequences, you would indeed need multiple entities to generate them.

Could you further elaborate on why need this behavouir, and why not a unique id per workflow is sufficient?

Regards,

Daniel

Daniel,

Let suppose, my workflow is to purchase orders, where orders could be for different category of items. For example, stationary, furniture or IT equipments.


Now order number for 

Stationary should start with 1 and then a sequence number starting from 1....N

Furniture should start with 2 and then a sequence number starting from 1....N

IT equipments should start with 3 and then a sequence number starting from 1....N


All are purchase orders, so it will be one Entity (i cannot create separate order tables for each category).


And these orders pass through different approvals and reviews. And requirement is that from order number pre-fix, item category should be predicted.


 


Hi Bilal,

Why is it a requirement that the order number is sequenced per category? Because of that you need to prefix it.

If you just have an order number it is already unqiue. Isn't that the purpose of an order number to unquiely identify a order.

From how I look at it, you are overcomplication your solution, If you ditch the requirement, than OutSystems by default will create a new order number any time you create one in the order entity.

If you stick to implement your requirement, than you could follow my initial suggestion.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Bilal,

Why is it a requirement that the order number is sequenced per category? Because of that you need to prefix it.

If you just have an order number it is already unqiue. Isn't that the purpose of an order number to unquiely identify a order.

From how I look at it, you are overcomplication your solution, If you ditch the requirement, than OutSystems by default will create a new order number any time you create one in the order entity.

If you stick to implement your requirement, than you could follow my initial suggestion.

Regards,

Daniel

Daniel


Thanks for the feedback but I am just implementing the requirements, and it is from client who need the solution in this way and client is always right.


If it would be in .net / oracle, i could create sequences but unfortunately OutSystems has only entities and not a sequence object, like i cannot create constants in outsystems.


Anyhow, if anyone else has any idea other than creating an entity for each category, please let me know. 

 

You can use sequences from en exteral DB... You can have an advanced sql returning a sequence value.

You need to have te correct grants to the sequence(s).

But to me it only adds more complexity, where there is an OutSystems solution, which is not difficult.

The Id's on an OutSystems are not more than a sequence: 

 https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Database_Reference/Auto_Numbers_on_Database

So doing it the OutSystems way hides away the database specifics of implemeting a sequence.

Hello, Bilal Iqbal.

I will leave the discussion on the requirements for a different session and I will just write a high level ideia on how I would go in order to implement the mechanism that you need. I will use the Purchase Order example you gave.


I would create three entities:

Entity: PurchaseOrder ( Id [PK], PurcaseOrderTypeId [FK to PurchaseOrderTypeId] )

Entity: PurchaseOrderTypeSequence (PurchaseOrderTypeId [PK, FK to PurchaseOrderType], NextValue)

Static Entity: PurchaseOrderType (Id, Description, PrefixCode)


In the PurchaseOrder entity, the Id column will be our column that holds the unique ID you mention. This can be a text field.

Then, I would have a central action for creating new PurchaseOrders. In this PurchaseOrder_Create, I need an input PurchaseOrderTypeId so we can know which type or purchase order are we gonna create.

In order to avoid dirty-writes, at the beginning of this PurchaseOrder_Create action I would have a GetPurchaseOrderForUpdate entity action call, for the PurchaseOrderTypeId we are trying to create. In this way, using the PurchaseOrderTypeSequence record and PurchaseOrderType.PrefixCode, we can just stamp our soon to be purchase order with the right sequence number (in the ID column), increment one value to the PurchaseOrderTypeSequence record (NextValue), and commit the database transaction.

PurchaseOrderTypeSequence has to be filled in with a timer, to run on Publish, to pre-fill this table with the starting values for your sequences.


Note: The GetPurchaseOrderForUpdate entity will guarantee the there are no 2 threads updating the sequence record at the same time. The first one that calls the entity action will block the other one while the database transaction is not committed.


Hope this helps you in finding the best solution for your problem :)


Best,

Tiago



Bilal Iqbal wrote:

I have a requirement where i am generating some work flow. Each work flow has its unique Id along with a pre-fix digit inidicating the type of flow.


I have created one table for all types of work flow but for unique Id, it should be in below form:


PreFixDigit + Sequence Code (Starting from one)


so If  1= type 1

2= type 2


so unique Id for type 1 will be


100000001, 100000002, 100000003.............., 10000000N


so unique Id for type 2 will be


200000001, 200000002, 200000003.............., 20000000N


How could i generate sequence for each type?


Good evening Bilal Iqbal,

My suggestion:

1 - Static entity to hold the type 1, 2 and so on... + add an attribute to this static one StartingNumber(or any other name that suit you better) + Add an attribute to hold the PREFIX

2 - Entity 1-1 to hold the relationship with the StaticTable and the last number saved for that kind of Type.

3 - Action to Get the last number for that type. Eg: GetLastNumber(), GetNextNumber(), (Depending on you, you can bring back the last number and add 1 or already deal with that inside the action and get the next number to use)

4 - MainEntity to Hold the data you want to save.


Possible Solution(Use as an example, follow the best practice(even if didn't in the example or missed any, adapt for your use case ):


 

Good Luck, hope I could help you.

Bilal Iqbal wrote:

Thanks for the feedback but I am just implementing the requirements, and it is from client who need the solution in this way and client is always right.

To just give a little feedback on the "client is always right" statement. This is not true by default and if I was a client I would expect that my hired help (either Internal and External) advises me in the best way forward, they are the expert in their field. 

So if for some reason one of my requirements is hard to do or doesn't make sense (like a sequence number to group type of objects) I would like to be challenged for this and be presented by good alternatives. What if this requirement will cost me 8 or 16 hours development time and the alternative just 1 hour. Then perhaps I will choose the alternative if the outcome is the same, or perhaps even better.

so the tldr; Talk with you client about the requirements set and discuss those. Don't forget to come up with viable alternatives! I'm sure that this will be appreciated by most clients, I know mine do.


I fully agree with Vincent's comments.

# Create a table with two columns: Type and Seq

# Read the table for each Type and Seq 

# Use Entity Action to avoid dirty write "Select for Update"

# Increment the Seq

# Update the table

That's it!

Incrementing a value in a record does not work well when using BPT for paralell processing.

Creating a new record in an entity always ensures a unique number.

# But, we already locked it before update, Sir.

Yes but that means you loose power of parallel processing. They have to wait on each other don't they.

# yes, you are correct