Hello,

I am trying to bootstrap data into an entity that has 2 foreign keys to static entities.  The static entities are populated and the numeric IDs of the associated static entity records were populated in the Excel spreadsheet.  No matter what format I use for the static entities in the Excel spreadsheet, I cannot get the foreign keys included as per the attached screenshot.  How do I get the foreign key values into the bootstrapped entity records in Outsystems ?

Thanks, Richard.

Hi Richard Pearce,

Maybe you could try to have the foreign keys in excel as integers, then use a structure to import those attributes as integers, and then finally when iterating results interpret each one to convert into correspondent identifiers.

Does this help?

Marco Arede wrote:

Hi Richard Pearce,

Maybe you could try to have the foreign keys in excel as integers, then use a structure to import those attributes as integers, and then finally when iterating results interpret each one to convert into correspondent identifiers.

Does this help?

I faced same issue , how do we use structures to import using bootstrap ? looks like this action is only available for database table. attached are dummy xls created and following is screen capture for tables. Will it be possible to share some example code on this ? 


 

Nilesh Rokade wrote:

Marco Arede wrote:

Hi Richard Pearce,

Maybe you could try to have the foreign keys in excel as integers, then use a structure to import those attributes as integers, and then finally when iterating results interpret each one to convert into correspondent identifiers.

Does this help?

I faced same issue , how do we use structures to import using bootstrap ? looks like this action is only available for database table. attached are dummy xls created and following is screen capture for tables. Will it be possible to share some example code on this ? 


 


attaching 2nd xls 

Marco Arede wrote:

Hi Richard Pearce,

Maybe you could try to have the foreign keys in excel as integers, then use a structure to import those attributes as integers, and then finally when iterating results interpret each one to convert into correspondent identifiers.

Does this help?

Hi Marco,

Thanks for the reply but it didn't immediately resonate so I'll try breaking it down:

1.  "Maybe you could try to have the foreign keys in excel as integers" - if you look at the image I have provided, that is what I have already.

2.  "then use a structure to import those attributes as integers" - are you suggesting I create a matching structure in Outsystems that matches the excel spreadsheet and bootstrap to that instead of bootstrapping directly to the original target entity ?

3.  "and then finally when iterating results interpret each one to convert into correspondent identifiers" - by this I assume that you are suggesting creating a preparation that uses the bootstrap to the structure to populate the original target entity ?

Cheers, Richard. 


Hi Nilesh Rokade,

how do we use structures to import using bootstrap ?

After that initial step (bootstrap from excel file), modify your bootstrap action to include a structure.

Hi Richard Pearce,

(...) are you suggesting I create a matching structure in Outsystems that matches the excel spreadsheet and bootstrap to that instead of bootstrapping directly to the original target entity ?

(...) I assume that you are suggesting creating a preparation that uses the bootstrap to the structure to populate the original target entity ?

My suggestion is to not use the identifiers, because they are assigned automatically and can change. The safer and standard solution is to lookup for records by name.

However responding directly to your first question, you could import identifiers by using a structure, just adapt a bootstrap action, and create a structure to be mapped later to your entity.

Marco Arede wrote:

Hi Richard Pearce,

(...) are you suggesting I create a matching structure in Outsystems that matches the excel spreadsheet and bootstrap to that instead of bootstrapping directly to the original target entity ?

(...) I assume that you are suggesting creating a preparation that uses the bootstrap to the structure to populate the original target entity ?

My suggestion is to not use the identifiers, because they are assigned automatically and can change. The safer and standard solution is to lookup for records by name.

However responding directly to your first question, you could import identifiers by using a structure, just adapt a bootstrap action, and create a structure to be mapped later to your entity.

The identifiers for my static entities don't change because they are not auto-generated.  I tried using integers in my Excel spreadsheet and used the data conversion function to convert the integers to identifiers.  There are no errors reported but still no data loaded into the target entity.  The search for a solution continues...


Solution

Hello Richard,

I'm sure you know, but I'll put here anyway.

You can't get the FK to be included automatically in the excel.
The platform won't recognize them even if the data in the excel has the same underlying data type as the Entity Identifier. 

The solution is:

1. Add the IDs to the excel
2. Do the bootstrap normally, ignoring the platform will not have a match
3. In the structure created by the automatic bootstrap, add the two fields that are missing. But add them as Integers instead of Entity Identifiers. Also, be sure the newly added attributes are in the same order as the columns in the excel, or you may get an error.
4. In the Bootstrap, select the Assignment node and add manually the missing two fields, making the conversion from integer to identifier (if they are integers), or from text to identifier (if they are text).

I did this many times in the past (and just tested it) and this works. 

Cheers

Solution

Result in the entity:

Note that in the entity, GenderId is an FK to the Gender Static Entity.

Cheers.

P.S. Don't remember if it is really necessary to change the GenderId to Long Integer in the structure or not.

Eduardo Jauch wrote:

Hello Richard,

I'm sure you know, but I'll put here anyway.

You can't get the FK to be included automatically in the excel.
The platform won't recognize them even if the data in the excel has the same underlying data type as the Entity Identifier. 

The solution is:

1. Add the IDs to the excel
2. Do the bootstrap normally, ignoring the platform will not have a match
3. In the structure created by the automatic bootstrap, add the two fields that are missing. But add them as Integers instead of Entity Identifiers. Also, be sure the newly added attributes are in the same order as the columns in the excel, or you may get an error.
4. In the Bootstrap, select the Assignment node and add manually the missing two fields, making the conversion from integer to identifier (if they are integers), or from text to identifier (if they are text).

I did this many times in the past (and just tested it) and this works. 

Cheers

I managed to work out all the above instructions through trial and error and it didn't work, until I found that the identifiers for my target entity were not auto-generated.  Fixed this and it now works and it is pretty simple once you understand a few key things i.e. you need to ignore a few of the warning messages and fill the missing gaps in once you've created the bootstrap.  One thing I would add to your steps above - for (1) add the foreign key IDs as integers in excel (and convert to identifiers in step 4).

Thanks to everyone for the help in getting me over the line !

Cheers, Richard.


Eduardo Jauch wrote:

Result in the entity:

Note that in the entity, GenderId is an FK to the Gender Static Entity.

Cheers.

P.S. Don't remember if it is really necessary to change the GenderId to Long Integer in the structure or not.

I used integer in the structure because the static entities don't use long integers.  But it looks like it works both ways.

Cheers, Richard.


Ah...

Yes, if the target entity had manual identifiers, the bootstrap would fail as they are not provided. :)

Glad it worked (and for the addings).

Cheers

Hi Richard Pearce ,

(...) I found that the identifiers for my target entity were not auto-generated (...) now works

Interesting point, great to know it worked. Cheers!

Eduardo Jauch wrote:

Ah...

Yes, if the target entity had manual identifiers, the bootstrap would fail as they are not provided. :)

Glad it worked (and for the addings).

Cheers

Hi Eduardo,

When I first encountered the problem, I looked at the Outsystems documentation article on bootstrapping.  Didn't find an answer, so searched through a lot of forum articles but didn't find the explicit references there, although in hindsight I understand some of the comments I found there better now.

It would be good if the issue with the foreign keys was explicitly referenced in the Outsystems documentation article.  Better still, because Outsystems has implemented integer based surrogate keys for the identifiers, the bootstrapping process could quite easily be modified to match the appropriate integer column in Excel with the identifier in Outsystems.  There shouldn't be too much of an issue with doing this and we are effectively doing it manually every time.  It would also benefit the majority of bootstrapping processes and increase productivity because let's face it, the bootstrapping process is an important part of pre-seeding the data for any Outsystems application.

What do you think ?

Cheers, Richard


Hi Richard,

I agree. In fact, No matter if the identifier is an integer/long integer or text, it would be possible to make the match with the Identifier or Foreign Keys in the entity.

Most of the time this can lead to problems if you need to reset the data, or the data must be published also in Production, as the ID counters are no reset and you may break the action or associate wrong records due ID's changing (if they are auto number, at least). In those cases, I usually use a Lookup column and fix the bootstrap manually.

But certainly, this is a scaffolding that shouldn't be difficult to implement.

Cheers.