Skip to main content
tenegabi12-Amethyst
12-Amethyst
Nov 20, 2024 8:41 AM UTC
Solved

Auto increment ID key Datatable from Mashup

Hello,

I want to add information from Mashup and I want to have an ID incremental from Mashup that is start from 1.

I try to find something but anything use some service. I have some services already and I don't want to do this incremental from service, I want to do it from Mashup.

 

Thank you.

tenegabiAnswer
12-Amethyst
Dec 6, 2024 2:15 PM UTC

I made a service separate that add automatically the number.

The service is only call in Mashup and don't need another specifies connexions except Clicked button trigger and Loaded Trigger from Mashup. 

try{
    var dtValues= Things["AuditItBOS_Schedule"].GetDataTableEntries({
maxItems: 99999 /* NUMBER {"defaultValue":500} */
    });
    
    var dtMaxParams = {
            t: dtValues, // INFOTABLE
            columns: "ID", // STRING
            aggregates: "MAX", // STRING
            groupByColumns: undefined // STRING
          };
        var dtMax = parseInt(Resources["InfoTableFunctions"].Aggregate(dtMaxParams).MAX_ID); //INFOTABLE
 
}
catch(err){
    result = "Service call error ["+me.name + "]: on line " + err.lineNumber + ". Error Name: " + err.name + ". Error Message: " + err.message;
}
 
var result = dtMax+1;
View in thread

18 replies

Bikash_Panda14-Alexandrite
14-Alexandrite
Nov 20, 2024 9:02 AM UTC

@tenegabi  WIll GUID work for your requirement ?

Translate
tenegabi12-AmethystAuthor
12-Amethyst
Nov 20, 2024 9:05 AM UTC

Hello @Bikash_Panda ,

I don't know what to tell you, I have try already but it create this type of input: 9beb3c03-0af8-4642-82fa-1114dda72e10
I want to create only number and start from 1, it is simple to identify by users and by me. 

Thank you for the answer

Translate
Bikash_Panda14-Alexandrite
14-Alexandrite
Nov 20, 2024 9:13 AM UTC

In that case, you may take a look at,

https://github.com/carlescm/ThingWorxConcurrencyExtension

 

This will help you generate unique number, but each time it will increment the value. So if you increment the value and dont insert to datatable, then your values will not be consecutives though.

Translate
mstarnaud
mstarnaud16-Pearl
16-Pearl
Nov 20, 2024 2:33 PM UTC

Hi Tenegabi.

 

I'm guessing the data will go in a DataTable entity? If that's the case I'm not sure what the best way would be, What I would do is on the Add service's Thing, I would put a property (like LastCreatedId) of type number and persistent, add every time the service adds a record I would look at that value and add 1. But I'm not sure it would be robust against odd scenarios, like concurrency.

 

If the data is stored in a database, there's a much easier way to do it. You can give the Identity setting to a column, this will do it automatically for you. Here's how you can set it with the wizard in MSSQL : 

mstarnaud_0-1732112926630.png

 

Translate
tenegabi12-AmethystAuthorAnswer
12-Amethyst
Dec 6, 2024 2:15 PM UTC

I made a service separate that add automatically the number.

The service is only call in Mashup and don't need another specifies connexions except Clicked button trigger and Loaded Trigger from Mashup. 

try{
    var dtValues= Things["AuditItBOS_Schedule"].GetDataTableEntries({
maxItems: 99999 /* NUMBER {"defaultValue":500} */
    });
    
    var dtMaxParams = {
            t: dtValues, // INFOTABLE
            columns: "ID", // STRING
            aggregates: "MAX", // STRING
            groupByColumns: undefined // STRING
          };
        var dtMax = parseInt(Resources["InfoTableFunctions"].Aggregate(dtMaxParams).MAX_ID); //INFOTABLE
 
}
catch(err){
    result = "Service call error ["+me.name + "]: on line " + err.lineNumber + ". Error Name: " + err.name + ". Error Message: " + err.message;
}
 
var result = dtMax+1;
Translate
Bikash_Panda14-Alexandrite
14-Alexandrite
Dec 6, 2024 2:22 PM UTC

If you want to adopt this approach, i woudl suggest query the data table, sorted by timestamp desc, and only query 1 row, rather then querying 9999 rows. That is performance intensive and also will not work if you have 100000+ rows.

I am not sure of but also check what happens when multiple users do the same operation, same time, 

Translate
    tenegabi12-AmethystAuthor
    12-Amethyst
    Dec 6, 2024 2:30 PM UTC

    The Thingworx work in a way first come first serv and this think can't happen in theory. 
    And this case must not happen. 

     

    For case that you thing I will have more than 100000 rows I make another service that is erase information older than 6 months and in an month create only 2-3 thousands rows, so it's everything is all right.

    Translate
    Terms & ConditionsAccessibility statement