Generic Inquiry Showing Blank Results Despite Indicated Records

Do you possibly have restriction groups? Are you sure you have access to the companies and branches assigned roles? When you are in Inquiry Design mode do you have any warning that you do not have access to this field?

There are no restrictions with my user or any other indications of restrictions within the building of the GI. 

Can you share one of the SQL Views, Its DAC, and associated GI?

In your SQL query do you have a CompanyID specified in like the Where part. If so sounds like this is not set to the correct tenant on production.

I believe the “CompanyID” is a kind of reserve word in the Acumatica Framework and when a DAC is created from a view that has CompanyID, the Framework will automatically ignore creating a DAC field for it. If a wrong CompanyID is in the WHERE clause, then logically no result should be returned. It seems the GI is finding some records but just can’t display it

@aaghaeiA SQL view based GI will show all the record specified in the view. It will not validate the companyID against the tenant you are in. I normally unless the record count is huge specify add the companyID to the select then use a condition on the GI to only display that companies records so i can use it in a test tenant and just change the companyID.

If there is some setting in the DAC so it will validate against the tenant you are in please let me know.

As you said need to see the DAC and SQL view to trouble shoot this.

@aaghaei @dcomerford Thanks for the responses and effort here! I do not have any where clause limitations. As requested, here are the 3 scripts, the auto-generated C# DAC, and the GI. I recreated the project adding the ‘Is Key = True’ but to no avail. Hopefully these will shed some light on the situation!
Thanks again, 
Alec 

I had another thought on this. Could it be related to different builds?
Production:                                        Test: 
Acumatica 2023 R2                           Acumatica 2024 R1
Build 23.212.0024                             Build 24.108.0034

@alecl 

About the SQL View DACs:
Please note that Acumatica doed not encourage the use of SQL Views and they are not supported officially.
 

However if you choose to take that route, here are some general rules(from past 😉):

Please confirm that the SQL View exists in the Database.
One should make sure that SQL view must include CompanyID field.  The DAC must include proper key field(s)  for them to work correctly (identity one record as unique).

In my experience, most common problem I see with views is DAC declaration. You must declare keys/IDs properly.

Can you expand a little more on the DAC declaration?   

@aaghaei I believe that I have a view that hits the requirements you have mentioned, but again it works as expected in a local environment and fails in production. It would be amazing if you could take another look at my current project to see if anything catches your eye. Thanks! 
-Alec 

I will look into this and get back to you over the weekend if someone else don’t respond sooner :)

@alecl Please see the attached that will generate this output screenshot. I didn’t spend time on properly setting up the DAC and just did a few of the fields for your reference. I have published this on the cloud so if you still experience issues reset your cache and if still not working create a support ticket with Acu. I have included the SQL Script, DAC and GI in the package so it is the only thing you need.

The GI was causing performance load in multi company tenants. I had a second look and I believe a join on CompanyID is missing on the first CTE. Try this as the body of the first CTE. It joins on CompanyID and also goes by the master calendar.

	SELECT
		H.CompanyID,
		H.BranchID,
		H.LedgerID,
		H.AccountID,
		H.SubID,
		P.FinPeriodID
	FROM
		GLHistory H LEFT JOIN
		FinPeriod P ON P.CompanyID = H.CompanyID AND P.OrganizationID = 0 AND P.FinPeriodID >= H.FinPeriodID
	GROUP BY
		H.CompanyID,
		H.BranchID,
		H.LedgerID,
		H.AccountID,
		H.SubID,
		P.FinPeriodID

@alecl  If I am to put my accounting hat on, I can say you have some errors in your amounts calculation too. I leave it to you to decide if it is right or wrong but what I can tell you is the account type A and L (Balance Sheet Accounts) balance calculation logic should be mimicking each other and E and I (Income Statement Accounts) each other. The only difference between A & L as well as E & I should be in their sign not how you calculate your cumulative amounts.

@aaghaei Thank you for your work on this solution! Everything seems to be working as expected.

When we look at the differences in the customization project code from your solution to my original, there seems to be much more code. 

Did you go through the “low code, no code” process of inserting a script and generating a DAC through the project editor? Or did your solution require actual written attritions in your C#?

I'm wondering why mine wouldn't work and why there are so many discrepancies between my solution and yours. 

Thanks!

@alecl Not sure what are you referring to but if you are talking about private/protected members I added them just for sake of compliance with the standard templates but it wouldn’t affect the code functionality. You definietly were missing the Keys and you had error on CompanyID as I pointed out in my original reply.