34
Views
12
Comments
How to insert data with existing id values?

I'm trying to bootstrap data by excel to Outsystems database. The data include a Id field (Auto Number). 

If set "Is AutoNumber" to Yes to Id attribute, then bootstrap data will ignore the Id values in excel. I also tried to set "Is AutoNumber" to No, then bootstrap data success. After that I try change "Is AutoNumber" to Yes , but it occur error when publishing.


Is there any solution for migrate data with Id values to Outsystems database?

Rank: #286

Hi Cindy,

You don't have to include ID attribute in your excel file as outsystems automatically insert numbers for Id attribute in an increasing order during bootstrap :)

Please try to remove Id column from your excel and try to import data in to the database then.

For more information, you can take a look at the below documentation.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Bootstrap_an_Entity_Using_an_Excel_File


Hope it helps, Thanks :)

mvp_badge
MVP
Rank: #44

Hi Cindy,

Are your Ids in the Excel data sequential? You could order your data by Id, then delete the column and let the autonumber generate the expected Ids.

Rank: #14200

Ids not sequential and maybe missing some number.

Rank: #386

Do you have any business impact of that column which is having missing numbers?

Rank: #14200

Id is a foreign key of other entity. If Id changed, it will affect the data relation.

Rank: #286

Hi Cindy, 

If I understood your problem correctly, you need to make some modifications in your Bootstrap Entity server action.

Refer to the following link and In For Each loop, assign  coming Id in to your new entity source assignment then use CreateEntity Action to create records with the  value stored in Id column of excel.

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

Hope this helps!!

Rank: #386

If this ID is used as foreign key to other entity attribute then set AutoNumber to No and  it's better to write logic to generate a new unique value to store into ID attribute, whenever you insert a new record into this bootstrapped entity.

Rank: #386

Hi Cindy,

Yes, you can do bootstrap with ID column with Integer data type. However, it would be better to keep default ID attribute which will be set Auto number as "Yes" and Datatype as "Long Integer" for any data action. And, once your data get uploaded to OS database, you can remove your ID column (Excel one) from entity and use OS default ID column for any CRUD operations.

Thanks & Kind Regards,

Sachin

Rank: #14200

I want keep the old Id value as identifier.

Rank: #1032

Hi Cindy, 

you cannot define a table with some fix values for the id, insert data, and after that change to be auto number.

What you can do is to create two columns id's. The internal (id) as auto number for the plataform and a externalId.

Plácido


Rank: #7579

Hi all, I am Cindy's colleague and please let me continue the conversation here. 

We have thought of creating a new field in the tables in order to contain the OldId while maintaining a NewId for OS. If doing it in this way, we either rely on the OldId forever for relationship mapping or we remap all the relationships into the NewId then abandon the OldId field afterwards. However, there are some CONS that I could think of:

1. there are over 100 db tables in the project and remapping all of them will take up a lot of man power and time to do the logic.

2. this method may serve this project right, but if the project has other external application with db tables that reference us, then it require all these external applications to remap their relationships and that's an enormous work.

The best way I could think of is to insert the exact value of OldId into the NewId field of Outsystems directly so we don't have to remap the relationship at all but can use such values for PK. Therefore, we want to know if there is a good way to achieve it?

mvp_badge
MVP
Rank: #2

Hi Stephen,

If you have a good grasp of the database tables, and know the exact relations between them, you could do the mapping while bootstrapping the data. You'll need a temporary Entity that has three Attributes: TableName, OriginalId and NewId, with a single composite index on TableName + OriginalId. You can do then the following:

  • For each table, read the data from the Excel file, Create the record in the OutSystems database, and store the table name, Id from the Excel file and Id from the Create in the table;
  • If the table contains one or more foreign keys to previously bootstrapped tables, use the table name of the foreign key and the foreign key Id in the Excel file to find the new Id in the OutSystems database. Update the Entity Record with these new Ids before Create-ing them.

This is also quite some work with a 100+ tables that have a lot of interdependencies, but at least you don't need to create extra columns and whatnot.