Skip to main content
Solved

Generic Inquiry Showing Blank Results Despite Indicated Records

Hello all,

I created a few SQL views in a test environment and uploaded them to a customization project to create a new DAC. Everything went well, and I was able to access the data through a generic inquiry as intended.

Moving the scripts to the production environment and creating the project there resulted in success and no issues.

Now, when I select the table in the GI, it shows up, and all the fields appear under the results grid. However, when I go to actually view the inquiry, it is completely blank except for the hundreds of pages and thousands of records indicated at the bottom of the inquiry.

Any insight into what is happening and potential fixes?

18 replies

aaghaei
Rank
Userlevel 7
Badge +9

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?

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
A
Rank
Userlevel 1

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. 

aaghaei
Rank
Userlevel 7
Badge +9

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

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
dcomerford
Rank
Userlevel 7
Badge +14

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.

Dermot Comerford
aaghaei
Rank
Userlevel 7
Badge +9

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

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
dcomerford
Rank
Userlevel 7
Badge +14

@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.

Dermot Comerford
A
Rank
Userlevel 1

@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 

5 Attachments
A
Rank
Userlevel 1

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

RohitRattan88
Rank
Userlevel 7
Badge +4

@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.

aaghaei
Rank
Userlevel 7
Badge +9

I checked the Scipts and DAC. I do not see any “CompanyID” Field in the scripts neither Key Field(s) in your DAC.

Also I see You are missing CompanyID Join in some of your queries. Make sure you have included CompanyID in every single join.

Add CompanyID to your Views and Key to your DAC and you should be good to go.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
A
Rank
Userlevel 1

@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?   

A
Rank
Userlevel 1

@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 

2 Attachments
aaghaei
Rank
Userlevel 7
Badge +9

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

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +9

@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.

 

 

1 Attachment
Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +9

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

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +9

@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.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
A
Rank
Userlevel 1

@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!

aaghaei
Rank
Userlevel 7
Badge +9

@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. 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings