How to work with filters applied to grid in Acumatica

Hello everybody,

today I want to describe how to work with Filters, that applied to grid in Acumatica page.

For demonstration purposes I'll use Requisition form. 

So, first of all, I want to say that dealing with Filters you'll need .View.GetExternalFilters() method.

At requisition form graph extension you can get it like this:

List<PXFilterRow> filters = Base.Lines.View.GetExternalFilters()?.ToList() ?? new List<PXFilterRow>();

Demonstrated line of code will give you list of applied filters, or empty list, if no filters were applied to grid.

honestly speaking, applied filters look very similarly to applied ODATA protocol. On my grid I have custom column UsrRQOwner, and you see, that word EQ is applied. It stands for Equal.

Second filter is applied by filtering by InventoryID, and as you see, inventoryID uses LIKE. It is not available in ODATA directly, but I'm under impression that Acumatica borrowed idea from ODATA protocol.

So, that basically it. If you need to know in your code, which filters were applied to your data in grid, then GetExternalFilters is your friend.

How to create unique index in MS SQL that will allow NULL

Hello everybody,

today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

How to achieve it? Following T-SQL can do this:

CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;

 

in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition.

What is Object_id in MS SQL

Hello everybody,

today I want to leave a short notice on OBJECT_ID in MS SQL.

Quite often I've seen sql like this:

IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL DROP TABLE dbo.Students;

Today I have found time to discover what it actually means. 

OBJECT_ID is function, that checks wheather object exists or not. It accepts two parameters: object name and object type. In my case object name is dbo.Students, object type U is internal representation of table.

How to react on clicking on Abort button in Acumatica processing screen

Hello everybody,

today I want to leave a notice with proposal on how to program reaction on clicking of Abort button in processing screen.

Recently it become needed for me to program some functionality to happen after user clicks on button Abort of processing screen. 

In my case I needed somehow to program exit from some threads which were running in parallel and I needed to finish one iteration of threads and then exit from each of those threds.

One more point was that just terminating of threads was also a bad idea. After a while I have found IPXCustomInfo interface. With that interface you can gain control on what to do in case of user 

clicking on Abort button. In my case I did the following:

  1. Created my own implementation of IPXCustomInfo like this:
public class PXImportCallback : IPXCustomInfo
{
    public void Complete(PXLongRunStatus status, PXGraph graph)
    {
        switch (status)
        {
            case PXLongRunStatus.Aborted:
                PXDatabase.Update<Sync>(
                    new PXDataFieldAssign<Sync.lastModifiedDateTime>(DateTime.Now),
                    new PXDataFieldAssign<Sync.wasExecuted>(false));
                break;
 
            case PXLongRunStatus.Completed:
                break;
 
            case PXLongRunStatus.InProcess:
                break;
 
            case PXLongRunStatus.NotExists:
                break;
        }
    }
}

As you can see, very simple interface, which requires of you to implement only one method: Complete, and inside of that method program supposed reaction.

Then for each StartOperation I've added following lines:

PXLongOperation.StartOperation(thisdelegate
{
    PXLongOperation.SetCustomInfo(new PXImportCallback());
 
    var orderFromDb = PXSelectReadonly<

and that's all! Each time users clicks on Abort, my programmed reaction appears, and I can clean memory and delete no longer needed files.

 

Excel spreadsheet commands

  • F1 Displays the Office Assistant or (Help > Microsoft Excel Help)
  • F2 Edits the active cell, putting the cursor at the end
  • F3 Displays the (Insert > Name > Paste) dialog box
  • F4 Repeats the last worksheet action (Edit > Repeat)
  • F5 Displays the (Edit > GoTo) dialog box
  • F6 Moves to the next pane in a workbook (if the window is split)
  • F7 Displays the (Tools > Spelling) dialog box
  • F8 Toggles whether to extend a selection with the arrow keys
  • F9 Calculates All the worksheets in All the open workbooks
  • F10 Toggles the activation of the Menu Bar
  • F11 Displays the (Insert > Chart) dialog box that creates a chart (on a chart sheet) using the highlighted range
  • F12 Displays the (File > Save As) dialog box
  • Shift + F2 Inserts or edits a cell comment (Insert > Comment)
  • Shift + F3 Displays the (Insert > Function) dialog box
  • Shift + F4 Repeats the last Find, the same as (Edit > Find Next)
  • Shift + F5 Displays the (Edit > Find) dialog box
  • Shift + F6 Moves to the previous pane in a workbook (if the window is split)
  • Shift + F8 Toggles between switching Add Mode on or off*
  • Shift + F9 Calculates just the active worksheet
  • Shift + F10 Displays the (Shortcut) menu for the selected item
  • Shift + F11 Inserts a new worksheet (before the active sheet) into the active workbook (Insert > Worksheet)
  • Shift + F12 Saves, Displays the (File > Save As) dialog box if a new workbook
  • Ctrl + F1 Closes and reopens the current task pane (ribbon in 2007)
  • Ctrl + F2 Print Preview
  • Ctrl + F3 Displays the (Insert > Name > Define) dialog box
  • Ctrl + F4 Closes the selected workbook window
  • Ctrl + F5 Restores the size of the active workbook or window
  • Ctrl + F6 Moves to the next open workbook or window
  • Ctrl + F7 Activates the Move window command
  • Ctrl + F8 Activates the Resize window command
  • Ctrl + F9 Minimises the size of the active workbook or window
  • Ctrl + F10 Maximises the size of the active workbook or window
  • Ctrl + F12 Displays the (File > Open) dialog box
  • Alt + F1 Creates a chart (on a chart sheet) using the highlighted range
  • Alt + F2 Displays the (File > Save As) dialog box
  • Alt + F4 Closes all the workbooks (saving first) and exits Excel (File > Exit)
  • Alt + F8 Displays the (Tools > Macro >Macros) dialog box
  • Alt + F11 Toggles between the Visual Basic Editor window and and the Excel window
  • Alt + Ctrl + F9 Calculates All cells on All worksheets in All workbooks
  • Alt + Shift + F1 Inserts a new worksheet (before the active sheet) into the active workbook (Insert > Worksheet)
  • Alt + Shift + F2 Displays the (File > Save As) dialog box
  • Alt + Shift + F4 Closes all the workbooks (saving first) and exits Excel (File > Exit)
  • Alt + Shift + F10 Displays the drop-down menu for the corresponding smart tag
  • Alt + Shift + F11 Activates the Microsoft Script Editor window
  • Ctrl + Shift + F3 Displays the (Insert > Name > Create) dialog box
  • Ctrl + Shift + F6 Moves to the previous open workbook or window
  • Ctrl + Shift + F10 Activates the Menu Bar or Shortcut Characters in 2007
  • Ctrl + Shift + F12 Displays the (File > Print) dialog box
  • Ctrl + 0 Hides the columns in the current selection
  • Ctrl + 1 Displays the formatting dialog box
  • Ctrl + 2 Toggles bold on the current selection
  • Ctrl + 3 Toggles italics on the current selection
  • Ctrl + 4 Toggles underlining on the current selection
  • Ctrl + 5 Toggles the strikethrough of text on the current selection
  • Ctrl + 6 Toggles between hiding, displaying
  • Ctrl + 8 Toggles the display of Outline symbols on the active worksheet
  • Ctrl + 9 Hides the rows in the current selection (Format > Row > Hide)
  • Ctrl + Shift + 0 Unhides the columns in the current selection
  • Ctrl + Shift + 2 Enters the value from the cell directly above into the active cell
  • Ctrl + Shift + 8 Selects the current region (surrounded by blank rows and columns)
  • Ctrl + Shift + 9 Unhides the rows in the current selection
  • Ctrl + A Displays formula palette given a function name or selects the whole worksheet or current date
  • Ctrl + B Toggles bold on the current selection
  • Ctrl + C Copies the current selection to the clipboard
  • Ctrl + D Copies the first cell in the selection downwards
  • Ctrl + F Displays the Find dialog box
  • Ctrl + G Displays the GoTo dialog box
  • Ctrl + H Displays the Replace dialog box
  • Ctrl + I Toggles italics on the current selection
  • Ctrl + K Displays the Insert Hyperlink dialog box
  • Ctrl + L Displays the Create Table dialog box
  • Ctrl + N Creates a new workbook
  • Ctrl + O Displays the Open dialog box
  • Ctrl + P Displays the Print dialog box
  • Ctrl + R Copies the leftmost cell in the selection to the right
  • Ctrl + S Saves, Displays the Save As dialog box if a new workbook
  • Ctrl + U Toggles underlining on the current selection
  • Ctrl + V Pastes the entry from the clipboard
  • Ctrl + W Closes the active workbook or window
  • Ctrl + X Cuts the current selection to the clipboard
  • Ctrl + Y Repeats the last workbook action
  • Ctrl + Z Undo the last workbook action
  • Ctrl + Shift + F Activates the Font Tab of the format cells
  • Ctrl + Shift + O Selects all the cells with comments
  • Ctrl + Shift + P Activates the Font Size tab of the format cells
  • Enter Enters the contents of the active cell and moves to the cell below (by default)
  • Shift + Enter Enters the contents of the active cell and moves to the cell above (by default)
  • Tab Enters the contents of the active cell and moves one cell to the right
  • Shift + Tab Enters the contents of the active cell and moves one cell to the left
  • Alt + = Enters the SUM() function (AutoSum) to sum the adjacent block of cells
  • Alt + 0128 Enters the euro symbol (€) (using Number keypad)
  • Alt + 0162 Enters the cent symbol (¢) (using Number keypad)
  • Alt + 0163 Enters the pound sign symbol (£) (using Number keypad)
  • Alt + Enter Enters a new line (or carriage return) into a cell
  • Ctrl + ' Enters the contents from the cell directly above into the active cell
  • Ctrl + Shift + 2 Enters the value from the cell directly above into the active cell
  • Ctrl + ; Enters the current date into the active cell
  • Ctrl + Shift + ; Enters the current time into the active cell
  • Ctrl + Shift + Enter Enters the formula as an Array Formula
  • Shift + Insert Enters the data from the clipboard
  • Alt + Down Arrow Displays the Pick From List drop-down list Esc Cancels the cell entry and restores the original contents
  • Delete Deletes the selection or one character to the right
  • Backspace Deletes the selection or one character to the left Shift + Delete Cuts the selection to the clipboard
  • Ctrl + Delete Deletes text to the end of the line Ctrl + - Displays the Delete dialog box
  • Ctrl + Shift + = Displays the Cells dialog box
  • Ctrl + \ Selects the cells in a selected row that do not match the value in the active cell
  • Ctrl + Shift + \ Selects the cells in a selected column that do not match the value in the active cell
  • Ctrl + / Selects the array containing the active cell ??
  • Alt + ; Selects the visible cells in the current selection
  • Ctrl + Shift + (8 or *) Selects the current region (surrounded by blank rows and columns)
  • Ctrl + * Selects the current region (using the * on the number keyboard)
  • Ctrl + [ Selects all the cells that are directly referred to by the formula in the active cell (precedents)
  • Ctrl + Shift + [ Selects all the cells that are directly (or indirectly) referred to by the formula in the active cell
  • Ctrl + ] Selects all the cells that directly refer to the active cell (dependents)
  • Ctrl + Shift + ] Selects all the cells that directly (or indirectly) refer to the active cell
  • Ctrl + Shift + Page Down Selects the active worksheet and the one after it
  • Ctrl + Shift + Page Up Selects the active worksheet and the one before it
  • Ctrl + Shift + Spacebar Selects all the objects on the worksheet when an object is selected or selects the whole worksheet
  • Ctrl + Spacebar Selects the current column
  • Shift + Arrow keys Selects the active cell and the cell in the given direction
  • Shift + Backspace Selects the active cell when multiple cells are selected
  • Shift + Spacebar Selects the current row
  • Ctrl + Shift + Arrow Key Extends the selection to the next cell adjacent to a blank cell in that direction
  • Ctrl + Shift + End Extends the selection to the last used cell on the worksheet
  • Ctrl + Shift + Home Extends the selection to the beginning of the worksheet
  • Shift + Arrow Keys Extends the selection by one cell in that direction
  • Shift + Home Extends the selection to the first column
  • Shift + Page Down Extends the selection down one screen
  • Shift + Page Up Extends the selection up one screen
  • End + Shift + Arrow Keys Extends the selection to the next non-blank cell in that direction
  • End + Shift + End Extends the selection to the last cell in the current row*
  • End + Shift + Home Extends the selection to last used cell on the worksheet
  • Alt + ' Displays the Style dialog box
  • Ctrl + Shift + ( ' or ) Applies the Time format "hh:mm" to the selection
  • Ctrl + Shift + (1 or !) Applies the Comma separated format "#,##0.00" to the selection
  • Ctrl + Shift + (4 or $) Applies the Currency format "£#,##0.00" to the selection
  • Ctrl + Shift + (5 or %) Applies the Percentage format "0%" to the selection
  • Ctrl + Shift + (6 or ^) Applies the Exponential format "#,##E+02" to the selection
  • Ctrl + Shift + (# or ~) Applies the General format to the selection
  • Ctrl + (# or ~) Applies the Custom Date format "dd-mmm-yy" to the selection
  • Ctrl + Shift + (7 or &) Applies the outline border to the selection
  • Ctrl + Shift + (- or _ ) Removes all the borders from the selection
  • Arrow Keys Moves to the next cell in that direction
  • Ctrl + Tab Moves to the next open workbook or window
  • Alt + Tab Moves to the next application open on your computer
  • Alt + Shift + Tab Moves to the previous application open on your computer
  • Enter Moves to the cell directly below
  • Tab Moves to the next cell on the right (or unprotected cell)
  • Home Moves to the first column in the current row
  • End + Arrow Keys Moves to the next non empty cell in that direction
  • End + Enter Moves to the last cell in the current row that is not blank
  • End + Home Moves to the last used cell on the active worksheet*
  • End + Home Moves to the last used cell on the active worksheet*
  • Page Down Moves to the next screen of rows down
  • Page Up Moves to the previous screen of rows up
  • Shift + Enter Moves to the cell directly above (opposite direction to Enter)
  • Shift + Tab Moves to the cell directly to the left (opposite direction to Tab)
  • Alt + Page Down Moves you one screen of columns to the right
  • Alt + Page Up Moves you one screen of columns to the left
  • Ctrl + Home Moves to cell "A1" on the active sheet
  • Ctrl + End Moves to the last used cell on the active worksheet*
  • Ctrl + Up Arrow Moves to the first row in the current region
  • Ctrl + Down Arrow Moves to the last row in the current region
  • Ctrl + Left Arrow Moves to the first column in the current region
  • Ctrl + Right Arrow Moves to the last column in the current region
  • Ctrl + Page Up Moves to the previous worksheet in the workbook
  • Ctrl + Page Down Moves to the next worksheet in the workbook
  • Ctrl + Shift + Tab Moves to the previous open workbook or window
  • Scroll Lock + Arrow Keys Moves the workbook or window one cell the corresponding direction
  • Scroll Lock + End Moves to the last cell in the current window
  • Scroll Lock + Home Moves to the first cell in the current window
  • Scroll Lock + Page Down Moves you down one screen (current selection unchanged
  • Scroll Lock + Page Up Moves you up one screen (current selection unchanged)
  • Enter Moves from top to bottom within a selection
  • Tab Moves from left to right within a selection
  • Ctrl + . Moves clockwise to the next corner within a selection
  • Shift + Tab Moves from right to left within a selection (opposite direction to Tab)
  • Alt + Ctrl + Left Arrow Moves to the left between non adjacent cells in a selection
  • Alt + Ctrl + Right Arrow Moves to the right between non adjacent cells in a selection = Starts a Formula
  • Ctrl + ` Toggles between the value layer and the formula layer
  • Ctrl + Insert Copies the current selection to the clipboard
  • Ctrl + Shift + ( Unhides any hidden rows within the selection
  • Ctrl + Shift + ) Unhides any hidden columns within the selection
  • Ctrl + Shift + \ Select unequal cells

Create view if not exists in Acumatica customization

Hello everybody,

today I want to leave a short post on how to create some custom view in Acumatica Customization if that view doesn't exist:

if not exists (select * from sysobjects where name='CustomerSelector' and xtype='V')
begin
exec (
    'CREATE view [CustomerSelector] as 
		select Distinct b.CompanyID, cc.Phone1, a.City, a.CountryID, b.TaxRegistrationID, c.CuryID, cc.Salutation, c.CustomerClassID, b.Status
		from BAccount b inner join customer c on b.BAccountID = c.BAccountID inner join [Address] a 
		on a.BAccountID = b.BAccountID inner join Contact cc on cc.BAccountID = b.BAccountID'
		)
 
end

This code will create view CustomerSelector only if such view doesn't exist in Acumatica

How to read information from Acumatica chunk by chunk

Hello everybody,

today I want to leave short fragment of code, which anybody can use for processing records in Acumatica chunk by chunk. By default, everybody in Acumatica including myself uses Select method. Select works quite 

well, but has one limitation: it reads all information that satisfies filtering conditions. But imagine that you need to read not all records, but some limited set i.e. top 5000 records.

Next code fragment shows how to read from table CSalesOrder top 5000 records:

PXSelectReadonly<CSalesOrder,
                    Where<CSalesOrder.CustImportedEqual<False>,
                        And<CSalesOrder.CustomerErrorIsNull>>>.SelectWindowed(this, 0, 5000).
                        Select(x => x.GetItem<CSalesOrder>()).ToList();

SelectWindowed will give you only 5000 records or less, depending what you have in your db.

How to avoid echo during usage of TeamViewer

Hello everybody,

here I want to leave a short notice on how to avoid hearing yourself if you are connected over TeamViewer to somebodies computer. By defualt TeamViewer duplicates sound that goes through sound system of computer to which you've connected.

Not many people know, that such functionality can be disabled. For this purpose you need to navigate to the following: 

  1. Communicate
  2. Computer Sound
  3. Remove checkbox. 

After those steps you'll be able to avoid hearing sound of yourself via skype or other communication utility.

For convenience I have also screenshot:

How to add parallelism to Shipments creation in Acumatica

Hello everybody,

Today I want to leave short note on new feature of Acumatica: ParallelProcessingOptions. Main purpose of that class is adding paralelism to processing screens. Take a look on it's declaration:

public abstract class PXProcessingBase<Table> : PXSelectBase<Table>, IPXProcessing, IPXProcessingWithCustomDelegate where Table : class, IBqlTable, new()
 {
   .
.
  .
   public System.Action<PXParallelProcessingOptions> ParallelProcessingOptions;
   .
.

}

As you can see, ParallelProcessingOptions is a member of PXProcessingBase, while PXProcessingBase is a parent for PXProcessing. For Acumatica developers it means that starting from 2018 R2 you can easily turn on parallel processing 

but only for processing screens. You'll not be able to turn it on for Data entry screens. Also you can turn it on for base Acumatica graph. For example to turn it on for creation of shipments, you can use following code:

public class SOCreateShipmentExt : PXGraphExtension<SOCreateShipment>
{
    public override void Initialize()
    {
        base.Initialize();
 
        Base.Orders.ParallelProcessingOptions =
            settings =>
            {
                string action = null;
                if (Base.Filter.Current != null)
                {
                    action = Base.Filter.Current.Action;
                }
 
                settings.IsEnabled = !string.IsNullOrEmpty(action) && action.StartsWith("PrepareInvoice"StringComparison.OrdinalIgnoreCase);
                settings.BatchSize = 10;
            };
    }
}

Similar behavior you can get for Invoicing of shipments:

public class SOInvoiceShipmentExt : PXGraphExtension<SOInvoiceShipment>
   {
       public override void Initialize()
       {
           base.Initialize();
 
           Base.Orders.ParallelProcessingOptions =
               settings =>
               {
                   string actionID = null;
                   if (Base.Filter.Current != null)
                   {
                       actionID = (string)Base.Orders.GetTargetFill(nullnullnull, Base.Filter.Current.Action, "@actionID");
                   }
                   settings.IsEnabled = (actionID == "2");
                   settings.BatchSize = 10;
               };
       }
   }

and for releasing of invoices:

public class SOReleaseInvoiceExt : PXGraphExtension<SOReleaseInvoice>
{
    public override void Initialize()
    {
        base.Initialize();
 
        Base.SOInvoiceList.ParallelProcessingOptions =
            settings =>
            {
                string actionID = null;
                if (Base.Filter.Current != null)
                {
                    actionID = (string)Base.SOInvoiceList.GetTargetFill(nullnullnull, Base.Filter.Current.Action, "@ActionName");
                }
                settings.IsEnabled = (actionID == "Release");
                settings.BatchSize = 10;
            };
    }
}

As you can see, pattern of usage is pretty straightforward: 

  1. Initialize
  2. Set ParallelProcessingOptions depending of action or automation steps.

In my tests, pefrormance improvement was up to 40 times faster. 

How to filter customer by Email in Acumatica API with Postman

Hello everybody,

I want to add few more words about Acumatica API usage.

Recently I was asked on how to find customer by email in Acumatica. And I was asked to provide example of Postman functionality. That wasn't easy task, first of all because I'm not very skilful in Postman, but after few more efforts I was able to deliver Postman url.

But first of all let's consider how to configure Acumatica.

Acumatica configuration.

First of all, you'll need to either use your own endpoint, or default. For learning sake I recommend you to use your own. Also add customers screen to it. First gif visualizes this process:

 

I want to add few comments on this visualtion. Object name, that I gave: Customers has meaning. Later on I will use it in forming request.

Also in this gif I didn't show clicking on Save button, but I hope it's understandable by default. Next step goes adding fields. Take a look on another gif: