Acumatica ARtran table import Unique Fields

Question

I have an "Import Scenario" that inserts records into the ARTan table on an hourly schedule. The problem I have is that at I have no way of making sure that records are not inserted twice. I have a Primary Key called FeeDueID the AR record should only be inserted one time. Is there a way to lock this down in Acumatica so multiple users can't accidently insert multiple records?

I was told that if I select my unique field in Acumatica "Data Provider" as a key field this would not allow the duplicate to be inserted. However, this is not the case. I'm really looking for a way to have the Acuamtica framework only allow one AR record based on my user defined primary key. I was able to solve this isue by putting a trigger on the table. I was told that Acumatica doesn't support triggers.

Additional Info:

· The “Key” property of the schema is only to help identify unique records during the “Prepare” portion of the import scenario. There appears to be some confusion on when this flag is being utilized, a point we’ve reached out to Acumatica to help clarify.

· Once records have been prepared, the settings for a data scenario have no direct involvement in the actual import process

· If an import scenario is currently being processed and if a 2nd prepare is executed, Acumatica will process any items it has not yet flagged as imported/processed. This includes items that were previously prepared.

Sergey Mar Sergey Mar · Accepted Answer · 2016-08-18T09:00:04

It is not correct to skip record by trigger, as you will have wrong totals on documents.

If it is ok for you to handle it with trigger, than i assume that you are ok if system will skip this item automatically without any message?

If yes, this customization may be useful for you. It will do check for similar records in database and just delete it. In case of different requirements you may change this code and throw error or to different select.

Be aware, that depend on size of data it may affect performance. I highly recommend you to create a custom index on your column.

public class ARInvoiceEntry_Extension : PXGraphExtension<ARInvoiceEntry>
{
    public delegate void PersistDelegate();
    [PXOverride]
    public void Persist(PersistDelegate baseMethod)
    {
        if (Base.IsImport)
        {
            PXView _view = new PXView(Base, false, BqlCommand.CreateInstance(typeof(Select<ARTran, Where<ARTranExt.usrCustomKey, Equal<Required<ARTranExt.usrCustomKey>>>>)));

            //Checking Unique Value
            foreach (ARTran tran in Base.Transactions.Select())
            {
                ARTranExt ext = Base.Transactions.Cache.GetExtension<ARTranExt>(tran);
                String search = ext.UsrCustomKey;

                if (search != null)
                {
                    int startRow = 0, maxRows = 2, totalRows = 0;
                    List<object> records = _view.Select(null, new[] { search }, null, null, null, null, ref startRow, maxRows, ref totalRows);

                    bool found = false;
                    foreach (ARTran record in records)
                    {
                        if (found) // if it's not the same record
                        {
                            Base.Transactions.Delete(record);
                        }
                        found = true;
                    }
                }
            }
        }
        baseMethod();
    }
}

Acumatica ARtran table import Unique Fields

1 Answers