Importing excel that should contain keys to existing entity records.

I have a set of records that exist in outsystems that have an identifier (primary key). For example Clients with a auto column of type Client identifier. I now want to import an excel sheet containing many products that are spread across those clients. I have manually put the identifier of the client on each product record in the excel and imported resulting in the proper relationships between clients and their products. While this works for my development portion it will be cumbersome to bootstrap the production app this way. 

How do I get the import of the products to determine at import time the proper Client identifier?


Hi Bradford,

You of course need some kind of identifier to get the correct client. I'm assuming that these data come from another system. Could it be a possibility to use some sort of external identifier, e.g. the identifier from the old or external system or a client number? You can add this as a field on the Client entity. This way you could include this external client identifier in the export for the product list that needs to be imported, so that you don't need to insert the Outsystems identifier by hand.

Regards,
Lennart


Lennart Kraak wrote:

Hi Bradford,

You of course need some kind of identifier to get the correct client. I'm assuming that these data come from another system. Could it be a possibility to use some sort of external identifier, e.g. the identifier from the old or external system or a client number? You can add this as a field on the Client entity. This way you could include this external client identifier in the export for the product list that needs to be imported, so that you don't need to insert the Outsystems identifier by hand.

Regards,
Lennart


Thanks for your response. 

These products are entered by hand in a spreadsheet now but they will be entered directly into this application after project completion, so this issue is only related to the bootstrapping excel import. 

A unique text value that was not suitable to be the client identifier, but present in that entity, can be put in the products excel to import. Is it possible to amend the bootstrap import to somehow to have it lookup the client identifier via this text field during the import?

Thanks, Brad

I inserted an action to look up the client by the identifier but it tells me that the import failed the max of 3 times. Unsure what this means. I'll continue to research but would love some pointers on why it stops at 3 or why no related errors are shown.

Bradford Jones wrote:

I inserted an action to look up the client by the identifier but it tells me that the import failed the max of 3 times. Unsure what this means. I'll continue to research but would love some pointers on why it stops at 3 or why no related errors are shown.

Hi Bradford, 

You're probably using a timer action to do the import. After an error, the timer schedules 2 retries, so that explain the 3 failures. 

If you're importing a lot of data, it might be that the timer action times out. By default this timeout period is set to 20 minutes. Best practise is to design the timer in such a way that you prevent the timeout. You can find more info here: https://www.outsystems.com/learn/lesson/1133/master-class-on-best-practices-and-timers/