Solved

Show Grid as a Tab in Customer Maintenance with data sourced from a view (duplicate records)

  • 2 April 2024
  • 7 replies
  • 59 views
J
Rank
Userlevel 4
Badge

Hi,  

 

I have this problem that has been driving me nuts for the last 3 hours.  This is something that I have done before without issue.  Here is the problem.

I want to show a grid as a tab on the Customer Maintenance screen (AR303000) with the data sourced from a view.

There is a view called PortfolioCustomerVW, which for an example account (717) gives the following:

There are 2,318 records in the data set above and the InventoryCD column is unique.  The total number of records in the views without any filters (eg on BAccountID) is in excess of 20 million and the results are sourced from multiple tables which is why I have gone for a view (for speed and performance)

I have a DAC for the above which has all the fields aside from CompanyID

And, a graph extension

The issue as follows:

  • In the grid, we have multiple records for the same item.  The below shows InventoryCD ZWL004-W20 replicated many times.  I exported the grid out to Excel and saw 2,318 rows of the same item code (ie. the number of rows matches what the SQL statement on the view returns, but we only see one line in the grid repeated 2,318 times)
  • Also, when you choose another customer the grid shows records for the previous BAccountID, and the new BAccountID (ie. records just seem to append to the grid as different customers are selected).

I have applied the same concept above on the Stock Items form and this works fine.  What am I missing?

 

Any help greatly appreciated.

 

Thanks,

John.

 

 

 

 

 

 

 

 

icon

Best answer by Leonardo Justiniano 2 April 2024, 16:16

View original

7 replies

J
Rank
Userlevel 4
Badge

Further to the above,  if I create a generic inquiry on the view there is no issue.

So, I strongly suspect that the SQL View is fine, the DAC is fine, but there is something wrong with the below in the Graph Extension (or maybe the ASPX?)

 

J
Rank
Userlevel 4
Badge

And,  I have also checked what SQL statements are running when a customer is selected using SQL Server Profiler.  The SQL statement looks OK, and is paginated:

 

The item that is being repeated in the view is the first one.

 

J
Rank
Userlevel 4
Badge

 

 

Hi,

Have spend a few more hours trying to figure out what’s going on here.  Here are my findings:

I added in a method to populate the view (original PXSelect comment out and replaced with the portfolioView()) method:

I then tried four different approaches.  

V1: BQL Select From

 V2: Linq

V3: Linq to retrieve Full Data set and then filter by BAccount

Never going to be good in practice due to data load of retrieving full list prior to applying the filter but…

V4: PXDatabase.Select

 

Outcome

In terms of the outcome of the above.  Tests V1 to V3 all delivered the same incorrect result.  The List<PortfolioCustomerVW> result variable contained the correct number of records, but the first record was duplicated through out.  I checked in the  SQL Server Profiler and the right query was run in each case eg.

 But the result variable contained the first record duplicated 13 times.

V4 worked!

 

Would appreciate it if someone more knowledgeable than I could give some feedback on what is going on here!  I have a solution to my problem but I don’t understand why this is the only approach that gives the right result.

 

Thanks,

John.

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @JWS539 

 

I could not see which columns were set as Key in your DAC declaration. Please set the specific columns that make the primary key of the table as IsKey = true.

 

Hope this helps

Leo
J
Rank
Userlevel 4
Badge

Hi Leo,

Thank you so much for taking the time to review this problem.  I have tried your suggestion and it works. 

Also reviewed my other customizations that employ the same technique successfully and I had set the Primary Key there.  Just neglected to do so in this case.  So that’s a key learning for me ie.  DAC’s on views have to have Keys on them even though the don’t directly relate to a table in the DB with Primary Keys.

Thank you so much for your help.

John

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @JWS539 

 

Happy to help. Keep in mind that those cache structures are stored in Dictionaries and therefore require a Key.

Happy coding!

Leo
J
Rank
Userlevel 4
Badge

Thanks.  Your cache structure/key requirement point makes perfect sense.  I think I knew that in the back of my mind.  Yesterday was very much a case of not being able to see the wood from the trees!

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