Acumatica PXSelector, search 2 columns in selector or get other values from selector fields.

Hi everyone,

I am having screen with Supplier (Right) Grid.

Having fields Vendor Ref, Ref Nbr.

When i will select Matched Vendor ref. selector the related record should populate in Ref Nbr.

But in my case, i am having same Vendor reference nbr with same datatype so there is no unique field.

Ref Nbr     Vendor Ref

A               000011158

B               000011158

C               000011158

When i am select  B  - 000011158 it will populate A - 000011158.

Selector Code:

    #region MatchRefNbrExcel // working here commented below n added my code WIP
    [PXDBString(255, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Matched Vendor Ref.")] // Rajani changed on 29 Jan 2024
                                                   
    [PXSelector(
typeof(Search5<APInvoice.invoiceNbr,
    InnerJoin<BAccountR,
        On<BAccountR.bAccountID, Equal<APInvoice.vendorID>>,
    InnerJoin<APRegister,
        On<APRegister.refNbr, Equal<APInvoice.refNbr>>,
    InnerJoin<APRegisterKvExt,
        On<APRegister.noteID, Equal<APRegisterKvExt.recordID>>>>>,
     Where2<
        Where<APInvoice.vendorID, Equal<APAutomatedReconciliation.vendorID.FromCurrent>,
            Or<BAccountR.parentBAccountID, Equal<APAutomatedReconciliation.vendorID.FromCurrent>>>,
        And<APInvoice.docType, Equal<Current<doctypeExcel>>,
        And<
           Where<APRegisterKvExt.valueString, Equal<AttrMatchType>,
               And<Where<APRegisterKvExt.valueString, NotEqual<AttrMatchTypeAuto>,
                  Or<APRegisterKvExt.valueString, NotEqual<AttrMatchTypeManual>>>>>>>>,
 Aggregate<
      GroupBy<APInvoice.refNbr,
    GroupBy<APInvoice.invoiceNbr,
    GroupBy<APInvoice.docType>>>>>),
typeof(APInvoice.docType),
typeof(APInvoice.docDate),
typeof(APInvoice.vendorID),
typeof(APInvoice.refNbr),
typeof(APInvoice.curyOrigDocAmt),
typeof(APInvoice.invoiceNbr),
ValidateValue = false,Filterable =true)]

    public virtual string MatchRefNbrExcel { get; set; }
    public abstract class matchRefNbrExcel : PX.Data.BQL.BqlString.Field<matchRefNbrExcel> { }
    #endregion

Logic :

 protected virtual void APAutomatedReconciliationLineExcel_MatchRefNbrExcel_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
 {
     APAutomatedReconciliation Header = this.Document.Current;
     APAutomatedReconciliationLineExcel line = e.Row as APAutomatedReconciliationLineExcel;
     string errormsg = string.Empty;

     if (Header.Status == "C")
     {

         APInvoice excel = PXSelect<APInvoice,
            Where<APInvoice.invoiceNbr, Equal<Required<APInvoice.invoiceNbr>>>>
            .Select(this, line.MatchRefNbrExcel); // Commented above and added this 9 Feb 2024 Rajani

         if (excel != null)
         {
             if (line.MatchRefNbrExcel == excel.InvoiceNbr && line.APStatementDocBalIncVatExcel == excel.CuryOrigDocAmt && line.DocTypeExcel == excel.DocType)
             {
           
                 line.MatchTypeExcel = "Unmatched";
               line.RefNbr = // Here i want Selected records Reference Nbr

                 APInvoiceEntry Graph = PXGraph.CreateInstance<APInvoiceEntry>();
                 Graph.Clear();
                 APInvoice AP = PXSelect<APInvoice,
                                     Where<APInvoice.refNbr, Equal<Required<APInvoice.refNbr>>,
                                     And<APInvoice.docType, Equal<Required<APInvoice.docType>>
                                     >>>.Select(this, excel.RefNbr, excel.DocType);
                 if (AP != null)
                 {

                     AP.PaySel = true;
                 }
                 else
                 {
                     AP.PaySel = false;
                 }
                 AP = Graph.CurrentDocument.Update(AP);
                 Graph.Actions.PressSave();

             }
             else
             {
                 errormsg = "Values Should Match."; // Rajani changed message 29 Jan 2024

                 line.Reconciled = false;
                 line.MatchTypeExcel = "Unmatched";
                 line.ReconciliationReasonExcel = "";
                 line.MatchRefNbrExcel = "";

                 PXUIFieldAttribute.SetError<APAutomatedReconciliationLine.matchDocNbr>(cache, line, errormsg);
             }
         }
     }
 }