Primary Unique DB Records

H

HT3

ProgressTalk.com Sponsor
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
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).

1762959732141.png

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.

1762960112994.png

Thanks in advance :)
 
Last edited:
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.
 
Last edited:
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:
Code: 
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:
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
 
Hi @PatrickTingen @George Potemkin,

This isn't my code, it's an old colleagues code that I'm picking up...

I will try and answer the questions I can.

1. Manifest.ShippingMethod is missing and they leave it blank which I believe is the reason it's not included in the create any longer.

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:
Click to expand...

2. In regards to the above, I had to include the order number as we have multiple records with the same ConsignmentNumber, ShippingMethod but different OrderNumber. I need a record for each OrderNumber.

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
Click to expand...

3. I think the FIND FIRST works in this scenario as we should have one record meeting the criteria below.

1763024537672.png

I will include the ShippingMethod in the create going forward and see if that works.

Thanks.
 
Based only on your example, I would recommend to add the TRANSACTION keyword to the DO block:
Code: 
                IF NOT AVAILABLE(Manifest) THEN
                DO TRANSACTION:
              
                iCnt4 = iCnt4 + 1.
            
                        CREATE Manifest.
                        ASSIGN Manifest.ConsignmentNumber = Boxes.BoxBarcode
 ...
                               Manifest.DespatchNumber = Boxes.DespatchNo.
                END. /* TRANSACTION */
 
2. In regards to the above, I had to include the order number as we have multiple records with the same ConsignmentNumber, ShippingMethod but different OrderNumber. I need a record for each OrderNumber.
Click to expand...
No. This is impossible with your current index. Your index dictates that the combination of ShippingMethod and ConsignmentNumber must be unique. You state that the ShippingMethod is left blank in all cases, so that basically sets you back to the ConsignmentNumber to be the only unique value.

If you cannot make changes to the database, AND the ShippingMethod is never used in your application, you could abuse that field and store the OrderNumber in it. That would make your records unique for the combination of ConsignmentNumber and OrderNumber. But beware that it adds an extra layer of fuzz on your application. Future developers will scratch their head as to what the ... is going on there.

Still, it *might* be a solution. Dirty, yes, but a solution all the same
 
But wait, there is another possibility. Perhaps even dirtier than the previous. If the field ShippingMethod is not mandatory (check it in that wonderful tool DataDigger :cool:), you could assign it the unknown value. Progress allows duplicate records when one or more of the fields in a unique index are unknown.

Your code could then look something like this:
Code: 
FIND FIRST Manifest NO-LOCK
  WHERE Manifest.ShippingMethod    = ?
    AND Manifest.ConsignmentNumber = Boxes.BoxBarcode
    AND Manifest.OrderNumber       = Order.OrderNumber NO-ERROR.
                       
IF NOT AVAILABLE Manifest THEN
DO:
  CREATE Manifest.
  ASSIGN
    Manifest.ShippingMethod    = ?              
    Manifest.ConsignmentNumber = Boxes.BoxBarcode
    Manifest.OrderNumber       = Order.OrderNumber
    Manifest.CustomerACCode    = Order.Shipto
    Manifest.CustomerName      = Order.ShipName
    Manifest.Address1          = Order.ShipAddress1
    ... etc

Note that in this case, you DO need the FIRST option on your FIND.

With a solution like this, you have a fair chance of becoming the proud winner of the Ugliest Code Award, perhaps even with a special shout-out for your one-of-a-kind indentation technique!
 
You must log in or register to reply here.
Back
Top