Hello,
I am trying to bootstrap data from Excel. The data were exported from a database that is already in use. Unfortunately, there are a lot of blank cells in columns that are supposed to be used as foreign Keys
In the attached file, I want to use ContentID as a foreign key.
Is there a way to do this?
Thanks,
Marios
Hi Marios,
I believe that you have already entity structure is created, and now just exporting the data from excel, and i tried like similar with what you are explain, i can able to import the data,
1) i created the Entity with new columns created and one with ContentID foreign key(parent table is already exists in database already)
2) After i have put my step which i follow to bootstrap data from excel, see the attachment (all before publish)
3) Then i completed all steps in word document, and finally i published, and i able to see the data in new table with foreign key inserted.
if you still error then, try this,
1) Can you try delete the table MVMEDIAINVENTORY(i believe new table and no data exists), Publish eSpace
2) After try the step in word document.
3) And publish eSpace again.
Balu
Balu wrote:
I created the entities using OutSystems Import Entity from Excel feature. I then changed the structure of the entity to remove the Identifier set automatically by the platform and set the original table’s ID field as the identifier.
I did publish to see that the data was imported correctly, and then I tried to set the FKs. I do believe that the issue is with the 0 values not recognized as Null Identifiers.
Are you facing a specific problem or error? FKs are the exception to the OS default values rule in the DB ans can hold null values.
Cheers,
Tiago.
Can you replace all blank cells with 0, it will be nullidentifier, after try bootstrap.
Balu,
I tried that, but I get the following error:
Upgrade ErrorCould not create foreign key constraint. This may have happened because there are 'Studio' values of entity 'MVMediaInfo' with no corresponding value in entity 'MVstudio', or attribute 'Studio' of entity 'MVMediaInfo' is creating a circular dependency between entities. Check the Error Log for more information.
Is there a setting/option to convert 0 values into Null?
Marios Tofarides wrote:
Hi Marios
Another manual approach,
1) Import excel manually in resource
2) do the configure the bootstrap manually, and in the assignment statement before createorupdate system method for the respecitive entity called, for column ContentID check condition income value of excel in the loop, if empty or 0 then pass nullidentifier(), else pass value as integer(make sure the value must be exists in the parent table)
3) After try publish eSpace.(Bootstrap with timer when Publish)
I would guess that the problem is with the filled values. Are you sure that the values in the ContentID column exist in the Content entity as PK? Did you bootstrap that entity also? If so how are you assigning the values to the PK?
Tiago,
I bootstrapped the table that contains ContentID again in order to get the latest data from the existing database
I still get that error.Could not create foreign key constraint. This may have happened because there are 'ContentID' values of entity 'MVMediaInventory' with no corresponding value in entity 'MVMediaInfo', or attribute 'ContentID' of entity 'MVMediaInventory' is creating a circular dependency between entities. Check the Error Log for more information.
I believe that Outsystems does not convert 0 to null, and is expecting a ContentID of 0 in MVMediaInfo
Again, can you please share the complete log of the error as it is written in Service Center?
Tiago Gafeira wrote:
This is the error message from Service Center's Error Log:
Are both entities already created ans only MVMediaInfo filled with values?
Hos was the bootstrap process built? By hand or using OutSystems built-in capabilities?
From the error, it appears that you're applying changes to the DB model when publishing.
No, both have values. Both MVMediaInfo and MVMediaInventory have values. ID from MVMediaInfo is the foreign key to MVMediaInventory’s ContentID attribute.
Ok, so you're getting the error because you imported the fields without having the FKs set. In that scenario, all columns will have the default value for the given data type.
If You don't have direct connectio to the database to transform the empty ContentID values into NULL, I'll recommend the following steps.
Another option should be start everything from scratch, with CompanyID defined as a FK from the beggining, as in that case the platform uses the NULL value.
Any progress on this?
Hi Tiago,
I'm currently implementing some of the solutions provided. It seems like there were redundant ContentID values in MVMediaInventory table. I'll get back to you ASAP
Thanks again,
Hi Guys,
I believe I have solved the problem.
I tried it on another entity and it worked, so I believe it will work on MVMediaInventory as well
The steps I took were:
1. I made sure that there were no redundant data in the consumer entity
2. I imported the consumer entity from Excel using Outsystems' relevant feature.
3. Before Publishing I set up the Entity Identifier as well as the Foreign Keys. At the bootstrap action I changed the assignment for each foreign key to something like this:
SyntaxEditor Code Snippet
If(ConvertFromExcel.Current.Excel_MVActors.ActorID = 0, NullIdentifier(), IntegerToIdentifier(ConvertFromExcel.Current.Excel_MVActors.ActorID))
4. Finally, I published the eSpace without any problems
Thanks, Tiago and Balu for helping and suggesting solutions to this problem.
Regards,