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

Question

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

AlsTricks · Answer

Thank you @Yoshiro_Fujimori I will explore using that approach.

Yoshiro_Fujimori · Answer

@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.

uniqueID.yxmd

AlsTricks · Answer

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