If you’ve been around ERP consulting for a while, then the odds are pretty good that you’ve had at least one experience with a historical data import nightmare.
The story goes something like this…
The salesperson selling the new ERP system wants to get the customer in the door. It’s a long sales cycle. Lots of demos. Lots of proof of concept stuff.
At some point in the sales cycle, the customer asks about importing historical data. The salesperson gives the correct answer which is, “sure, we can do that.” The conversation moves on to something else and the details on importing historical data aren’t written into the contract (you only have to get burned once before you start writing it into the contracts).
Then it comes to implementation time and the customer hands the consultant a bunch of historical data files that they want the consultant to import into the new ERP system.
And the consultant gets a big headache because scope is MUCH larger than the salesperson assumed. Can you say scope creep?
Business Logic
The real headache with importing historical data has to do with something called business logic.
Every ERP system has tons and tons of business logic.
The problem is that the business logic can be very different between two different ERP systems.
You can’t just take the data from another ERP system and import it into Acumatica without doing some cleanup work. You have to find a new home for all of the fields (and sometimes you can’t find a home for every field), you have to make sure that the new data fields are large enough to handle each piece of data, and, then the real headache, you have to find a way to link the data together.
For example, linking AP Payments to AP Bills. The linking part is a real headache, especially with partial payments. Oftentimes it has to be done manually.
And the way data was linked together in the old ERP system might be very different from the way the data is linked together in the new system. Maybe not with AP Payments, but what about shipping transactions or things like pick lists that don’t even currently have a home in Acumatica.
Bypassing Business Logic
When it comes down to it, many times what the customer really wants is just a way to lookup their old data without having to login to their old system.
Maybe they want to turn off that old server or stop making that SaaS payment on their old NetSuite ERP system (heh heh heh). But they need a new place to store all of that data.
The method that I personally prefer in this situation is to dump the data into “Historical Data Import” tables in Acumatica. Just create your own tables beginning with “HistoricalDataImport” (or something like this). You would wind up with something like the following tables, for example:
- HistoricalDataImportARInvoices
- HistoricalDataImportARPayments
- HistoricalDataImportInventoryTransactions
- HistoricalDataImportAPBills
- HistoricalDataImportAPPayments
It’s nice to begin them all with the same thing (eg. HistoricalDataImport) so they get sorted together in your list of database tables.
The advantage of doing this is that you can create whatever columns you want in the tables and give the columns the same names as the fields in the old ERP system, making it easier for people to look up the data because it’s in the same structure with the same names that they are used to.
Using this method, you effectively bypass all of the business logic in Acumatica and create a disconnected mini data warehouse of historical data.
Displaying within Acumatica
But you need a way to display the data within Acumatica.
Thanks to Doug Johnson for writing this post which is how I learned how to do this.
Let’s say that I have a database table called HistoricalDataImportAPBills which has 4 columns in it. Here is the SQL code that I used to create the table:
CREATE TABLE [dbo].[HistoricalDataImportAPBills](
[CompanyID] [int] NOT NULL,
[InvoiceNbr] [nvarchar](40) NULL,
[Amount] [decimal](19, 4) NULL,
[NoteID] [uniqueidentifier] NULL
)
Every one of your HistoricalDataImport tables needs to have a CompanyID column with data type INT. This is because Acumatica is a multi-tenant solution and this field should be in all of your tables to respect the multi-tenancy.
The InvoiceNbr and Amount columns are just two sample columns that I’m using for this example to keep things really simple. But you can add as many columns as you want and call them whatever you want.
The NoteID column is optional. But it’s needed for the “cool twist” that I mentioned in the title of this post. More on this later.
After creating the SQL Table, I then populate it with some sample data like this (you would likely import it from Excel):
Now I go into the Customization Projects screen in Acumatica, click the + button, and create a new Customization Project called HistoricalDataImport like this (make sure to press the save button):
Then I click the HistoricalDataImport hyperlink in the screenshot above which brings up a separate window.
In this new window, I click Code on the left-hand side, click the + button which pops up a dialog box, choose New DAC in the File Template field, put the table name that I created above (HistoricalDataImportAPBills) into the Class Name field, and check the Generate Members from Database checkbox.
After clicking the OK button, I get a code window that looks like the screenshot below.
Note that you need to pick a column for your primary key by adding “IsKey = true” to it like the highlighted part in the screenshot below. You might even be able to use more than one column as your primary key, but I haven’t tried it yet.
The full text in my code window looks like this:
using System;
using PX.Data;
namespace HistoricalDataImport
{
[Serializable]
public class HistoricalDataImportAPBills: IBqlTable
{
#region InvoiceNbr
[PXDBString(40, IsKey = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Invoice Nbr")]
public string InvoiceNbr { get; set; }
public class invoiceNbr : IBqlField{}
#endregion
#region Amount
[PXDBDecimal()]
[PXUIField(DisplayName = "Amount")]
public Decimal? Amount { get; set; }
public class amount : IBqlField{}
#endregion
#region Noteid
[PXDBGuid()]
[PXUIField(DisplayName = "Noteid")]
public Guid? Noteid { get; set; }
public class noteid : IBqlField{}
#endregion
}
}
Then I publish my Customization Project by going to Publish -> Publish Current Project (Ctrl+Space) on the top like this:
I know that it’s finished when I see this on the bottom. Once I see this, I can close the entire window
Now I can create a Generic Inquiry and pull from the new DAC that I just created.
And I get a nice little Generic Inquiry that allows me to browse through my historical data and do all the stuff that you can do with a Generic Inquiry like sorting, applying filters, exporting to Excel, and even exposing via OData if you want to.
Pretty cool huh?
Personally, I think that this is a great way to bring in historical data and show it inside of Acumatica.
Now for the Cool Twist
But we aren’t done yet. Now for the cool twist and really the whole reason why I wanted to write this blog post.
You might have noticed something in the screenshots above. I didn’t include the NoteID column in the list of columns in my Generic Inquiry. Then why did I bother to put that column in my database table?
Well, if I didn’t include the NoteID column in my database table, then I wouldn’t get the Paper Clip and Notepad icons in my Generic Inquiry on each record like this:
Those icons mean that I can attach notes (kind of cool) or files (really cool) to each record.
So think about that historical AP Bill data. What if the customer also has scanned images of each AP Bill? Now I have a way to attach those scanned images to each historical AP Bill record within Acumatica.
But, wait, I tried using the icons and they don’t work.
Oh no! Maybe this isn’t such a cool twist after all.
That’s when I went to StackOverflow and Ruslan helped me out. Ruslan lives here in Columbus, Ohio and we used to work in the Acumatica office together. We even visited a customer in Germany together (that was a fun trip). Ruslan is the most social developer that I know. Usually you don’t find “social” and “developer” together. But you can find both with Ruslan and he’s a huge asset to Acumatica. He’s also very active on StackOverflow.
Anyways, Ruslan suggested that I go back into the Customization Project and change the NoteID area of my code to look like this:
Now the full text of my code looks like this (and I needed to do Publish -> Publish Current Project (Ctrl+Space) again):
using System;
using PX.Data;
namespace HistoricalDataImport
{
[Serializable]
public class HistoricalDataImportAPBills: IBqlTable
{
#region InvoiceNbr
[PXDBString(40, IsKey = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Invoice Nbr")]
public string InvoiceNbr { get; set; }
public class invoiceNbr : IBqlField{}
#endregion
#region Amount
[PXDBDecimal()]
[PXUIField(DisplayName = "Amount")]
public Decimal? Amount { get; set; }
public class amount : IBqlField{}
#endregion
#region Noteid
public abstract class noteID : PX.Data.IBqlField
{
}
[PXNote()]
public virtual Guid? NoteID { get; set; }
#endregion
}
}
And now I can use the Paper Clip and Notepad icons in my Generic Inquiry. I tested by adding a note to the first record and a document attachment to the second record. You can tell because the icons change color like this:
Note (ha, get it?) that you can also tell that it’s working because the NoteID field gets populated in the custom SQL table (HistoricalDataImportAPBills) that we created.
The NoteID field is used to track both notes and document attachments. There are additional tables involved depending on whether it’s tracking a note or a document attachment, but I won’t get into that now.
In case you’re wondering, I also tested that this really is using the regular document management feature.
Before I uploaded the document attachment to the second record in my Generic Inquiry above, I went to the External File Storage screen and told Acumatica that I wanted all new files to get stored in the C:\Downloads\Temp folder on my computer rather than in the database.
Then, after I uploaded the document attachment to the second record in my Generic Inquiry above, I checked the C:\Downloads\Temp folder on my computer and, bam, there was the file (it’s the one ending in .bin).
That’s proof enough for me that this really is using the Document Management engine inside of Acumatica.
So I hope you agree that this is a cool twist. And thanks again to Ruslan for showing me how to do it.