Solved

How to pick the current tenant Id for a Database Script?

  • 26 July 2023
  • 4 replies
  • 107 views
M
Rank
Userlevel 4
Badge

There are three tenants in our Acumatica Instance. For the purpose of creating a SQL view, we used the below DB Script.

create or alter view ardiscountView as
select  ROW_NUMBER() OVER(ORDER BY Usrdiscountcoden ASC) row,RefNbr,TranType type,Usrdiscountcoden discount,sum(CuryDiscAmt) amount from artran where Usrdiscountcoden is not null
and CompanyID=2
group by RefNbr,TranType,Usrdiscountcoden

But in here we had to hardcode the CompanyID. But we want dynamically pick the CompanyID depending on the current tenant. If you can provide a way to perform this it id highly appreciated.  

icon

Best answer by Naveen Boga 27 July 2023, 06:45

View original

4 replies

dcomerford
Rank
Userlevel 7
Badge +12

I would like to know if it can be part of SQL script too but i suspect not so you could if you are creating a DAC and  GI use a condition in the GI on the relations tab to select the tenant so performance of the GI is not affected

Dermot Comerford
D
Rank
Userlevel 7
Badge +5

You’ll want to include a column called CompanyID in your view and then Acumatica will automatically apply the filter you want.

Naveen Boga
Rank
Userlevel 7
Badge +17

@malinthawarnakulasooriya08   As you are creating the DAC from the SQL VIEW, then no need to hard code the COMPANYID column If you include the COMPANYID filed in the Select and Group BY.

 

Here is the modified SQL view for reference:

Create or Alter view ardiscountView as
select CompanyID, ROW_NUMBER() OVER(ORDER BY Usrdiscountcoden ASC) row,   RefNbr,TranType type,Usrdiscountcoden discount,sum(CuryDiscAmt) amount from artran where Usrdiscountcoden is not null
group by CompanyID, RefNbr,TranType,Usrdiscountcoden

 

 

Naveen B
amitr70
Rank

@Naveen Boga  What if I have multiple tables in that view and using joins. How to decide which company id to use?

-- Amit Ranjan

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