Not able to insert record. Error:Insert statement conflicted with foreign key

4. I have 1 form in which i have to fill some data and upload file at the end of form. For saving binary file i am using EquipmentCalibrationUpload entity.

5. In the save action, first i am saving all data of main entity and then using createOrUpdateEquipmentCalibrationUpload action in which i am passing ID which come from main entity.

6. When i am saving form main entity details are saving but file is not uploading.

Please suggest some solution.

Note: Refer screenshot.

Thanks and Regards,

Nayana

10 Mar 2021

Vonnelize Haupt

Solution

Hi Nayana

I managed to replicate the issue that you are having by building my own small app similar to what you built and I got the same foreign key error.

This is what I can see from my test:

When you create a record in a local entity, Table A, OutSystems assigns an id to it.
You then take that id and assign it to the record for the second linked entity, Table B. That is correct and it works. Let’s say this is the first record for the user on the device, so OutSystems assigns id 1 to both records in both entities.
However, at the same time another user does the same on a different device and in local storage. Since it is also his first record, OutSystems assigns id 1 to the record in local storage to both records in both entities.
Now user 1 syncs his data to the server.
OutSystems read all the new records that were created in the first table, and creates them into the server database using a standard create action, which means that the record gets a new id on the server. This is needed because user 2 is also going to sync his data later and he also has an id 1, and you cannot create two of the same id’s on the database. See my screenshot:
This works for Table A, because it is the first entity and it has an auto id as an identifier.
However, now OutSystems sync the data for the second entity, Table B and it tries to do the same. It creates the record with a null id, but that causes the referential error because the Table B does not have an auto id identifier and so it cannot use a nullidentifier. You have to tell outsystems which id to give it …. But you don’t know because you have lost the old id when the sync action deleted all the old local records and replaced it with the new ones.
So, you cannot use the standard logic to sync one-to-one entities.
You will either have to revert to using a normal entity table for Table B, with an auto id and add a reference id to Table A to it,
or you will have to customize the sync logic to sync the first record and immediately afterwards sync the linked record with the newly created id. So, you basically have to change the sync logic so that you do the syncs for both tables in one step and directly after each other, instead of running the sync one after the other:

I hope this explains your issue and that you have a better idea on how to fix it.

See solution in context

12 Mar 2021

Goncalo Duarte Almeida

Hi Nayana Sonawane

Usually, for these cases, we use a one-to-one relationship between the detail entity and the detail binary entity, and the most common error is when we are creating a new record the ID that is been sent on the binary entity goes null. So after you save the detail, you must set the ID of the binary as CreateOrUpdateActicon.id.

Something like this

I think this is your problem because you forgot to upload the screenshot ;)

Hope this helps

Regards

Gonçalo Almeida

10 Mar 2021

Vonnelize Haupt

Hi Nayana, I don't see a screenshot on your post, could you please add your screenshot?

I have a question though.

Are you using the createorupdateequipmentcalibrationupload action directly or are you using the

createorupdateequipmentcalibrationuploadFORSYNC action?

You should be using the action that ends with ...FORSYNC.

If you don't have those actions you have to create them. Right-click on your local database entity and click create action to sync data (read/write)

Also, when you test the app, are you testing on a computer or are you testing on a mobile device using a native app that you installed? Sometimes the local storage on a computer does not behave as it should on a device and you can get foreign key errors.

Hope this helps

10 Mar 2021

Nayana Chaudhari

Hello Experts,

Please check attached screenshot.

I am using for sync action. Also for binary entity Id, I am using ID of my main entity i.e. CreateOrUpdateEquipmentCalibration.id.

When I debug app on computer, Id is correctly passing but getting error at server sync action.

forum1.PNG

11 Mar 2021

Goncalo Duarte Almeida

Nayana Sonawane

Can you send a screenshot inside of that server the SyncEquipmentCalibrationUploads?

It's possible that you are using a create action or an update action instead of a createorupdate.

Regards

Gonçalo Almeida

11 Mar 2021

Vonnelize Haupt

Solution

Hi Nayana

I managed to replicate the issue that you are having by building my own small app similar to what you built and I got the same foreign key error.

This is what I can see from my test:

When you create a record in a local entity, Table A, OutSystems assigns an id to it.
You then take that id and assign it to the record for the second linked entity, Table B. That is correct and it works. Let’s say this is the first record for the user on the device, so OutSystems assigns id 1 to both records in both entities.
However, at the same time another user does the same on a different device and in local storage. Since it is also his first record, OutSystems assigns id 1 to the record in local storage to both records in both entities.
Now user 1 syncs his data to the server.
OutSystems read all the new records that were created in the first table, and creates them into the server database using a standard create action, which means that the record gets a new id on the server. This is needed because user 2 is also going to sync his data later and he also has an id 1, and you cannot create two of the same id’s on the database. See my screenshot:
This works for Table A, because it is the first entity and it has an auto id as an identifier.
However, now OutSystems sync the data for the second entity, Table B and it tries to do the same. It creates the record with a null id, but that causes the referential error because the Table B does not have an auto id identifier and so it cannot use a nullidentifier. You have to tell outsystems which id to give it …. But you don’t know because you have lost the old id when the sync action deleted all the old local records and replaced it with the new ones.
So, you cannot use the standard logic to sync one-to-one entities.
You will either have to revert to using a normal entity table for Table B, with an auto id and add a reference id to Table A to it,
or you will have to customize the sync logic to sync the first record and immediately afterwards sync the linked record with the newly created id. So, you basically have to change the sync logic so that you do the syncs for both tables in one step and directly after each other, instead of running the sync one after the other:

I hope this explains your issue and that you have a better idea on how to fix it.

12 Mar 2021

Nayana Chaudhari

Hello @Vonnelize Haupt ,

Thank you for solution. It really works.

I appreciate your response on my post.

Regards,

Nayana

15 Mar 2021

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines