Phantom Data? Error: 1:N Relationship Error: Record already linked to another.

[stellarfinn]

I have two databases. A Cost of Goods (COGs) database and a Products database. Once we're happy that a product will meet our margins, I push the product data from the COGs database to the Products database (create a new row). This has been working fine until a couple of products didn't get pushed to the Products database. I got this error when testing my automation manually with the data from the specific products that are failing.

The products in question had previously existed in the Products database, so is there some kind of phantom data that its comparing to and making this conclusion? I have other products that the automation works fine and get created in the Products database.

{
"success": false,
"response": "Error: 1:N Relationship Error: Record already linked to another."
}

[melohagan]

The error implies that you have configured a One-to-Many relationship, but you are trying to create a record that would violate this relationship.

So you may be trying to create a record on the many side, that is linked to the one side, but that particular record has already been linked to another record.

It may help if you shared how your relationships are configured in Budibase, and what test data you are using when doing your manual run.

[stellarfinn]

@melohagan I understand that is the typical expectation.

The relationship field in the Products db is One Row Row in Products to Many Rows in CostAnalysis.
The relationship field in the CostAnalysis db is Many Rows in Cost Analysis to One Row in Products.

In this case, there is no established relationship between any row in Products or any row in CostAnalysis for the these two products. This was working before the Support Team had to fix my Budibase because of a failed backup restore.

I've triple checked and the relationship I'm trying to establish does not exist for any product in the Products database where I'm trying to have my automation create a new row.

This is an isolated issue to two rows in the CostAnalysis that I'm trying to establish the relationships in the Product database upon creating a new row. All other automations run successfully.

I've emailed a response to Conor with Support that details it in depth but I will include it in another response below. I also shared an export of the app with Conor.

[stellarfinn]

My email to Conor (support@budibase.com):

I checked the relationship fields in both the CostAnalysis db and the Products db.

The relationship field in the Products db is One Row Row in Products to Many Rows in CostAnalysis.
The relationship field in the CostAnalysis db is Many Rows in Cost Analysis to One Row in Products.

The automation issue is happening in my COG Merge Single Product (and I assume the COG Merge All Products in Brand, although I haven't tested that one).

Example, if I delete the 147th row in the front end, CEN112 (field product_code), I can "Merge Product" (run the automation) from the Cost Analysis page and it will create the product in the Products database. No problem.

I'm unable to successfully run the automation for CEN367 and CEN787 without getting the error:

{
"success": false,
"response": "Error: 1:N Relationship Error: Record already linked to another."
}

I've checked both the CostAnalysis & Products dbs and there's no existing relationships, even erroneously, with other products.

I would love to be able to do a video chat with someone and show them the problem because it seems complex to explain and I think its phantom data (a product that doesn't show up in the database view, but somehow still exists in it).