How To Clean Duplicated Elements From Acumatica Database

 

Hello everybody,

Today I want to leave a short snapshot which addresses following issue. 

One of my friends got table in Acumatica created, but for some reason at DB level he decided not to set there any field as part of Primary key. He had IsKey attributes set only at DAC class of Acumatica, but not at Database level. As outcome he got duplicated items at database level, and it was needed somehow to delete duplicated items. How to achieve it? 

After a bit of research, I've come with T-SQL code like this:

DELETE  Record
FROM    (
			SELECT  rowno = ROW_NUMBER() 
				OVER (
				PARTITION BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID
				ORDER BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID )
			FROM  ACGLiquidFamilyPrice
        
        ) AS Record
WHERE   Record.rowno > 1

Columns LiquidFamilyCD, PriceEntryDateTime, CompanyID - are key fields.

ROW_NUMBER in connection with Partition by allows to delete only records, which are more then one. 

 

 

Covid 19 Dealing

 

Hi everybody,

It happened. I've got COVID-19 myself. As well as my wife. Very unpleasant feelings honestly speaking. 

On the day 1 of symptoms I've got those:

a. Temperature ( ~38 )

b. Headache

c. Weakness

Now I have one more concern about my parents. I'm worried if they got or not got COVID-19. And in case if yes, I'll have one more reason to worry.

Update on 2020-07-16

As of now by being in hospital I've got some improvements in my health. Temperature become lower. But now  I got another symptom: caught with blood. So my advice for anyone who says that COVID-19 is fake is this: it is not fake, but very real disease. Don't look on it lightly.

Update on 2020-07-24

I was sent to continue dealing with Covid-19 to home. Luckily for me, treatment went fine, and I'll continue on way of recovering. 

Update on 2020-08-11

I got two negative PCR tests on Covid-19. Besides that I have also regained my working memory back. During Covid-19 my working memory was affected. In the middle of the sentence I've used to forget what I supposed to say. But for now I don't have this issue which makes me very happy as of now.

 

How To Use Const In Fbql For Acumaitca

 

Hello everybody,

I want to leave a quick hint on how to use Const values in Acumatica for FBQL. Below goes sample:

public class someBranch : PX.Data.BQL.BqlInt.Constant<someBranch>
{
    public someBranch() : base(48)
    {
    }
}

Then later on you can use it in your BQL and FBQL queries for filtering

 

How To Use PXLongoperation

 

Today I want to write a few words on usage of PXLongOperation. 

Compare two following scenarios:

Base.Save.Press();
            try
            {
                PXLongOperation.StartOperation(Base,  ()=>
                {
                    //Some other code
                    Base.Save.Press();

 

with this:

Base.Save.Press();
 
var doc = Base.Document.Current;
var orderType = doc.OrderType;
var orderNbr = doc.OrderNbr;
 
try
{
    PXLongOperation.StartOperation(Base,  ()=>
    {
        var grp = PXGraph.CreateInstance<SOOrderEntry>();
        grp.Document.Current = grp.Document.Search<SOOrder.orderNbr>(orderNbr, orderType);
 
        grp.Save.Press();
    });

and tell me what will be the difference in execution of those two types of code?

I spent pretty big amount of time wondering why in Acumatica source code I often seen scenario #2. Reason why I was puzzled is that I don't like to create instance of something, if I can use some variable that exists already. 

And finally I've discovered reason on why scenario #2 is preferable. After our team spent some time on digging on the following use case scenario. We've used scenario #1 and QA gave us very interesting bug: some buttons on UI level got disabled after execution of #1 scenario. The only way to enable them in scenario #1 was just to call refresh of the page:

throw new PXRedirectRequiredException(Base, false"Sales Orders");

which is not the worst in life of end user, but definetly not the most convenient. How to avoid total refresh of the screen? Use scenario #2. 

Another important aspec of scenario #2 is usage of variables. Take note, that inside of PXLongOperation I don't use Base.Document.Current.OrderType. Instead I use local variables doc, orderType and orderNbr which is then used at async thread. 

Summary

Starting from today I plan to use #2 whenever I will deal with multithreading scenarions. Otherwise some UI problems will become some kind of guarantee. 

Why Code Changes From Dll Are Not Shown On The Form In Acumatica

 

today I want to share with you interesting use case which stolen one night of sleep from me, as well as from one of my collegues. 

We had very trivial case. Or at least we thought it is trivial. In Acumatica we had something like this:

#region UsrCardTheme
[PXDBString(256)]
[PXUIField(DisplayName="Card Theme")]
 
public virtual string UsrCardTheme { getset; }
public abstract class usrCardTheme : PX.Data.BQL.BqlString.Field<usrCardTheme> { }
#endregion

For some reason we weren't able to update it in database, so we've made changes to it like this in order to check if we will see it in UI:

#region UsrCardTheme
[PXDBString(256)]
[PXUIField(DisplayName="Card Theme2")]
 
public virtual string UsrCardTheme { getset; }
public abstract class usrCardTheme : PX.Data.BQL.BqlString.Field<usrCardTheme> { }
#endregion

went to the page, open it, and .... label of column remained the same. We've did our best in order to find what is the problem, what's wrong with the syntax, removed completely, restarted IIS, thrown away garbage, nothing worked. 

Until we've found stackoverflow advice to take a look at folder App_RuntimeCode:

and only after we've cleared up folder App_RuntimeCode we've noticed dream of all night, 2 in the end!

How To Get User Friendly Error Message Out Of Webexception

 

Hello everybody,

today I want to leave a short notice on how to get user friendly text of WebException. Imagine that you need to know which part of data in your json is missing. How can you quickly figure out which part? For one of my projects I wanted to get easy way of saying user which field is potentially missing. In order to achieve that I've used following fragment:

catch (WebException ex)
{
  string errorMessage = string.Empty;
  if (ex.Response != null)
  {
      using (var errorResponse = (HttpWebResponseex.Response)
      {
          using (var reader = new StreamReader(errorResponse.GetResponseStream()))
          {
              errorMessage = reader.ReadToEnd();
          }
      }
  }

After that I've just used to throw exception with adding additional details over Data property of Exception class.

 

How To Call Non Public Method Of Acumatica

 

Hello everybody,

today I want to share with you how it's possible to call some methods of Acumatica, which are not public, and which you don't want to copy/paste completely into your source code. In that case reflection will save you. Consider calling of InsertSOAdjustments method of graph SOOrderEntry below.

MethodInfo invokeSOAdjustment = typeof(SOOrderEntry).GetMethod(
    "InsertSOAdjustments"BindingFlags.Instance | BindingFlags.NonPublic, Type.DefaultBinder,
    new[] { typeof(SOOrder), typeof(ARPaymentEntry), typeof(ARPayment) }, null);
 
invokeSOAdjustment.Invoke(Base, new object[] { orderdocgraphpayment });

Also I want to give you a word of warning, that such approach potentially will not be certified, and another way of usage will be the one below:

In extension of SOOrderEntry create lines like those:

[PXOverride]
public void InsertSOAdjustments(SOOrder orderARPaymentEntry docgraphARPayment payment,
    Action<SOOrderARPaymentEntryARPaymentbaseAction)
{
    baseAction(orderdocgraphpayment);
}

and then just call InsertSOAdjustments method whenever you'll have a need for this.

Summary

Because Acumatica is written with C# which is very powerful language which gives you a lot of features you can easily achieve a lot of thigs, also be careful with usage of reflection. Somtime even more then Acumatica team anticipated themselves. 

How To Avoid Navigation Away From Created Item After Calling Presssave Or Persist Action

 

Hello everybody,

today I want to tell you a story, that swallowed quite big amount of time of whole team.

Recently we've got seemingly easy to fulfil requirement: 

  1. Add button to the grid
  2. Inside of the button fullfil 
    1. Persist
    2. Call to db with modifications
    3. Persist one more time
  3. Leave the page opened on created item in UI

Initially our code looked like this:

public PXAction<SOOrder> SomeAction;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Some Action", Visible = true)]
protected virtual IEnumerable someAction(PXAdapter adapter)
{
    Base.Actions.PressSave();
    //API call
    Base.Actions.PressSave();
    return adapter.Get();
}

I could say that everything worked perfectly except one tiny detail: after clicking of the button page was navigated away to creation of new Sales order. After plenty of research inside of Acumatica source code we have found this option:

public PXAction<SOOrder> SomeAction;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Some Action", Visible = true)]
protected virtual IEnumerable someAction(PXAdapter adapter)
{
    Base.Actions.PressSave();
    List<SOOrderresult = new List<SOOrder>();
    //API Call
    result.Add(Base.CurrentDocument.Current);
    return result;
}

Another way of dealing with this bug is this:

public PXAction<SOOrder> SomeAction1;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Some Action", Visible = true)]
protected virtual IEnumerable btnCreatingNew(PXAdapter adapter)
{
    Base.Actions.PressSave();
    //some other code
    adapter.Searches[adapter.Searches.Length - 1] = Base.CurrentDocument.Current.RefNbr;
    return adapter.Get();
}

Summary

Reason of such behavior is fact that Acumatica uses value returned from action in order to know which order to open. Then happens this:

  1. on the first line of the code your adapter has information that Acumatica should go to <New> sales order
  2. Base.Actions.PressSave() generates sales order and persists it to Db, but doesn't notify adapter about this fact
  3. When adapter.Get is executed, Acumatica reads from it order which should be opened, finds there <New> and navigates away from created SO

In order to deal with you can choose option 1, or option 2. Option 1 I've discovered in Acumatica source code, and option 2 is mentioned at stackoverflow by Ruslan

 

Template For Usage Of PXLineNbr Attribute In Acumatica

 

Hello everybody,

today I want to leave a note on how to use PXLineNbr attribute in Acumatica pages. Recently myself and one of my team members struggled a bit with a question on how to add it properly. Below goes a bit changed worked tempalte:

public class PrimaryTable : IBqlTable
{
    #region LineId
    public abstract class lineId : PX.Data.BQL.BqlInt.Field<lineId> { }
 
    [PXDBInt]
    [PXDefault(0)]
    public virtual int? LineId { getset; }
    #endregion
 
    #region LineCntr
    public abstract class lineCntr : PX.Data.BQL.BqlInt.Field<lineCntr> { }
 
    [PXDBInt]
    [PXDefault(0)]
    public virtual int? LineCntr { getset; }
    #endregion
}
 
public class DetailsTable : IBqlTable
{
    #region LineNbr
    public abstract class lineNbr : IBqlField { }
 
    [PXDBInt(IsKey = true)]
    [PXDefault]
    [PXLineNbr(typeof(PrimaryTable.lineCntr))]
    [PXParent(typeof(SelectFrom<PrimaryTable>.Where<PrimaryTable.lineId.IsEqual<PrimaryTable.lineId
        .FromCurrent>>))]
    public virtual int? LineNbr { getset; }
    #endregion
}

Summary

As you see, nothing special in this code. Add PXParent, PXLineNbr and enjoy with line counter feature.

 

How To Override Buildtaxrequest Method Of Apinvoiceentryexternaltax Class

 

Hello everybody,

today I want to leave a short techy post on how to override method BuildTaxRequest of APInvoiceEntryExternalTax class.

public class APInvoiceEntryExternalTaxExtPXGraphExtension<APInvoiceEntryExternalTaxAPInvoiceEntry>
{
    [PXOverride]
    protected virtual GetTaxRequest BuildTaxRequest(APInvoice invoiceFunc<APInvoiceGetTaxRequestbaseAction)
    {
        var result = baseAction(invoice);
        //Your code
 
        return result;
    }
}

Reason for such a behavior is a fact that class APInvoiceEntryExternalTax is graph extension for graph. That's why such a construction is needed.