Foreing key conflict when importing data from excel

Foreing key conflict when importing data from excel

  
Hi!
I have defined my tables and the relationships between them, and also a few screens to view, edit information, etc.
When I try to populate the tables using an excel bootstrap, the collumns containing the cross-table identifiers are not imported due to the constraints on foreing keys. 
After reading the forums and watching this video: http://www.outsystems.com/academy/11/1692/609/get-even-better-usability-with-real-data/ I realise that it would have been better to import the database and them develop the rest, but I would hate to have to start from scratch. And I am also working on the data outside, and need to do that while the platform is developed

Is there a way to override this foreign key protection, given that I am sure that the correspondances will be correctly done in the excel files?

Another question is: is it possible to reset the primary key counters? They keep increasing at every bootstrap tentative, even when I clear the whole table and are getting really large!

Thanks an lot in advance!
Because the platform is strongly typed you can't just set foreign keys to integer values.  You must use the ConvertIntToRefInt function.  So, in the bootstrap action, modify foreign key fields to call that function instead of just setting the numeric value.

To reset the primary key, create an action that calls an advanced query.  In that query use TRUNCATE TABLE {<entity name>}.  This will delete all the records and reset the identity value.  The curly brackets cause the platform to translate between the entity name and the actual SQL table.   Create a web screen with a button that calls this action.  (Ref - http://www.codeproject.com/Tips/264381/How-to-RESET-identity-column-in-SQL-Server)

Hope this helps.
Ricardo,

I really think the best way is to continue using referential integrity and auto numbers.
As you pointed out, there is this video at the Academy that shows how to customize the Excel bootstrap for this.

Even if you fixed the database to stop using autonumbers (check the attribute IsAutoNumber property), when deploying your application to another environment, you would need to do this process in the new environment.
If you customize your bootstrap, you might save some time ahead and avoid these problems.

I'm sure it's a bummer starting from scratch, but if you only scaffolded some CRUD screens, the platform makes it very easy to do so - easy to a point that it's just 3 drag and drops.
See the videos on how to List, Show and Create records.

Of course, if you already had several customizations, starting from scratch might not be productive.
Hi thanks João a Kurt for the answers.

We adapted the databases outside OS to be able to use the Excel bootstrap and it works fine. Now we only need to solve all the inconsistencies in the old database, but that is not an Ousystems problem!
 
We still haven't figured out how to reset the primary keys, but will get to that in a few days and then comeback for more help!
Ricardo,

I understand your concern. You are constantly importing and deleting data, and the primary keys keep increasing since they are auto number.
Given that in OutSystems, primary keys are Ints, this means that you can "only" represent 2^31-1 (2,147,483,647) numbers.

OutSystems simply creates database tables when you model your Entities. This means that everything (from auto increments to referential constraints) is managed by your own database.
So to reset your autoincrement you can:
  • Connect to your database and use database specific commands. See this stackoverflow answer for SQL Server, and this one for Oracle.
  • Keep developing until you know your bootstrap logic is correct. Then you can copy the Entity you have, and delete the original one. For Service Studio everything is going to be the same, but under the hood, you have just created a new database table. The OutSystems Platform always plays on the safe side: you may delete the entity but it does not delete the database table, since you might have valuable data stored in there.
I will start with a clean slate here. We will need to upload data from our excel files into our nearly created application on the Outsystems Platform. Our application is not going to be connecting to external databases so our situation is simpler but we need to grasp how to upload our data for tables which do contain columns with data that in Outsystems will be referenced via foreign keys. So, I will provide a basic example. I have an Outsystems Entity called "Student" which will have biographical information including Gender, The Student Entity will include an attribute called "GenderId". We will go ahead and create the Static Entity called "Gender" and add two records..."Male" and "Female". Now, I know that the two IDs for these two records will be 1 and 2. Now, for future reference, our Entities that will serve as reference tables are not going to just have two records but I wan to keep the example basic.

My current Excel file with the Students table has a column for gender and it of course contains either "Male" or "Female" and is a text column.

How do I go about bootstrapping the Students table into Outsystems knowing that my Student entity has a foreign key called "GenderId" which is used to reference the "Gender" entity? Am I supposed to change my excel file so that the Gender column is converted into integers and is renamed GenderID? Will that even work? That maybe too complicated if my reference entity has more than just two records. Do I leave the column as "Male" and "Female"? I don't think that works either.

Please provide me with a basic solution.

Sam
Hi Sam,

Check out this video tutorial.
Hi Andre,

I will definitely do that. I just finished Bootcamp training and currently reached Module 14 in the training videos so did not realize that the question I posed will be addressed in the videos later. Thanks for the trip and great job overall on the videos. They are an excellent resource for those new to the platform.

Sam


André Vieira wrote:

Hi Sam,

Check out this video tutorial.