Custom Action Creates Duplicate Key Value

Forgot to mention, the new record works fine with user entry.

Hi @andriitkachenko,

Thank you for your advice.

I added the below to my code and it seemed to work:

while (true)
{
    try
    {
        qcEntry.Actions.PressSave();
        break;  
    }
    catch (PXException ex) when (ex.Message.Contains("duplicate key row"))
    {
        // Generate a new NoteID and try again
        record.NoteID = Guid.NewGuid();
        qcEntry.QCCheck.Update(record);
    }
}

However, I will look at the cache contents and see if there are clashes.

I will also remove .Update() and see what that does.

The chance that Guild.NewGuid() generated 2 same Guids in one method are close to 0, and if that happens consistently for you it tells me something’s wrong with the process.

Your solution helps, but:

1. You don’t seem to save the updated record, meaning it gets lost (it might also tell Acumatica that there are unsaved changes, which would force a pop-up each time you’re trying to move to another record/screen or close the page)
2. The 2nd record is still there, while it looks from the code snippets that you expect only 1 record to exist. This might cause table bloat and ghost records to exist, making it hard to work with

You shouldn't need to specify a value for the NoteID at all. Set it to null and Acumatica will find one when it is inserted.

It doesn't look like anything is being changed in the 'moveEntry' graph. Why are you saving it?

Is there an index on the NoteID field in your table? Is it set to be unique?

Hi @darylbowman & @andriitkachenko 

Removing .Update() solved the issue.

I changed one of the actions to not include it resulting in the below:

public PXAction<AMBatch> RecordPressGlueLogs;
[PXButton]
[PXUIField(DisplayName = "Record Press Glue Logs", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select)]
protected virtual IEnumerable recordPressGlueLogs(PXAdapter adapter)
{
    var moveEntry = PXGraph.CreateInstance<MoveEntry>();
    if(moveEntry == null)
    {
         return adapter.Get();
    }

    var logEntry = PXGraph.CreateInstance<GCPressGlueLogEntry>();
    var move = Base.batch.Current;
    var header = logEntry.Header.Insert();
    header.BatchNbr = move.BatNbr;
    header.DocType = move.DocType;
    header.Date = move.TranDate;


    logEntry.Actions.PressSave();
    moveEntry.Actions.PressSave();

    PXRedirectHelper.TryRedirect(logEntry, PXRedirectHelper.WindowMode.New);

    return adapter.Get();
}

and this works as expected!

Thank you!

@darylbowman, I have just checked and there is no index on the table, should there be an index/how should i set one up.

@darylbowman I was getting the action working, I have added fields to the AMBatch which will display the Log entry no. and the qc check no.

There's nothing wrong with how you had it, and it's how I would write it (to update before saving). However, upon insert, Acumatica should be assigning a NoteID and then immediately after, you're changing it to a new one. This effectively creates two records (I think). Have you tried removing that line?

@darylbowman with or without  the  record/header.NoteID = Guid.NewGuid(); it gave the error. It was my attempt at generating a unique one.

If removing the Update() solves it, great. But I would say that it indicates an issue somewhere else.

You mentioned a Index on the NoteID field, my table doesnt have that, should i set this up?

Does it have to be clustered or can it be non-clustered?

@aiwan each table has at least 1 index (clustered) if you’ve set the primary keys for your table properly.
There can only be 1 clustered index.

A non-clustered index is a specific-use-case tool, where you optimize a table for search by a specific set of fields. They can improve the speed of retrieval of records by the said set of fields, but inserting and updating speed suffers with each new index. So it’s an instrument to use with caution and only when the need arises.

I’d say you don’t need to bother about indexes right now. Again - please check your DAC declaration and table script. Outside of some niche cases, you should have the same set of keys both in the C# and SQL part (aside from CompanyID - as we don’t need to add it to the C# class, we can’t mark it with IsKey = true).

The fact that .Update() helped you to remove the issue reinforces my theory about keys not working as expected.

[PXDBGuid] has a logic to default the field to a new Guid, so I agree with Daryl, you don’t need to set this manually. But I don’t think that something breaks when you manually set it - it’s a redundant move, but not a code-breaking one.

If your DAC is meant to have a Guid as a key field, it should be defined like this in C#:

        #region YourGuid
        [PXDBGuid(IsKey = true)]
        [PXUIField(DisplayName = "Remove this attribute if you don't want to show it on the UI")]
        public Guid? YourGuid { get; set; }
        public abstract class yourGuid : BqlGuid.Field<yourGuid> { }
        #endregion YourGuid

And this should be your only Key field in the class.

In SQL, make sure your table has Primary Key - CompanyID + YourGuid

Unclustered is fine. Use CompanyID and NoteID as the columns. Set IsUnique to Yes.

Thanks @darylbowman!

From:

        var record = qcEntry.QCCheck.Insert();
        record.NoteID = Guid.NewGuid();
        record.BatchNbr = move.BatNbr;
        record.DocType = move.DocType;
        record.Date = move.TranDate;
        qcEntry.QCCheck.Update(record);

To:

        var record = new WhateverTable(); // qcEntry.QCCheck.Insert();
        record.BatchNbr = move.BatNbr;
        record.DocType = move.DocType;
        //record.NoteID = Guid.NewGuid(); // Probably don't need, but can leave it in till the update works
        record.Date = move.TranDate;
        record = qcEntry.QCCheck.Update(record); // Could use Insert here