Friday, November 30, 2018

Acumatica Partner Meeting 2018.

Dear Friends,

Thank you all for participating at our Acumatica Singapore Partner meeting.

What was discussed:

1. Acumatica Branding in Asia Pacific

2. Licensing Changes in version 2018 R2

3. Enterprise and Medium size customer challenges and demands

4. Ways to grow in 2019

5. Enhanced Implementation techniques

Short quiz for the readers. Try to guess who is on the picture :)




All the best,
Sergey.

Wednesday, November 14, 2018

Adding Links to Notification Emails

Hi Everyone,

There is sometimes a task to add dynamic or static links to system generated Notifications.
Here is how to do it.

1. Add a field to a screen, where your dynamic link will be generated and stored.

URL Field
Adding URL Filed


2. Create a notification Template adding there a link with the content from the field above:

Notification Screen
Referencing a link in the Notification

As a result your notification will get the Link from the field.

Notification with the link
Notification Template with the Link added


All the best,
Sergey.

Tuesday, October 30, 2018

Enabling and Monitoring TLS on Acumatica

Hi Guys,

There is a lot of hoo-ha recently about switching to TLS 1.2....

Well, mainly, the sound comes from Acumatica marketing team and also from some of those  "security advisers", reviewing for us what is secure and what is not.

So, here is the proposed way to enable TLS 1.1 and/or TLS 1.2 on your IIS server.

And also proposing a way to make sure (or monitor) that your dear clients are connecting their browsers using secure protocols.

What we need to do first is to disable insecure protocols and enable secure ones.

Adding TLS 1.1 and/or TLS 1.2 to your server

If you wish to add just TLS 1.2 please download a file and add registry information to your IIS SERVER, here is the example of the file from Rahul Kumar's blog:

TLS 1.2 ONLY

You can also manually create reg file with this content. Please Note that this will also disable some less secure protocols like SSL 2.0, SSL 3.0, TLS 1.0 and TLS 1.1:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

If you wish to leave TLS 1.1 enabled, please use another script instead:

TLS 1.1 and 1.2

Content would be like below, if you prefer to create .reg files manually:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

After registry is updated please restart your server.

There could be an issue with FIPS enabled in your system, if .NET throws an error that:

"This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms."

All you need to do is to disable FIPS under Local Security Policies.

Disabling FIPS under Local Security Policies

1. Open Control Panel, click Administrative Tools, and then double-click Local Security Policy.
2. Under Local Security Settings, expand Local Policies, and then click Security Options.
3. Under Policy in the right pane, double-click System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing, and then click Disabled.
4. Run gpupdate /force

Monitoring the client communications protocol

This is the best part actually. After you enabled TLS 1.2 it is time to show the auditors that you are compliant 100% with what is called security. Today. So we may have IE, FireFox and Chrome. 

Check the security protocol used on Internet Explorer

First you need to connect to your Acumatica using IE. I am demonstrating customer who is using Acumatica version 3.0. And, yes, 3.0 also supports TLS 1.2. Please note it should be HTTPS protocol of course:

https protocol acumatica tls 1.2

Secondly, right click on the header of the screen (do not right click on grid!) and you will see a choice drop down. Click Properties:

TLS Version


Third, check the Connection. 

Connection Properties


Check the security protocol used on FireFox

Login, Click on (i) option, list to second page, enjoy

TLS Firefox


Click on this arrow > and you will see option then click More Information

More Information

Then you will see detailed info

Firefox TLS


Check Security protocols used on Google Chrome

Here you need to press F12 and then choose Security option:

TLS Options



 All the best,

Sergey.

Friday, September 14, 2018

Retrieving Large Volume of Data via WEB Service in Acumatica

Hi Everyone,

Promised to write few posts about Web Services...

Getting large volume of data via Generic Inquiry Endpoint


Prerequisite - no knowledge about Acumatica platform whatsoever :), common sense and C# as a newbee.

First of all I would create a Generic Inquiry in Acumatica to get our data ready.
Please note that in order to get data without timeout errors, it must be arranged in batches.

Due to the fact that Acumatica does not handle Rowcount in GI's well enough, and we can't relay on it, so we would need to arrange our data in a table properly BY OURSELVES.

I tried few scenarios and it looks like getting data in batches of 1000 lines works the best way.
WEB service does not die out and process is not getting interrupted.

So, step by step now.

1. Please create a GI (Generic Inquiry) first. 

Silly example below:

Generic Inquiry in Acumatica

Please take note, I have added two special columns, Processing Batch and Maximum Batch Number.
This is to organize WEB service better, later you will see why do we need these two.
In short - it will minimize server calls and facilitate uninterrupted data flow.

2. Look at the design screen for this GI:


a) Data in the GI must be sorted by Processing Batch. In order to appear in the orderly manner for export. This is important!

b) Put some conditions in the GI so you have only that number of rows that you need. In my case getting 112 batches 1000 rows each, looks scary already... 112k records...

c) create underlying View or Table in the database to reflect Processing Batch and Maximum Batch numbers correctly. Correctly means have a Primary Key, don't make table too wide to facilitate speed of data extraction.

d) Convert that table into a Data Access Class (DAC) to then later use in the GI as a data source (hint - use my older blog to find how to convert view/table to DAC)

e) Format GI nicely so when you look at it, you experience proud and pleasure. :)

Regarding those special fields:

Processing Batch - is a number (int), representing a chunk of lines, by 1000 lines each.

Maximum Batch - is the last Processing Batch number (int).

Note: "Maximum Batch" number is the same for all rows, while "Processing Batch" number is changing for every thousand of rows.

In order to create such numbering in a table I used a process on SQL, in that update, LineNbr is just a Primary Key, you can use any other PK actually:

update MYTABLE set BatchNbr = s.BatchNbr, 
[MaxBatchNbr] = (select FLOOR(count(*)/1000)+1 from MYTABLE) 
from MYTABLE p 
inner join (select BatchNbr = FLOOR(ROW_NUMBER() OVER(ORDER BY LineNbr ASC)/1000)+1,LineNbr,CompanyID from MYTABLE) s 
on p.CompanyID = s.CompanyID and p.LineNbr = s.LineNbr

Again, you will see later, why we need these numbers to be IN an SQL TABLE itself.
Just accept it for now, ok.

3. Give that GI Access rights in Acumatica.

So the user we use for the web service, will be able to see it. Place it somewhere on your Site Map. Important, I call our GI CRMPAY

4. Create the Endpoint. 

Insert CRMPAY there. Add fields. Please note our special fields are there too:

Creating an Endpoint in Acumatica


5. Click VIEW ENDPOINT SERVICE button to get a link to the web service. 

Just copy the link to somewhere to keep, from the address bar, it will be something like: https://yourserver/yourendpointname/endpointversion?wdsl

6. Open your Visual Studio and create a new template

 Project type C# Console, give it a name, I called it BLOG.

Creating Visual Studio Project


7. In order to connect to your web service via https we will need to add some code to the project. 

Open Program.cs in your project and add this block to it:

Before:

Adding Project


After. What was added is in BLUE:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;

namespace BLOG
{
    class Program
    {
        static void Main(string[] args)
        {

            //This code is necessary only if you connect to the website
            //through the HTTPS connection and
            //you need to use custom validation of an SSL certificate
            //(for example, if the website uses a self-signed certificate).
            ServicePointManager.ServerCertificateValidationCallback += new
            RemoteCertificateValidationCallback(ValidateRemoteCertificate);
            
            using (DefaultSoapClient soapClient = new DefaultSoapClient())
            {
                //Log in to Acumatica ERP
                soapClient.Login
                (
                Properties.Settings.Default.UserName,
                Properties.Settings.Default.Password,
                Properties.Settings.Default.CompanyName,
                Properties.Settings.Default.Branch,
                null
                );
                try
                {
                    //You will add the integration code here
                    CRMDataRetriever.ExportCRMPayment(soapClient);
                }
                catch (Exception e)
                {
                 
                    Console.WriteLine(e);
                    Console.WriteLine();
                    Console.WriteLine("Press any key to continue");
                    Console.ReadLine();
                }
                finally
                {
                    //Log out from Acumatica ERP
                    soapClient.Logout();
                }
            }
        }

        //Callback, which is used to validate the certificate of
        //an Acumatica ERP website in an SSL conversation
        private static bool ValidateRemoteCertificate(object sender,
        X509Certificate cert, X509Chain chain, SslPolicyErrors policyErrors)
        {
            //For simplicity, this callback always returns true.
            //In a real integration application, you must check
            //an SSL certificate here.
            return true;
        }

        }
    }
}

8. You have noticed, Visual Studio prompting that some parts of the code above are not in yet:

 It is time to add web service to the Project. Click Add Service Reference:




Then indicate the link from Item 5. And give it a name. Click OK:


You may be prompted for a certificate mismatch, just accept it.

9. Add a namespace to the top portion of the Program block and you will immediately notice some errors are gone:


10. Do 1,2,3,4 as displayed. Just right click at your project then do 1,2,3,4 actually:


11. Now we should provide some security references here to get logged into Acumatica. So just add four items exactly as below, then save project:



12. And, we supposed to get left with the only Error. Will be fixing that one then:


13. Lets create that class CRMDataRetriever, where we are going to add the code:

a) Add the class:



b) Give it a name and Add:


c) Add into the top section of the class these two namespaces:

using BLOG.BLOGSERVICE;
using System.IO;

d) Insert code into the class. In my case code will retrieve the first Batch, then one by one subsequent:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BLOG.BLOGSERVICE;
using System.IO;

namespace BLOG
{
    class CRMDataRetriever
    {

        public static int? processBatchNbr = 0;

        //Retrieving the list of CRM Payment Items
        public static void ExportCRMPayment(DefaultSoapClient soapClient)
        {
            Console.WriteLine("Retrieving the list of Students...");
            
            var crmPaymentsToBeFound = soapClient.GetList(
                new CRMPAY

                {
                    //Specify return behavior - we are getting ALL the fields
                    ReturnBehavior = ReturnBehavior.All,
                    //Filter the items by the BatchNumber
                    BatchNbr = new IntSearch
                    {
                        Condition = IntCondition.Equal,
                        Value = 1
                    }
                });

            //I am writing it into a csv file
            using (StreamWriter file = new StreamWriter("CRMPayments.csv"))
            {

                //Add headers to the file
                file.WriteLine(
                "BatchNbr,LineNbr,StudentCRMID,Term,Year,CourseID,PaymentStatus,");

                //This is maximum batch to process and writing batch one
                foreach (CRMPAY crmPaymentsToBeFoun in crmPaymentsToBeFound)
                {
                    processBatchNbr = crmPaymentsToBeFoun.MaxBatchNbr.Value;


                    Console.WriteLine("Retrieving the First Record ...");

                    //Write the values for each item
                    file.WriteLine(string.Format("{0},{1},{2},{3},{4},{5},{6},",
                    crmPaymentsToBeFoun.BatchNbr.Value,
                    crmPaymentsToBeFoun.LineNbr.Value,
                    crmPaymentsToBeFoun.StudentCRMID.Value,
                    crmPaymentsToBeFoun.Term.Value,
                    crmPaymentsToBeFoun.Year.Value,
                    crmPaymentsToBeFoun.CourseID.Value,
                    crmPaymentsToBeFoun.PaymentStatus.Value));
                }

                while (processBatchNbr != 1)
                {
                    CRMPAY filter = new CRMPAY
                    {
                        BatchNbr = new IntSearch
                        {
                            Condition = IntCondition.Equal,
                            Value = processBatchNbr
                        },
                        ReturnBehavior = ReturnBehavior.All
                    };
                    Entity[] crmPayments = soapClient.GetList(filter);

                    processBatchNbr = processBatchNbr - 1;


                    //Write the values for each item
                    foreach (CRMPAY crmPayment in crmPayments)
                    {

                        file.WriteLine(string.Format("{0},{1},{2},{3},{4},{5},{6}",
                        crmPayment.BatchNbr.Value,
                        crmPayment.LineNbr.Value,
                        crmPayment.StudentCRMID.Value,
                        crmPayment.Term.Value,
                        crmPayment.Year.Value,
                        crmPayment.CourseID.Value,
                        crmPayment.PaymentStatus.Value));

                        Console.WriteLine("Subsequent Rercord written into a file...");

                    }
                }
            }

        }


    }

14. Please note every time we call web service, we know what will be the next batch from the processBatchNbr variable, which we set once we retrieved the first record. Within a batch of records we follow based on the BatchNbr field condition. We do not do any calls to the web service which are redundant or unnecessary.

15. In order for WEB service to login correctly we have to enable cookies in the project config file. We should do it manually. 

a) Open app.config file in the notepad


b) Modify it slightly to allow cookies at the areas highlighted. Save. Close:



16. Build the project, get BLOG.exe file at the bin/Debug folder and run it.

Enjoy csv file getting produced and endless messagess popping up at the console :)




All the best,
Sergey.

P.S. Next blog will be about using OData to get the BIG DATA via WEB service of course...

Wednesday, September 12, 2018

Acumatica Notification. How to Pass Empty Field Condition.

Hi Guys,

Today will be a short one.

Just realized Acumatica is not very good at handling conditions at Notifications screen.

When you need to Notify someone you create Automation Notification with the exact condition of when to do it.

Potentially, there could be a case when you wish to call a notification ONLY when certain field is Empty. So, how?

It is empty, does not mean NULL, because field may be not nullable, so IsNull will not work.
How? ='' Does not work..... Any other ideas?

After 30 mins of various tries found a solution.

Acumatica Notification Screen

In my case TnC field supposed to be empty. So we use:

Start With in the Condition column, AND we just put ONE single SPACE into Value field.

You cant see space on the picture but I tell you it is there....

OMG.....

Best,
Sergey.