bootstrap numeric data from Excel with blank cells as foreign key

bootstrap numeric data from Excel with blank cells as foreign key

  

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,


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.



Hi Marios,

Can you replace all blank cells with 0, it will be nullidentifier, after try bootstrap.


Thanks,

Balu

Balu,


I tried that, but I get the following error:

Upgrade Error

Could 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


Hi Marios,

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?


Cheers,

Tiago.

Marios Tofarides wrote:

The values are bootstrapped from Excel. I've checked the "Studio" values saved in "MVMediaInfo" table and they all exist in "MVStudio". It can't be otherwise; in the legacy app that I am replacing, the values are selected from a drop-down list box.

If that's the case, great. I was asking because a common mistake is to bootstrap tables with Id as AutoNumber and not dealing with that in "child" tables.

Are you able to send the complete message from the Error Log? That should be really useful.




Marios Tofarides wrote:

Balu,


I tried that, but I get the following error:

Upgrade Error

Could 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


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)


Balu


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


Marios

Again, can you please share the complete log of the error as it is written in Service Center?

Tiago Gafeira wrote:

Again, can you please share the complete log of the error as it is written in Service Center?

This is the error message from Service Center's Error Log:

Message: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.
Stack:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_22L_MVMEDIAINVENTORY2_OSUSR_22L_MVMEDIAINFO3_CONTENTID". The conflict occurred in database "MORI1A027", table "dbo.OSUSR_22L_MVMEDIAINFO3", column 'ID'.

While executing:
ALTER TABLE [MORI1A027].DBO.[OSUSR_22L_MVMEDIAINVENTORY2] ADD CONSTRAINT [OSFRK_OSUSR_22L_MVMEDIAINVENTORY2_OSUSR_22L_MVMEDIAINFO3_CONTENTID] FOREIGN KEY ([CONTENTID]) REFERENCES [MORI1A027].DBO.[OSUSR_22L_MVMEDIAINFO3] ([ID])

from file 'CreateForeignKeys.sql' of module 'CMM_Personal'.

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.


Solution

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.


Thanks,

Balu

Solution

Tiago Gafeira wrote:

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. 


Balu wrote:

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.


Thanks,

Balu

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. 


Marios


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.

  1. Create a new attribute ContentID2 as a FK
  2. Create a timer that runs on bootstrap that copies the values  different form 0 of ContentID into ContentID2
  3. Publish and wait for the timer to run
  4. Delet2 old attribute ContentID and rename ContentID2

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.


Hi Marios,

Any progress on this?


Cheers,

Tiago.

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,


Marios

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,


Marios