Acumatica : Is my joined selector badly written?

Question

I'm just starting coding in Acumatica and I'm trying to add Sectors to the customer screen. I created a SQL Sector table (TRSector) with the 18 different sectors' names and codes. I also created another SQL table (TRCustSectorActive) that has all the combinations of customers' accounts and sector codes with additional information (is it active or not, dates, etc). The idea is that each customer will have information about each sector.

I'm trying to add a selector for the sectors on the customer page (AR303000). The problem I'm facing is that even though my selector is showing correctly if I try to change the sector, my selection automatically comes back to the first line. I'm guessing I'm doing something wrong when joining my two tables? Or should I have a CurrentSector view in my graph ?

Here are more details about my code. The selector in my TRCustSectorActive DAC looks like this :

#region SectorCD
[PXDBString(20, IsKey = true, IsFixed = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Filière")]
[PXSelector(
  typeof(Search2<TRCustSectorActive.sectorCD, LeftJoin<TRSector, On<TRCustSectorActive.sectorCD, Equal<TRSector.sectorCD>>>, Where<TRCustSectorActive.bAccountID, Equal<Current<Customer.bAccountID>>>>),
  typeof(TRCustSectorActive.sectorCD),
  typeof(TRSector.name),
  typeof(TRCustSectorActive.active)
)]
public virtual string SectorCD { get; set; }
public abstract class sectorCD : IBqlField { }
#endregion

I joined the TRSector DAC so that I can show the names of the sectors in the selection.

The view in the CustomerMaint extension looks like this :

public PXSelect<TRCustSectorActive, Where<TRCustSectorActive.bAccountID, Equal<Current<Customer.bAccountID>>>> Sector;

And in the page, I added this bit :

<px:PXFormView ID="DefFiliere" runat="server" Caption="Activation Filières" DataMember="Sector" RenderStyle="Fieldset" DataSourceID="ds" TabIndex="2100">
    <Template>
       <px:PXLayoutRule runat="server" ControlSize="SM" LabelsWidth="SM" StartColumn="True" />
       <px:PXSelector runat="server" ID="edSector" DataField="SectorCD"/>
       <px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="SM" ControlSize="SM" />
       <px:PXCheckBox runat="server" ID="edActive" DataField="Active"/>
    </Template>
</px:PXFormView>

score 1 · Accepted Answer · answered Mar 11 '20 at 06:04

Your selector is ok
The problem is in your view declaration.

This view returns several records for the current Customer. As a result, Search method will take only the first line.

Customer1 Sector1
Customer1 Sector2
Customer1 Sector3
...

Further, depending on what you need there are several possible options to fix the issue:

Use the grid to show all available sectors for the current Customer (see "Locations" or "Payment Methods" tab as an example)
Add a new filter (SectorFilter for example) to show Sector data only for the selected sector

see code below

public PXFilter<SectorFilter> sectorFilter;

[Serializable]
public partial class SectorFilter : IBqlTable
{
    #region SectorCD
    public abstract class sectorCD : IBqlField { }

    [PXDBString(20, IsFixed = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Filière")]
    [PXSelector(
        typeof(Search2<TRCustSectorActive.sectorCD, LeftJoin<TRSector, On<TRCustSectorActive.sectorCD, Equal<TRSector.sectorCD>>>, Where<TRCustSectorActive.bAccountID, Equal<Current<Customer.bAccountID>>>>),
        typeof(TRCustSectorActive.sectorCD),
        typeof(TRSector.name),
        typeof(TRCustSectorActive.active)
    )]
    public virtual string SectorCD { get; set; }
    #endregion
}

public PXSelect<TRCustSectorActive, 
    Where<TRCustSectorActive.bAccountID, Equal<Current<Customer.bAccountID>>, 
        And<TRCustSectorActive.sectorCD, Equal<Current<SectorFilter.sectorCD>>>>> Sector;

and aspx

<px:PXFormView ID="formSectorFilter" runat="server" DataSourceID="ds" Style="z-index: 100" Width="100%" CaptionVisible="False"
    DataMember="sectorFilter" SkinID="Transparent">
    <Template>
        <px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="S" ControlSize="XM" />
        <px:PXSelector CommitChanges="True" ID="edSectorCD" runat="server" DataField="SectorCD" AutoRefresh="True"/>
    </Template>
</px:PXFormView>

<px:PXFormView ID="DefFiliere" runat="server" Caption="Activation Filières" DataMember="Sector" RenderStyle="Fieldset" DataSourceID="ds" TabIndex="2100">
<Template>
   <px:PXLayoutRule runat="server" ControlSize="SM" LabelsWidth="SM" StartColumn="True" />
   <px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="SM" ControlSize="SM" />
   <px:PXCheckBox runat="server" ID="edActive" DataField="Active"/>
</Template>

Oh I didn't think about that ! I'll try the Filter solution as I don't want a grid on this tab. I'll keep you informed, thank you. — AlexS, Mar 11 '20 at 08:43
It worked ! You were right about the view declaration, I needed a filter in there. — AlexS, Mar 11 '20 at 09:47

Joseph Caruana · Answer 2 · 2020-03-10T12:54:16.753

0

Could you please include your entire property definition (not just the selector attribute)? Make sure that you have defined your field correctly, including the BqlField class definition.

Also, to start troubleshooting your issue, I would start with a simpler selector such as the below, so that you can eliminate whether it is related to the join or not.

[PXSelector(
    typeof(TRCustSectorActive.sectorCD),
    typeof(TRCustSectorActive.sectorCD),
    typeof(TRSector.name),
    typeof(TRCustSectorActive.active)
)]

edited Mar 10 '20 at 12:54

answered Mar 10 '20 at 11:17

Joseph Caruana

2,241
3
31
48

I included the entire property definition. I will try using a simpler selector as you suggested. – AlexS Mar 10 '20 at 13:08
I noticed that you are setting this field as IsKey. Are you sure that is required? Try without it and see whether it impacts anything – Joseph Caruana Mar 10 '20 at 13:15
as a side note, remember to follow Acumatica conventions and prefix your fields with Usr – Joseph Caruana Mar 10 '20 at 13:17
The simpler Selector still has the problem. As for the IsKey setting, it's there because my SQL tables have these constraints. I generated the DACs automatically. If I try to remove it, I'll get a constraint violation error. Also, I'll refactor my code to follow the naming convention as you mentioned, good catch. – AlexS Mar 10 '20 at 13:36
2

Just a note: The "Usr" convention only applies to adding custom fields to Acumatica's tables. There is no "Usr" convention for field names in your own custom tables. – Jerry Kurtz Mar 10 '20 at 14:10
You might need to change/optimize how you have structured your DACs/Views. You have a TRSector table which I understand but you also have a TRCustSectorActive table. What is the relation with the Customer. Is this a 1:1, meaning that a Customer can have only one entry in the TRCustSectorActive? If that's the case, it could be simpler to consider adding the fields directly to the Customer – Joseph Caruana Mar 10 '20 at 14:32
But if you intend to have a multiple relation between Customer and TCustSectorActive, then you might need to add a grid table to the Customer. – Joseph Caruana Mar 10 '20 at 14:33
To clarify the relations between my tables, the Customer table has a 1:n relation to the TRCustSectorActive table. If we have 2 sectors in our TRSector table, each customer will have 2 lines in the TRCustSectorActive table (1 line for each sector) so we can track which sector is active for which customer. Maybe I should make a grid table as you suggest and show the details when selecting in this grid. I'll try that next. – AlexS Mar 10 '20 at 14:34
Also, if you might want to implement something like how users are linked to Roles (as per Users screen). In this screen you have tickboxes to link Roles to Users. In your case you want to link Sectors to Customers. However, the approach of the Users screen is very complex to implement. You can have a look at the screen of that Graph to understand the complexity – Joseph Caruana Mar 10 '20 at 14:35
I don't think I want the same implementation as the Users and Roles. In my case, every customer is linked to every sector. When a new customer is created, I'll have to insert a line for this customer in the TRCustSectorActive table for each Sector in TRSector. – AlexS Mar 10 '20 at 14:47
Yes, that would do. But if you add a new sector, you might need to update all Customers, whcih might be tricky – Joseph Caruana Mar 10 '20 at 14:56
The Sectors are fixed so we shouldn't have to worry about creating new ones hopefully. – AlexS Mar 10 '20 at 15:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209383/discussion-between-joseph-caruana-and-alexandre-sarazin). – Joseph Caruana Mar 10 '20 at 15:53

Acumatica : Is my joined selector badly written?

2 Answers2