Adding new Field to DB Schema and Screen in 8 Minutes.

Hi Everyone,

Please refer to the video for adding new fields into Acumatica screens, by modifying DB schema directly from Acumatica Application.

I was using Mozilla Waterfox browser. Any browser will do.

All the best,

Sergey.

What is Automation, Work Flow and how to Reproduce it.

Hi All,

Well, I am not saying you do not know what is a work flow, sure you do.
Just wanted to point - in Acumatica it is called Automation Steps and located System->Automation menu.

We can create work flow nodes for any screen in Acumatica, as many steps per screen as we want. In the above example I show standard Automation Step for Purchase Order screen, Step called NL Open. Based on the author's idea it supposed to mean Normal Order Open State ;)
Plain English - When the Purchase Order of type Normal is on Open Status, we should exercise this step. We are on this step. Whatever you like to call it.

Q: So, once we are here, what will define position of this step in a work flow?
A: Tab called Conditions, there you can see:
1. Approved Checkbox must be checked.
2. Hold checkbox must not be checked.
etc.

Q: Once we are here, what is configured under this step?
A: We can define Actions, Reports and Inquiries seen from the screen.

For example, action Approve is disabled, which is reasonable, since we approved the order already. Option to place order on Hold is available, so we have it active.

Last thing, we can define some fields on the screen here.

So basically its just a step in a long tree, so where is this hierarchy stored?
Here it is:

In order to see actual tree in XML form we should press the button Show Populated:

There was a question from the reader: How can we add to this list steps that we have customized?
A: Before you press Show populated button, simply depress Populate Definition button, that will copy actual steps into XML code.

And here is the best part: ALL STEPS, FIELDS AND BRANCHES are in this XML code.

And we CAN COPY this code to another company. Meaning - WE CAN replicate our automation steps for another customer :)

In order to do it, just copy the XML text into a clipboard.

Then open another company, then Add definition , then Press "Show populated".
It will be blank, then just copy the text into it, then save the definition.

Then Press Activate Definition.

This way you can copy tailor made work flows to a newly created companies.

All the best,

Sergey.

Securing Web Configuration

Hi Everyone,

One of the questions from the audit team could be why do we keep some sensitive data in our configuration files. Luckily .NET provides fast and effective way of encrypting content of the web.config or machine.config files.

Of course if we use Windows Authentication, no data will be exposed in Web.Config, but just in case we need SQL based authentication to be used. Please use a simple script to encrypt content of the <connectionStrings> section of your web.config file.

1. Get the ID of your web site from the IIS Administration panel:

2. Open Command Prompt. Navigate to a folder where .NET resides and execute the following statement:

aspnet_regiis -pe connectionStrings -app / -site 9

Where connectionStrings is the area what we plan to encrypt, -app / means we have our site at root, and
-site 9 is the site ID.

Web.Config before the "surgery":

And After:

The only drawback of this - Configuration Tool, that is using local executable application, will not be able to find a database. But this I believe for good, you can control the attempt of changing configurations for the system.

If you need to decrypt the web.config, just run this statement:

aspnet_regiis -pd connectionStrings -app / -site 9

You Enjoy :) ,
Sergey.

Understanding Company Setup Options

Hi Everyone,

Lets discuss companies setup using Configuration Setup application.

Company Setup in Acumatica designed to be using the same tables by multiple entities.
And when the record is shared, we are not replicating it but truly share.
To isolate one company from another within a table we have Masks as a column, they are processed automatically by Server application, to ensure we see a correct company data.

In order to understand the design, we need to put few considerations:

1. There is a single top level company and we use it as  a system storage for kernel data. This company is in Read Only mode for the end user. No data can be amended or inserted into that company and it stays at the top level node of the hierarchy tree.

What it means to us? Effectively, this top company is a break-point for any updates coming from below nodes. No data can be passed through this node to another nodes. Its a true dead end for any data coming from its children.

This company has fixed ID and it is always equal to 1 [One]. Consider it as Master Parent company.

2. We can create unlimited number of "normal" Parent Companies. But the trick is - if company is Parent, it can not be used for the users to log into it. It looks like a nonsense but you will soon understand what is the purpose of such Parent node. Parent company is the one that have child companies. Parent company can be a child of another, higher Parent company. Parent Companies data can be modified, updated or inserted. And because we can not directly log in into Parent Company, these updates/insertions can only be made either by it's child companies, it's parent companies or directly inserting data into it from a Template, using Configuration Tool.

3. We can create unlimited number of child companies, which can not be parent to any other companies. Forever young, in a sense :) It means we call a child a company that has parent but does not have its own children. Data in those companies are modifiable as well. And because we can log in into them we can modify data directly, or modification can be done by it's parent.

4. We have a data sharing mode or setup for all companies on a PER TABLE basis. Say, we define sharing data or data isolation (whatever you like) once only in a single place, for each table in the database. And it applied to all and any company immediately. There is no individual sharing/isolation mechanisms per company. But soon you will get that it is not necessary to setup isolation per company, it must be global setting :), per table. I will discuss how it works later, but let me mention: there could be Separate, Split or Share data isolation modes.

Lets start from a simple configuration with 2 child companies, 1 parent and of course 1 master parent.

In this example companies 3 and 4 will be used for log in, they are "true companies". The end user will think that there are only companies 3 and 4. Company 2 will be invisible. And company 1 is not only invisible, but it is Read Only :).

So you may ask a question, why do we need company 2?
The answer is - we need it to transfer data for the tables in SHARING DATA MODE from company 3 to company 4, because we can not transfer via company 1, remember, its Read Only one.
Secondly - we can use company 2 to propagate template data to child companies 3 and 4 for the tables in SPLIT DATA MODE.

So what if we need to share GL Accounts between companies 3 and 4, at the same time we would like to pre-populate the list of Customers for companies 3 and 4 with some list, but we would like to let end users to modify customers independently in companies 3 and 4.

Basically, for Account table we will use SHARE data isolation mode. While for Customer table (BAccount in fact) we will use SPLIT data isolation mode.

Exactly, when we use SHARE, data will be shared between companies 3 and 4, whatever update you made in co 3 will be propagated to co 4 and vice verse. But when we use SPLIT, data from Parent company are VISIBLE in child companies, and the end user will think that these data are actually company 3 or 4 data.
But at the moment you will update/delete/insert data in any of the child companies 3 or 4, data will become independent and for that particular data row from table Customer, system will SPLIT the row between company 3 and 4, creating 2 independent rows.

Well, all other tables we will keep in SEPARATE data isolation mode, which means they are absolutely isolated and independent in companies 3 and 4.

I predict some of you will say - we can use data import instead, why do we need this strange SPLIT mode at all! Well here are two reasons for it:
1. In split mode, before you made any modification to a data row, you save significant amount of database space, by not creating a row of data per company, instead, using a SINGLE row for ALL companies.
2. There are cases when we need to setup exactly the same list of entities for every new company. And running Data Import every time may not be a good option. As we might need to spent significant amount of time for it.

Anyway, if you do not like SPLIT mode, just use Data Import. And concentrate on SHARE and SEPARATE modes only :)

So, lets get to practice. Setup from above should look like this:

Take note, for Parent companies we must NOT check Visible option.
Then we have to setup companies first before going to data isolation configurations.



Now we have to add template data to company 2, I will use Demo company data.
Later it supposed to propagate Chart of Account to both companies 3 and 4, as well as pre-define customers and vendors in companies 3 and 4. Of course companies 3 and 4 will need to get setup for gl/ar/ap etc, which I will do manually but skip here.

Once setup is processed lets assign that Account we share and BAccount (Customer,Vendor,Employee) we split.

Then press Next and process the changes into a database.

Please take note, we should insert template data first then change isolation mode.

Now lets take a look at companies 3 and 4. COA is in place and shared and Customers are propagated as well.

And if I now change the customer in company 3, it will not affect company 4. Where the same list of customers if pre-created.

I only hope that concept of Companies and their data sharing modes became a bit clearer. Also, there is nothing wrong in setting companies structures like below:

Here we have Parent->Parent->Parent relationship between companies 1, 2 and 5.
And even if we switched on SHARE mode data from left company group (2,3,5,8,9) will never go to either company 4 or group of companies (6,7). Simply because company 1 is in Read Only mode and data can not pass through it.

So three blocks (2,3,5,8,9) ; (4) ; (6,7) are completely isolated from one another and can exchange data only within itself.

Have a great time configuring your companies :)

All the best,
Sergey.

Customization in Multi-Company Environment

Hello Everyone,

Would like to discuss customization used in Acumatica Cloud ERP, specifically when more than one company are in a single database.

As you already know, we can setup multiple companies in a single database, by using Configuration Tool, based on Example from DEMO40 Instance:

I will navigate to Company Maintenance:

In my case there are 2 companies available for the end users: ID=2 and ID=3
So what if I need to customize Co3 and Co2 independently?

We should look at it from our system design prospective. We are using a single database, so if we say add a field to a table, and this table is say Customer. Then any company that are using this table will see this new column, it is basically because of the SQL Table design concept. IIS will pick up this column and show up in the Application. Tables are shared among all the database users, so here is:

Rule number One:
When Adding a new fields to a table, be prepared that all the companies from your Acumatica instance (sharing single DB) will see this field.

And immediately after it,

Rule number Two:
We can always use Access Rights on the selected Companies, to restrict the access to the field, if we want the only specific company to see it.

Another question - can we share or split functional customization?

Again, looking into design, we share the same Web Site for all these companies. We also share the same Application Pool. So, naturally, whatever I publish will be seen by all.

Rule number Three:
When Publishing customization, it will be seen to all Companies. Make sure all your companies are ready to use the same customization set.

But if you still wish to split functions, there is a workaround:

Rule number Four:
Please add hard coded logic into your customization to execute certain code only if CompanyID = <Desired Company>. To make sure you apply customization to specific company only. Something like this:
if (company1).PXUIFieldAttribute.SetVisible

All the best,
Sergey.

Importing Invoices. Multiple Lines. Multiple Times. Using Search Function.

Hi Everyone,

One of our customers importing Invoices from ageing Oracle based system into Acumatica.
I will skip here technical aspects of this integration, just mention it is done via MS SQL Integration Services, data from Oracle DB are placed on to our Azure SQL server.

Main issue is not Oracle->SQL transport but the interface dynamics.

Customer has multiple lines in the invoice with each line having its unique number per invoice.
Also customer wants to import into Acumatica multiple times, and that the SAME Invoice will dot be imported twice or lines will not get duplicated.

This can be achieved by adding a user field into Invoice Transaction area, and then modifying Import Scenario to look into that field before inserting a NEW transaction line.

Here is additional field added to Transaction Line, you may refer to my earlier posts on how to add a field to grid and map it to the database.

 

And then, at the Interface Import Scenario we should indicate Search parameter to be Our newly added field. Then next line we should indicate that this parameter should be tally to importing data file.

@@UserLineRef will be a search variable, that is used to compare content of LineRef customized field and JobLineNbr field from the importing data.

As a result we can import the same file multiple times without the risk of duplicating the line data.

All the best,
Sergey.

Cloud Fixed Assets. Split, Transfer, Change of Useful Life, Accelerated Depreciation and more.

Hi Everyone.

In Acumatica version 4 FA module was greatly reworked and improved. Original goal was to make it better than JDE. You are the one to judge.

The module is used by KFC/Pizza Hut for nearly 20,000 assets around 200 locations island-wide. 

Here are some of the features that makes us distinct and unique among other ERP:

1. Asset Split.
It is when we purchase assets in bulk, for example chairs for the restaurant, then entered it into our system as a Single Asset. Well you may not wish spent time labeling each chair individually, but rather put them in as a "lot number". It works fine until we need to shift part of these chairs to another location or even another shop...
One step solution for that - Asset Split process.

2.Asset Transfer. 
There could be a transfer of asset without changing the ownership, when sales purchase of asset is not needed. It can be between branches or Departments

Inter Departmental Transfer:












Second type is when we have to Sell the Asset in location A and re-purchase it at cost on location B.
Inter Brand transfer:

You may notice Disposal Method, that will be used to write off the asset during the transfer. Its because second method require sender company to get rid of asset in their books completely.

3. Change of Useful Life (UL).
There are two ways of changing the useful life of an asset. Either re-depreciate it from the beginning of life of the asset, and then, posting difference to the period of UL change.
Or use so called "accelerated depreciation" mode to spread remaining Net Book Value of the asset over remaining life of the asset proportionally. Depreciating in a nicer and more flat manner.
In Acumatica you are free to choose the method of changing UL for your assets:

4. Cost Additions and Deductions.
It is clear that some assets may get additions. Like for a PC we can buy a new HDD or RAM upgrade.
This is usually managed by changing the asset cost at certain point of parent asset life.
In Acumatica we have a dedicated transaction type and screen to process asset cost additions OR deductions.

5. Reports.
Here I just provide a list of reports we have:

All the Best,

Sergey.