Addition to PXDatabase.Update in Acumatica

Hello everybody,

today I want to share a new feature for me in Acumatica from code standpoint. 

So, suppose that you want to update some column in Acumatica database, but you want to avoid graph creation, loading necessary fields, and all that you need to do is just update column. For example over adding some value.

You can't achieve it with simple call to

PXDatabase.Update<>()

Or if you can, then let me know, because I didn't find the way how to do it. 

But instead I found another new method in Acumatica which is named PXUpdate.

For example you can use this method for adding to each email in table contact value ".someaddition"

PXUpdate<Set<Contact.eMailAdd<Contact.eMailRequired<Contact.eMail>>>, Contact>.Update(Base, ".someaddtion");

What if you want to make update not all fields, but just some of them? Is it possible to add filtering condition? You'd be surprised to know that yes, you can add filtering condition as well. For example like this:

PXUpdate<Set<Contact.eMailAdd<Contact.eMailRequired<Contact.eMail>>>, Contact, 
    Where<Contact.eMailLike<Contact.eMail>>>.Update(Base, ".someaddtion""%somemask%");

With all of this staff you have in your hands two-folds sword: great power and great responsibility, direct update in database.

How to get companyID in Acumatica

Hello everybody,

today I want to leave another post related to getting Acumatica. There is one more way of gettinc current company id. For this purpose you can use class of Acuamtica named SharedFunctions.

It has plenty of interesting staff, as well as getting Current company id. Take a look on it:

var companyId = SharedFunctions.GetCurrentCompanyId();

How to override properly CreatePaymentProc method of SOOrderEntry graph in Acumatica

Hello everybody,

today I want to leave short note on how to override and call CreatePaymentProc method of Acumatica which I discovered today with Naveen from Kensium.

My favorite way of overriding methods in Acuamtica with usage of Action and passing there parameters doesn't work. That is because Acumatica uses in method CreatePaymentProc out modifier. Due to this, delegate declaration is needed. 

After some efforts and refactorings we found following code that is working:

public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
{
    public delegate void CreatePaymentBase(SOOrder order, out PXGraph target, string paymentType);
 
    [PXOverride]
    public virtual void CreatePaymentProc(SOOrder order, out PXGraph target, string paymentType,
        CreatePaymentBase baseAction)
    {
        baseAction(order, out target, paymentType);
 
        var resultGraph = target as ARPaymentEntry;
        resultGraph.Document.Current.ExtRefNbr = "12xx14";
    }
}

With such code you can call base method, and modify what it produces.

How to increase length of Customer id column

Hello everybody,

today I want to describe interesting case. Recently I was asked to increase length of Customer ID at page AR303000.

As usually such tasks can be achieved via creating extension to DAC class with setting new length, 

and with SQL statement that increases length of column. But that is not the case with Customer ID. That is because

Customer ID is Segmented Selector. And everything related to segmented selectors should be managed 

at page Segmented Keys page ( CS202000 ). All that is needed to be done is increasing the Length field of segmented keys.

Other approaches can lead to corruption of segmented keys. Especially if you decide to do it in database with DB scripts. 

At screenshot below you can see how to change field length to 30:

 

How to shrink database of Acumatica with rebuilding of

Hello everybody,

today I want to leave another portion of SQL Server optimization for Acumatica database. In case if your db become huge, and you want to make it smaller, you can try following SQL code:

DECLARE @Table NVARCHAR(128)  
DECLARE @Database NVARCHAR(128)
DECLARE @Command NVARCHAR(500)
 
PRINT N'Shrinking database files'
DBCC SHRINKDATABASE(0)
 
PRINT N'Rebuilding all indexes'
SET @Database = DB_NAME()
SET @Command = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + 
    TABLE_NAME + '']'' as TableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = ''BASE TABLE'''   
EXEC (@Command)  
OPEN TableCursor   
 
FETCH NEXT FROM TableCursor INTO @Table   
WHILE @@FETCH_STATUS = 0   
BEGIN   
    PRINT 'Rebuilding all indexes on ' + @Table    
    SET @Command = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
    EXEC (@Command) 
FETCH NEXT FROM TableCursor INTO @Table   
END   

this code does two steps:

  1. Shrink database
  2. Rebuild indexes

Remember for yourself forever that any shrink will destroy your indexes. So each time you decide, or risk to make shrink your database don't forget to rebuild indexes. Otherwise you'll be hugely surprised to discover that also db is small, but performance is terrribly bad.

Just want to say that I've tried that approach on db of one of mine customers and results were the following: 646 Gb got shrinked to 161 Gb. Pretty impressive, huh?

And of course, word of warning. Never try that SQL, without recent back up of your production.

How to find sizes of tables in Acumatica

Hello everybody,

today I want to share with you SQL statement that may help you to find sizes of tables occupied by Acumatica. It goes below:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    --t.Name
	TotalSpaceMB desc, t.Name

with that sql info you can check and see if you need to split some data or clean it

How to make calculated field available in Generic Inquiry screen of Acumatica and Starship

Hello everybody,

today I want to describe interesting case. 

I was asked to add on Acumatica screen Date field, which calculates according to some business logic rules. Task was pretty simple initially to do, so I've jumped, and added that field like this:

public abstract class usrExpectedByDate : IBqlField
{
}
 
[PXDBDate()]
[PXUIField(DisplayName = "Expect Date", IsReadOnly = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
public virtual DateTime? UsrExpectedByDate { getset; }

I provided this customization to customer and had expectation that I can enjoy from my life. 

But with time, customer notified me, that shiping didn't work as expected. Why, because in program starship, which customer used Acumatica added some time information and starship wasn't able to get it properly.

I've decided to add formatting to Acumatica with help of InputMask attribute and DisplayMask attribute like this:

public abstract class usrExpectedByDate : IBqlField
{
}
 
[PXDBDate(InputMask = "MM/dd/yyyy", DisplayMask = "MM/dd/yyyy")]
[PXUIField(DisplayName = "Expected By Date", IsReadOnly = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
public virtual DateTime? UsrExpectedByDate { getset; }

But it didn't change outcome at all. On Acumatica screen it looked perfectly fine, but in import again looked with added hour, minute, second information. 

What programmers do in such cases? Exactly, add string, and add formatting to that string. Here is the string that I've added:

public abstract class usrExpectedByDate2 : IBqlField
{
}
 
[PXString]
[PXUIField(DisplayName = "Expected By Date", IsReadOnly = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
public virtual string UsrExpectedByDate2 {
    get
    {
        if (this.UsrExpectedByDate == null)
            return "";
        return this.UsrExpectedByDate.Value.ToString("MM/dd/yyyy");
    }
    set
    {
        if (value != null)
            this.UsrExpectedByDate = DateTime.Parse(value);
    }
}

That lead to chaning of situation. Instead of added zeros, field become empty. Just null. 

After wondering through source code of Acumatica, I found interesting attriubute: PXDependsOnField. After I've added that attribute, import worked fine. Here is final result:

public abstract class usrExpectedByDate2 : IBqlField
{
}
 
[PXString]
[PXUIField(DisplayName = "Expected By Date", IsReadOnly = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
public virtual string UsrExpectedByDate2 {
    [PXDependsOnFields(typeof(usrExpectedByDate))]
    get
    {
        if (this.UsrExpectedByDate == null)
            return "";
        return this.UsrExpectedByDate.Value.ToString("MM/dd/yyyy");
    }
    set
    {
        if (value != null)
            this.UsrExpectedByDate = DateTime.Parse(value);
    }
}

After those changes Starship support confirmed that information from my field become available on their end.

How to add validation to Create shipment and confirm shipment in Acumatica

Hello everybody,

today I want to write a few words on my latest time spending in Acumatica. Recently I was asked to add additional validations to actions Create shipment:

and Confirm Shipment:

Idea was the following, if user clicks on Create shipment action or at Confirm Shipment action, some function should be executed which throws exception and prevents Creation/confirmation of shipment if some conditions are not meet. 

At first glance task was trivial. I supposed that all that will be needed, just override method Create Shipment of SOOrderEntry and Cofirm Shipment of SOShipmentEntry. And jumped right to the code. 

In order to modify behavior of those two methods, I've created override of Action of SOOrderEntry and added validation inside of that method. My code looked like this:

		[PXOverride]
		public IEnumerable Action(PXAdapter adapter, int? actionID, DateTime? shipDate, String siteCD, String operation, 
			String ActionName, 
			Func<PXAdapterint?, DateTime?, stringstringstringIEnumerable> baseMethod)
		{
            
			List<SOOrder> list = new List<SOOrder>();
			foreach (SOOrder order in adapter.Get<SOOrder>())
			{
				list.Add(order);
			}
			if (actionID == 1)
			{
                //throwed exception here!. WRONG WAY!!!!

I've tested it on Sales Orders page, got exception, added additional code and send it to QA with feeling that I'm great. Unfortunately I was far from making task as done. The next day QA told me that on processing screen my code didn't work at all, 

and asked me to deal with it in another way. As usually programmers and QA's have love-hate relationship, but I myself always happy to deal with QA. I can say that it's always better if QA return you a bug, then angry customer with your boss 

asks you to fix a bug.

So I started coding one more time. I've tried few other ways, for example I've overrided method Persist of SOShipmentEntry like this:

                [PXOverride]
		public void Persist(Action del)
		{
                      //if validation fails, throw exception here. WRONG WAY
}

and before giving that staff to QA I've decided to give a test for this approach and much to my shame it also didn't work as expected. And on the level of processing screen I've got plenty of weird exeptions, and not even exceptions created by my code.

In case if nothing works, the only think that you can do involves deep immersion in Acumatica source code. After deeging deeper here is what I've found inside of action Create shipment:

  1. Create shipment method is not executed right away after click on Actions -> Create shipment. Before that some plumbing code is executed.
  2. Save.Press() is executed multiple times, so when I've throwed exception, then methods for Shipment creation as well as confirmation weren't executed at all.
  3. After pretty big amoung of plumbing code method CreateShipment is executed.

After seeing such behavior I've decided to override methods CreateShipment and ConfirmShipment. Both of those methods are implemented in graph SOShipmentEntry. Override in Acumatica looks pretty stratightforward, especially with anonymous delegates.

Both of those methods I've included in extension of SOShipmentEntry, and methods looks like those:

[PXOverride]
public void CreateShipment(SOOrder order, int? SiteID, DateTime? ShipDate, bool? useOptimalShipDate,
    string operation, DocumentList<SOShipment> list, PXQuickProcess.ActionFlow quickProcessFlow,
    Action<SOOrderint?, DateTime?, bool?, stringDocumentList<SOShipment>, PXQuickProcess.ActionFlow> baseCreateShipment)
{
    var unpaidBalacne = GetDocumentBalance(order);
 
    ShipmentValidator.ValidateShipment(Base, order, unpaidBalacne, WorkFlowMessages.creditLimitExceeded);
    baseCreateShipment(order, SiteID, ShipDate, useOptimalShipDate, operation, list, quickProcessFlow);
}
[PXOverride]
public virtual void ConfirmShipment(SOOrderEntry docgraph, SOShipment shiporder,
    Action<SOOrderEntrySOShipment> baseConfirmShipment)
{
    var shipLines = PXSelect<SOShipLineWhere<SOShipLine.shipmentNbrEqual<Required<SOShipLine.shipmentNbr>>,
            And<SOShipLine.shipmentTypeEqual<Required<SOShipLine.shipmentType>>>>>
        .Select(Base, shiporder.ShipmentNbr, shiporder.ShipmentType).ToList().Select(a => a.GetItem<SOShipLine>());
 
    foreach (SOShipLine shipLine in shipLines)
    {
        ValidateSOShipLine(shipLine, false);
    }
    baseConfirmShipment(docgraph, shiporder);
}

After those changes each page, including processing pages started to work smoothly.

Summary

All of those conclusions that I've made would be impossible to make without debugging of Acumatica source code. Next time when I'll face some not working out of the box my code, I'll jump into debugging and proper debugging right away.

 

 

One more description on how to modify behavior of Sales Orders screen in Acumatica

Hello everybody,

today I want to present a bit differnt code sample on how to append to behavior of Actions menu items of Sales Orders screen. 

Quite often there are tasks like add something before ( preprocessing ) or after some menu item action was executed. Once I've described it alredy here, but just want to give another example with usage of Func.

Take a look on following code sample:

[PXOverride]
public IEnumerable Action(PXAdapter adapter, int? actionID, DateTime? shipDate, String siteCD, String operation, 
    String ActionName, 
    Func<PXAdapterint?, DateTime?, stringstringstringIEnumerable> baseMethod)
{}

as you can see, it's very similar to previous post, just with difference where you have full description of datatypes.

How to modify Process Shipments screen in Acumatica

Hello everybody,

recently I was asked to modify behavior of Process shipments screen in Acumatica. Issue was the following: in case if one of the rows on Process shipments generated error, it was needed to show on the status of processing.

Default behavior was like showed on the screenshot:

 

as you can see on the screenshot, despite errors were generated on the row level, whole status of processing is fine ( green in the top ).

Of course for my QA such kind of behavior is weird and he asked me to fix it. 

Such behavior happens because Process shipments screen ( SO503000 ) uses PXProcessing.SetProcessTarget() to set the processing delegate which doesn't care at all about errors in the lines.

In such cases it means that you need somehow to say to PXProcessing, please take care of errors in the lines. It can be achieved in the following way:

1.  Create yours PXProcessing, that takes care about lines via inheriting from Acumatica created PXProcessing

2. Substitute standard Orders of SOInvoiceShipment view with your extension from point 1.

 

Point 1 looks like this:

public class PXCustomFilteredProcessing<TableFilterTable> : PXFilteredProcessing<TableFilterTable>
    where FilterTable : classIBqlTablenew()
    where Table : classIBqlTablenew()
{
    protected ProcessListDelegate _BaseProcessDelegate;
 
    protected PXCustomFilteredProcessing() : base()
    {
    }
 
    public PXCustomFilteredProcessing(PXGraph graph) : base(graph)
    {
    }
 
    public PXCustomFilteredProcessing(PXGraph graph, Delegate handler) : base(graph, handler)
    {
 
    }
 
    // hook processing delegate and inject error reporting logic
 
    protected override void _SetProcessTargetInternal(string graphType, string stepID, string action, string menu, IEnumerable parameters)
    {
        base._SetProcessTargetInternal(graphType, stepID, action, menu, parameters);
 
        _BaseProcessDelegate = _ProcessDelegate;
        _ProcessDelegate = delegate (List<Table> list)
        {
            _BaseProcessDelegate(list);
 
            PXProcessingInfo info = PXLongOperation.GetCustomInfoForCurrentThread("PXProcessingState"as PXProcessingInfo;
            if (info != null)
            {
                PXProcessingMessagesCollection messages = info.Messages;
                for (var i = 0; i < messages.Length; i++)
                {
                    PXProcessingMessage rowInfo = (PXProcessingMessage)messages.Get<SOShipment>(i);
                    if (rowInfo != null && rowInfo.ErrorLevel == PXErrorLevel.RowError)
                        throw new PXOperationCompletedWithErrorException(ErrorMessages.SeveralItemsFailed);
                }
            }
        };
    }
}

Point 2 has much smaller amount of code:

public class SOInvoiceShipmentExt : PXGraphExtension<SOInvoiceShipment>
{
    [PXFilterable]
    public PXCustomFilteredProcessing<SOShipmentSOShipmentFilter> Orders;
}

After all of those additions, I've got the following result:

As you can see, now errors at row level affect total outcome.