Question

How do I join a table (EPEmployees) twice for a view.

  • 2 April 2024
  • 1 reply
  • 33 views

I want to join a table (EPEmployees) twice to get employee and supervisor info and based on the user logged in I want to show either only an employees customers or the supervisors customers + the employees that report to them. This is what I have tried among a couple other ways:

public SelectFrom<Customer>
      .LeftJoin<Address>
          .On<Address.addressID.IsEqual<Customer.defAddressID>>
      .LeftJoin<CustSalesPeople>
          .On<Customer.bAccountID.IsEqual<CustSalesPeople.bAccountID>>
      .LeftJoin<SalesPerson>
          .On<CustSalesPeople.salesPersonID.IsEqual<SalesPerson.salesPersonID>>
      .LeftJoin<Users>
          .On<SalesPerson.descr.IsEqual<Users.email>>
      .LeftJoin<EPEmployee>
          .On<Users.pKID.IsEqual<EPEmployee.userID>>
      .LeftJoin<Supervisors>
          .On<EPEmployee.supervisorID.IsEqual<Supervisors.bAccountID>>
      .Where<
        Brackets<CustSalesPeople.isDefault.IsEqual<True>
          .And<Users.pKID.IsEqual<AccessInfo.userID.FromCurrent>>
        >
          .Or<
            Brackets<CustSalesPeople.isDefault.IsEqual<True>
              .And<AccessInfo.userID.FromCurrent.IsEqual<Supervisors.userID>>
            >
          >
      >
      .AggregateTo<GroupBy<Customer.acctCD , Max<SalesPerson.salesPersonID>>>
      .View CustomersView;
    
    public class Supervisors : EPEmployee { }

 

Thanks in advance!


1 reply

Userlevel 4
Badge

I haven’t tried it - but if you create a Supervisor class inherited from the EPEmployee class - then use the supervisor DAC instead in the FBQL - it should work.

 

There might be an aliasing property in FBQL that I am not aware of.

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