Solved

Company Filter in GI Not Working

  • 2 December 2021
  • 8 replies
  • 306 views

Userlevel 1
Badge

Hello all:

I am attempting to create a GI that allows the user to retrieve records not only by individual branch, but also by an entire Company (included any branches under it). I know this has been implemented in various built-in GIs and Reports, so I used on of those as my base - the PR-Employee Pay Rates and Earnings GI. I see this GI is using the `ARDocumentFilter.OrgBAccountID` Schema Field for its `OrgBranchID` parameter. I further noticed that this parameter is used in the Condition section: e.g. PRPayment.BranchID Is In [OrgBranchID].

 

I have created a GI that mimics this approach.

 

While it works as expected when selecting an individual branch, it does function as expected when selecting a Company. Specifically, records from Branches from another Company are brought in. For example, I have a Company with ID of 1000. This Company has three Branches: 1001, 1002, and 1003.  However, if I select the 1000 Company in the GI, the Inquiry brings in not only records from branches under that company, but also records from another Company (3503 which is from another Company with ID 3500).

What might the issue be?  The 3503 records also show up when I pick the 3500 Company (which is expected). I have also tried using the `'OrganizationBranchReportParameters.OrgBAccountID` field with the OrigBranchID parameter (as this seems to be what the reports use), but with the same results. Thanks for any advice.

icon

Best answer by Naveen Boga 3 December 2021, 01:27

View original

8 replies

Userlevel 7
Badge +18

Hi @KellyMarchewa  In the Conditions tab, instead of using “Is in” have you tried with “Equal”?

 

If you already tried not able to fetch the proper results, can you please attach your XML format of GI here. I can take a look from my end and let you know.

Userlevel 1
Badge

@Naveen B 

Thanks for the response. Unfortunately, using “Equal” generates an error: “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”


XML export is attached. Thanks!

Userlevel 7
Badge +18

@KellyMarchewa  Thanks for sharing the GI, will look into it and let you know.

Userlevel 7
Badge +18

Hi @KellyMarchewa  I have verified from my end and it is working as expected and not able to replicate the above issue.

In my instance I have installed, Demo data, since this is not having the employee records all branches unable to verify.

Data is available only on few branches and observed GI is pulling the proper results. Also verified the SQL Query, when we select the Company it is retuning related branches and showing getting the expected results from sub query.

 

Userlevel 1
Badge

@Naveen BThanks so much for taking a look. Sounds like it might be something peculiar to my environment/data.

You mentioned you verified the SQL query. That would seem to be the next best step for me. How do you do this? I enabled SQL logging under Request Profiler and did not see any data relevant to the GI being logged?

Thanks again!

 

Userlevel 7
Badge +18

Hi @KellyMarchewa  You can see the SQL Query on the Trace window.

Once you click on the View Inquiry on the GI, and then Tools → click on Trace. Please find the screenshots for reference.

 

 

Userlevel 1
Badge

@Naveen B 

Great info; thanks! Will look into it from my end and see if I can figure out what is causing the behavior.

 

Userlevel 7
Badge +18

Sure. Let me know if I can help you anywhere on this

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