Fixed query issue

[Former Member]

Hi,

Can someone help to fix the issue with Fixed query.

INSERT INTO IdentifiedMaterial (
        MaterialIdentificationID,
        BOMSID,
        DateTime,
        Barcode)
VALUES (
         [Param.1],
        [Param.2],
        getdate(),
        '[Param.3]'
);

When I pass the values and execute it thorws an error as

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Identifie__Mater__76B698BF". The conflict occurred in database "SAP_BM", table "dbo.MaterialIdentification", column 'ID'. The statement has been terminated."

I checked the ID which seems to be a FOREIGN KEY in IdentifiedMaterial database which seems to be maintianed relationships with MaterialIdentification column "ID" and ID in BOMS database.

Thanks,

Raveen

Edited by: Michael Appleby on Aug 4, 2011 7:28 PM adding in the code indicators

[sidnooradarsh · Accepted solution]

Hello Raveen,

Foreign Keys in SQL are used to maintain the referential integrity of the data which means say for example there are two tables

1) Customer Table - Contains List of all Customers with CustID as Primary field

2) Orders Table - Contains all Orders and Customers associated with them

So Orders Table is related to Customers Table via "CustID" field what it really means is you cannot insert a CustID in Orders Table which is not available in Customers Table.

The same rule goes to your situation as well, you cannot insert a Material ID in IdentifiedMaterial if that MaterialID is not present in MaterialIdentification table. First you need to have the Material in MaterialIdentification table only then you can Identify a Material.

Hope this makes sense

PS: please include your code in code block while posting thread it makes much readable.

Regards,

Adarsh

[Former Member · Accepted solution]

Hi Adarsh,

Thanks for the explanation now when I pass the values of " ID" Column taken from BOMS table and Material Identification table to the Parameter's field in the query then the Query got executed and I see these values in for Identified Material table.

This issue I am relating to the Batch Manufacturing Material identification, on the "Identify Material" screen all planned components for the process order should be displayed i,e Material number, batch number( if applicable), and the planned material quantity but in this case I dont see any Material List for the Process order. And I was checking the queries associated with them on workbench and came across with Insert Identified Material command query. Is someone came across such issues while implementing Batch Manufacturing templates if would be great to know if I am missing anything or need to configure anything more.

Thanks,

Raveen