Primary Unique DB Records

[HT3]

Hi All,

I just need some guidance on an issue I'm having, I have some code that creates a new manifest record if one isn't found.

SNIP OF CODE

FIND FIRST Manifest
            WHERE Manifest.ArEntity = Order.ArEntity
            AND Manifest.OrderNumber = Order.OrderNumber
            AND Manifest.ConsignmentNumber = Boxes.BoxBarcode
            NO-LOCK NO-ERROR.
                         
                IF NOT AVAILABLE(Manifest) THEN
                DO:
              
                iCnt4 = iCnt4 + 1.
            
                        CREATE Manifest.
                        ASSIGN Manifest.ConsignmentNumber = Boxes.BoxBarcode
                                          Manifest.CustomerACCode = Order.Shipto
                                          Manifest.CustomerName = Order.ShipName
                                          Manifest.Address1 = Order.ShipAddress1
                                          Manifest.Address2 = Order.ShipAddress2
                                          Manifest.Address3 = Order.ShipAddress3
                                          Manifest.Country = Order.ShipCountry
                                          Manifest.PostCode = Order.ShipPostCode
                                          Manifest.Weight = string(Boxes.Weight)
                                          Manifest.DespatchRef1 = Boxes.ShipmentTrackingNumber
                                          Manifest.licenceplateno = int64(Boxes.ShipReference)
                                          Manifest.OrderNumber = Order.OrderNumber
                                          Manifest.ServiceID = Order.ServiceID
                                          Manifest.ArEntity = Order.ArEntity
                                          Manifest.DespatchNumber = Boxes.DespatchNo.
                END.

I know a record isn't found as the iCnt4 has a value and therefore one should be created for an order and box record however it fails. The error logged in the app server is as follows
[25/11/12@10:21:12.562+0000] P-000000 T-013300 1 AS -- (Procedure: 'NLUPSTrackUpdate.r' Line:1508) ** Manifest already exists with Carrier Code "" ConsignmentNumber "DEL04266434". (132)

Now the database table does have an existing record with the same ConsignmentNumber and a blank ShippingMethod but a different order number. Having looked further into it the manifest table, the ConsignmentNumber and ShippingMethod have been set as a Primary Unique Index and both fields have not been set as Mandatory.

Question 1

Has my new record been correctly stopped from being created, as I already have an existing record with the same ConsignmentNumber and ShippingMethod ?
(The app server log states the CARRIER CODE as that is the fields label, field name is Shipping Method).



The reason I'm confused is because the database table has thousands of records with the same ConsignmentNumber and ShippingMethod but with different Order
Numbers...

Question 2

How could duplicate records be created with the same ConsignmentNumber and ShippingMethod?

I've already asked the question to see if the database table properties\scheme were changed but apparently it's always been the same.


TABLE INDEXES IF IT HELPS FURTHER.



Thanks in advance

 

[George Potemkin]

Where do you assign a value to Manifest.ShippingMethod? Your code is totally forgot about this field. FIND statement uses all components of the OrderNumber index. ASSIGN statement also missed at least the Transferred, CarrierCode and LocationCode fields.

Your ASSIGN statement does not create the key in the ShippingMethod index. What is the statement on the line 1508 in debug-list of NLUPSTrackUpdate procedure? Is it the FIND statement? Or the END of transaction block? End of loop for the iCnt4 counter?

Why the transaction scope in your code is wider than the DO block above? You're creating a new record while the previous db changes are not yet committed.

 

[PatrickTingen]

According to your screenshot there is only one unique index on your table, consisting of two fields: ShippingMethod and ConsignmentNumber. Yet, your FIND FIRST searches for a record with 3 fields: ArEntity, OrderNumber and ConsignmentNumber.

There is your problem. Your unique index consists of fields A and B and your search for fields B, C and D.
Based on your code, your query should look something like this:

FIND Manifest NO-LOCK
  WHERE Manifest.ShippingMethod = <some shipping method>
    AND Manifest.OrderNumber    = Order.OrderNumber NO-ERROR.

  IF NOT AVAILABLE(Manifest) THEN
  DO:             
    iCnt4 = iCnt4 + 1.
    CREATE .....

Your code currently searches for some record, using one of the two unique fields but since there is a second field in your unique index, the record you might find could have any value for that. Then you create a record and assign the OrderNumber, but not the ShippingMethod, so that will be assigned the default value, which is blank, according to the error you get in the log (Manifest already exists with Carrier Code "").

If ShippingMethod is not relevant in your code, you could ignore it, using this code:

FIND Manifest NO-LOCK
  WHERE Manifest.ShippingMethod = ""
    AND Manifest.OrderNumber    = Order.OrderNumber NO-ERROR.

  IF NOT AVAILABLE(Manifest) THEN
  DO:             
    iCnt4 = iCnt4 + 1.
    CREATE .....

If your code needs a FIND FIRST (emphasis on "FIRST"), you know there is something wrong with the indexes. FIND FIRST is basically a "FIND ANY" operation, and in >90% of the cases probably not what you need