Solved

Confusion on how a View can get populated by a SelectFrom statement even when the View is not directly referenced?


Userlevel 6
Badge +3

I thought that the only time a View gets populated with records if is you make a call to “View.Select()” or if the View references a “fromcurrent”.

In the following simplified example, I have a graph with a view.  When I run a SelectFrom statement, without referencing the view, the view gets populated with records and I don’t know why.

PXGraph joesGraph

in the joesGraph there is a view:

public SelectFrom<ICSARAddressSQL>.View JoesView;

In my method, I use a fluent BQL to fetch records in the ICSARAddressSQL table (which is actually a SQL View) where the customerAddressID is equal to “item” where item is the CustomerAddressID I am fetching.  

Then I check to see if that record is in the joesView cache.  If it is ALREADY in the cache I update the cache record.  Otherwise I add it to the cache in the ELSE section.

foreach (ICSARAddressSQL record in SelectFrom<ICSARAddressSQL>.Where<ICSARAddressSQL.customerAddressID.IsEqual<@P.AsInt>>.View.Select(joesGraph, item))

{

                    ICSARAddressSQL cached = joesGraph.JoesView.Locate(record);
                    if (cached != null)
                    {  //do some work here
                        graph.JoesView.Update(cached);
                    }
                    else
                    { //do some work here
                        graph.JoesView.Update(record);
                    }
}


As soon as the SelectFrom is executed, the JoesView view is filled with the records.  There is no direct reference to the View in that call.  How does the view get filled with records from the SelectFrom?  I’m not calling graph.JoesView.Select().

Is this because the graph is automatically associating the ICSARAddressSQL table with the the SelectFrom statement because the source table is the same? 

I would think that the SelectFrom is “not connected” to the view because it is not referenced directly.

This is not a problem, I just want to know if this is what you folks know should be happening. This unexpected behavior would actually simplify my code since the View is already populated and I don’t need to check if the record has already been cached.  This is to help me understand some underlying logic in Acumatica I did not know.  

 

 

icon

Best answer by Dmitrii Naumov 21 March 2023, 18:21

View original

13 replies

Userlevel 6
Badge +4

Hi @Joe Schmucker 

 

If the view is linked to a Grid, then the grid is executing a Select on that view when the screen is refreshed the first time. If you alter the content and you want to be reflected in the grid, you do a RefreshView. Also in any business logic when you invoke JoesView.Select(), it will take the data from the cache if it was already fetched.

Userlevel 6
Badge +3

@Leonardo Justiniano The view is just for processing records.  It is a “processor” graph and there is no grid or form related to it.  This is the first place in the entire process that the View is referenced.  There is no place prior to this call where that view is referenced.

Thanks again for your efforts to help me!

Userlevel 6
Badge +4

@Joe Schmucker 

 

Seems that Acumatica is pre-fetching data. You can confirm that by creating a delegate

public IEnumerable joesView()
{
return null; // <- Put a breakpoint here
}

 

Userlevel 6
Badge +3

Little update.  If I create a generic graph an pass that into the SelectFrom statement, the View does NOT get populated!  

            PXGraph g = new PXGraph();
                foreach (ICSARAddressSQLView reg in SelectFrom<ICSARAddressSQLView>.Where<ICSARAddressSQLView.customerAddressID.IsEqual<@P.AsInt>>.View.Select(g, item))

This kind of confirms for me that there must be some inherent “auto mapping” of the View to the SelectFrom when I pass in the joesGraph since that graph has the joesView in it, and both the SelectFrom and the JoesView reference the same table.

Knowing that if I pass in joesGraph it automatically fills the View, that will simplify my code because I won’t have to check if it is cached.  The only reason I was checking if it was cached is that I could prevent the error “this record was already updated/deleted etc.  

Userlevel 6
Badge +4

Hi @Joe Schmucker 

 If I create a generic graph an pass that into the SelectFrom statement, the View does NOT get populated!  

Awesome Joe. Yes sounds correct.

 

Userlevel 6
Badge +3

@Joe Schmucker

 

Seems that Acumatica is pre-fetching data. You can confirm that by creating a delegate

public IEnumerable joesView()
{
return null; // <- Put a breakpoint here
}

 

@Leonardo Justiniano 

When I put that code into the graph that has the View, I get an error that “The type ‘JoesView’ already contains a definition for ‘JoesView’

I am embarrassed that I don’t know what to do to fix this.  

Note: I am no “PRO III”.  The algorithm used to rank me is way off.  😊

Userlevel 6
Badge +4

@Joe Schmucker 

 

Probably you already have a joesView delegate in your code. Are you using the Screen Editor for your code? or Visual Studio? Just curious. In the end you found the answer yourself ! 💡

Userlevel 6
Badge +3

@Joe Schmucker

 

Probably you already have a joesView delegate in your code. Are you using the Screen Editor for your code? or Visual Studio? Just curious. In the end you found the answer yourself ! 💡

@l

Just VS.  This impacts hundreds lines of code in my actual project.  That is why I really hoped I could get a definitive answer as to whether or not there is some kind of “auto mapping” from the graph to the SelectFrom statement.  Maybe the coding I am doing is just not very “normal” and that is why I am seeing this behavior which others may not have found.  I’m not marking this as answered yet.  I may submit a request to Acumatica support to get the answer about the underlying logic here.  To me this is kind of a big deal.  HA!

Userlevel 7
Badge +5

@Joe Schmucker

I think the confusion here is because we are not clear in the terminology. 

Let me clarify. 

The View.Locate method is just a shortcut for View.Cache.Locate

See the actual code:

public virtual Table Locate(Table item)
{
return (Table)View.Cache.Locate(item);
}

As for the cache, it’s not 1:1 bound to a specific view. A cache object is linked to specific DAC type(1:1 relation within specific graph).

So, there may be multiple views referencing the same cache.

So, whenever you interact with any view or select bound to the DAC type, the same cache is going to be used.

 

Hope that clarifies the behavior here.

Userlevel 6
Badge +3

@Dmitrii Naumov 

When I am in debug, joetest1 has no records.  After firing the foreach, joetest2 has records.  I haven’t even hit the locate at that point.

 

 

                var joetest1 = graph.ICSARAddressSQLVw.Select();

                foreach (ICSARAddressSQLView reg in SelectFrom<ICSARAddressSQLView>.Where<ICSARAddressSQLView.customerAddressID.IsEqual<@P.AsInt>>.View.Select(graph, item))
                {

                    var joetest2 = graph.ICSARAddressSQLVw.Select();
 

Before foreach statement, there are no records in the View.

 

After the foreach, there are 24 records in the View

 

Userlevel 7
Badge +5

@Joe Schmucker  an expression like  var joetest1 = graph.ICSARAddressSQLVw.Select(); does not execute the select because the selects in Acumatica ERP are ‘lazy’(that is they are going to be executed when you try to access the data, not when you declare the select)

 

        var joetest1 = graph.ICSARAddressSQLVw.Select().FirstTableItems.ToList(); would be proper test, because ToList() will trigger the actual select execution

Userlevel 6
Badge +3

@Dmitrii Naumov 

Thanks for that clarification.  When I use that code for my test, it returns the entire table for both test1 and test2.

It still doesn’t make sense why the view gets 24 records after the foreach and has no records before the foreach.  Somehow, that “cache” for the view is getting records added to it during the execution of the foreach statement.

I will just take your word on this “lazy” stuff.  HA!  Thanks for taking the time to help out here.

 

Userlevel 7
Badge

Hi @Joe Schmucker were you able to find a solution? Thank you!

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