New Sql Server 2016 Features To Use In Acumatica

 

Hello everybody,

today I want to write about new additions for SQL Server T-SQL language, which are there starting from 2016. It is create or alter syntax.

In the past, if you've made some custom SQL view, it was needed to have constructions like this ( pseudo code ):

if view ( stored procedure, function, trigger ) exists

     create view ( stored procedure, function, trigger )

else

   alter view ( stored procedure, function, trigger )

But startring from 2016 SP1 you can use following syntax ( pseudo code ):

Create or alter view ( stored procedure, function, trigger ).

Or in code form it may look like this:

create or alter procedure yourProcedure
as
begin
 print (1)
end;
go
create or alter function yourFunction()
returns int
as
begin
 return(1)
end;
go
create or alter view yourSqlView
as
 select 1 as col;
go
create or alter trigger yourTrigger
on Product 
after insert, update 

Summary

With those SQL features your code of customizations may become much simpler and less prone to errors 

LastModifiedDateTime Has Wrong Datetime Information

 

Hello everybody,

today I want to share interesting use case which raised recently when dealt with syncrhonization of records between Magento and Acumatica.

I had following declaration:

#region LastModifiedDateTime
[PXDBDateAndTime()]
[PXUIField(DisplayName = "Last Modified Date Time")]
public virtual DateTime? LastModifiedDateTime { getset; }
public abstract class lastModifiedDateTime : PX.Data.BQL.BqlDateTime.Field<lastModifiedDateTime> { }
#endregion

and for my disappointment as well as disappointment of Magento developers we had to add some hours shift to each call, in order to filter out properly by Last modified Date time.

One of the solutions was to add one more flag to attributes in LastModifiedDateTime: UseTimeZone. By default that attribute is set to true, but in my case it was necessary to set it to false:

#region LastModifiedDateTime
[PXDBDateAndTime(UseTimeZone = false)]
[PXUIField(DisplayName = "Last Modified Date Time")]
public virtual DateTime? LastModifiedDateTime { getset; }
public abstract class lastModifiedDateTime : PX.Data.BQL.BqlDateTime.Field<lastModifiedDateTime> { }
#endregion

After I've made usage of that attribute, values in UI started to display according to what I've seen on db level.

Summary

In case if you see discrepancy between value on db, and UI, and want to have only db level displayed in UI, use flat UseTimeZone set to false.

 

SelectFrom For Usage In View Select

 

Hello everybody,

I want to leave a short note on how to use SelectFrom for legacy code, and pass it in View.Select for Acumatica newer versions. 

var cmd = new SelectFrom<PartsCatalog>.
    InnerJoin<INSiteStatus>.On<Use<PartsCatalog.inventoryID>.AsInt.IsEqual<Use<INSiteStatus.inventoryID>.AsInt>>.View(this);
 
var s = (currentFilter.PageNbr ?? 0) * (currentFilter.PageSize ?? 0);
int startRow = s > 0 ? s : PXView.StartRow;
int totalRows = 0;
int maxRows = (currentFilter.PageSize ?? 0) == 0 ? PXView.MaximumRows : currentFilter.PageSize ?? 0;
 
var list = cmd.View.Select(new[] { currentFilter }, nullPXView.Searches,
    PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRowmaxRowsref totalRows).ToList();

As you can see, each time, when field is not converted to a newer Acumatica FBQL, you can refer to Use and AsType.

How To Overrde Authorize Cc Payment Action In Acumatica

 

Hello everybody,

as it was mentioned in one of my previous blog posts, regarding overriding base actions of Acumatica graphs, family of graph extensions grows and grows. 

 

Today I want to share one more code snippet which you can use for extending modification of Acumatica actions. Below goes code fragment, which you can use for modification of Authorize CC Payment:

public class PaymentTransactionExt : PXGraphExtension<PaymentTransactionSOOrderEntry>
{
    [PXOverride]
    public virtual IEnumerable AuthorizeCCPayment(PXAdapter adapterFunc<PXAdapterIEnumerablebaseFunc)
    {
        
        var result = baseFunc(adapter);
        
        return result;
    }
}

Of what I foresee now, is a loooooot of work for Acumatica developers upgrading to a newer versions of Acumatica. 

Why I say so? 

Because as usually ISV have their code in SOOrderEntry extensions. But now they either will need to move their code from SOOrderEntry extensions or for example to reference Base1 ( which is your extension ) as well as Base.

Reason why Acumatica does this is probably following few general programming principles of: SRP ( single responsibility principle ), DRY ( Dont Repeat Yourself ) principle, Open Closed principle on graph level, which in long term will bring more stability to the system.

Basic advice will be this, don't expect that upgrade of your code base to 2019 R2 will be as fast, as it used to be in the past, and before giving estimate which you used to give, pay special attention to how new actions are declared.

PXLongOperation Cleans Everything How To Avoid

 

Hello everybody,

today I want to write a few words about interesting behavior of PXLongOperation.StartOperation in Acumatica.

On the glance that looks pretty simple and straightforward. You have something processing of which will take a long time? Use PXLongOperation.StartOperation. So imagine, you extend ARPaymentEntry graph like this:

public PXAction<ARPayment> StartOperation;
[PXProcessButton]
[PXUIField(DisplayName = "Some long running operation")]
protected virtual IEnumerable startOperation(PXAdapter adapter)
{
   
   PXLongOperation.StartOperation(Base, () => SomeLongRunningOperation(Base.Document.Current));
return adapter.Get(); }

In this case you may notice interesting behavior. If your ARPayment is saved, then everything will work relatively fine. But if not, any kind of data, that user enetered will be lost. How to deal with it. Below goes implementation that will deal with this:

public PXAction<ARPayment> StartOperation;
 
[PXProcessButton]
[PXUIField(DisplayName = "Some long running operation")]
protected virtual IEnumerable startOperation(PXAdapter adapter)
{
    Base.Save.Press();
    PXCache cache = Base.Document.Cache;
    List<ARRegisterlist = new List<ARRegister>();
    list.Add(Base.Document.Current);
    PXLongOperation.StartOperation(Base, () => SomeLongRunningOperation(Base.Document.Current));
    return list;
}

Besides that, in the end of your function SomeLongRunningOperation you may consider adding code like this:

ARPayment arPayment = cuArPayment;
var newGraph = PXGraph.CreateInstance<ARPaymentEntry>();
newGraph.Document.Current =
    Base.Document.Search<ARPayment.refNbr>(arPayment.RefNbr, arPayment.DocType);
 
//  Some additional lines of code
newGraph.Persist();
PXRedirectHelper.TryRedirect(newGraphPXRedirectHelper.WindowMode.Same);

In that case after SomeLongRunningOperation function will finish it's processing, it will refresh current screen of user.

Summary

If you deal with PXLongRunningOperation, you'll need to have following elements:

  1. Save existing item
  2. Return list with one element ( current primary element of view ), which will leave user on the same element
  3. In the end of PXLongRunningOperation call TryRedirect which will refresh currently selected view

 

 

How To Make Dynamic List With Check Boxes In Acumatica

 

Hello everybody,

today I want to share with you a piece of code, with help of which you can get list of all activer order types in Acumatica and also you'll get a chance to select those types with checkbox. Take a look on a screenshot below:

how to achieve this?

Code below without description of DAC class gives you this:

protected virtual void _(Events.FieldSelecting<SetupClassSetupClass.orderTypese)
{
    if (e.Row == null)
    {
        return;
    }
 
    var orderTypes = SelectFrom<SOOrderType>.Where<SOOrderType.active.IsEqual<True>>.View.Select(this).Select(a => a.GetItem<SOOrderType>())
        .ToList();
    var allowedValues = orderTypes.Select(a => a.OrderType).ToArray();
    var allowedLabels = orderTypes.Select(a => a.Descr).ToArray();
 
    var returnState = PXStringState.CreateInstance(e.ReturnValue, allowedLabels.Length, true,
        typeof(SetupClass.orderTypes).Name,
        false, -1, string.Empty, allowedValuesallowedLabelsfalsenull);
    (returnState as PXStringState).MultiSelect = true;
    e.ReturnState = returnState;
}

On aspx level, it look trivial:

<px:PXDropDown ID="PXDropDown1" runat="server" DataField="OrderTypes" CommitChanges="true" />

With help of this code, you'll get list of all order types, which you'll be able to persist in database.

 

How To Insert Varbinary Data In Ms Sql For Acumatica

 

Hello everybody,

sometime it is needed to insert some binary information in one or another table inside of Acumatica. Quite often developers just modify existing record in table UploadFile or UploadFileRevision.

But I don't like such approach, as it is prone to errors and potentially can harm some of your existing data. That's why I propose to use cast operator of MS SQL. Take a look at following example:

insert into UploadFileRevision(CompanyID, FileID, FileRevisionID, Data, Size, CreatedByID, CreatedDateTime, CompanyMask) values
								(2, '35b15ad7-b5c3-4a19-aa77-3a24c046d689', 1, 
													CAST('wahid' AS VARBINARY(MAX)),
													4, 
													'B5344897-037E-4D58-B5C3-1BDFD0F47BF9',
													'2016-06-08 08:53:50.937',
																	0xAA)

 

Take notice of 

CAST('wahid' AS VARBINARY(MAX)),

line. It will insert into local dev instance database varbinary representation of wahid. 

 

Pagination In Custom Inquiry

 

Hello everybody,

today I want to share with everybody, and with myself code fragment, that allows to achieve pagination in custom inquiry pages. 

Take a look on the code below:

  public class YourGraphInquiry : PXGraph<YourGraphInquiry>
  {
    public PXCancel<YourDACFilter> Cancel;
        public PXFilter<YourDACFilter> Filter;
 
        [PXFilterable]
        public PXSelect<YourDACDetails,
            Where2<
                Where<YourDACDetails.SomeidEqual<Current<YourDACFilter.SomeID>>, 
                    Or<Current<YourDACFilter.SomeID>, IsNull>>,
                And<
                    Where<YourDACDetails.SomepartidEqual<Current<YourDACFilter.SomePartId>>, 
                        Or<Current<YourDACFilter.SomePartId>, IsNull>>>>> Details;
 
        public virtual IEnumerable details()
        {
            PXView cmd = new PXView(thistrue, Details.View.BqlSelect);
            var currentFilter = Filter.Current;
 
            var s = (currentFilter.PageNbr ?? 0) * (currentFilter.PageSize ?? 0);
            int startRow = s > 0 ? s : PXView.StartRow;
            int totalRows = 0;
            int maxRows = (currentFilter.PageSize ?? 0) == 0 ? PXView.MaximumRows : currentFilter.PageSize ?? 0;
            foreach (var result in cmd.Select(new[] { currentFilter }, nullPXView.Searches,
                PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRowmaxRowsref totalRows))
            {
                yield return result;
            }
            startRow = 0;
        }
        public YourGraphInquiry ()
        {
            Details.Cache.AllowInsert = false;
            Details.Cache.AllowDelete = false;
            Details.Cache.AllowUpdate = false;
        }
 
       
  }
    
    [System.SerializableAttribute()]
    public class YourDACFilter : PX.Data.IBqlTable
    {
 
        #region SomeID
        public abstract class SomeID : BqlInt.Field<SomeID>
        {
        }
 
        protected int? _SomeID;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSome.SomeID>),   new Type[]{ typeof(MVP.CWSome.SomeCD), typeof(MVP.CWSome.description), typeof(MVP.CWSome.SomeCD) }, SubstituteKey = typeof(MVP.CWSome.SomeCD))]
        public virtual int? SomeID
        {
            get { return this._SomeID; }
            set { this._SomeID = value; }
        }
 
        #endregion SomeID
 
        #region SomePartId
 
        public abstract class SomePartId : BqlInt.Field<SomePartId>
        {
        }
 
        protected int? _SomePartId;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some Part", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSomePart.SomePartIdWhere<CWSomePart.SomeIDEqual<Current<YourDACFilter.SomeID>>>>), 
                    new Type[] { typeof(CWSomePart.SomePartCD), typeof(CWSomePart.SomePartFullCD) }, SubstituteKey = typeof(CWSomePart.SomePartCD))]
        public virtual int? SomePartId
        {
            get { return this._SomePartId; }
            set { this._SomePartId = value; }
        }
        #endregion SomePartId
 
        #region Page number
        public abstract class pageNbr : BqlInt.Field<pageNbr> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Number")]
        [PXDefault(0)]
        public virtual int? PageNbr { getset; }
 
        #endregion Page number
 
        #region page size
 
        public abstract class pageSize : BqlInt.Field<pageSize> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Size")]
        [PXDefault(3)]
        public virtual int? PageSize { getset; }
 
        #endregion page size
    }
}

 

Presented code has few important features:

  1. With this code you can achieve fragmentation of reading of your data. 
  2. In order to avoid returning everything, used structure of yield return. It's efficient because allows to return not all array, but elements of array one by one
  3. In the end of details method, you can see startRow = 0. If you wonder why, reason is simple, after all elements in db will be executed, that line of code will reset reading of pages to the begining. 
  4. cmd.Select allows you to read chunk of data from db, in a very similar manner to SelectWindowed. If you check generated sql, you'll see staff like this:

SELECT TOP (3) [YourDACDetails].[Column1], [YourDACDetails].[Column2], .....

5. But for second and other pages, generated sql will not be very efficient, but on UI Side you'll not notice it. For example, when I've set Page number = 3, and page size = 5, I've got following sql:

exec sp_executesql N'SELECT TOP (20) [YourDACDetails].[Column1], [YourDACDetails].[Column2], ..... which leads to a conclusion, that Acumatica framework on later stage just throws out redundant values.

Summary

After all of those details description you may wonder, what can be the case, when you may need pagination for custom inquiry. And the main scenario, where such pagination may be useful is web services synchronization. If you want to achieve pagination for some of your data, then simplest way would be usage of mentioned pattern.

 

Database Of Acumatica Is In Recovery Pending Condition

 

Hello everybody,

recently I've got interesting situation, when my database for Acumatica developed turned to be in Pending condtion. In order to deal with it, I've executed following SQL:

 

ALTER DATABASE YourDatabase SET EMERGENCY;
GO
ALTER DATABASE YourDatabase set single_user
GO
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE YourDatabase set multi_user
GO

 

and my database turned back to normal.

Update on 10/08/2019

declare @dbName nvarchar(50);
set @dbName = 'yourDatabase';
 
exec( 'ALTER DATABASE' +@dbName  + ' SET EMERGENCY;')
 
exec ('ALTER DATABASE ' + @dbName + '  set single_user')
 
exec ('DBCC CHECKDB (' + @dbName + ' , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;')
 
exec ('ALTER DATABASE ' + @dbName +' set multi_user');

I've modified script to a bit another

 

Three States Of Fields In Acumatica

 

Hello everybody,

today I want to write a short note on three states of fields existing in Acumatica:

  1. Exists but is empty
  2. Exist and have value
  3. Has null

If to speak about string, it can exist like this:

  1.  
  2. Some value
  3.  

Do you see difference between 1 and 3? Not easy. As usually developers of C#, show this difference like this:

  1. ""
  2. "Some Value"
  3. null

And following screenshot with explanation shows how it works in case of SOAP contracts:

 so, while you make integration, keep that in mind