How to set an unbound selector to another value from the same table

  • 11 January 2021
  • 6 replies
  • 1198 views

Userlevel 6
Badge +3

I have a table that holds a text value of the GL account code from an import. On the Grid in the form, I want to display a Account Selector on the same view that is defaulted to the GL account code from the text field.

The PXSelector for AccountID is an unbound field.

Here are the two fields from the DAC

        #region AccountID
        [PXInt()]
        [PXUnboundDefault]
        [PXUIField(DisplayName = "Account")]
        [PXSelector(typeof(Search<Account.accountID>),
            typeof(Account.accountID),
            typeof(Account.description),
            SubstituteKey = typeof(Account.accountCD),
            DescriptionField = typeof(Account.description))]
        public virtual Int32? AccountID { get; set; }
        public abstract class accountID : PX.Data.BQL.BqlInt.Field<accountID> { }
        #endregion

        #region AccountCD
        [PXDBString(10, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Account CD")]
        public virtual string AccountCD { get; set; }
        public abstract class accountCD : PX.Data.BQL.BqlString.Field<accountCD> { }
        #endregion
 

I could probably set the value of the AccountID field in the RowSelected event but I know that is a no-no.

I tried using the Search string get it’s value from the AccountCD field but it only restricts the lookup to that GL account.

The Selector works fine on the Grid and you can select an account.  I just want it set to whatever the respective AccountCD value is.

Anyone have a trick to do this?


6 replies

Userlevel 7
Badge +17

Hi @joe21 

You can write the FieldDefaulting event for this “AccountID” field and, add logic to fetch AccountID based on the AccountCD value from that particular row and assign it to the AccountID field. 

This way, the selector field will show the Account ID, which respects the AccountCD value.

 

Hope this helps!!

 

Userlevel 6
Badge +3

Thanks Naveen for your assistance.  I really appreciate it.

For some reason, I cannot hit the breakpoint in the FieldVerifying event for either of the two fields.  I put a FieldVerifying event on the AccountCD field because I couldn’t hit the one for AccountID.  I can’t hit either one.

        protected void _(Events.FieldDefaulting<ICSPending, ICSPending.accountID> e)
        {
            ICSPending row = e.Row;
            if (row.AccountCD != null)
            {
                Account lookupaccount = SelectFrom<Account>.Where<Account.accountCD.IsEqual<@P.AsString>>.View.Select(this, row.AccountCD);
                Account account = PXSelectorAttribute.Select<Account.accountID>(e.Cache, lookupaccount) as Account;
                e.NewValue = account.AccountID;
            }
        }

        protected void _(Events.FieldDefaulting<ICSPending, ICSPending.accountCD> e)
        {
            ICSPending row = e.Row;
            if (row.AccountCD != null)
            {
                Account lookupaccount = SelectFrom<Account>.Where<Account.accountCD.IsEqual<@P.AsString>>.View.Select(this, row.AccountCD);
                Account account = PXSelectorAttribute.Select<Account.accountID>(e.Cache, lookupaccount) as Account;
                e.NewValue = account.AccountID;
            }
        }
 

I cannot even hit the RowSelected event for the table.  

        protected void _(Events.RowSelected<ICSPending> e)
        {
            ICSPending row = e.Row;
            if (row == null) return;
        }
The form looks good though…

This is the DAC for the two fields in case you see anything weird:

        #region AccountID
        [PXDBInt]
        [PXDefault(typeof(Account.accountID))]
        [PXUIField(DisplayName = "Account", Enabled = true)]
        [PXSelector(typeof(Search<Account.accountID>),
            typeof(Account.accountID),
            typeof(Account.description),
            SubstituteKey = typeof(Account.accountCD),
            DescriptionField = typeof(Account.description))]
        public virtual Int32? AccountID { get; set; }
        public abstract class accountID : PX.Data.BQL.BqlInt.Field<accountID> { }
        #endregion

        #region AccountCD
        [PXDBString(10, IsUnicode = true, InputMask = "")]
        [PXDefault]
        [PXUIField(DisplayName = "Account CD", Enabled = true)]
        public virtual string AccountCD { get; set; }
        public abstract class accountCD : PX.Data.BQL.BqlString.Field<accountCD> { }
        #endregion
 

To add insult to injury, I cannot get either of these fields to be editable in the Grid.

I’m about to blaspheme Acumatica.

 

Userlevel 7
Badge +17

Hi @joe21,

The above events will NOT invoke to load the data in the grid. We need to write a logic in view delegate to load the data. Please find the details below.

As per your requirement, I have created a sample screen and loaded the Account ID, Description in the grid.

Below are the steps, which I did from my side.

-- Initially, I created a table with (Line Nbr, AccountID, Description, AccountCD) and added only the AccountCD column in the table, as shown in the screenshot.

-- Create a screen, DAC, and Graph and written  logic in Graph.cs file to load the AccountID, Description in the grid. 

 

Please find the source code and screenshots attached for your reference. 

 

Hope this helps!!

 

View Delegate Code 

 public class PractiseGraph : PXGraph<PractiseGraph>
{
public PXSelect<TestTable> TestView;
public PXSave<TestTable> Save;
public PXCancel<TestTable> Cancel;

public virtual void testView()
{
foreach (TestTable record in PXSelect<TestTable>.Select(this))
{
Account objAccount = PXSelect<Account, Where<Account.accountCD, Equal<Required<Account.accountCD>>>>.Select(this, record.AccountCD.Trim());
if (objAccount != null)
{
record.AccountID = objAccount.AccountID;
record.Descr = objAccount.Description;
TestView.Cache.Update(record);
}
}
}

TestTable DAC Source Code

 [Serializable]
[PXCacheName("TestTable")]
public class TestTable : IBqlTable
{
#region LineNbr
[PXDBIdentity(IsKey = true)]
public virtual int? LineNbr { get; set; }
public abstract class lineNbr : PX.Data.BQL.BqlInt.Field<lineNbr> { }
#endregion

#region AccountID
[PXDBInt()]
[PXUIField(DisplayName = "Account ID")]
[PXSelector(typeof(Search<Account.accountID>),
typeof(Account.accountID),
typeof(Account.accountCD),
typeof(Account.description),
SubstituteKey = typeof(Account.accountCD),
DescriptionField = typeof(Account.description))]
public virtual int? AccountID { get; set; }
public abstract class accountID : PX.Data.BQL.BqlInt.Field<accountID> { }
#endregion

#region Descr
[PXDBString(100, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Description")]
public virtual string Descr { get; set; }
public abstract class descr : PX.Data.BQL.BqlString.Field<descr> { }
#endregion

#region AccountCD
[PXDBString(50, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Account CD")]
public virtual string AccountCD { get; set; }
public abstract class accountCD : PX.Data.BQL.BqlString.Field<accountCD> { }
#endregion
}

Screenshots

Intially Account ID and Description columns are empty in database

 

 

Account ID and Description fields are loaded on page load
Account ID is selector field

 

 

Finally, data is saved to the Database

 

Userlevel 6
Badge +3

OMG.  I need to send you some money...

Userlevel 7
Badge +17

@joe21  Lol :smiley:

Userlevel 6
Badge +3

Hi Naveen,

Just to follow up...

 

Thanks to the code you provided I was able to update the AccountID's. I didn't even know you could write code to handle the process of creating a view. (public virtual void testView()).

 

I did have a little trouble getting the IsDirty setup so that simply opening the entry didn’t prompt the user to Save or lose data.

 

Here is something I am going to elaborate on in case anyone else reads this and is having the same issue I had.

I was having a hell of a time getting the Details view to work. I was unable to edit any of the fields in the grid. I figured out why after 8 hours of trying different things. I solved it.

 

In my details table, I had CompanyID, GLBranchID and GLLineNbr set up as a primary key. When I created my SelectFrom for the the Details, I just said "where details.glbranch = headerbranch.fromcurrent.  For each Header record there are multiple details.  It returned 4 records but they were the same record. If you run that same query in SQL, you get four unique detail records. I finally figured out that if I set the GLLineNbr field in Details as "IsKey=true" it would pull the 4 lines properly. I did not realize that "IsKey" in the DAC doesn't directly relate to the SQL table keys!  In my train of thought, the key should be IsKey on BOTH the GLBatchNbr and the LineNbr.  But you can only set one DAC field as IsKey.

 

Before setting the LineNbr as IsKey, the only way I could get the view to show four unique records was to do an aggregate on the GLLineNbr field in the Details table. Did you know that selecting records with an aggregate make those fields in the Grid READ ONLY! I didn't!!!

 

So, I removed the aggregate and set IsKey = true on the line number field and voila. It works.

 

I thought that by setting IsKey on the Line Number, it would cause a problem because when I copy over lines from GLTran into my Details table, the LineNbr's will NOT be unique for that column. They WILL be unique for a given GL Batch. I set the GLLineNbr as IsKey and it worked.

 

I wish that during the training someone would have pointed out that the IsKey in the DAC does not necessarily correlate to the PK in the database.

Thank you so much for the code on manipulating the AccountID!  It saved me!

 

Warm regards, 

Joe

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved