Skip to main content
T
tenegabi12-Amethyst
12-Amethyst
November 20, 2024
Solved

Auto increment ID key Datatable from Mashup

  • November 20, 2024
  • 3 replies
  • 3900 views

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.

Best answer by tenegabi

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;

3 replies

B
Bikash_Panda14-Alexandrite
14-Alexandrite
November 20, 2024

@tenegabi  WIll GUID work for your requirement ?

T
tenegabi12-AmethystAuthor
12-Amethyst
November 20, 2024

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

Rocko
Rocko19-Tanzanite
19-Tanzanite
November 20, 2024

Not sure what you're trying to do and why it is a problem to call a service. Mashups are executed on the client machine, so there is no way to create a unique ID client side when you have multiple people access the mashup. How should the different clients know what the others are doing?

Services are executed server-side and they can coordinate this. You have to call a service anyway to update the information you added, so why not letting this service take care of it?

M
mstarnaud16-Pearl
16-Pearl
November 20, 2024

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

 

T
tenegabi12-AmethystAuthorAnswer
12-Amethyst
December 6, 2024

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;
B
Bikash_Panda14-Alexandrite
14-Alexandrite
December 6, 2024

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, 

    T
    tenegabi12-AmethystAuthor
    12-Amethyst
    December 6, 2024

    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.

    Terms & ConditionsCookie settingsAccessibility statement