How to bootstrap text column to Entity with foreign key identifier?

I spent some time reading the forums and trying to implement the suggested solutions.

Notably, official Outsystems documentation here

Secondly, a forum which contained answers from Outsystems experts here. They simply don't work.

I am hoping an answer for my question could address the issue for persons in the future. Especially, for text columns that must be added. 


The problem:

I have created an Entity  for e.g. Football players. They possess attributes such as FirstName, LastName, Age and foreign key identifiers for 'PositionId'.  How can their positions ("Midfielder, "Forward", Defender", "Goalkeeper") be imported to the Footballers entity?

The Position Static Entity which contains the Records for positions is below: 


The Footballers Entity is represented below:

Once this Entity is created I use a Bootstrap Action to populate the table. However, the PositionId column is omitted as follows:

The solution to this stage of the problem which I found helpful in other posts was to add an attribute to the Structure created. 


It was recommended to set this Structure Attribute to an Integer or Long Integer type (which means it would have to be recorded as such in the original Excel table)

However, I believe that many situations where data must be bootstrapped, data will be stored in human readable format not integers denoting some other value. For example football clubs could be stored 1, 2, 3 to represent Arsenal, Chelsea, Everton. However, most tables swill simply have the 'text' for the data. For e.g.




Therefore I set my attribute to a text value, I use a data conversion function TextToIdentifier(). And from here attempt perform the Bootstrap action again. The values of the PositionId column are never correctly updated. 


The PositionId column of the Footballers Entity has 0 value for all columns.


Is it even possible to get the text column from an Excel file into an Entity that contains reference to a Static Entity?


Hi,


Yes, you need to guarantee that the text values in the static entity are the same that you have in the Excel. Than, make an aggregate with your static entity and put the filter with that values, the text value from your static entity equal to the text value from your excel. In the assigng that  you're using to map the values for the new record, get the value from that aggregate. You need to understand that you must need to take care of cases that don't have text value or the text value isn't present in your static entity.


Hope this can help.


Best regards,

Ricardo M Pereira

Hi Raphael, I think the problem is that you try to assign a text field you have on your excel to an identifier you have on the Footballers Position_id, include on your loop a filter from the position id from excel to position record on Position static entity and with this filter assign to the Position_id on Footballer before the create action 


Solution

Raphael please check the example bellow

Solution

Result after creation:

Carlos Lessa wrote:

Raphael please check the example bellow

Hey Carlos. I implemented your suggested solution above. On two occasions I had to publish the module twice. Only after the second Publish it stored the correct PositionId 'value' NOT the PositionId 'text'. I can solve this issue myself by simply refering to the Label of the Record when I am building out the application.

 However if you think it might be helpful could you explain why the Position Identifier takes the format of an Integer instead of the text that comes from the Excel sheet?

Also. Can you suggested a scalable solution if for example I had 3 or 4 foreign keys? Would I need to create some complicated joins? Or would I simply put different aggregates for each possible attribute.

Thank you for a neat solution to 'How to bootstrap text column to Entity with foreign key identifier?'


Carlos Lessa wrote:

Hi Raphael, I think the problem is that you try to assign a text field you have on your excel to an identifier you have on the Footballers Position_id, include on your loop a filter from the position id from excel to position record on Position static entity and with this filter assign to the Position_id on Footballer before the create action 


This answer helps in thinking about how to solve the problem. However I chose the one where you provided a mock example because I think it will be more helpful to others. Thank you.


Raphael Jones wrote:

Carlos Lessa wrote:

Raphael please check the example bellow

Hey Carlos. I implemented your suggested solution above. On two occasions I had to publish the module twice. Only after the second Publish it stored the correct PositionId 'value' NOT the PositionId 'text'. I can solve this issue myself by simply refering to the Label of the Record when I am building out the application.

 However if you think it might be helpful could you explain why the Position Identifier takes the format of an Integer instead of the text that comes from the Excel sheet?

Also. Can you suggested a scalable solution if for example I had 3 or 4 foreign keys? Would I need to create some complicated joins? Or would I simply put different aggregates for each possible attribute.

Thank you for a neat solution to 'How to bootstrap text column to Entity with foreign key identifier?'


The reason is bcuz you make this attribute as a identifier from thee static entity

But in the case, you have 3 or 4 identifiers if you can join all in just one aggregate would be good if not you need to call the aggregates inside the loop and make the filter you need and assign before the create action

Carlos Lessa wrote:

Raphael Jones wrote:

Carlos Lessa wrote:

Raphael please check the example bellow

Hey Carlos. I implemented your suggested solution above. On two occasions I had to publish the module twice. Only after the second Publish it stored the correct PositionId 'value' NOT the PositionId 'text'. I can solve this issue myself by simply refering to the Label of the Record when I am building out the application.

 However if you think it might be helpful could you explain why the Position Identifier takes the format of an Integer instead of the text that comes from the Excel sheet?

Also. Can you suggested a scalable solution if for example I had 3 or 4 foreign keys? Would I need to create some complicated joins? Or would I simply put different aggregates for each possible attribute.

Thank you for a neat solution to 'How to bootstrap text column to Entity with foreign key identifier?'


The reason is bcuz you make this attribute as a identifier from thee static entity

But in the case, you have 3 or 4 identifiers if you can join all in just one aggregate would be good if not you need to call the aggregates inside the loop and make the filter you need and assign before the create action


Ok I understand how you would do this. Adding several static entities to the aggregate should help if I have multiple foreign keys. Thank you Carlos.