Excel import and add a foreign key

Hi people,

I just started to discover the outsystems platform. I did the first course and now i wanted to make a simple test system.

I import an excel file with person info into the database. This works fine. Now I want to connect the people to the department they work in (not in the excel so it has to come from a local filled variable), so I created a Department entity with some Attributes in there. 

After that I added an Attribute (DepartmentId) to the Person entity with data type Department identifier.

After this change when I import an excel file I get the following error message:

Excel read error in row 2: Column 'Name' has an invalid type. Expected a 'Int32' but received a 'String'.

With debugging I saw that the error is thrown before the end of the ExcelToReordList_Persons.

How can I fix this?

Hope someone can help. Thanks in advance

Hi Willem,

Can you share your OML file?

Thanks,

Renato

Willem Schulte wrote:

Hi Willem,

if I'm not mistaken, the order, name and data types of the columns in the Excel are all taken in consideration when matching with the attributes in the Structure you use for the ExcelToRecordList tool.

For a better understanding of how this is used, see this documentation.

Hi,

For newer document see this: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Bootstrap_Data_from_Excel_Files

Could be:

regards,

indra

Thanks for the quick reply!

@Renato: I sadly dont have a working version to exportand i'm concerned that the OML file contains people data? (Its against the law in the EU to publish this data in any way..).

@George & Indra: Thanks, I found these alredy, but they dont help my issue.

I will try to explain in more detail:

I need to create a continues update stream between to systems so i need to update te DB with info from the excel file.

My steps were as follows:

Created an import action to pick a file and import it. This works! So there are no empty columns and the Attribute names are consistent with the excel file. The import works fine, I have the data in the outsystems DB.

Then I added an Attribute 'DepartmentId' to the People entity. This Also works fine and I can choose a department in the PeopleDetail screen.

After this if i read another excel file into the system I get the error. So I suspect I need to tell Outsystems to expect an Attribute less then is in the excel file and fill that last attribute after reading the row from excel with a local variable?

regards,

Willem

hi WIllem,

I still do not understand what you do in this paragraph:

"After this if i read another excel file into the system I get the error. So I suspect I need to tell Outsystems to expect an Attribute less then is in the excel file and fill that last attribute after reading the row from excel with a local variable?"

Do you create your own action or just import? If you import then it will no effect as there is already data in the people entity. If you create your ow action, there must be logic where you should insert a value of type Department Identifier, but you insert with a string. You can use, say it NullIdentifier() for the DepartmentId column.

regards,

indra 

Hi Indra,

I copied the action from an example I found:

And while going through the steps of debugging to tell you where the error is thrown everything suddenly works... The records get added with NULL in the departmentkey??

Probably some beginners mistake somewhere :)

Since this post will be useful to no one can I delete it?

Willem Schulte wrote:

Hi, 

Instead of deleting it, I would suggest mark your last answer as the solution   

I'm glad you make it!

Vera 

Hi people,

My issue is still occurring so I suppose i do something wrong...

I added the .OML file (the working version).

What I intend to do is add an attribute to the person entity that points to a department.

I add in the test_core persons entity a attribute called DepartmentId.

The datatype is set to Department Identifier as expected.

If I now import the excel file with personel it throws the error...

If I remove the Department attribute it works again.

If anyone has a tip, i'd love to here it.

Regards,

Willem

An example file that i try to import..

Hi Willem,

looking into the excel file, I'm assuming that "Attribute1" represents "DepartmentId", correct?

So, first issue: You shouldn't use IDs on the excel, because they will be created dynamically in the DataBase. Instead, you should have "Department Name". 

After fixing that, you should have a way to validate if the department already exists in DB. 

If exists »» return the Id

If don't exists »» create new record on Department table and return the Id

You can encapsulate this logic into a new action (see the image below):

Note: I used a local variable "Person" to assign DepartmentId before creating the record.

Let me know if this works for you.

Vera

Hi Vera, thanks for responding so fast.

The Attribute1 is actually the persons ID from the system the data comes from (numeric and cannot be changed). But in the excel header there is a '#' only and outsystems translates this automatically into Attribute1.

In the excel there is no Department. 

My thinking was: Import the records into the Person table and add a field to fill in the application (Make a Combo, pick a department, put it in a local variable and post that into the record at creation after i press import). In this example i dont even fill this extra field, but just leave it empty for now.

The issue is that if i change the person entity by adding a departmentid the process already throws the error before i get to the for each loop.

Rgrds,

Willem

huuummmm... so, that's a different issue. What's the error you are getting? Are you creating DepartmentId as a mandatory field?

btw, you should rename Attribute1 to "ExternalId".

Yes it is. 

The Error i get is:

Excel read error in row 2: Column 'Name' has an invalid type. Expected a 'Int32' but received a 'String'.

With debugging I saw that the error is thrown before the end of the ExcelToReordList_Persons.

this happens when i take the test.oml, add an attribute to Person (DepartmentId) publish and import the excel.

The departmentId is not mandatory. 

I will :)

Hi Willem,

The record definition property of the ExcelToRecordList should be of type structure. In that structure put all the attributes that you need. This way you will have a structure that has the attributes of the excel columns (with the Department attribute as Text) and a Person Entity with the Department attribute as an an Identifier.

Then do has Vera has shown you.

Cheers,

José

Vera and José,

PRESTO! i have a working solution.

So logical and obvious if you think about it.. don't read directly into an Entity with a different layout.

Can't believe i didn't see that :)

Thanks for your help!

Willem

A W E S O M E!