Pxprpojection Data Projection What Is It

 

Hello everybody.

Imagine that you need to have implementation of several tables with possibility to update them all. 

Also you want to implement by the Acumatica side rather than by the database.

  • You know that Joined tables in Acumatica are read-only and you cannot update fields there
  • Can we create/use SQL-like views in Acumatica?
  • How can I join grouped (statistical) view to the DAC?

So, yes we can do it. For this purpose Acumatica gives you PXprojection attribute.

PXProjection attribute  binds the DAC to an arbitrary data set. The attribute thus defines a named view, but is implemented by the server side rather than by the database.

Lets start)

Steps:

  • Think whats tables you need to implement and whats of fields from this table;
  • Create new DAC that will represent view. This DAC may have less columns than you have in DACs you will select later. So just define fields you need. In the end it will help a bit with select performance;
  • Create new graph;
  • Create page - view of implementatoin;

For examle, I want to see implementatioin of 2 tables(CRactivity and BAccount) and fields like baccountID, baccountName from BAccount table and contactID, subject from CRactivity table.

In my solution I create new DAC class and define PXProjectionAttribute on this DAC.

 

[Serializable]
    [PXProjection(typeof(
        Select2<BAccount,
            InnerJoin<CRActivity,
                On<CRActivity.bAccountIDEqual<BAccount.bAccountID>>>>),
       Persistent = true
        )]
    public partial class PXprojectionJoinDacClass : IBqlTable
    {
        public abstract class baccountID : PX.Data.IBqlField
        {
        }
        // The field mapped to the BAccount field (through setting of BqlField)
        [PXDBInt(IsKey = true, BqlField = typeof(BAccount.bAccountID))]
        [PXExtraKey]
        [PXUIField(DisplayName = "BAccount ID")]
        public virtual int? BAccountID { getset; }
 
        public abstract class contactID : PX.Data.IBqlField
        {
        }
        // The field mapped to the CRactivity field
        // (through setting of BqlField)
        [PXDBInt(IsKey = true, BqlField = typeof(CRActivity.contactID))]
        [PXUIField(DisplayName = "Contact ID")]
        public virtual int? ContactID { getset; }
 
        public abstract class subject : PX.Data.IBqlField
        {
        }
        // The field mapped to the CRactivity field
        // (through setting of BqlField)
        [PXDBString(IsKey = false, BqlField = typeof(CRActivity.subject))]
        [PXUIField(DisplayName = "Subject")]
        public virtual string Subject { getset; }
 
        public abstract class acctName : PX.Data.IBqlField
        {
        }
        // The field mapped to the Baccount field
        // (through setting of BqlField)
        [PXDBString(IsKey = false, BqlField = typeof(BAccount.acctName))]
        [PXUIField(DisplayName = "Account Name")]
        public virtual string AcctName { getset; }
 
    }

As you see I use Select2 attribute for select from 2 tables and use BQL command. In my way it is InnerJoin.

For PXProjection constructor you should provide BQL command that will define what tables you want to select. You may use all possible commands of BQL (Join, Where, GroupBy, OrderBy).

Also I define "Persistent = true" for update both tables, without it my implementatiioin will be only for read. 

Go next and create new graph.

 

public class ProjectionMaint : PXGraph<ProjectionMaintPXprojectionJoinDacClass>
   {
       public PXSelect<PXprojectionJoinDacClass> Projections;
   }

Easy, we only use PXSelect from PXprojectionJoinDacClass.

Go next and create new page.

 

<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" 
AutoEventWireup="true" ValidateRequest="false"  CodeFile="PR101000.aspx.cs" Inherits="Page_PR101000" Title="Untitled Page" %> <%@ MasterType VirtualPath="~/MasterPages/FormDetail.master" %>   <asp:Content ID="Content1" ContentPlaceHolderID="phDS" runat="Server">     <px:PXDataSource ID="ds" runat="server" Visible="True" SuspendUnloading="False"                       TypeName="ClassLibrary1.ProjectionMaint" PrimaryView="Projections">     </px:PXDataSource> </asp:Content> <asp:Content ID="cont2" ContentPlaceHolderID="phG" runat="Server">     <px:PXGrid ID="grid" runat="server" Height="400px" Width="100%" Style="z-index100"                AllowPaging="True" AllowSearch="True" AdjustPageSize="Auto"
 DataSourceID="ds" SkinID="Primary"                 TabIndex="800" TemporaryFilterCaption="Filter Applied">         <Levels>             <px:PXGridLevel DataKeyNames="BAccountID" DataMember="Projections">                 <Columns>                     <px:PXGridColumn DataField="BAccountID" Width="90px">                         <ValueItems MultiSelect="False">                         </ValueItems>                     </px:PXGridColumn>                     <px:PXGridColumn DataField="ContactID" Width="200px">                         <ValueItems MultiSelect="False">                         </ValueItems>                     </px:PXGridColumn>                     <px:PXGridColumn DataField="Subject" Width="200px">                         <ValueItems MultiSelect="False">                         </ValueItems>                     </px:PXGridColumn>                     <px:PXGridColumn DataField="AcctName" Width="200px">                         <ValueItems MultiSelect="False">                         </ValueItems>                     </px:PXGridColumn>                 </Columns>             </px:PXGridLevel>         </Levels>         <AutoSize Container="Window" Enabled="True" MinHeight="200" />     </px:PXGrid> </asp:Content>

So here we use only PXGrid where we define fields.

After add this page to site map and Go to page PR101000 to test.

All work fine. Make sure that fields updateble in both tables.

Also do not forget that you can use 2 and more tables in one implementation. 

Thank you for reading. Have you question? Please leave comments here.

No Comments

Add a Comment
 

 

How To Separate Automation Schedules In Acumatica

 

Hello everybody,

take a loot at the following picture:

Let's say that you would like to have two Acumatica instances connected to the same database. Is it possible? Definetely yes, just with pointing both of them to the same connection string and you'll get some kind of scalability. 

But imagine that your Acumatica has execution of some automation schedules. How to make sure, that only one of them will be executor of Automation schedules, not both of them?

Very simple. Just add this key to web.config of Acumatica which should not be Automation schedules executor:

 <add key="DisableScheduleProcessor" value="True"/>

Default value of DisableScheduleProcessor key is false, so you need to say which Acumatca instance shouldn't think about execution schedules. With this simple trick you can have two or even more instances of Acumatica and regulate which of those instances will execute schedules

No Comments

Add a Comment
 

 

How To Modify Stock Item Screen In202500 In Acumatica

 

Hello everybody,

today I want to describe how to extend Stock Item screen IN202500 in Acumatica. Imagine that you need to add to tab General settings two selectors. Suppose that you need to have two selectors:

as you can see following need to be achieved:

  1. To tab General Settings it is needed to add selectors: "Clase articulo web" and "Subclase articulo web".
  2. In case if selector "Clase articulo web" changes, then "Subclase articulo web" should show some other values.

The first step should be start Acumatica developer project as described here.

For cases if we have dependency of one selector from another it is possible to program in two ways:

  1. Custom selector for dependent code.
  2. Describe dependency in DAC class or DAC class extension.

Option number 1 or custome selectors were already described at mine blog here

Let's take a look at second scenario. Before we continue let's create two tables: UsrArticul and UsrSubArticul. In order to make life simple, you can use SQL below in order to follow me:

SET ANSI_NULLS ON
 
 
SET QUOTED_IDENTIFIER ON
 
 
CREATE TABLE [dbo].[UsrArticul](
	[CompanyID] [INT] NOT NULL,
	[ArticulID] [INT] IDENTITY(1,1) NOT NULL,
	[ArticulCD] [NVARCHAR](50) NULL,
	[ArticulName] [NVARCHAR](50) NULL,
 CONSTRAINT [PK_Articul] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[ArticulID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[UsrSubArticuls](
	[CompanyID] [int] NOT NULL,
	[ArticulID] [int] NOT NULL,
	[SubArticulID] [int] IDENTITY(1,1) NOT NULL,
	[SubArticulCD] [nvarchar](50) NULL,
	[SubArticulName] [nvarchar](50) NULL,
 CONSTRAINT [PK_UsrSubArticuls] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[ArticulID] ASC,
	[SubArticulID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) 

I always use prefix Usr in Acumatica in order to notify updater of Acumatica that during upgrade of version those two tables shouldn't be deleted. 

Next let's insert some demo data:

INSERT INTO [dbo].[UsrArticul] ([CompanyID] ,[ArticulCD] ,[ArticulName]) VALUES (2, 'ART1', 'Articul 1' )
INSERT INTO [dbo].[UsrArticul] ([CompanyID],[ArticulCD],[ArticulName]) VALUES (2, 'ART2', 'Articul 2' )
INSERT INTO [dbo].[UsrArticul]([CompanyID],[ArticulCD],[ArticulName]) VALUES (2, 'ART3', 'Articul 3' )
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,,'SUB1A' ,'SUB 1 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1, 'SUB1b' ,'SUB 1 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1, 'SUB1c' ,'SUB 1 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1,  'SUB1d','SUB 1 d')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2,'SUB2A','SUB 2 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2b' ,'SUB 2 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2c' ,'SUB 2 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2d' ,'SUB 2 d')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3A', 'SUB 3 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3b', 'SUB 3 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3c', 'SUB 3 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3,'SUB3d', 'SUB 3d')

We have three root articuls and each of those root articuls has 4 child sub articuls. And task is the following, if User select Articul 1 at top selector, then bottom selector should show SUB 1 A, SUB 1 b, Sub 1 c and Sub 1 d. 

In order to have access to those two controls on page IN202500 we need:

  1. Create DAC classes for tables UsrArticule and UsrSubArticuls
  2. Exted DAC class InventoryItem with declaration of two fields as selectors
  3. Add two selectors on the page

In order to create two DAC classes, you can use either standard Acumatica DAC class generator, or download mine utility which does the same. If you decide to use utility then also keep in mind that you need delete from generated code following fields: CreatedByID, CreatedByScreenID, CreatedDatetime, LastModifiedByID, LastModifiedByScreenID, LastModifiedDateTime, Tstamp. I skipped those fields that Acumatica uses in order to make sample easier to understand. But in real life projects I definetely recommend to have those fields especially if you have multiuser environment where few users can modify the same entity. In that case those service fields is a must.

Take a look at two DAC classes about UsrArticul and UsrSubArticuls:

[Serializable]
    public class UsrArticul : IBqlTable 
    {
        #region ArticulID
        public abstract class articulID : IBqlField
        {
        }
        [PXDBInt(IsKey = true)]
        [PXUIField(DisplayName = "Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? ArticulID { getset; }
 
        #endregion
 
        #region route
        public abstract class articulCD : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Articul CD", Visibility = PXUIVisibility.Visible)]
        public virtual string ArticulCD { getset; }
        #endregion
 
        #region route
        public abstract class articulName : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Articul Name", Visibility = PXUIVisibility.Visible)]
        public virtual string ArticulName { getset; }
        #endregion
       
    }

and another class:
    [Serializable]
    public class UsrSubArticuls : IBqlTable 
    {
        #region ArticulID
        public abstract class articulID : IBqlField
        {
        }
 
        [PXDBInt()]
        [PXUIField(DisplayName = "Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? ArticulID { getset; }
 
        #endregion
 
        #region SubArticulID
        public abstract class subArticulID : IBqlField
        {
        }
 
        [PXDBInt(IsKey = true)]
        [PXUIField(DisplayName = "Sub Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? SubArticulID { getset; }
 
        #endregion
 
        #region route
        public abstract class subArticulCD : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Sub Articul CD", Visibility = PXUIVisibility.Visible)]
        public virtual string SubArticulCD { getset; }
        #endregion
 
        #region route
        public abstract class subArticulName : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Sub Articul Name", Visibility = PXUIVisibility.Visible)]
        public virtual string SubArticulName { getset; }
        #endregion
        	
    }

 Next step - create extension class, what are you use. In this example it InventoryItem DAC class:

public class InventoryItemExt : PXCacheExtension<InventoryItem>
   {
       public abstract class usrArticul : IBqlField
       {
       }
 
       [PXDBInt()]
       [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
       [PXUIField(DisplayName = "Articul CD")]
       [PXSelector(typeof(Search<UsrArticul.articulID>), SubstituteKey = typeof(UsrArticul.articulCD))]
       public virtual int? UsrArticul { getset; }
 
       public abstract class usrSubArticul : IBqlField
       {
       }
 
       [PXDBInt()]
       [PXSelector(typeof(Search<UsrSubArticuls.subArticulIDWhere<UsrSubArticuls.articulIDEqual<Current<InventoryItemExt.usrArticul>>>>), 
           SubstituteKey = typeof(UsrSubArticuls.subArticulCD))]
       [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
       [PXUIField(DisplayName = "Sub Articul CD")]
       public virtual int? UsrSubArticul { getset; }
 
   }

Here I describe two selectors. Overhead and downhead. 

Second selector depend of first. Also pay attention that I used  Equal<Current<InventoryItemExt.usrArticul> (not UsrArticul.articulID), for correct filtering because in this moment when you select first field it is "Current "selector, and you use "YourDacExt.Field".

or in full picture:

[PXSelector(typeof(Search<UsrSubArticuls.subArticulID, Where<UsrSubArticuls.articulID, Equal<Current<InventoryItemExt.usrArticul>>>>), 
           SubstituteKey = typeof(UsrSubArticuls.subArticulCD))]

Staff in bold will allow you to select only sub group.

Do not forget build your project!

After that add controls(PXSelector) to .aspx  view page:

<px:PXSelector CommitChanges="True" ID="usrArticul1" runat="server" DataField="UsrArticul" AllowEdit="True" ></px:PXSelector>
<px:PXSelector CommitChanges="True" ID="usrSubArticul1" runat="server" DataField="UsrSubArticul" AutoRefresh="True" AllowEdit="True" ></px:PXSelector>

After that open this page, and you can see this controls:

 Verify second selector:

No Comments

Add a Comment
 

 

Operator In In Bql

 

Hello everybody,

today I want to write a few words about operator in which was presented in SQL for long ago, but weren't available in Acumatica BQL. But time goes on and now you can use it. For example like this:

Object[] values = new String[] { "BXW000004""BXW000005" };
 
                POOrder item = PXSelect<POOrder,
                    Where<POOrder.orderNbrIn<Required<POOrder.orderNbr>>>>.Select(Base, values);

that code will generate following sql statement:

Select * from POOrder POOrder Where POOrder.OrderNbr In ('BXW000005', 'BXW000004')
	Order by POOrder.OrderNbr

I can say that such approach simplifies some tasks that require dynamic passing of arguments.

No Comments

Add a Comment
 

 

Scaling Acumatica Horizontally

 

Hello everybody,

today I want to share one interesting piece of information about horizontal scaling of Acumatica. I mean as usually for majority of people it is clear that Acumatica can work on one machine when DB, IIS and Acumatica lives on one machine. 

One more variant of scaling can be when you have IIS with Acumatica on one machine, and DB on another machine. It can look like this:

Here User lives on his machine, Acumatica ERP server lives on second machine, and site database lives on third machine. That is pretty good working schema especially for cases if you need vertical scalability.

But recently I've discovered little bit more horizontal scalability for Acumatica which looks like this:

And take note, that with such a schema user request will pass through load balancer. You can use as way of example nginx. And one more important feature that really speeds up your performance will be Redis. That scalability option will have following features:

  1. If data is not persisted to db, then it will be stored at Redis. 
  2. You can have as much as you'd like ( or can afford ) to have Acumatica ERP server

Some additional comments on such a schema. First of all I'd like to mention that nginx is quite efficient load balancer, so you can really use it for making your life easier. There are some options but for now I'm inclined to recommend to use nginx. One of the reasons of such a sticking is plenty of documentation over it.

For session storage currently I'm more sticked to Redis. And main reason for such an approach is that Redis is also scalable. While MS SQL is also scalable, but as mathematicians say oranges should go with oranges, apples with apples. Redis was born with scalability in mind, while SQL was born for efficient persisting your data. 

As green lines on the schema shows, some time it can happen that your user persisted some data on Node1, made some manipulations and switched to Node2 and that will be done seemlessly. 

No Comments

Add a Comment
 

 

Automation Shedule Screen Is Not Executed

 

Hello everybody,

today I want to describe interesting feature of Acumatica related to back ups of database. Imagine the following: you've restored database from back up at your dev environment. And let's say you have automation schedule that every hour you should send to each contact some emails. So, you've restored customization and would like to find the button that will block sending of all emails. At which screen it is? At none. It is interesting to know, that Acumatica already "pressed" at this button when you've restored db from back up. But for now you probably have another question. How to debug execute any schedule at all?

I propose following steps:

  1. Delete all automation schedules.
  2. Create new automation schedule just fory our screen.

Delete all automation schedules

That is relatively simple task. As one of the ways you can execute following sql:

UPDATE AUSchedule set DeletedDatabaseRecord = 1

After that your screen SM205030 ( aka Automation Schedules ) will look pretty lonely and orphaned, but your partners will not mark your mail server as spam after you initialize schedule

Create new schedule

  1. Navigate to screen SM205020
  2. Create schedule with usage of screen that you want to use

Come back to screen SM205030 and press at button Initialize Scheduler as presented at screenshot:

and after that you'll have opportunity to debug your screen. Take note that button Initialize scheduler can't be unpressed. 

No Comments

Add a Comment
 

 

How To Modify Default Time Zone At Acumatica

 

Hello everybody,

today I want to make a short notice on how to change default time zone in Acumatica. 

In my case such need appeard when it was needed to modify default time zone of Acumatica that I've restored from database backup. In order to do this you'll need to go to screen that is named Site preferences. Id of that screen is sm200505. 

No Comments

 

Add a Comment
 

 

Branch Restriction In Automation Schedule In Acumatica

 

Hello everybody,

today I want to describe one behaviour of Acumatica processing screen. 

So, once upon a time I created processing screen. Purpose of that screen was simple: take data from some external source and insert it into Acumatica. 

As that processing screen worked fine, it was taken a decision to create automation schedule step that will make that screen to be executed by Acumatica automatically. And then following issue arised: also that screen worked great in manual mode, it didn't work at all in Automation schedule mode at all. 

After long investigation I found the following:

  1. Automation screens are executed from the user acount admin
  2. Also admin should have access to everything, that is not always the case with Acumatica. In my case admin account didn't have access to branches.

As result, nothing was imported. So, what was solution to that issue? Change scope to PXReadBranchRestrictedScope like this:

                var thr = new Thread(
                    () =>
                    {
                        using (new PXReadBranchRestrictedScope())
                        {
                            var portionsCustomers = customers.Skip(a * sizeOfOneChunk).Take(sizeOfOneChunk).ToList();
                            InsertCustomersFromList(portionsCustomers);
                        }   
                    }
                );

as soon, as I've applied PXReadBranchRestrictionScope life become easier, and data started to flow into Acumatica at automation schedule also. If to speak particularly about PXReadBranchRestrictionScope, it has following purpose: remove restriction by branch, that is automatically applied by default to current user. 

No Comments

Add a Comment
 

 

Different Types Of Search In Acumatica

 

Hello everybody,

recently friend of mine gave me wonderful question:

In PXSelect command, I saw Search, Search2, Search3… keywords, please explain the difference.

Thats really good question which shows his attentiveness to details.

So, no let's go part by part.

Targets

First of all, Search statement can be applied to those kinds of attributes: PXSelector, PXDbScalar and PXDefault. 

Also you can apply Search statement to cases when you updated something in cache of Acumatica and what to reopen that part. It can look like this:

Document.Search<POOrder.orderNbr>(currentPoOrder.OrderNbr, currentPoOrder.OrderType);

or like this:

[PXDefault(typeof (Search<Company.baseCuryID>))]

Don't mix attribute use with search in graph. The first form you can use in your methods, while second you can use in DAC class. 

As attributes

If to speak about attributes Search allows you to set specific value of of field in your DAC class. In other words it selects exact field rather then a record. The field specification goes as paramter. If to speak about syntax it is identical to PXSelect.

Following search options exist:

Type of search Description
Search<Field> Gets field value
Search<Field, Where> Gets field value with filtering by Where condition
Search<Field, Where, OrderBy> Gets field value with filtering by Where condition and ordering
Search2<Field, Join> Gets field value with filtering by using Joins with other tables
Search2<Field, Join, Where> Gets field value with filtering by using Joins with other tables and applying where condition
Search2<Field, Join, Where, OrderBy> Gets field value with filtering by using Joins with other tables and applying where condition and ordering
Search3<Field, OrderBy> Gets field with ordering application
Search3<Field, Join, OrderBy> Gets field value with joins and order by application
Search4<Field, Aggregate> Gets aggregated field value
Search4<Field, Where, Aggregate> Gets aggregated field value with filtering by where condition
Search4<Field, Where, Aggregate, OrderBy> Gets field value with filtering by where, aggregation and order by
Search5<Field, Join, Aggregate> Gets field value fiwth application of joins and aggregateds
Search5<Field, Join, Where, Aggregate> Gets field value with application of joins and where and aggregate
Search5<Field, Join, Where, Aggregate> Gets field value based on join, where and aggregate condition
Search6<Field, Aggregate, OrderBy> Gets field value based Aggregate and order by
Search6<Field, Join, Aggregate, OrderBy> Gets field value based on join, aggregate and order by
Coalesce<Search1, Search2> Gets field value with using Search1 or if Search1 gives null uses Search2

I hope with this table you can now better understand which search to use

 

7 Comments

  • Dmitrey Makarov-Paton said

    Thanks, Yuriy, great post. Please post more. Pls, can you clarify one thing though?

    Case 1. (DataView.Search) Does the query executed against the database every time or it searches the caches first? What happens on the consecutive calls to the search.

    Case 2.
    Does the query executed when the item inserted into the cache only.? What happens on the consecutive calls also. Does the cache of the type specified is searched?

  • docotor said

    Hello Dmitrey,
    for case 1 queries will be executed first time against db, other times if possible against cache in order to minimize load on db.
    For case 2.I'm not sure that I understand it. Can you please add a bit more description to your question

  • Dmitrey said

    Hi Yuriy, thank you for answering. I'm observing a different behaviour though. Perhaps you can spot an issue. Please see my contrived example below. My observations as follows...
    1. DataView.Search always executes SELECT TOP(1) ... for a new key value. Regardless, where there items of this type in the cache.
    2. Same behaviour for Search in PXDefault

    Please try the code below. Set up SQL PROFILER to for RPC:Competed & SQL:BatchCompleted events.
    Thanks. Keep up with your awesome posts.

    public class DataContext : PXGraph<DataContext>, IDisposable {

    public PXSelect<PriceList> PriceList;
    public PXSelect<ParkingLot> ParkingLots;

    public void Init() {
    PriceList.Select();
    }

    public void Dispose() => this.Clear();
    public ParkingLot CreateParkingLot() => (ParkingLot)ParkingLots.Cache.CreateInstance();
    }


    public class Test {

    public void DoThings1() {

    using (DataContext dc = PXGraph.CreateInstance<DataContext>()) {
    dc.Init(); // Select all prices

    int parkingLotId = 2;
    dc.ParkingLots.Select(); // Database is quired, how many records you have in db now in cache
    ParkingLot item = dc.ParkingLots.Cache.Cached.Cast<ParkingLot>().FirstOrDefault(row => row.ParkingLotID == parkingLotId);
    Debug.Assert(item != null, "Must in the cache");
    dc.ParkingLots.SetValueExt<ParkingLot.grade>(item, "B"); // Your other example! item's status does not change to Updated!!

    //.....
    var instance1 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // Database queried again. SELECT TOP(1) ... WHERE ID=1
    Debug.Assert(Object.ReferenceEquals(instance1, item), "Same");
    Debug.Assert(instance1.ParkingLotID == item.ParkingLotID, "Same");

    //....
    var instance2 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // 2nd call, Database NOT queried here unless you change ID to 2.
    Debug.Assert(Object.ReferenceEquals(instance1, instance2), "Same");


    var instance3 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "C")); // Triggers query on pxdefault
    Debug.Assert(Decimal.Compare(instance3.PricePerDay.Value, 5) == 0);


    var instance4 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "B")); // Triggers query on pxdefault
    Debug.Assert(Decimal.Compare(instance4.PricePerDay.Value, 10) == 0);
    }
    }
    }


    public class PriceList : IBqlTable {
    public abstract class grade : IBqlField { }
    [PXDBString(1, IsKey = true)]
    public string Grade { get; set; }

    public abstract class price : IBqlField { }
    [PXDBDecimal]
    public decimal? Price { get; set; }
    }

    /// <summary>
    /// </summary>
    [Serializable]
    public class ParkingLot : IBqlTable {
    public abstract class parkingLotID : IBqlField {}
    [PXDBIdentity(IsKey = true)]
    [PXUIField(DisplayName = "ID", Enabled = false, Visible = false)]
    [PXDefault]
    public virtual int? ParkingLotID { get; set; }


    public abstract class grade : IBqlField { }
    [PXDBString(1, IsFixed = true)]
    [PXDefault("A")]
    public virtual string Grade { get; set; }


    public abstract class pricePerDay : IBqlField { }
    [PXDBDecimal()]
    [PXDefault(typeof(Search<PriceList.price, Where<PriceList.grade, Equal<Current<ParkingLot.grade>>>>))]
    public virtual decimal? PricePerDay { get; set; }
    }


    create table [dbo].[PriceList](
    [Grade] [nchar](1) NOT NULL PRIMARY KEY,
    [Price] [decimal](18, 2) NOT NULL,
    )

    create table [dbo].[ParkingLot](
    [ParkingLotID] [int] IDENTITY(1,1) NOT NULL primary key,
    [Grade] [nchar](1) NULL,
    [PricePerDay] [decimal](18, 2) NULL,
    )

  • docotor said

    Hi Dmitrey,
    thanks for valuable test and comment. You are right.

  • docotor said

    Just want to add. The query that always execute against database are PXSelectReadOnly and it's modifications. Other kind of queries can execute against db, but also can omit db, and read everything from cache or part from cache and part from db.

  • Dmitrey Makarov-Paton said

    Thanks Yuri. Where do you work? What's your skype/email?

  • docotor said

    My skype is zaletskiy. For now I work as remote contractor and I mainly specialize on Acumaitca extending. I've send you an email message also.

Add a Comment
 

 

How To Modify Approve And Reject Actions In Purchase Orders Screen

 

Hello everybody,

today I want to share some knowledge about interesting feature of Acumatica: Approve and Reject actions in Purchase orders screen.

When I was asked how long it will take to modify behaviour of Approve and Reject actions, I've thought it will be easy task. Find appropriate Actions, overload then and enjoy life. But with those two actions life is more complicated. 

After speaking with Acumatica support I've realized that those two actions are declared as Automation steps, so in order to work with those actions it will be needed to look into knowledge about Automation steps. The only memeber that has relation to those actions are type of EPApprovalAutomation. 

So, in order to modify behaviour of those two actions following code snipped is useful:

public class POOrderEntryExt : PXGraphExtension<POOrderEntry>
{
    public override void Initialize()
    {
        Base.FieldVerifying.AddHandler<POOrder.rejected>((s, a) =>
        {
            if ((bool?)a.NewValue == true)
            {
                if (Base.Document.Ask("Custom Warning""Do you want to proceed?"MessageButtons.YesNo) != WebDialogResult.Yes)
                {
                    string errorMessage = "The Reject operation was canceled";
                    PXUIFieldAttribute.SetError<POOrder.approved>(s, a.Row, errorMessage);
                    throw new PXSetPropertyException(errorMessage);
                }
            }
        });
    }
}

Suppose that you also need to modify captions on button. For example caption of button Reject. I have step by step picture manual which you can use for your purposes:

No Comments

Add a Comment