Solved

Need help with a Selector to get related entities

Joe Schmucker
Rank
Userlevel 6
Badge +3

I am sorry I ask so many questions.  I work alone and I don’t have anyone around to ask for help.  I really do try to figure things out and not just ask a question as soon as something doesn’t work.

I want to add a selector to the Details grid on the Cash Sales screen.  The Selector needs to pull related entities from the Relations tab on the Business Accounts screen.

These are the items I want to show up in the selector.

 

I’ve added the following UDF in the ARTran extension

#region UsrDonorBusAccountID
[PXDBInt]
[PXUIField(DisplayName = "Donor ID")]
[PXSelector(typeof(Search2<CRRelation.entityID,
    InnerJoin<BAccount, On<BAccount.noteID, Equal<CRRelation.refNoteID>>>,
    Where<CRRelation.refNoteID, Equal<BAccount.noteID>>>),
    typeof(BAccount.bAccountID),
    typeof(BAccount.acctCD),
    DescriptionField = typeof(BAccount.acctCD))]
public virtual Int32? UsrDonorBusAccountID { get; set; }
public abstract class usrDonorBusAccountID : PX.Data.BQL.BqlInt.Field<usrDonorBusAccountID> { }
#endregion

This is what pops up when I click on the selector.  I don’t know why Vendor is showing as the ID field and acctCD is probably due to my Selector not being valid.

 

From my looking into Acumatica, maybe I need some kind of CacheAttached code?

I tried this in the ARCashSaleEntry_Extension.  I’m sure it is completely wrong, but I think it might be needed somehow.

[PXSelector(typeof(Search2<CRRelation.entityID,
    InnerJoin<BAccount, On<BAccount.noteID, Equal<CRRelation.refNoteID>>>,
    Where<CRRelation.refNoteID, Equal<BAccount.noteID.FromCurrent>>>),
    typeof(BAccount.bAccountID),
    typeof(BAccount.acctCD),
    DescriptionField = typeof(BAccount.acctCD))]
protected virtual void _(Events.CacheAttached<CRRelation.entityID> e) { }

 

Thanks to all of you for your help.

 

 

icon

Best answer by Joe Schmucker 1 November 2022, 01:33

View original
Joe Schmucker, CPA

14 replies

Joe Schmucker
Rank
Userlevel 6
Badge +3

@ddunn DANG!  I knew about that!  Shame on me!!  :-)

Joe Schmucker, CPA
D
Rank
Userlevel 7
Badge +5

Make sure that you also join the records/tables using the CompanyID column in your SQL View.

Joe Schmucker
Rank
Userlevel 6
Badge +3

It feels like guerrilla coding, but I did it with a SQL View.  The lookup now works.

 

CREATE VIEW [dbo].[QTCSoftCreditSQLView]
AS
    SELECT 
        B.CompanyID,
        B.BAccountID,
        B2.BAccountID AS SoftCreditID,
        B2.AcctCD,
        B2.AcctName
    FROM
        dbo.CRRelation R
        INNER JOIN
        dbo.BAccount B
            ON B.NoteID = R.RefNoteID
        INNER JOIN
        dbo.BAccount B2
            ON B2.BAccountID = R.EntityID

GO

[PXSelector(typeof(Search<QTCSoftCreditSQLView.softCreditID,
    Where<QTCSoftCreditSQLView.bAccountID, Equal<ARTran.customerID.FromCurrent>>>),
    typeof(QTCSoftCreditSQLView.acctCD),
    typeof(QTCSoftCreditSQLView.acctName),
    SubstituteKey = typeof(QTCSoftCreditSQLView.softCreditID),
    DescriptionField = typeof(QTCSoftCreditSQLView.acctCD))]

 

If anyone has an internal Acumatica code solution, I’d prefer that, but I have a demo tomorrow and I needed to get it working.

Thanks @Shawn Burt for your help.

 

 

 

Joe Schmucker, CPA
Joe Schmucker
Rank
Userlevel 6
Badge +3

After a few hours of trying pretty much everything I could think of, I am not getting it working.

This is my current selector statement.  It looks good to me.

[PXSelector(typeof(Search2<BAccount.bAccountID,
        InnerJoin<CRRelation, On<CRRelation.refNoteID, Equal<BAccount.noteID>>,
        InnerJoin<BAccount2, On<BAccount2.bAccountID, Equal<CRRelation.entityID>>>>,
    Where<BAccount.bAccountID, Equal<ARTran.customerID.FromCurrent>>>),
    typeof(BAccount2.bAccountID),
    typeof(BAccount2.acctCD),
    SubstituteKey = typeof(BAccount2.bAccountID),
    DescriptionField = typeof(BAccount2.acctCD))]

It still brings up a wonky lookup.

 

Joe Schmucker, CPA
Shawn Burt
Rank
Userlevel 5
Badge +1

let me know I am happy to “play” its a nice distraction when I get stuck on my own stuff :)

Shawn Burt
Joe Schmucker
Rank
Userlevel 6
Badge +3

@Shawn Burt before you invest into this, I’m thinking maybe I can use a PXRestrictor on the field.  I’ll work on that and see if I get anywhere.

Joe Schmucker, CPA
Joe Schmucker
Rank
Userlevel 6
Badge +3

Here are the .cs files

 

1 Attachment
Joe Schmucker, CPA
Joe Schmucker
Rank
Userlevel 6
Badge +3

Not sure if my files got uploaded.  I’ll try again...This is the Acumatica project file.

1 Attachment
Joe Schmucker, CPA
Shawn Burt
Rank
Userlevel 5
Badge +1

If you are willing to share your project I am happy to play here to see if I can work it out.

Sorry, sometimes for these odd ones I have to just play with the source :)

Shawn Burt
Shawn Burt
Rank
Userlevel 5
Badge +1

I think you need to use the Customer object everywhere you are using BAccount

Shawn Burt
Joe Schmucker
Rank
Userlevel 6
Badge +3

I did try BAccount2.   But that resulted in the error:

I’ve used BAccount2 in other places but it probably doesn’t have NoteID in it.

Joe Schmucker, CPA
Shawn Burt
Rank
Userlevel 5
Badge +1

no problem just confirming what you are looking for.

The Vendor Label I think is coming from the Graph related to the Vendor on the Inventory Item.

So to make sure you get customers I think you need the Customer type, I would need to play a bit to confirm. I know there are other types as well. e.g. BAccount2 so you can have separate caches depending on what and why you are pulling.

Shawn Burt
Joe Schmucker
Rank
Userlevel 6
Badge +3

I want to get customers that are related to the “Business Account”.  In my test, I’ve selected AACUSTOMER - Alta Ace as the customer for the cash sale.  The person I’m doing this for had me go to the Business Account screen for that customer and add a couple related entities.  Honestly, I’m not sure what the select statement is to get to the Relations data on the Business Accounts screen.

In SQL, I can see the relationship in the CRRelation table so I know “where” it is.

I’m a super noob here, so I appreciate your patience with my lackluster reply.

I also feel there is some way I have to connect the CRRelation table in the grid to the customer account in the header.  Is the CacheAttached the right direction for that?

I’ll try changing the Selector to a [Customer] and see if I can get any further.

 

Joe Schmucker, CPA
Shawn Burt
Rank
Userlevel 5
Badge +1

I think, if I remember correctly, there was a change recently that vendor was defaulted as type if BAccount is used alone. It may have been graph specific, but I ran into the issue.

Have you tried to use Customer (Customer inherits BAccount) instead, or are you really looking for BAccount?

Shawn Burt

Reply


Terms & ConditionsCookie settings
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