Hi Kai Min,

The auto reset in sql doesn't happen by default and in any case your data should be independent from the IDs, right?

Being that said, you can see on the article below, a step by step instructions with screenshots on how to update a bootstrap action to include foreign keys:

https://success.outsystems.com/Documentation/How-to_Guides/Data/How_to_update_a_Bootstrap_Action_to_fetch_an_entity_identifier_from_Excel

Hope it helps.

Regards,

João

Hi Kai Min,

The reasons for the Id column increasing and not resetting, is because that is an Identity Column in the database. As long as the database table representing the Entity exists, that column will never reset to 0. There's ways to do that if you have access to the database directly, but in your case there's none.

Option 1: Same as proposed by João Marques

On your excel sheet, include business/natural key for your foreign key.

Then modify bootstrap action logic to lookup the foreign key id using aggregate.

Option 2: Turn ON IDENTITY_INSERT on Sql Server if possible (has permissions)

Here's the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

Example: use Advanced SQL Node and put this code:

SET IDENTITY_INSERT [EntityName] ON;

After insertion, don't forget to turn it off:

SET IDENTITY_INSERT [EntityName] OFF;

You must bracket entity name with square bracket [ ]


Option 3 (my personal pick): Replace all auto number ID with Text data type GUID.

What is a GUID?

A GUID is an acronyom that stands for Globally Unique Identifier, they are also referred to as UUIDs or Universaly Unique Identifiers - there is no real difference between the two. Technically they are 128-bit unique reference numbers used in computing which are highly unlikely to repeat when generated despite there being no central GUID authority to ensure uniqueness.

What does a GUID look like?

A GUID follows a specific structure defined in RFC 4122 and come in a few different versions and variants. All variants follow the same structure xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx where M represents the version and the most significant bits of N represent the variant.

How unique is unique?

A GUID is a unique number that can be used as an identifier for anything in the universe, but unlike ISBN there is no central authority - the uniqueness of a GUID relies on the algorthm that was used to generate it. We'll look at the types of GUIDs later, but assuming a randomly generated GUID you have about the same chance of getting hit by a meteorite in a year as getting a collision in 10-30 trillion GUIDs.

For more info: http://guid.one/guid

You can add dependency for server action to generate guid:

Change your entity identifier data type to Text.
Use TextToIdentifier(GenerateGuid()) to generate GUID identifier.

For mobile app client action, you can use javascript to generate GUID (just google it).

If you don't understand about GUID, better pick other options.