How to assign unique IDs (primary key values) that continue from previous API calls

[AlsTricks]

Hi everyone,

We are developing the use of API calls, which will be run manually for the time being.

The data resulting from the calls is going to be stored in a database.

We would like to establish how to assign unique IDs (primary key values) that will make each record uniquely identifiable.

Let's say that we run an API call and it returns 100 records. We assign the unique IDs 1 to 100 to the records, and then output these records to our database to be stored.

Let's then say that we come back one week later, and we run the same API call. This returns, say, another 150 records. How can we enable the assigning of the unique IDs to continue from the previous last ID number (that is, ID 101 will be the next value to be assigned)?

Many thanks for your support!

Alex

[OllieClarke]

Hi @AlsTricks 

One way of doing this is reading in the data you've already written and finding the max ID value. You can then add this to your ID field after you've connected to the API to make sure that it starts from there (assuming IDs starting from 1).
You'd then append the output of this process to your table so you have all data there (in the example 1-250)

Does that make sense?

Ollie

[AlsTricks]

Hi Ollie,

Thank you for your thoughts. It makes sense in principle, but I'm not totally clear on how to implement this approach in practice (though I have given it a go).

As a test, I've created an Excel file towards the end of my existing workflow which starts off with a record ID of 0. The Summarize tool identifies this as the maximum ID number and then the Append Fields tool adds the maximum ID to the table that is produced by the API call.

Separate to this part of the workflow, the API call results table uses the Record ID tool to assign temporary record numbers.

Next, following the use of the Append Fields tool, the Formula tool adds the maximum ID number to the temporary record numbers to create the "Record ID" field that will be retained and outputted. The output file overwrites the data that is already in the Excel file, and the next time that it is run, it picks up the new maximum ID number and adds this to the new temporary record IDs.

Hope I've explained that okay :)

Was that what you had in mind please?

Any other suggestions are welcome!

Alex

[Yoshiro_Fujimori]

@AlsTricks ,

I understand you want to start from the next number of the Max ID in the last API call.

So you should have the historical data sent in the past as below(simplified).

Old data

And you have the New data as below.

And you want to give unique ID starting from 6 (=5+1).

I suppose the workflow below would work.

The output would be:

Please let me know if I misunderstand your requirement.

[AlsTricks]

Thank you @Yoshiro_Fujimori I will explore using that approach.