Auto-number sequence generation not working in Creatio Online environment
Hello,
We are importing data from an external ERP system and creating records (e.g., Orders / Invoices) via backend code. These objects have an auto-number column (“Number”) configured with a prefix such as ORD- or INV-.
We attempted to generate the next auto-number value by querying the database sequence directly using SQL like NEXT VALUE FOR based on the column UID. This approach works in some local environments but fails in Creatio Online. The query either throws an error or does not return the expected value.
Below is the code we have written for next sequence:
EntitySchema entitySchema = userConnection.EntitySchemaManager.GetInstanceByName(objectName);
var numberColumn = entitySchema.Columns.GetByName("Number");
string sequenceName = numberColumn.UId.ToString();
long nextValue=0;
try
{
string sql = $"SELECT NEXT VALUE FOR dbo.[{sequenceName}]";
using (var dbExecutor = userConnection.EnsureDBConnection())
{
using (var reader = new CustomQuery(userConnection, sql).ExecuteReader(dbExecutor))
{
if (reader.Read())
{
nextValue = reader.GetInt64(0);
}
}
}
}
catch(Exception ex)
{
}
The trail online version of Creatio is "8.3.2.4199".
What are we missing here?
Any help would be appreciated.
Like
First of all, how are you loading the orders/invoices? It should be creating these numbers unless you're going directly to the database (or the numbers are being created using the older method on the client-side page only).
I assume that somewhere you're setting the nextValue in the Number field and that just isn't showing in the code?
Ryan Farley,
We are currently performing a direct SQL insert using the following code:
Guid newRecordId = Guid.NewGuid();
var insertQuery = new Insert(userConnection).Into(objectName).Set("Id", Column.Parameter(newRecordId));
foreach (var property in objectData.Properties())
{
string propertyName = property.Name;
string propertyValue = property.Value.ToString();
insertQuery.Set(propertyName, Column.Parameter(propertyValue));
}
int affectedRows = insertQuery.Execute();
However, in the online environment, the order number is not incrementing as expected. Instead, all orders are being created with the same number (ORD-0).
Hello,
Perhaps the issue with the trial might be that it uses a PostgreSQL DB that is not compatible with your SQL script.
Chetana Gharat,
Dmytro is likely correct. If the system is Postgresql (if it's a cloud hosted Creatio, it likely is), then you need to generate the next ordinal number using the Postgres way, see here: https://customerfx.com/article/resetting-the-value-of-autonumber-fields-in-creatio/
For example:
SELECT nextval('ed398640-de69-842b-c50c-6c673da5aa98')That would explain why it's working in your local environment and not in the cloud, assuming that is MSSQL.
Ryan