Acumatica is an exciting Cloud ERP product and this is my personal Acumatica Blog. Feel free to contact me with any Acumatica questions that you might have.
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
There are a few low hurdles that you need to overcome. Checkout this post and video.
From Data Hell to Bliss: Getting the Most Out of Your Acumatica Data
Seven weeks ago I started working for Acumatica. During the past seven weeks, I have been considering whether or not to continue posting on this blog.
I have a lot of ideas for posts (some written down, some in my head), but I wanted to see how the first seven weeks would go as an Acumatica employee before deciding whether or not to continue blogging here.
Here is what my Evernote blog post ideas document looks like at the moment:
- Power BI Top 5 Customers by Revenue
- Creating a data source based on ARTran
- Connecting to Power BI and adding a monthly bucket column, hardcoding the revenue account filter
- Add monthly filter and drilldown to item
- Top 5 mobile
- Add an alert if top 5 are more than 50% of total
- Power Update Introduction
- Mainly needed if you want a simple way to “mashup” with on-prem data sources
- Tableau Public map visualization of sales by state connected to OData
- Power Query Intro in Power BI Desktop
- Add custom calculated column -> PT/PC
- Data shaping (more than just rows and columns) – IT tools vs new Business tool called Power Query
- Doug Johnson SQL View vs Power Query
- Link to Miguel Escobar training course
- Power Pivot Intro and link to Rob course
- Chandoo dashboard stuff
- Why ERP summary tables are obsolete
- Talk about the experience with a CFO where he was using a report based on summary tables and they didn’t have enough detail when he wanted to slice and dice
- Power BI lets you filter or pivot on anything you want. You don’t get handcuffed. You are free to ask whatever questions you want about your data.
- Talk about pivoting with Power Query, basically on-the-fly summary tables
- What lurks behind CSV?
- What is CSV and why does it exist?
- Rob Collie’s comment that most of his engagements are sourced from legacy CSV files
- ERP (French) and Excel (Chinese)
- Until now, you had to hire a translator named CSV
- Acumatica decided to go out and learn how to speak Chinese (OData)
- Acumatica Reporting – Where to Start?
- Graphic with Report Designer logo and arrows to “no options”
- Graphic with Excel logo and arrows to Power BI, Office 365, Power Update, Report Designer
- Excel is the best prototyping tool and many times good enough for the end user
- Acumatica and Microsoft are a winning combination
- I noted a while back that Acumatica is aligned with Microsoft while NetSuite is aligned with Oracle.
- Since Satya took over the reigns at Microsoft, they have sped ahead with their technology. So much so that NetSuite recently ditched Amazon for Microsoft Azure.
- Of course, it pays to go with Acumatica which allows you to deploy with SaaS or on Azure, Amazon, 3rd party hosting, or on-premise.
- Simple, low-powered devices that display location-relevant KPI metrics throughout an office:
- Steve Jobs design of Apple headquarters intentionally to help people run into each other and interact
- The importance of collaborative working space and displaying relevant information throughout a three dimensional environment, not just on a screen in a cubicle
- The danger of the all-in-one remote or the cell phone screen that does too many things
- The reliability of the digital stadium scoreboard being due to it’s fixed geographical location and that it only delivers one piece of information and delivers it well (without the interruption of commercials like on the jumbotron)
- The human brain’s ability to remember things based on their geographical context
- Binary vs Analog
- Human beings will always be analog
- Implementing ERP is more about the analog side of things than the binary side of things
- FRx and multiple data source reporting
- Remodeling vs building from scratch
- Building up vs tearing down
- There are two philosophies: fit your business to the ERP software or fit the ERP software to your business
- Pick a product that can meet your current needs or pick a product that can conform to your current and future needs
- Acumatica and the SSRS Rumor
- Bye bye paper
- Paper separates Gen X from Millennials
- Living Information vs Information Corpses
- Almost want to replace the word Report with the phrase “Information Delivery Vehicle” (IDV)
- Organic, biological, static, inorganic, DNA
- The unchangeable trust factor: why paper lives on
- Paper is illicit, like cash to a drug dealer
- I still use paper occasionally, not because I’m old but because I’m human. Human beings are analog, not digital, beings.
- Democrats vs Republicans and Domo vs Excel
- Are we destined for 50/50?
- Does this same debate apply to Hosted vs On-Premise
- Waterfall Income Statement
- A Pivot Table Income Statement
- My Story (Part 1)
- Started at Deloitte and Touche in audit. Clients always had trouble pulling information from their systems to give to us in a usable format.
- Serendipitously landed in ERP in 2005 where I learned SQL and how to pull whatever information I wanted out of the system. A dream come true!
- I eventually was introduced to the world of Business Intelligence, Data Warehousing, and Data Discovery tools. So much better than Crystal reports!
- My Story (Part 2)
- From “focus on tools” to “focus on people”. The real Business Intelligence is people. Reports don’t make decisions, people make decisions.
- From working with reporting tools that were like “working with concrete” to those that were like “working with clay”
- From focusing on “how to” geeky technical nuances to philosophical musings which is how we got to where we are today with this website
- The importance of numbers
- The numbers themselves tell a story: Height, Weight, Age, Grade Point Average, Batting Average, Salary, Annual Revenue, Headcount, Outside Temperature. All of these numbers paint a picture and tell a story.
- Data viz is the cherry on top, not the ice cream sundae.
- The importance of data visualization
- The importance of infographics
- Trust – The foundation of enterprise reporting
- A number queried directly from the ERP system versus a number from the Excel spreadsheet on a VP’s laptop. The VP’s laptop will always win because there is a greater level of trust.
- Report Designer MICR
- Dynamically splitting the GL segment using Power Query in Excel
- Integrating Power BI tiles into Acumatica screens
- Building ERP-specific visualizations with D3 and the Power BI open framework
- Why Power BI makes sense for Mid-market ERP consulting firms
- Zero price barrier to entry for clients
- Allows you to have a conversation with EVERY SINGLE ONE of your clients, not just the big spenders
- Easy to prove massive value in a short amount of time
- Most clients are already reporting in Excel, the BI Trojan Horse (at least to some extent).
So, ya, have a lot of ideas for future blog posts.
However, after a lot of careful consideration, I have decided to hit the pause button.
Things are much busier at Acumatica than they were at my last job and I just don’t have the time or energy at the moment to continue blogging. I really want to, but I’m just not able to at this time.
When I started work at Acumatica, I also started going to the gym again (it’s been a while), in the morning before work, and I LOVE it. If I were to continue late night blogging, it would kill my morning gym time and I’m just not willing to give it up.
In order to hit the gym in the morning, I have to go to bed at a reasonable time (10ish to 10:30ish). Basically I get home, play with the kids for a little bit, help put them to bed (a long process), and then it’s time for me to go to bed.
So, that’s it. Things will go quiet here on the blog.
I might pick it up again at some point in the future, but we’ll have to wait and see.
It’s a shame because there are many, many exciting things going on with Power BI and I would have loved to tie them in with Acumatica and the world of ERP. I firmly believe that paper-based reports will continue to give way to reports on your laptop screen, on your phone, or on the tv monitor in the conference room. Power BI is in a great position to deliver these kinds of reports and Acumatica is in a great position to feed Power BI with the kind of ERP data it needs to be valuable.
Blogging was a very fun hobby for me and I have enjoyed it very much. Regarding hobbies, I have always liked Mark Twain’s explanation of a hobby in the book The Adventures of Tom Sawyer:
If he had been a great and wise philosopher, like the writer of this book, he would now have comprehended that Work consists of whatever a body is obliged to do, and that Play consists of whatever a body is not obliged to do. And this would help him to understand why constructing artificial flowers or performing on a tread-mill is work, while rolling ten-pins or climbing Mont Blanc is only amusement. There are wealthy gentlemen in England who drive four-horse passenger-coaches twenty or thirty miles on a daily line, in the summer, because the privilege costs them considerable money; but if they were offered wages for the service, that would turn it into work and then they would resign.
If I were a wealthy gentleman in England, then I would probably continue blogging about Acumatica and Power BI.
But, I’m not.
I will focus instead on hitting the gym in the morning, keeping up with my new job responsibilities, and getting to bed at a reasonable hour.
Thanks to all of you who have stopped by over the last two years.
It’s been fun.
Guest post by Haibin Zhou
Quick intro from Tim: I first saw Haibin’s posts on LinkedIn sometime last year. He is an independent ERP consultant in China and he does Acumatica development work, including creating reports like the one that he takes us through below. Take it away Haibin!
Note: This posted originally appeared on LinkedIn and is reposted here with Haibin’s permission
A Landed Cost Report in Acumatica
Guest post by Robert Lightner
Quick intro from Tim: Robert Lightner (like Brade Kostreva last week) is one of a growing number of Acumatica users who have been using the product and really like what they see. So much so that they do crazy things like writing posts on their own time to share with other Acumatica aficionados. What we are witnessing here is a grassroots community that is forming before our very eyes. Speaking of community, Robert has expressed interest in starting an Acumatica User Conference. Today though, he shares with us a report that he created and asks for feedback.
Note: This posted originally appeared on LinkedIn and is reposted here with Robert’s permission
Acumatica – Fiscal Year Monthly Analysis Report by Salesperson or by Inventory Item or both (On Demand)
Guest post by Brad Kostreva
Quick intro from Tim: I was thrilled when Brad asked me last month about doing some guest posts here on the blog. We first connected about a year ago through the blog and have kept in touch ever since. I mentioned him recently on the bottom of the What is Power BI post. Unlike me, an Acumatica reporting hobbyist, Brad is actually using Acumatica, Excel, and Power BI in the real world. I’m looking forward to hearing his real world experiences in this and future posts. Take it away Brad!
Of Acumatica and Power BI – A Tale Loosely Inspired by the title of a classic novel by John Steinbeck
Tomorrow, September 17th, I will become an employee of Acumatica, filling the ERP Solutions Consultant position in the Columbus, OH office.
Acumatica has been a fun side project / hobby for me since April 30th, 2013. But I’m happy that I will now be working with it full-time. Of course, I’m also happy that I will now be getting paid too 🙂
Am I Excited?
Currently, Acumatica is the only ERP product listed on PowerBI.com:
But Power BI is a fairly new thing and many people aren’t exactly sure what it is.
So, in this post I’ll attempt to answer the question, “what is Power BI?” [Read more…]
The Current Excel Online Problem
Three weeks ago we looked at using Excel Online to deliver your awesome Excel reports to people without them having to know that you made it in Excel.
However, there currently is a problem with Excel Online. The problem is that there are actually different versions of Excel Online. There is a Free version, there is a Business version, and there is an Enterprise version. For more details, checkout this post by Mike Alexander (click here).
I personally do not understand all the differences between the versions or even if these are indeed all the versions of Excel Online. There might be others that I am not even aware of yet. This is the downside of the Microsoft BI strategy at this time. It can be tricky to navigate all of the different options.
Wow, I can’t believe that it’s been two months since my last substantial blog post! But it is Summertime and family activities take priority over the blog. We did some camping, made a trip to California to see family, and have generally been having a blast enjoying the nice Summer weather.
Now it’s time for a long overdue blog post.
Two months ago we built a very simple example of a dashboard in Excel (click here). Since we built it from an Acumatica OData connection, we were able to refresh it with live, up-to-date data by simply clicking a button in Excel.
Of course, I am a big proponent of Excel because it has a long history of being a mature reporting product. It has been built over three decades and has over 800 million users. These users have used Excel for all sorts of different applications in the real world. They of course generate feedback which has in turn steadily transformed Excel into a very mature reporting solution.
Currently, in my opinion, Microsoft Excel is the best application for consuming the OData connection that is available in Acumatica for two main reasons:
- Most companies already own Excel
- Excel is already a robust calculation and reporting software application
How robust is it? Consider this, if you go to business school, the chances are that you are going to be using Excel heavily in order to solve real world business problems. If you go to work on Wall St., you will not be able to survive, unless you acquire advanced Microsoft Excel skills. There is even an annual competition called ModelOff (click here) designed for these Wall St. types and the competition attracts over 4,000 people.
Schools like UT Austin in Texas have entire business courses that are dedicated to teaching their students Microsoft Excel. One such course is taught by professor Clint Tuttle, a man who is so passionate about Excel, that he even makes music videos like this one (click the picture to see the video):
I already mentioned the over 800 million users who are constantly placing demands for more and more features. When you consider that Excel has been around for 30 years, you realize that there are a lot of features available. It is a very mature application. Even Bill Jelen (aka Mr. Excel) admits that he rarely walks into a room to give a seminar about Excel without learning something new himself.
Bottom line, even though Excel is not an all-encompassing reporting solution, it is a lot more powerful than most people realize.
The Excel Problem for Acumatica Users
This is all great, but we have a problem.
With the simple dashboard that we built two months ago, someone has to open Microsoft Excel on their desktop computer in order to use it.
This is especially problematic for an Acumatica user because an Acumatica user is accustomed to being free to access Acumatica anywhere they want. They can access it from their computer, they can access it from their phone, and they are not tied to their Microsoft Windows desktop environment.
I’ll admit that it can seem archaic to an Acumatica user when they are told that they need to use Desktop Microsoft Excel in order to do some reporting.
Wouldn’t it be nice if I could open an Excel file in my web browser just like I open a regular web page, just like I open Acumatica?
The good news is that there already is such an application and it is called Excel Online.
Excel Online is web-based Excel.
Applications like Excel Online have received a lot of attention from Satya Nadella, the new CEO at Microsoft. Satya has talked a lot about making Microsoft a cloud company, a company that delivers their software in the cloud, in the browser, on mobile devices, and not just on Desktop computers.
What has been interesting is to see how quickly he is taking Microsoft in this new direction and I think many are surprised to see how quickly Microsoft is actually executing on the vision outlined by Satya.
Excel Online is one of many examples which demonstrate what the future looks like for Microsoft applications. As time goes on, Excel Online is beginning to feel more and more like Desktop Excel. Interestingly enough, Desktop Excel is beginning to feel more like Excel Online. For example, you can now embed web apps into Desktop Excel documents by inserting them from the ribbon.
This just goes to show that the entire Excel experience, both with Excel Online and with Desktop Excel, is beginning to feel like one giant web application. This is not an accident, it is simply the execution of Microsoft’s new strategy. It seems like eventually there will only be one Excel product with two versions: a Web version and a Desktop version.
Microsoft has already accomplished this with Windows. The next version of Windows, Windows 10, is one Windows code base. It is just one collection of computer code, but it can be deployed on a Desktop computer, on a Tablet, on a Phone, or even in the Cloud. I believe that we will eventually see the same thing happen with Microsoft Excel.
If this “one code base, any device” philosophy sounds familiar, it should. Acumatica has been doing this since it began in 2008 and it is a part of the Acumatica DNA.
Web-based Acumatica, meet Web-based Excel (Excel Online)
Recently I realized that, since OData is a secure way to deliver Acumatica data over the internet, it might be possible to connect to an Acumatica data source from the Excel Online web application, not just from the Excel Desktop application.
I was pleasantly surprised to find that this is indeed possible using Excel Online and Office 365.
In the video below, I would like to show you how to take the dashboard that we created two months ago, upload it to Excel Online, and actually refresh the Excel Online dashboard with live data from Acumatica, all within your web browser, without ever having to open the Desktop Excel application.
Then, as a final touch, we can take our Excel Online dashboard and embed it within Acumatica, completely hiding the fact that we used Excel to make the report! We just need to use the following secret code: &action=embedview&wdbipreview=true
Checkout the video!
The VAR 100 lists the Top 100 Value Added Resellers, ranked by revenue. Click the link above to see the report.
Included in the report is a one page summary of what happened this past year. I was very happy when I read the second paragraph (underline added for emphasis):
Meanwhile, of the VAR 100 that partnered with new software providers, Acumatica was the most popular portfolio addition, with Intacct and NetSuite also forging new partnerships with a few of the top 40 resellers.
Holy cow, this is big news! And it doesn’t even include the recent NexTec announcement because Acumatica isn’t listed under their products on this report.
It has taken a little while for Acumatica to gain traction in the mid market, but I think that we can officially say, “traction gained”.
The consulting companies that are the boots on the ground, the forces in the trenches, have recognized that Acumatica is serious about sticking to their original game plan which I like to summarize as, “make an awesome product that is delivered by an awesome partner channel”.
Many ERP software companies get sidetracked and they keep shifting their priorities around to invest in different areas of the business. The result is a confusing situation where consulting companies don’t know what to expect year after year. Acumatica has bucked this trend by continuing to invest heavily in their product and by continuing to demonstrate that they are 100% devoted to their partner channel. Bottom line, you know what you’re getting with Acumatica.
I personally think that this steady approach has a lot to do with John Howell. John sits on the board at Acumatica and he has been through this process before with Solomon ERP Software, taking a steady long-term approach.
Whatever the cause, the results speak for themselves. VARs understand the market better than anyone and they only pick up a new product like Acumatica if they truly believe that it is going to meet the needs of their clients in the market.
That’s why the popularity of Acumatica in the VAR 100 is such a big deal.
The journey of a thousand miles begins with one step.
– Lao Tzu
The two year journey that I began on February 5th, 2015 has turned out to be only a 4 month journey.
I have decided to stop posting a review of one of the out-of-the-box reports on Thursdays. Basically, I think that I have reviewed enough reports to become comfortable with the process and there is no need to continue. I will review more reports in the future on an “as needed” basis.
However, I’m still glad that I started the journey. Rather than try to determine where a road heads, sometimes it’s best to just start walking. Then you’ll know for sure if it’s heading in a direction that you want to go. If the journey doesn’t appear to be worthwhile, then you can always stop. And that’s what I have decided to do.
Summertime, family vacation, etc. has caused my posting on this blog to suffer. But I still plan to continue posting on Tuesdays. It just might take me a little while to get back into the swing of things.
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The CA Edit (CA612000) report in Acumatica shows you the transactions that have been entered through the Transactions (CA304000) screen and that are not On Hold and not Released.
Here are some screenshots from the report:
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The Reconciliation Statement (CA627000) report in Acumatica assists you went reconciling your bank accounts. You can see a list of the transactions that have not yet been reconciled in the period that you select.
Here are some screenshots from the report:
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The Cash Account Details (CA633500) report in Acumatica allows you to see the individual transactions in a cash account for a chosen date range. The Cash Account Summary (CA633000) report shows a summary of the transactions per day, but this report actually shows you each individual transaction.
Here are some screenshots from the report:
Last week we built a very simple dashboard in Excel by connecting Acumatica data to Excel using OData.
This week I have been super busy so I wasn’t able to put together a video post.
However, I did find a couple of helpful links that demonstrate the power of Microsoft Excel Dashboards in Acumatica ERP:
The Cash Account Summary (CA633000) report in Acumatica gives you an overview of your cash situation. You can see the daily balance of each of your cash accounts along with a summary of the receipts and disbursements.
Here are some screenshots from the report:
Easy to Use and Massive Community
There is a reason that Microsoft Excel is the world’s most popular BI reporting tool. It’s easy to use and you can find tons how-to information online.
If you aren’t sure how to do something in Excel, just type your question into Google and the chances are that you will find an answer.
Excel formulas are very powerful and there are myriads of ways that you can combine them together to create powerful calculations.
The GL Reversing Batches (GL690010) report in Acumatica allows you to enter a batch and then see all the reversing entries that have been made against that batch, including their individual debits and credits. This isn’t my favorite report, but I suppose it could come in handy when you aren’t sure if there have been any reversing entries made against a batch.
Here are some screenshots from the report:
Update February 5th, 2017: Alas, the TryAcumatica.com links don’t work anymore since that site has been taken down. So, I removed the links.
Then, at the very end, we checked a box to enable it for OData.
Checking that box allows us to connect to the data in our Generic Inquiry screen using other applications, including the world’s most popular business intelligence application: Microsoft Excel.
The Open GL Documents (GL656000) report in Acumatica allows you to view all the unposted GL documents in Acumatica. It’s easy to forget about an unposted transaction. This report helps you to track them down and clean them up.
Here are some screenshots from the report:
The GL Recurring Transactions Detailed (GL640500) report in Acumatica shows your recurring transactions that have been setup in the Recurring Transactions (GL203500) screen. It is very similar to the GL Recurring Transactions (GL641500) report except that it shows the individual debits and credits for each scheduled batch.
Here are some screenshots from the report:
Last week I mentioned the recent announcement by Acumatica which connects Acumatica to Microsoft Excel, Power BI, Power Pivot, and Power Query.
Then I made my case for why I think that Excel and the “Power” tools are a big deal.
What about Report Designer?
But where does the Report Designer in Acumatica fit into all of this?
Hold onto your seats, the world’s fastest growing Cloud ERP software (Acumatica) is about to collide with the world’s most popular Reporting and Business Intelligence software (Microsoft Excel).
Acumatica made the announcement this morning (click here) at Microsoft Build, the annual conference aimed at developers who use Microsoft’s next generation technologies. I just watched the event live at www.buildwindows.com and the Acumatica OData solution occupied the stage for 5 minutes which is a lot of time considering how much they cram into the keynote (watch and you’ll see what I mean).
The OData feature in Acumatica allows users to connect Excel to virtually any Acumatica data that they want. No more manual exporting and importing. You can now build an Excel report with Acumatica data and have it refreshed with the click of a button.
So what’s the big deal about Excel?
“Excel,” I hear you asking, “isn’t that just a spreadsheet program?”
Ah, yes, it is a spreadsheet program, but it is also the most popular Reporting and Business Intelligence software on the planet.
Wall Street would collapse without Excel, the government’s budget and census projections would be nonexistent without Excel, and university research would be nothing more than data, lacking any analysis, without Microsoft Excel.
Ok, maybe I exaggerate a little, but take a look at the following video featuring the two creators of VisiCalc, essentially the first version of Microsoft Excel, and maybe you’ll agree with me.
Click the picture to see the video.
In the June 1981 issue of Fortune magazine, VisiCalc was featured in an article entitled, “Software’s Greatest hits.”
The January 1982 issue of Inc. magazine featured the creators of VisiCalc on the cover highlighting the birth of the new computer industry. Notably, Bill Gates was pictured on the inside story, but not prominently on the cover.
In September 1985, The Wall Street Journal made the following comment in an editorial:
We’ve been reading stories this week about all the returning Members of Congress who say that virtually none of their constituents are interested in Ronald Reagan’s tax reforms. Could be, but we doubt it. Our guess is that people everywhere have by now filled Visicalc spreadsheets, endless pages of eight-column accountant’s paper, yellow legal pads, blank stationery and envelope backs with calculations of how they’d fare with the president’s tax-revision proposals…
It’s notable that in 1985 the spreadsheet had already become synonymous with important decision-making.
VisiCalc eventually became Microsoft Excel and Microsoft Excel has now been installed on over a billion computers worldwide.
We have the same situation today that we had in the 1980s. Walk the halls of any company, duck into a meeting, and look at the reports that are being used to make critical business decisions. You will probably find that the majority of those reports were created in Microsoft Excel.
The hidden truth about Excel
By now, all the reporting professionals are nodding their heads. Why? Because they share a common experience with Mr Excel (aka Bill Jelen).
Click the image below and listen from the 2 minute mark to the 5 minute mark as Bill tells his story of how he used spreadsheets to do what a $100,000 reporting software package couldn’t do.
Bill discovered what every reporting person eventually discovers. That is, the most popular button in any reporting software:
I personally have experienced this many times, whether it was during my consulting days or now at the manufacturing company that I work for. As an IT person, I used software like Crystal, SSRS, and FRx to create reports. But, time after time, people would ask me how they could export the reports to Excel.
Eventually I started to pay attention to the reports that were being brought to meetings. It was then I discovered that my reports were simply exported to Excel where the “real” reporting actually took place.
Fancy Reporting Software
These days, the Reporting and Business Intelligence software market has become very crowded: Tableau, Qlikview, Domo, Logi Analytics, Solver, BizNet, Renovo, and the list goes on…
Some of these vendors (notably Tableau, Qlikview, and Domo) try to promote what I call “out of the box analysis” where they imply that, if you buy their software, you will instantly have insightful dashboards at your fingertips.
It’s as if their software can somehow magically understand your business and spit out meaningful reports without you having to do any thinking.
Rob Collie sums this up with the 3 Big Lies of Data which are:
A new era for Excel
In late 2006, a Microsoft employee launched a secret incubation project called “Gemini” with a goal to make SSAS (SQL Server Analysis Services) available to users of Microsoft Excel.
Microsoft SQL Server Analysis Services is one of the most popular analysis software solutions for medium sized businesses in the world.
It’s important to note that “project Gemini” was initiated by someone from the SSAS team, not the Excel team, at Microsoft.
“Project Gemini” was released as Power Pivot in 2009 as a free add-in for Excel. The goal was achieved in that Excel users now had access to the power of SSAS, but in an interface that was familiar to them.
Power Pivot gained popularity very quickly and it eventually became the centerpiece of the entire Microsoft BI strategy.
This strategy eventually landed them in a very prominent position on the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms:
After Power Pivot, Microsoft added Power Map, Power Query, and Power View to the stack. All these solutions were built as add-ins for Excel.
More recently, Microsoft launched Power BI which makes the stack available in your web-browser or on your phone, without the need for Excel. You can also query your data by typing sentences and the software will create a graphical report for you based on what you typed.
These tools basically amount to “Excel on steroids” and they have taken what was already the world’s most popular Reporting and Business Intelligence software and made it 100 times more compelling.
I personally have been using these “power” tools since 2012 at the company I work for and have found them to be better than any reporting software that I have ever worked with.
Acumatica and Excel with Power Pivot, Power Query, and Power BI
Most ERP packages for the mid-market have their own proprietary reporting tools.
Or, you can pay for a 3rd party solution.
By connecting directly to Excel, Acumatica just picked up 30 years of reporting experience (Excel was introduced in 1985).
The Acumatica reporting tools just went from a bicycle (Report Designer) to a Lamborghini (Excel with Power Pivot, Power Query, and Power BI) overnight.
My Personal Direction
When I started PerpetualAcumaticaLearner.com in April 2013, I was just interested in learning about Acumatica in general.
The idea to connect Acumatica to Excel first occurred to me in January 2014 when I was trying to do it with Power Query (click here), but Gabriel from Acumatica had a much better idea to use OData (click here).
When I moved things here to TimRodman.com in January of this year, I did it with the expectation that Acumatica would eventually be connecting to Excel with OData.
I had become very excited about Power Pivot because of what I experienced at the company that I work for. For more on that experience, click here.
I figured I would learn Report Designer while I waited for Acumatica to implement OData.
I just never thought that they could implement it so quickly. I think that part of the reason why they were able to do it so fast was because they partnered with the Microsoft engineers. This is a pattern with Acumatica that I have noticed in that their development team doesn’t operate as a closed unit. They aren’t afraid to reach out and build on top of other solutions. This is a philosophical difference between a synergistic web-based vendor like Acumatica and the siloed approach that most traditional ERP vendors have become enslaved to. And it’s another reason why I think Acumatica is more of a platform than just an application.
I have already used OData to deploy Excel reports for two of my clients and it works great.
The Future of This Website
I think that Report Designer is only for 5% of the reports that a company needs. It’s great for things like Checks, Purchase Orders, Sales Orders, Invoices, Customer Statements, etc.
As for the other 95%, I believe that Excel with Power Pivot, Power Query, and Power BI is going to become the go to solution.
With this in view, my posts going forward will focus on Excel with Power Pivot, Power Query, and Power BI. However, I will continue my review of the standard reports on Thursdays because they give me insight into DAC which is critical to building the Generic Inquiry screens that the Acumatica OData connections depend on. But more on that in future posts.
Suffice to say, I am very happy about the OData announcement today, and thrilled that it happened on the 2 year anniversary of my first post (click here).
There are a lot of new possibilities to explore and it’s going to be a fun adventure.
Please feel free to comment with your thoughts and let’s keep learning together.
The GL Recurring Transactions (GL641500) report in Acumatica shows your recurring transactions that have been setup in the Recurring Transactions (GL203500) screen.
Here are some screenshots from the report:
Great news for Acumatica fans. There is a new website for Acumatica University and it’s open to everyone.
Joel Gress has a nice write-up about it (click here).
To access, just go to AcumaticaOpenUniversity.com.
I have to say that this move by Acumatica doesn’t surprise me at all. It is consistent with the “we have nothing to hide” company culture that I have been observing since April 2013.
Acumatica has an “open” philosophy. You can open any screen and see the code behind it without having to pay for source code. You can take the application and deploy it wherever you want: SaaS, Hosted, or On-Premise. And you can try out an actual installation of the latest version at TryAcumatica.com without having to put in any of your contact information.
This kind of culture is Engineering-led, not Marketing-led and it indicates to me that Acumatica is planning on staying in this for the long haul. A product-focused organzation takes longer to gain momentum, but does very well in the long run because the product begins to market itself.
Acumatica is a company of geeks who focus their attention on getting the core product perfect so there is a strong foundation to build on when implementing in a customer’s unique operating environment. Acumatica appears happy to focus on building an awesome platform and leave the vertical solutions to their growing network of ISV partners.
Things have definitely started off on the right foot with Acumatica
Now we just need to get an Acumatica User Group in place. The group should be independent of Acumatica in every way so we can ensure that this “open” culture continues even after Acumatica becomes the dominant player in mid-market ERP and the money starts rolling in.
Robert Lightner is working on creating such a group (click here) and I personally think that this is one of the strongest signs that Acumatica has a bright future ahead.
The GL Register Detailed (GL621000) report in Acumatica shows your all the batches in the system that have been Posted or Released, regardless of the module.
The format of the report is almost exactly the same as the GL Edit Detailed report, except the GL Edit Detailed report shows batches that have not been Posted, Released, or Voided whereas the GL Register Detailed report shows batches that have been Released and Posted.
Here are some screenshots from the report:
Would anyone out there be interested in a comprehensive Acumatica Report Designer Training Course?
I’m thinking about creating one…
The training course would be comprehensive, covering everything from beginner topics like “creating your first report” and “creating fields that use calculations” to advanced topics like “determining the proper DAC relationships” and “delivering embedded reporting with subreports and hyperlinks”.
The GL Register (GL620500) report in Acumatica shows your all the batches in the system that have been Posted or Released, regardless of the module.
This is the opposite of the reports that we looked at the past few weeks because it shows batches that HAVE been Posted or Released, not those that HAVE NOT been Posted or Released.
Here are some screenshots from the report:
The GL Edit Account Distribution (GL611000) report in Acumatica shows your GL batches that have not been Posted or Released.
It is similar to the GL Edit Account Distribution (GL611000) report that we looked at last week (click here), except that it shows the Batches underneath each Account instead of the Accounts underneath each Batch.
Here are some screenshots from the report:
The GL Edit Detailed (GL610500) report in Acumatica displays your GL batches that have not been Posted, Released, or Voided.
It is exactly the same as the GL Edit Summary (GL612001) report that we looked at last week (click here), except that it shows the individual debits and credits underneath each batch.
Here are some screenshots from the report:
The GL Edit Summary (GL612001) report in Acumatica displays your GL batches that have not been Posted, Released, or Voided.
You have the option to include batches that are on hold if you want.
Clicking on the hyperlink in the Batch Nbr. column will take you to the Journal Transactions (GL301000) screen for the batch that you clicked on.
Here are some screenshots from the report:
The GL Budget Edit (GL612001) report in Acumatica shows you the amounts that you have budgeted for each GL Account.
You have to pick a Ledger and a Financial Year that you want to view and then the report shows you the following:
- Amount – The total amount that has been budgeted
- Distributed Amount – The amount that has been distributed to financial periods (most commonly months)
- Released Amount – The amount that has been released through the Release Budgets (GL505510) screen so that it is approved
You can also click on the link in the Account column to be taken to the Budgets (GL302010) screen for that Account.
Here are some screenshots from the report:
The Transactions for Account (GL633500) report in Acumatica allows you to see all the individual Journal Transactions that posted to a specific General Ledger Account.
The General Ledger Account is required and you can only pick one so you can only look at one account at a time. If you want to look at more than one account at a time, you can use the Transactions for Period (GL633000) report which we looked at last week (click here).
The report is grouped by year so you can see a transaction total amount for each year.
You can also click on the link in the Batch Nbr. column to be taken to the Journal Transactions (GL301000) screen for that batch.
Here are some screenshots from the report:
The Transactions for Period (GL633000) report in Acumatica allows you to look at each General Ledger account and see all the individual Journal Transactions that posted to a financial period range that you get to specify.
You get to see all the journal entry batches that were posted during the period range so you can easily explore the detailed general ledger activity. Accountants like this kind of stuff.
You can also click on the batch number to be taken to the Journal Transactions (GL301000) screen for that batch. Note: the screenshot below is only showing batches that originated in the GL module, but the report will show you any batch, regardless of the originating module.
Here are some screenshots from the report:
The Trial Balance Detailed (GL632500) report in Acumatica is very similar to the Trial Balance Summary report that we looked at last week (click here), but with one additional column added.
The main difference is that the Trial Balance Detailed report shows a separate row for each Account/Subaccount combination whereas the Trial Balance Summary report only shows a separate row for each Account. Bottom line, the Trial Balance Detailed report is more, um, DETAILED.
Even the Account hyperlink field functions the same as it does in the Trial Balance Summary report by taking you directly to the Account Details (GL404000) generic inquiry screen. The only difference is that it pre-populates both the Account and Subaccount fields instead of just the Account field. When you leave the Subaccount field blank in the destination generic inquiry screen, it shows all subaccounts for the Account selected.
Here are some screenshots from the report:
The Trial Balance Summary (GL632000) report in Acumatica lets you view your Balance Sheet for a financial period of your choice. A Balance Sheet, or Trial Balance, is a very common report in an accounting system. You see the balance at the beginning of the period, any debits and credits that happened during the period, and finally the ending balance for the period.
When you first run the report, you need to enter a Ledger and a Financial Period, but you can leave the Branch empty which will give you a Consolidated Balance Sheet across all of your branches. This is pretty cool because most ERP systems require special software if you want to do consolidated reporting. And although you can’t do consolidated reporting across multiple Companies in Acumatica Report Designer (at least not that I know of), Branches are very powerful and can often replace the need for a separate Company in Acumatica.
You’ll notice in the second screenshot below that the Account field is a hyperlink. Clicking an account hyperlink will take you to the Account Details (GL404000) generic inquiry screen with the Account field pre-populated.
Here are some screenshots from the report:
UPDATE June 25th, 2015 – I have decided to stop this two year journey. For more on why, click here.
I have decided to embark on what will likely be a two year journey to explore the out of the box reports in Acumatica.
Each week I will post a review of one of the existing reports in Acumatica. This will be a long and sometimes tedious process, but I expect to learn a lot from it.
If you have had to manage an ERP system for a company, then you know how difficult it can be to keep track of your customized reports. While you are making changes to a report, you always think that you’ll remember what changes were being made and why. But as time rolls on, month after month, and year after year, the reports in your ERP system can become chaotic.
When was that report changed last? Who requested the change? What changes were made? Was this report even customized or is it the stock report?
Or maybe you need to roll the report back to a prior version, some version in between the original out of the box version and the most current version. Did you save an archived copy of the report every time you made a change?
One of my favorite features in the Acumatica Report Designer program is the ability to save a report as a new version.
When saving a report, all you have to do is click the Save as new version box, then put something in the Version description field:
Have you been wondering if anything has changed with Acumatica Report Designer in Acumatica 5.0?
Well, I have. So, I decided to install Report Designer 5.0 alongside Report Designer 4.2 to see if I could spot any differences. Then I opened them both next to each other:
If your user has the ReportDesigner role assigned to it, things can get a little confusing.
First, check to see if you have the ReportDesigner role by going to Configuration –> User Security –> Manage –> Users (SM201010) and checking to see if the ReportDesigner role is checked like this:
Last week we covered how to login to Acumatica Report Designer (click here) so you can retrieve a report directly from the Acumatica ERP application.
This week we’ll cover how to build a simple report from scratch that will display the list of our customers and run it from the Acumatica application.
Step 1: Open the Acumatica Report Designer application
After it opens, you should see a blank page like this.
When I modify a report using the Acumatica Report Designer, I personally like to open the report directly from the server. Then I don’t have to download a copy and then upload my new version.
However, when I first tried to use the File -> Open From Server… option, which looks like this:
There is a big movement in the ERP market these days involving Cloud ERP vs On-Premise ERP and every analyst out there is predicting big things for Cloud ERP. It seems like there are new Cloud ERP vendors popping up every day. Even the traditional ERP vendors are building Cloud products. Of course, the traditional vendors are pretending that they are moving their existing product into the Cloud, but the reality is that they have to build an entirely new product because the underlying technology framework is so different.
Most of the analysts out there sound like Buzz Lightyear: “to the Cloud and beyond.” There seems to be infinite expectations and grand projections about where things will be in the next 5-10 years.
But where are we today?
Things are beginning to take shape with my new Acumatica Reporting business. I have begun work on a new website and already picked up a customer in need of some Acumatica reports. There is a lot of opportunity here and I’m very excited.
The new website and blog will be located at TimRodman.com
Up until today, this was my approach towards Acumatica:
Last week was the 1.5 year anniversary for this blog and my personal Acumatica learning journey (click here). That milestone, coupled with a rejuvenating two week vacation, caused me to consider where I would like to go next.
Up until now I have considered Acumatica to be a hobby. But, while on vacation, I decided to take this thing to the next level: from “Acumatica Hobby” to “Acumatica Business”…
I had a great time on vacation in Southern California the past two weeks.
We visited my side of the family in San Diego, then we stayed in San Diego for a few more days for my brother-in-law’s wedding. The wedding was in Mission Beach so we rented a beach house right on the boardwalk and stayed with my wife’s siblings and their kids. We had 8 adults and 8 kids in the house and it was a blast. Here we are eating dinner in the living room with the extended family:
No post this week. We’re back home in Southern California for a wedding and to visit family.
The title of this post is actually a joke because Acumatica doesn’t utilize Windows Terminal Server.
But I was reminded of that fact recently when someone at work was getting a windows error upon trying to login to our ERP software (Sage 500). The problem was that we use the remote application feature in windows terminal server so that when a person closes Sage 500, it doesn’t actually close completely. The windows session continues to run in the background.
Currently, you can’t build Microsoft Excel reports in Acumatica. However, if Acumatica implements OData in the future, this will become a possibility.
In the meantime, I created a simple Excel report as an example of the powerful things that you can do with Excel. This report was built for Sage 500 and is being sold through The Report Store (click here), but it would look exactly the same for Acumatica if the OData option is included at some point.
So, maybe this is a glimpse into future Acumatica? I hope so
Here is a quick 5 minute video showing the report:
This week I started the Distribution courses in Acumatica University and I’m having a great time! It’s nice to be out of the technical courses and back into learning more about the application functionality.
The first course up in Distribution is called D100 Inventory Management: Basic and it walks you through the Inventory module.
I’m still making my way through, but I couldn’t help notice that the LIFO inventory valuation method seems to be missing in the Stock Items (IN202500) screen. What’s going on?
I added a new button to the blog called Ask a Question. My hope is to receive questions highlighting real-world problems that people are trying to solve with Acumatica. Nothing helps me learn better than real-world examples. So fire away! I can’t promise an answer, but I can promise a response, maybe even in the form of a blog post.
Questions help take me down the byways on my Acumatica road trip (click here).
Last night (technically early this morning) I finished the S400 Introduction to Acumatica Technology course. This course mainly focused on two things: the Customization Engine and Web Services.
It feels a lot like climbing into the cockpit of an airplane. There are lots of options to choose from.
Last week (in part 1 of 2), I covered why I think BizNet BizInsight and Solver BI360 are important for Acumatica (click here).
I currently work for a company that uses Sage 500 ERP and we were looking for an Excel-based reporting solution (BizNet BizInsight and Solver BI360 have the same functionality for Acumatica that they have for Sage 500 ERP). A colleague and I sat through demonstrations of both products and asked lots of questions. Based on that process, we chose Solver BI360 over BizNet BizInsight for our company. Here are 5 reasons why.
Note: I would love to hear comments from others who have compared these two products. This post is in no way intended to be a comprehensive comparison, just my two cents.
In this post (part 1 of 2), I’d like to cover why I think BizNet BizInsight and Solver BI360 are important for Acumatica. Next week, I’d like to give my opinion on which one I personally think is better and 5 reasons why.
First, some background
Last week was a big week for Acumatica news because of the annual Partner Summit.
Here is a quick recap of 15 recent Acumatica news stories that I am aware of:
Recently I completed three courses in Acumatica University: S125 Document Management, S200 Integration Services, and S300 System Management.
Only one more course to go (S400 Introduction to Acumatica Technology) before I complete the Application Engineer badge. Although I don’t like to focus too much on the system maintenance stuff since I personally find it boring, it’s still good to go through the courses so I can at least be familiar with things. Once I finish the Application Engineer badge, I’ll be able to move onto the Distribution courses which I think will be a lot more interesting.
Here are some things that stood out to me in these three courses (note: Some of these features have been improved in Acumatica 5.0 which was announced earlier this week at the Acumatica Partner Summit). [Read more…]
Acumatica handed out some awards last night at their partner summit and they created a special category for this blog. Click the picture below to see the full announcement. So far, this blog has landed me a bottle of wine (click here) and now an award. This whole Acumatica thing is purely a hobby at this point for me, but it’s still very nice to get recognition. Thanks Acumatica!
I’m looking forward to see what news comes out of the annual Acumatica Partner Summit which takes place this week in Denver, Colorado.
Continuing the “Real-World” theme from last week (click here), I wanted to cover another real-world Acumatica situation. This question came to me through the Contact page here on the blog (click here). That’s right, this came from a person who is actually using Acumatica at their company! Here’s the question and a solution. I say a solution because I’m sure there is more than one way to tackle this.
Note: I’ll keep this shorter than usual because I’m writing this in an empty living room, tired from a long evening of packing. 90% of our stuff is in the moving van that is currently sitting in our driveway. We’ll load up the remaining 10% in the morning and head to our new house in Columbus (click here).
How do I prevent users from changing the status of a customer?
Basically what we want to do is make the following field read-only for certain users.
Recently at my job I was confronted with a challenge to create a new business process for capturing shipping information and integrate that process into our ERP system. I’d like to describe the solution that I came up with and how I think it would have been easier to do with Acumatica.
First, Some Background
I work for a company that makes machines that dig tunnels. We are an old-school company with a proud owner who takes care of his employees and employees who are proud to work here. And we make a product that everyone here is proud of too.
Basically, our machines are used to dig tunnels all around the world. Here are a couple of pictures as examples of our machines. The machine in the first picture was used to dig the Chunnel from England to France. The machine in the second picture was used to dig a hydro tunnel near Niagara Falls.
Acumatica has taken a very aggressive marketing approach over the past couple of years with some crazy stunts, but I guess that’s what you do when you are a small company looking for recognition. Last week at Sage Summit, the annual Sage conference in the US, Acumatica once again took an “in your face” approach by offering free coffee at the event.
This time, however, Sage fought back. Here is the news as told by Bob Scott:
Last week I wrote in general about the Acumatica email engine (click here). This week I’d like to go further and cover something more specific related to email in Acumatica. Acumatica can basically act as an email client, just like Microsoft Outlook, Gmail, Yahoo, or other popular clients. It just needs to connect to an email server. In order to test out the Acumatica email functionality, I think it’s easy to use a Gmail account. Why? Because if you don’t have one you can easily create an account on Gmail.com without having to call your IT department.
Once you have a Gmail account, setting it up in Acumatica is actually very easy. How easy?
Step 1: Navigate to Configuration –> Email –> System Email Accounts (SM204002)
Acumatica isn’t just a flashy new ERP product with a nice user interface and fancy dashboards. There are a lot of substantial features under the hood and the email engine is one of those features. Note: this post is an expansion of the last point from a previous post (click here).
First, Some Thoughts On Email
I was planning to do a post this week on the email features in Acumatica, but the announcement by Lebron James last Friday was such HUGE news here in Cleveland that I thought this post would be better. I’ll pickup the email features post next week. [Read more…]
For the past three months, I have been posting on Mondays and Tuesdays with links to other Acumatica-related articles. I was following the pattern of Mark Polino who is one of the prominent bloggers in the Microsoft Dynamics GP community. Mark regularly posts links to other blog articles on his blog (click here). [Read more…]
For those who don’t already know, you can hide columns in Acumatica inquiry screens. This feature is a perfect example of why there is always more to learn with Acumatica. I’ve been using Acumatica for over a year now, but didn’t notice the icon until recently.
Personally, I don’t like what Larry Flynt stands for and I don’t think that the empire he built is anything to be proud of. But, he does have a recognizable name and I thought it was interesting that his company appears to be using Acumatica. At least that’s what I gather from the job that was posted today on Indeed.com.
The first part of the job description makes it clear that it’s a position at Flynt Management Group:
Acumatica allows you to attach input validation preferences to fields. This is very useful when you can’t use a drop-down list field for your scenario so you need a text field, but you want to put some amount of control on the text field. Good data entry is very important in an ERP system. The idea is that you can use the data in your ERP system to make critical business decisions. However, if the data isn’t very clean, you might waste a lot of time cleaning the data before it can be usable. Input validation helps to keep your data clean as it is getting entered into the system.
This kind of thing is probably best demonstrated by example. [Read more…]
Bob Scott recently released his 2014 Top 100 VARs. This is an annual listing of the top mid-market ERP resellers (based on annual revenue) which gives you a very good idea of which mid-market ERP products are the most popular.
Regarding the top 100 VARs, the cloud products aren’t yet dominating the list, but they are definitely gaining traction: [Read more…]
Sergey reminds us that Acumatica supports the latest version of Microsoft SQL Server (2014). This is the kind of stuff that you get when you go with a modern ERP platform like Acumatica. They support other modern tools like the latest version of Microsoft SQL Server. For those who don’t know what Microsoft SQL Server is, it’s the engine that powers most of the mid-market ERP systems. SAP and Oracle (the top ERP systems) have their own database engines, but most everyone else uses Microsoft SQL Server to store their data.
The Field-Level Audit feature allows you to track changes to any field on any screen (I haven’t actually tried this on all fields on all screens, but this seems to be the case). This can help your company comply with Sarbanes-Oxley requirements, or just give you better visibility into change log history on some of your data.
Where I think this feature is especially useful is on screens that are used to maintain your master level records. Master level records are things like Customers, Vendors, Purchase Orders, Items, etc. Many times it’s important to know what information changed on a record and when. This is where Field-Level Auditing can help.
How do I use it?
I’m trying out a new WordPress Theme here on the blog which will hopefully make things look a little nicer both on computers and on mobile devices.
A few months ago, Christian Lindberg posted about Acumatica and the Millennial Generation (formerly known as Generation Y).
It reminded me of the Esurance commercials, “born on the web, raised by technology” (click here). I like his point that “Acumatica’s technology was built from scratch in the millennial years.” Acumatica is not an older product getting a Facelift or a Botox injection.
Acumatica was born less than 10 years ago: it’s brand new. Yes, that means it isn’t as mature as the older ERP products out there, but it does have the strength and vigor of youth, born in an age where computer applications are built for web browsers, not for operating systems.
I barely squeaked into the millennial generation (I was born in 1981). So, I’m old enough to have had early childhood years full of playing outside (year-round since I grew up in San Diego). We rode bikes and roller blades, played roller hockey and football in the street, and played baseball in an old neighborhood dirt lot using a ball made of old socks and duct tape. We went camping, fishing, played in the sand at the beach during the Summer, played in the snow in the mountains during the Winter, and generally went around without wearing any socks or shoes. [Read more…]
Sergey has a new post up about how to reuse a project lookup field. I don’t really understand all the code, but I do understand that he is adding a project lookup field to a screen that didn’t have a project lookup field. And, in order to do this, he is taking the code from an existing screen. Also, there is something about this being possible in the latest version of Acumatica (version 4.2). Again, I don’t completely understand what is going on here, but it’s encouraging to see that you can add a project lookup field to a screen that didn’t have one before. I would LOVE to be able to do this in Sage 500 ERP, but, alas, it’s not possible unless we were to pay tens of thousands of dollars to buy the source code. It’s definitely much simpler in Acumatica.
Recently I passed the S100 Installation and Site Management and S120 Configuration Settings courses.
Personally, I’m not a big fan of software installation because I find it boring, but it’s an important part of implementing Acumatica so it needs to be done correctly. It’s also good to be aware of the different deployment options available since Acumatica allows you to choose.
I found the configuration course to be much more interesting because it introduced some new features that I’d like to learn more about in the future. Here is a quick recap of my takeaways from the two courses.
I’m pretty much done going through the archives on Zaletskyy’s blog (click here). Going forward though, I’d like to continue to highlight interesting posts as he makes them available. Here is a recent one regarding Acumatica and Microsoft Visual Studio. Personally, I prefer to focus on the power user features rather than the super geeky technical stuff, but there is definitely the need to “open the hood” if you need to do some serious customization and that’s what Microsoft Visual Studio offers you. The nice thing about Microsoft Visual Studio is that it’s a very popular development tool which a lot of developers are already familiar with. This is a further illustration of the Microsoft Stack point that I’ve mentioned before (click here).
Christian Lindberg has a new post up about the Acumatica Cloud XRP platform. I’m not really sure if this is anything new or if it is just a new marketing way of describing the Acumatica platform that has always existed. This press release seems to indicate that it is indeed something new, but I’m still not sure. It will be interesting to hear more about this as time goes on.
This post is just a random thought about Acumatica and a historical event that I remember learning about as a kid. I remember learning about the “Golden Spike” in my American history class when I was in high school. The “Golden Spike” is the ceremonial final spike which was driven to join the rails of the First Transcontinental Railroad across the United States connecting the Central Pacific and Union Pacific railroads on May 10, 1869 in the Utah Territory (click here for the Wikipedia article).
There are a few things about this historical event that stand out to me: [Read more…]
This morning I got stuck in the waiting room at the Doctor’s office so I pulled out my iPhone and went over to Acumatica University to get in a little Acumatica learning. Acumatica University actually works very well on an iPhone. You can see all the courses, all the .pdfs/videos for a specific course, and even take an examination. The only downside is that it’s hard to see the details in the videos since the screen is so small.
It took 30 minutes after I saw the doctor before someone came in to discharge me. Not sure why it took so long (maybe just the inevitable lethargy that Obamacare is injecting into the American medical system), but I can’t complain too much because I was able to keep my Acumatica learning going.
Then I realized, “hey, not only could I be learning Acumatica, I could also be using Acumatica!” Since Acumatica is 100% web-based and works wonderfully on mobile devices, I could just as easily have been finishing my timesheet for the week, filling out an expense report from a recent business trip (and uploading the receipt images since I always scan them immediately with my TurboScan iPhone app instead of keeping the paper copy), or reviewing a customer quote that a salesperson put together this past week. Full access to my company’s front and back office information, all on my phone. That is, only if my company used Acumatica and if I had been granted access to that information in Acumatica.
Of course, this kind of mobile access can be a bad thing. It’s probably not a good idea to learn or use Acumatica during your kids’ baseball games, while crawling along the 405 freeway in Los Angeles at rush hour, or when you’re at a restaurant on a first date and you’ve run out of things to talk about.
There are some situations though where you are stuck somewhere and mobile access can be a good thing:
- You’re on Jury Duty, stuck in a room all day with nothing to do.
- Your Flight is Delayed and you just heard the voice over the speaker announce that it will be delayed at least another 2 hours (click here).
- You took your car in for an Oil Change, but decided to take advantage of the discount that they offered on all the preventative maintenance stuff that was offered (you’ll be waiting for a while).
- You are at the Doctor’s Office for a checkup!
Ok, I give up. Exactly one month ago (click here) I attempted to abandon the Acumatica Data Access Classes. Now, at the time I wasn’t sure if it would work out, but I thought I’d at least try it temporarily. One month later and I’ve given up. I feel like I need to go to a chalkboard and write “Don’t abandon the Acumatica Data Access Classes” over and over again.
I recently discovered Yuriy Zaletskyy’s blog (click here). He is a developer and writes about Acumatica from a technical perspective. I took some time and skimmed through all of his posts. For the next few weeks I’m going to highlight the ones that I found interesting.
For this week, here is a post about how to create a brand new screen in Acumatica. This is over my head right now, but I hope to come back to this post in the future and get comfortable with it so I can create my own screens in Acumatica when the situation calls for it. Anyways, check out the post:
Sergey has a great post up about Acumatica branch security. Branches are a part of the multi-company setup configuration strategy in Acumatica (click here). The nice thing about branches is that they aren’t just accounting buckets because they also carry security along with them. So you can have multiple locations or business units and you can keep your users from seeing things outside of their respective location or business unit. It seems to me that situations which used to required you to setup multiple companies in older ERP applications only require you to setup multiple branches in Acumatica which allows you to keep things nicely organized in one company.
Sergey also touches on Multitenancy. I only learned recently that the SaaS version of Acumatica supports Multitenancy (click here). If you’re like I was when I first heard this, you’re probably saying, “what’s Multitenancy?” I think it’s a pretty new word because my spell checker doesn’t even recognize it (maybe I need to add a dash?). If you want to know more, this Wikipedia article on Multitenancy is helpful to get you up to speed (click here). As I see it, you have to be pretty confident about your security setup if you’re going to offer customers Multitenancy. Acumatica must be confident!
Anyways, click below for Sergey’s post:
After taking a detour last week and exploring how to add new fields to existing screens in Acumatica (click here), I decided to return to the task of learning to create Excel reports that point directly to the Acumatica database. I began with this idea three weeks ago (click here) and continued two weeks ago by taking some baby steps to create a very simple Excel report (click here). This week I decided to pickup with where I left off a couple weeks ago and attempt to continue reproducing the Vendor Summary (AP401000) screen in an Excel report.
Continuing With Baby Steps
I ended the post a couple of weeks ago (click here) with the following report in Excel:
Jean Gea has a new post up on the Acumatica blog with a nice simple overview of how multi-company works in Acumatica. I blogged about this last year after taking the F300 Inter-Company Accounting (V3.0) course (cilck here). It looks like what they used to call companies, they are now calling entities. Acumatica is definitely in a good position to appeal to companies with multiple distinct business units because of how easy it is to deploy web-based technology and because of the strong multi-language support that Acumatica offers.
Sergey gives us a tip on how to overcome the basic authentication error that you get when you try to refresh an exported Excel file over a connection that is not using SSL. This is a common occurrence when you are giving a demonstration of Acumatica because there is a high likelihood that you won’t be using a secure server for the demo.
Sergey’s post interests me because it’s related to the Excel Export feature in Acumatica. I have also been interested in this feature recently and have been trying to find a better way (click here) to pipe Acumatica data into Excel. While Sergey’s post doesn’t solve my current problem, it does comfort me to know that others are interested in this feature too.
Acumatica has a new blog post up. I really like this post because I didn’t know that such a matrix ranking even existed for mid-market ERP products. This is good stuff! I downloaded the entire 13 page report and read it very carefully. The report includes a write-up on each vendor included in the matrix.
A few of my random thoughts:
Update (October 4th, 2016): This post was written on Acumatica 4.2, but things have changed since Acumatica 4.2. To see this done in the latest version of Acumatica, checkout this post by Mark Franks.
Michael Coman provided another great Acumatica learning experience for me. The last one was related to creating a Project Quote (click here). This time he responded to a post on this blog and asked a question about adding new fields to existing screens in Acumatica (click here).
At first I didn’t want to get distracted from what I’m doing with reporting in Microsoft Excel directly on the Acumatica database, but eventually I realized that a real-world example is too good to pass up.
It’s just a lot more fun when you are learning something that you know will actually be useful to someone. So, I put on my PAL hat and went to work learning how to do this. The result is the video at the bottom of this post.
Sergey has a new post up about how to access your Acumatica database from outside your network. This is interesting to me because I’m currently experimenting with making Excel reports that point directly to the Acumatica database (click here). Sergey’s trick would be needed if these kinds of reports were to be implemented in a production environment.
Richard Duffy put together a nice video about how to publish your implementation of Acumatica as a Google Chrome App. This isn’t anything fancy from a technology standpoint, but I think that it provides a nice user experience and has the “cool factor” which is very important.
I personally am a big fan of using Google Chromebooks (click here) for internal training. It’s very easy to bring a stack of ten Google Chromebooks into a conference room, setup a projector for sharing your screen, and conduct a simple one hour training session. It makes it easy to have training sessions on a regular (maybe weekly) basis. If you had to carry in ten computers, ten computer monitors, ten keyboards, and ten computer mice then you would be much less likely to conduct regular training sessions. The other advantage is the cost. I just checked Amazon.com and they are selling Google Chromebooks for $250 (click here) which is very reasonable.
Google Chromebooks make it easy for you to conduct simple regular training sessions in your organization and regular training sessions are very important to ensuring a successful Acumatica implementation.
Here is the video from Richard:
I discovered yet another Acumatica blog out there. This is great for the Acumatica community.
Zaletskyy has a great post about what the screen IDs mean in Acumatica. I did a post a while ago about how great screen IDs are (click here), but I didn’t realize that there was a method to the numbering.
Acumatica has a blog post up about what differentiates Acumatica from the other cloud ERP options out there:
I haven’t gone through the P200 Project Management Configuration course yet. I’m just poking around the screens in the Projects module.
What, No Phases?
In the Project Accounting module in Sage 500 ERP, I’m used to having three levels: Project, Phase, and Task. You have to charge costs to a Project and to a Phase, but the Task is optional. However, every Task must belong to a Phase. The Phase is kind of like the parent to the Task.
However, in Acumatica I can only see Tasks, no Phases. So, I want my Phases!
It has been almost a year since I started this blog and I wanted to revisit what I mean and what I don’t mean by the PAL acronym (Perpetual Acumatica Learner).
My first post on the topic of PAL (I called it APAL at the time) was mainly to describe the difference between a learner and an expert.
However, recently I was feeling pressure from the word “perpetual” because I haven’t been spending enough time to learn Acumatica. I was beginning to think that I shouldn’t call myself a Perpetual Acumatica Learner anymore if I wasn’t spending every waking hour on Acumatica learning.
But, it’s just not possible to be learning Acumatica all the time. My wife and I have two kids, a mortgage, and a car payment. I also have a full-time job during the day. So, it’s just not possible to be learning Acumatica all the time.
What is possible is to always approach Acumatica as a learner. That I can do.
So, I am comfortable to call myself a Perpetual Acumatica Learner. When you are a learner, there are no such things as stupid questions or dumb ideas. Every time I sit down at my computer to spend time with Acumatica, my goal is simply to come away having learned something new.
It doesn’t matter to me where I am in the learning process and it doesn’t matter how fast I am learning. Others may pickup things faster than me and require less time to learn a new thing like Acumatica. But, when I spend time to learn Acumatica, I am simply content to have learned something new.
I am determined to take the approach of a tortoise.
So what about you?
If you are also learning Acumatica, I hope that you will also take the way of a Perpetual Acumatica Learner.
Even if you somehow reach a point where you know everything there is to know about Acumatica, there will always be different ways to apply that knowledge because every company is different. That is what makes the ERP world so interesting.
There is no such thing as a standard cookie-cutter implementation. Every company has unique requirements and many times those unique requirements are what give them a competitive advantage in the marketplace.
You may find yourself in a company that is implementing Acumatica and you are afraid of all the changes that are taking place. Don’t be afraid, just do your best to make progress, one step at a time.
You can ignore all the fast-talkers and those who try to make it seem like they know everything even when they don’t.
Just keep your head up and know that you are not the only one on this Acumatica learning journey. There are others out there and there will be more and more as Acumatica gains popularity.
I still have a lot to learn about Acumatica and I hope that will never change. No matter how much I learn, I will always realize that there is more to learn.
So, it’s time to settle into a nice long marathon pace and continue on as a Perpetual Acumatica Learner. Who’s with me?
Recently I was working on a Purchases Clearing reconciliation as a favor to the Controller at my company. It wasn’t a very fun project, but we needed to make sure that the Purchases Clearing account agreed to the General Ledger so our auditors would be happy. I was asked by the Controller to help out and so I went to work.
In Sage 500 ERP, the individual debit and credit transactions to the General Ledger maintain a link back to the source transaction that they came from. For example, if you post an Accounts Payable Invoice, the individual debit and credit transactions that get created in the General Ledger maintain a link back to the Accounts Payable Invoice in the Accounts Payable module. This allows you to go into a screen that lists General Ledger transactions, select an Accounts Payable Invoice debit/credit, and click a button to have Sage 500 ERP automatically open the related invoice screen. It’s a nice feature and I thought that I would be able to leverage it to help me reconcile the Purchases Clearing account to the General Leger. HOWEVER, I quickly discovered that the link back the Accounts Payable Invoice is only to the invoice itself, not to the specific line on the invoice. So, if there were five lines on the invoice which generated five General Ledger transactions, each of the five General Ledger transactions would have the same reference value, that of the Accounts Payable Invoice (but not the specific line).
Eventually, I had to group my Purchases Clearing transactions by day and compare them to the General Ledger (also grouped by day) in order to do the reconciliation. It would have been nice if I had the link back to specific lines because I could have reconciled things by Purchase Order Line level instead of by day. But, alas, the world of ERP isn’t perfect and many times you are forced to choose the best option available, even if it isn’t the most elegant.
Now, this is a post on an Acumatica blog so I should probably say something about Acumatica. This little reconciliation project that I did got me wondering how I would have approached this in Acumatica. So, I took a look at the General Ledger table that holds the debits and credits. I was very pleased to find TWO relevant columns. Not only does Acumatica store the header information (in the GLTran table). It also stores the line information (in the GLTran table). So, in my example above, it would not only have stored the Accounts Payable Invoice, but also the specific LINE in the Accounts Payable Invoice that triggered the individual debit/credit to be posted to the General Ledger. I haven’t looked at this specifically for Purchase Order receipts, but it definitely looks promising.
Sergei has a nice post up on geeky hardware recommendations. This is especially useful if you are running Acumatica on-premise.
Here is a good article pointing out that the cloud hype hasn’t reached the ERP world yet and maybe never will. Acumatica is positioned for this by allowing their customers to buy and host Acumatica on-premise. NetSuite, on the other hand, only allows the SaaS option.
This reminds me of the Western Digital commercial that I posted a link to a few months ago:
Michael Coman started a discussion on how to create a Project Quote form in Acumatica over on the Acumatica ERP Software User Group. I came up with something very simple which hopefully will be a good start. This was my first experience actually doing a real report/form with the Acumatica Report Designer. It definitely has a long way to go before becoming a user-friendly report designer like Crystal or SSRS. But, it is pretty flexible and, once you get the hang of it, it’s not too bad.
First I opened Acumatica Report Designer on my Windows Start Menu.
Sergey has a nice post up about creating separate numbering sequences for data that is being imported from other systems like Oracle, etc.
Jon Roskill, the new CEO at Acumatica, posted something on the Acumatica website:
There are two things that he said which stood out to me that I completely agree with and have noticed about Acumatica since I have been following it for about a year now: [Read more…]
I thought it might be interesting to periodically check how the Acumatica groups on LinkedIn compare to some other ERP groups on LinkedIn. I looked up 18 different ERP groups on LinkedIn (including Acumatica) to compare their number of members and number of discussions posted this month in order to gauge how large and how active they are. Now, this is not a very accurate method to try and determine how large the user base is for these ERP products and how active they are, but I think it’s still an interesting thing to track. See graphics below.
Some things to consider:
- A large group membership doesn’t necessarily mean that there are a lot of people still using the product as it is likely that many people won’t bother to leave a group after joining it, even if they haven’t used the product for a long time.
- Acumatica is almost on the bottom, but at least it’s ahead of rival Intacct!
- The Acumatica ERP Software User Group had by far the highest number of discussions this month per member. I’ll be putting a link to this post on this group which will make this number even higher.
- It’s probably a little unfair to include Dynamics AX in this group because it is really a Tier 1 product like SAP and Oracle, but I included it because the other Dynamics ERP products belong in this group.
- NetSuite has a very active group and a large number of members. Let’s see if Acumatica can gain ground this year.
This week I put together a document called Case For Ending Sage 500 Maintenance (click the link to see the PDF document). My company currently pays a very sizable sum of money every year to Sage for the privilege of upgrading to the latest version of Sage 500 ERP. In recent years, however, it has become apparent that this money would be better spent on other things.
In contrast, each new version of Acumatica is providing substantial new features. Even the upcoming release of the lowly version 4.11 is full of many new features (the number of the version suggests that version 4.11 is supposed to be only slightly better than the current version 4.1 and significantly less “cool” than version 5.0 which is due this Summer).
I would gladly recommend that my company pay maintenance on Sage 500 ERP if it continued to deliver substantial new features like Acumatica.
It looks like Acumatica will be adding a customer portal:
This is good news. Customers will now have access to Acumatica University which has become a great repository of Acumatica learning material.
Also, this is another logical step in the direction of Acumatica becoming an ERP provider that is here to stay.
This week I was reviewing the security options in Acumatica and I was pleased to discover that Acumatica offers field-level security on screens. [Read more…]
My last post was about hyperlinks and I’d like to continue on the same theme in this post, but use an actual example in Excel.
Sometimes it would be nice to make an Excel file that contains hyperlinks to the actual screens in Acumatica for each record that you’re viewing. In this example, I’m going to take a simple list of vendors in Excel and add a hyperlink to each vendor ID that will drill into the Vendors (AP303000) screen in Acumatica for that vendor.
Basically, all you need to do is the following:
- Start with an Excel file that has a list of vendor IDs.
- Go to the Vendors (AP303000) screen in Acumatica, pick a random vendor, and click Help -> Get Link in the upper right-hand corner.
- Copy the External Link: value to your clipboard.
- Paste the External Link: into an Excel formula like this:
- Change the hard-coded vendor ID to an Excel cell reference like this:
- Wrap the link in the Excel HYPERLINK function like this:
- Hide the original column in Excel that had the vendor ID and just display the new vendor ID with the hyperlink.
- Clicking the hyperlink for a vendor should take you to the Vendors (AP303000) screen in Acumatica for that specific vendor.
- You will need to login to Acumatica and your login will need permission to the Vendors (AP303000) screen.
Here is a quick video which walks through the steps:
I noticed on the top of Acumatica screens, under the Help menu, there is an option called Get Link. I tried it out and got very excited about the potential. Basically, you can open a record on a screen and then get a hyperlink that will open the screen for you and take you to the exact record that you were looking at.
This got me to thinking about the lowly hyperlink. There are lots of great advantages to using web-based software like Acumatica. When we think of web-based software, things like “always on”, “accessibility”, “bring your own device”, “cloud”, “redundancy”, “scalability”, “SaaS”, etc. come to mind. Not many people think of the lowly hyperlink, but it’s a huge part of why we like web-based software so much. Think about it, when you click on a hyperlink, it takes you to where you want to go, it happens fast, and it’s very reliable. It works on any computer, on any smart phone, on any tablet, and in any application regardless of if you are in Microsoft Office, Apple iTunes, Adobe Acrobat, etc. There is nothing to install or configure, it just plain works. I can put a hyperlink in a Microsoft Word document, a PowerPoint presentation, or a slick looking Marketing PDF file and have the confidence that it is going to work. The only thing that prevents a hyperlink from working is if the person trying to use it doesn’t have an internet connection, but that is increasingly rare these days.
Also, hyperlinks can be very intelligent, containing lots of information. When you click on an advertisement on a webpage, for example, you are taken to the advertisers website. However, the hyperlink that sent you there had the webpage that you came from embedded in it. This allows the advertiser to know which advertising affiliates are giving them the most leads.
There is even the new QR Code which many people are using like a physical hyperlink. They put it on printed brochures so a person can scan the code to be taken directly to a relevant web page for more information. Real estate agents can put one on an info sheet about a house so a buyer can scan and easily be taken to a webpage with more information about the house. A grocery store can put one on their weekly mailer with a caption that says “scan this for exclusive web coupons” in order to drive more traffic to their website. A business person can put one on their business card which will take a person directly to their LinkedIn profile. It’s basically a hyperlink that exists on a physical piece of paper or sign, etc. Here is an example of a brochure about QR Codes that uses a QR Code.
Another way web-based applications use hyperlinks is by tying them to images. Many applications use images that look like buttons and then tie them to a hyperlink. For example, take these image hyperlinks from Google as examples. They look like buttons, but they are really just lowly hyperlinks.
So, basically the hyperlink is a very under appreciated, but very important part of web-based applications. It’s kind of like the tires on your car. You might like to focus on the powerful engine, the immaculate paint job, or the shiny rims, but you aren’t going anywhere without the tires. Hyperlinks are like tires, they enable you to get to where you want to go.
So enough about hyperlinks, let’s get back to the Help -> Get Link feature in Acumatica. Let’s say that you work in the Purchasing department and, while you are looking up a vendor using the Vendors (AP303000) screen in Acumatica, you notice that the phone number is not correct. Now, when your company implemented Acumatica, they put the Accounts Payable department in charge of maintaining the vendor information. So, you can view the information, but you aren’t able to make any changes. Instead, you need to ask someone in the Accounts Payable department to make changes for you. You can send the AP person a link to the specific vendor record that you are looking at. You can click on Help -> Get Link in the upper right-hand corner of the screen. This will open a window containing two hyperlinks.
Notice how the hyperlink has both the screen id (AP303000) and the vendor id (V000005) embedded in it. This is what allows it to take you directly to the specific vendor record that you want. Now, you might want to change the text of the hyperlink to be more pleasant looking. You can change the text without losing the hyperlink. Just type in something new like this:
I’ll admit that this wasn’t a very exciting example, but at least it demonstrated the point. I do think that there are many different interesting applications of this. Some others that I can think of off the top of my head:
- You notice that a salesperson’s expense report is incomplete so you send them an email with a link to their expense report in the email. Especially if the salesperson is travelling, it makes it much easier for them to open the expense report on their phone when they have a direct link to it.
- A customer who submitted a support case using the Customer Management module in Acumatica calls to get a status update. While you are on the phone with them, you update the notes on the case and send them an email with a link to the case with the updated notes.
- You print a small QR Code on your customer invoices. When a salesperson is out at a customer, the customer shows them the invoice and complains that they shouldn’t have been billed for the service. The salesperson scans the QR Code using their phone which takes them to the invoice screen in Acumatica where they are able to see further information about the invoice, including private notes which give them a better understanding of why the customer was billed.
Acumatica 5.0 will launch at the partner summit this coming Summer. I had a random thought that, since Acumatica is already so closely aligned with Microsoft, maybe they should follow Microsoft’s lead when it comes to naming new versions of Acumatica. Microsoft uses the year of the release in many of their products (eg. Microsoft Dynamics AX 2012, Microsoft Excel 2013, and Microsoft SQL Server 2012). Also, Acumatica seems to have slipped into releasing new versions once a year anyways, so using the year as the version would work well.
What do you think? Should we lobby for the next version to be called Acumatica 2014?
I completed the R180 Abbreviated Business Intelligence course tonight which is a really short course that just points you to some videos on the Acumatica website and then you take a test.
I had seen these videos before when I first started looking into Acumatica almost a year ago. However, true to APAL form, I always seem to learn something new. This time around, there were a few things that caught my attention:
- In the Favorites and Usability Features video, it talks about how you can be reviewing a customer invoice, then a vendor calls wondering if a bill has been paid. You can jump to another screen, then go back to the customer invoice screen, and it will remember which invoice you were looking at. I hadn’t noticed this because I always use the Google Chrome Duplicate feature when I need to jump quickly to another screen. I have to admit though that this is a nice feature. Sometimes it’s the little things like this that make a person really like an ERP system.
- The ability to add an inquiry screen to a dashboard. I haven’t really looked into the dashboard feature that much yet, but I did recently “discover” the inquiry screens and the way that you can create your own custom inquiry screens. I also got a terminology lesson. Even though the inquiry screens are in the Explore folder, they are called “inquiry” screens, not “explore” screens as I was calling them. I leaned toward calling them “explore” screens because I was influenced by my Sage 500 ERP terminology. I stand corrected!
- The financial report writer. I did one post on this a while ago, but, other than that, I haven’t really looked at the financial report writer. The more I think about it, the more I realize how bold of a move this is. In the 1990s, most mid-market ERP systems relied on FRx for their financial reporting which was a 3rd party application. Acumatica’s approach to rely on 3rd party developers to build industry vertical applications on top of Acumatica allows them to focus on the core product which includes their own report writer program. It’s nice to have this built into the product without having to integrate a 3rd party application, especially when it comes to something as sensitive as your company’s financial data.
- The respecting of security policies during imports and in inquiry screens. I haven’t look at security much yet, but I know that Acumatica allows you to go beyond screen-level security to control things like who has access to certain GL Accounts or certain Vendors. There might also be the ability to control security at a field level, such as read-only access to the primary salesperson field on a customer (however, I’m not sure if this is possible yet). The point is though that the imports and inquiry screens use the same business logic that the regular screens use and they respect the security settings for the specific user. No more setting up a separate user that has unlimited permissions for the sake of importing data. Also, no need to design multiple reports with different sections hidden because the security will automatically restrict access to certain data depending on the user who is running the report. You can design one master report with the confidence that the users will only be able to see the data that they have permission to see. I made a previous post about how I wondered why Acumatica needed it’s own report writer when it could have utilized another web-based report writer like SSRS. I hadn’t considered the affect of security. By making their own report-writer, Acumatica was able to make it respect user security. This is pretty cool.
I wasn’t able to spend much time on direct Acumatica learning this week, but I did get to entertain an Excel reporting idea that I think would be very powerful if I can get it to work.
First, a word about Power Pivot. I began using Power Pivot early last year and have become a big fan. At my company, we now do a lot of our reporting in Power Pivot. Recently, over the past few months, I have been attending the Cleveland Excel User Group which focusses on Power Pivot and is organized by a guy named Rob Collie. Rob is the most vocal Power Pivot voice on the planet. If you haven’t heard of Power Pivot, Rob has a nice overview on his blog. He also wrote a book on Power Pivot which does a great job of introducing the Power Pivot formula language (called DAX). Rob has a rare gift for writing about technical stuff in an entertaining way.
Microsoft is structuring their whole BI strategy around Power Pivot and they are adding components to the “Power” family. One of these components is Power Query. Now, Excel has had data connections for a while. You can use data connections by going to Data -> Get External Data on the Excel ribbon. Here is a screenshot from the latest version of Excel (Excel 2013).
Power Query is like a revamp of the Excel data connection feature. Actually, the end result of using Power Query is that an Excel data connection still gets created, but Power Query makes it MUCH easier while giving you A LOT more functionality. You can download Power Query for free here. When it installs, it will show up on the Excel ribbon like this:
There are some interesting options in the From Other Sources menu drop-down which include SharePoint, Email (Exchange only currently), Active Directory, and Facebook. Microsoft continues to add features to Power Query so I would expect this list to continue to grow.
Now, you might ask, “what does all this have to do with Acumatica?” Well, I’m glad you asked. In the most recent version of Acumatica (4.1), an Excel Connectivity feature was introduced. This feature allows you to export the results of an explore query to Excel without losing the live link back to Acumatica. During the export to Excel, Acumatica creates an Excel data connection which allows you to refresh Excel with live Acumatica data by simply clicking the Refresh All button on the Data ribbon.
Now you might ask, “what does this have to do with Power Query?” Great question! Power Query has the ability to connect to web pages and extract the data that is currently being displayed in tables on those web pages.
For example, you could connect to this Wikipedia page (http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population) to bring the list of largest US cities into Excel using the From Web button in Power Query. Once you do this, you have created a link to the webpage. Now, if Wikipedia ever updates the data in the table on that webpage, a simple click of the Refresh All button in Excel will instantly grab the current contents of that table and bring them into Excel.
Why not just use the Excel Connectivity feature that Acumatica worked so hard to develop? What’s the big deal about Power Query? The reason to use the From Web button in Power Query rather than the old From Web button under Data -> Get External Data on the Excel ribbon is that Power Query provides much more functionality. You can remove columns, filter, group, insert calculated columns, etc. all before the data even comes into Excel. Power Query has it’s own language so you can do even more, but I haven’t looked into it too much yet. Also, the Excel Connectivity feature loads the Acumatica data into your Excel file, while Power Query gives you the option to load the data directly into the Power Pivot data model by checking the Load to Data Model box. I hope to write a future power about the benefit of bringing the data into Power Pivot rather than into a normal Excel worksheet.
The Excel Connectivity feature that was recently introduced in Acumatica version 4.1 utilizes the old Excel data connection From Web feature. When you open an Excel file that was exported from an explore query in Acumatica, you can go to Data -> Connections on the Excel ribbon to see the Excel data connection that Acumatica creates during the export. I tried exporting the Account Summary explore screen to Excel and the Data -> Connections screen looks like this:
and finally the Edit Query… button. A login prompt appears and I entered my Acumatica credentials. Don’t forget to enter both your username and the company that you want to connect to with an @ symbol in between like this:
Now, for some reason, I get a The webpage cannot be found error. However, I am able to refresh the Excel file so something must be working. This puzzles me, but more on that later.
I can click the disk icon in the upper right next to the Options button to save the data connection web query definition to my desktop as a .iqy file. I then open the file using Notepad to see the details of the data connection definition:
And this is where I get stuck. I think that I should be able to take the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL and paste it into the Power Query From Web feature like this:
I actually had an exchange with a very helpful Microsoft employee named Curt on the Microsoft forum:
He pointed out that res://ieframe.dll/navcancl.html# portion before the URL in the data connection definition file looks funny.
I also tried pasting the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL into my Chrome web browser, but I get a You can’t be here right now!!! error which seems similar to me to the 404 error that Power Query gives.
So, basically I’m stuck because I don’t understand the Excel Connectivity feature well enough and I’m wondering if someone out there can help me out.
My next step will be to see if I can get myself into the Acumatica forum to get help on this issue.
I definitely think that the combination of Acumatica, Power Query, and Power Pivot would be great for reporting.
Yesterday was my first day back in the office in four weeks. Our holiday travel plans took us to California for three weeks where I used PTO on some days and worked remotely on other days. Then the cold spell last week halted our flight, extending our stay in California by another week. No complaints from us though since I was able to continue working remotely without having to burn vacation days.
So, when I finally got back into the office yesterday, I was surprised to see a package on my desk from Acumatica. Inside was a cool wooden box with a bottle of wine and a card inside. I can only assume that someone from Acumatica saw this blog and then got the address of my company from my LinkedIn profile. I was tickled by the gesture. As APAL, I get the satisfaction of knowing that I am investing my time in a cutting-edge ERP product that I expect will pay dividends in my career later on down the road. In the meantime, it’s still nice to get something tangible. Thanks Acumatica!
This week I passed the F140 Advanced Cash Management (V4.0) course. It’s a pretty short course and it didn’t take too long.
The payment reclassification feature stood out to me this time around. It’s nice that you can get all your deposits entered even though you might not know who the customer is, then associate the customer later if needed. This way you can easily do daily bank reconciliations every morning, even if you don’t have all the information for each deposit.
This week I passed the F130 Advanced Accounts Payable (V3.0) course.
I like that you no longer need to schedule an appointment with an instructor in order to take the exam. You can just answer the exam questions while logged into the Acumatica University website. Then, once you pass, your certificate appears immediately next to the course name on the Achievements tab.
While going through the course features, I noticed the Approve Bills for Payment (AP502000) screen which I hadn’t noticed before. I’m assuming that this would be used by the CFO (or someone with approval authority) to approve payments, but I’ll have to look at it further in the future to figure out exactly what it does.
Another thing I began to wonder while going through the course is if Acumatica has the ability to handle invoice approval routings where you could scan invoices and key-in information to be routed to people in the organization who have approval authority on invoices before they become actual bills in the Accounts Payable module. Maybe there is already a 3rd party developer working on this?
Lastly, I noticed on the bottom of my course certificate that Acumatica is registered with the National Registry of CPE Sponsors. I didn’t even know about this registry since I haven’t been renewing my CPA license in an active status and therefore haven’t had to deal with getting CPE credit. So, it’s interesting to know that there is an official registry for CPE sponsors. I looked up the Acumatica sponsor ID # (116008) on this website because I was curious why the hours aren’t listed on my course certificate. Unfortunately, it appears that Acumatica is only approved for the Group Live delivery method which means that you would have to attend one of their courses live in order to qualify for CPE credit. This explains why the hours aren’t listed on my course certificate. I also noticed that the address is the old Virginia address for Acumatica which makes me wonder if this whole CPE thing is something that they still care about. I hope it is, because it would be nice to somehow get CPE credit for Acumatica learning in the future.
One of my favorite features in Sage 500 is Business Insights Explorer. Recently though, I was pleased to discover that Acumatica has a similar feature.
Each module in Acumatica has an Explore section in the Work Area area. For example, in the Accounts Payable module, there are five menu options available in the Explore section: Vendor Summary, Vendor Details, Bills Pending Payments, Checking Pending Printing, and 1099 Year Summary.
However, you have the option to add your own menu options using the Generic Inquiry feature located at System -> Customization -> Manage -> Generic Inquiry.
Just create a title for your inquiry and fill out the tabs on the bottom. I’m going to create a simply list of vendor invoices by filling out the following information in the Generic Inquiry screen:
As long as you fill out the Site Map Location and Site Map Title fields, your new custom inquiry should be available on the Acumatica menu like this (after you save it of course):
In my Vendor Invoices inquiry, you can now just input a beginning and ending invoice date, then click the Refresh icon, and the list of invoices will appear in the grid below. Since Acumatica knows what each field is, it makes the file attachment and record note icons available for each record in the grid. It also gives you a hyperlink on the Reference Nbr. and Vendor fields which allows you to click the link and automatically be taken to the actual invoice or vendor screens.
In conclusion, this is a very flexible and powerful feature that allows you to create data inquiry screens on the Acumatica menu that are customized to fit your company’s specific business needs.
I think this recent TV commercial does a good job of illustrating why it’s good to have the option to run your own private cloud.
I was doing one of the exercises for the F130 Advanced Accounts Payable (V3.0) course today (I know, I’m still not done re-taking the courses, grrr). I was supposed to enter the word “Certification” in the Description field on the Bills and Adjustments (AP301000) screen, but I accidentally entered “Certificatin” instead. However, because I’m using Google Chrome, the spelling error was highlighted with a red line underneath it. It jumped right out at me. Then it dawned on me, I now have a spell checker built into my ERP system! The best part is that I didn’t have to install anything for Acumatica, it’s already available as a feature of my browser. This is yet another great example of why web-based applications are so great (and I’m sure there are more examples to come).
This is a pretty nice feature to have. Imagine if you are an accountant and you are typing a wordy record note on journal entry line to explain why that particular debit or credit is being made. Or maybe you are a salesperson entering your notes from a call with a customer into the CRM module. Or maybe you are doing an inventory physical count and you want to enter a record note on a line that is going to cause a large discrepancy in order to explain the details behind why the inventory can’t be found. In all these situations, you might find yourself writing a few paragraphs and it would be nice to have a spell checker. Before today, I never even thought of the benefit of having a spell checker built into an ERP system, but now I can think of many scenarios where this would be beneficial.
Come to think of it, I rely on the same Google Chrome spell checker while I’m writing this blog post because I’m writing this post in my Google Chrome browser. So, if I need to add a word (like “Acumatica”) to my spell checker dictionary while writing my blog post, that word is now added to the dictionary in my Acumatica ERP spell checker as well because it’s the same spell checker!
Here is a screenshot of the Google Chrome spell checker catching my “Certificatin” spelling error:
Recently, on November 18th, Acumatica announced that it had raised $10 million in funding. At first $10 million didn’t look like that much to me because Xero, a SaaS ERP product aimed at smaller companies, announced on October 14th that they had raised $150 million in funding. However, the problem with Xero is that they are still not profitable, while Acumatica CEO Yury Larichev claims to be already profitable with the comment, “We do not need more funding because we are already making money.” However, the latest funding will enable Acumatica to continue its aggressive growth strategy (they are on track to grow 350% again this year).
Another comment in the announcement caught my attention. An early Acumatica investor says, “We see Acumatica growing to more than a billion in revenue in less than 10 years.”
When I saw the one billion revenue number, it got me wondering about the other major players in the ERP market and how much they do in annual revenue. According to the graphic below from Gartner, here is a breakdown of the market share for the worldwide ERP market. I decided to focus on SAP, Sage, and Microsoft since they are the major players that I’m most familiar with.
SAP is the cadillac of ERP products. They provide the most sophisticated (and expensive) solution. It looks like their most recent annual revenue number is about $18 billion (I multiplied 13.165 by a recent exchange rate of 1.37 USD per EUR). I excluded the consulting dollars because Acumatica doesn’t provide consulting, instead choosing to rely on their partner network (which I think is a great idea).
I’m most familiar with Sage because most of my ERP experience is with the Sage 500 product. Sage is a UK-based company that has a number of different business software products. It looks like their most recent annual revenue is about $2.2 billion (I multiplied 1.376 by a recent exchange rate of 1.63 USD per GBP).
Microsoft doesn’t publish revenue for the Dynamics division which includes their CRM and ERP products, but it looks like a good estimate is between $1.2 billion and $1.7 billion.
Bottom line, if Acumatica can get to the point where they are doing $1 billion in annual revenue, they will become one of the major players in the ERP market. This is especially true due to the following facts:
- Acumatica is one software product, while both Sage and Microsoft rely on a number of different software products in their portfolio in order to reach their revenue numbers.
- Sage and Microsoft got to where they are today by acquiring existing products while Acumatica has organically grown their one product from scratch.
November wasn’t a very perpetual month for this endeavoring PAL. My wife and I had our second child and we have been adjusting to life with two kids. So Acumatica took a backseat (as it should have).
However, in this post I’d like to revisit the previous post on my experience with Acumatica on iPhone. Thanks to Gabriel for commenting on the post and setting me straight that indeed Acumatica does work on the iPhone. I tried using my iPhone to connect to the Acumatica Partner Portal which runs Acumatica of course and which I have access to thanks to ARCVAR. Just as Gabriel said, the Partner Portal works fine on my iPhone. So, of course the problem is not with my iPhone, but with my local laptop installation of Acumatica.
At first I was tempted to try and figure out what was wrong with my laptop version of Acumatica. I concluded that it must have something to do with my installation of Microsoft Internet Information Services (IIS). When I initially installed Acumatica, I didn’t do anything more with IIS other than to turn it on because I wanted to get things up and running as soon as possible. I do know that getting a web server like IIS configured correctly requires a good deal of expertise, especially when you start considering the need for strong security due to the fact that a web server is under constant attack. Since I have a local laptop installation of Acumatica that I don’t care to make available on the internet, I didn’t really care about securing my IIS. However, I didn’t consider that I would run into functionality problems with Acumatica due to my lack of IIS configuration.
The first time I noticed a problem was when I tried to use the new Excel Connectivity feature in Acumatica 4.1. It didn’t work for me on my laptop Acumatica installation. I can’t remember the exact error, but it had something to do with me not configuring a public certificate for https (which I don’t really know how to do).
My inability to use my laptop Acumatica installation on my iPhone is now the second time that I have run into a problem due to my lack of IIS configuration. And, I won’t be surprised if I run into more issues in the future.
So, I decided to try and learn a thing or two about IIS configuration. Maybe I could even get it configured so that I could use my laptop Acumatica installation on my iPhone. I opened up the Internet Information Services (IIS) Manager program located under Administrative Tools on my Start menu and this is what I saw:
I stared at this for a few seconds and then it dawned on me, “this is why why people like SaaS!” This is a perfect concrete example of why SaaS makes sense. Of course, I could spend a few hours going through the different IIS configuration options and using Google to help me understand things. But, my wife and I just had our second child. I don’t have time for this. I want to get back to learning Acumatica. Someone already got Acumatica working on the iPhone as evidenced by the Acumatica Partner Portal. Why should I waste time reinventing the wheel?
Now, of course, there comes a certain point when it makes sense for a company of large enough size to bring their Acumatica installation on premise and invest in people who know how to make it work properly. But, for a guy like me who just wants to learn the product or for a business who just wants to get up and running quickly, why not go with the SaaS option?
So, needless to say, I won’t be spending anymore time on trying to get my laptop Acumatica installation to work with my iPhone. But, at least I know that it works and that’s good enough for me.
Update 06/16/2015: Now, with TryAcumatica.com, I rarely use my local laptop installation of Acumatica.
When I first began using Acumatica, I tried to connect using my iPhone but was unable to. Tonight I decided to try again, but I’m still having the same problem. I can login, but I’m not able to click on any of the navigation links (like Finance or Time & Expenses in the screenshot below).
I’m not sure what I’m doing wrong. Does Acumatica work on the iPhone? This article seems to indicate that it does, but it’s old (from 2010) and the link that it refers to (http://www.acumatica.com/iphone) doesn’t work anymore. So, maybe Acumatica doesn’t work on the iPhone. If that’s the case, I’ll be really disappointed.
It seems like everything that we hear about these days in the IT arena is focused on the cloud. Of course, Acumatica can be run in the cloud and even was the first ERP application to be available for the Windows Azure cloud.
However, I think that we will see the cloud burst at some point, or at least have a hole punched in it. Why?
- People still own houses and cars. We don’t all rent and we don’t all lease our cars. If you have a long-term view, there is an economic advantage to owning a house or a car. I think that there is also an advantage to owning your own digital real estate. Now, of course, the cloud makes sense if you have a smaller scale need, but there are economies of scale associated with technology, just like with anything else.
- The cloud bubble has to burst or at least have a hole poked in it at some point. The dot com bubble and the recent housing bubble are good examples of bubbles that burst. I also think that our society is increasingly susceptible to bubbles because of the flash mob mentality related to trends. The news cycle has shortened, the “what’s cool in school” fads are more short lived, and the well-thought-out research paper gets passed over for the 60 second sound bite. All this creates more frenzy around bubbles.
Eventually, when the cloud hype begins to wane and people return to common sense, Acumatica will still be positioned to accommodate them. You can start in the cloud, but bring Acumatica on-premise when you become large enough. Then, if your company downsizes or your business unit breaks off on its own and you’re small again, you can take Acumatica back into the cloud. Bottom line, it’s nice to have both options.
One of the many benefits to using software that is web-based is that you do all your work in an internet browser and internet browsers are pretty flexible. There are lots of them to choose from and most of them have a pretty extensive list of features.
For me, I prefer Google Chrome. It’s fast (this is the most important feature), all my bookmarks and browsing history sync with the Chrome app on my iPhone, it’s minimalistic design makes more room for viewing webpages, it has one field for typing in a website or doing a Google search, etc. One of the features that is also prevalent in most major browsers (including Chrome) is the ability to do tabbed browsing.
Now, since Acumatica runs within Google Chrome for me, I can take advantage of tabbed browsing in Acumatica.
Right-click on a tab and choose Duplicate to make a copy of your tab to another tab. Some ERP systems don’t even allow you to have the same screen open more than once. With Acumatica, you can duplicate a screen as many times as you want. Maybe you need to run the same report for five different customers. You can quickly duplicate the report screen five times and then run each report simultaneously rather than waiting for each report to finish before starting the next.
Open in new tab
Drag to new window
If your Google Chrome browsing window is getting too cluttered, you can drag a tab to a new window. Just hold down the left mouse button and drag your desired tab down. You will see the window turn into a funny see-through window. Once you let go of the left mouse button, the window will become its own standalone window on the bottom of your screen. You can use this method to group similar screens together.
You can hold down Ctrl on your keyboard and press the Tab key to alternate between Google Chrome tabbed windows. Of course, holding down Alt and pressing Tab will move you through each window, such as Journal Transactions and Invoices and Memos in the screenshot above. But Ctrl -> Tab will move you between tabs that are within the same window.
Tonight I passed the F120 Advanced Accounts Receivable (V3.0) course.
I had some trouble with the customer statement feature in the beginning because it seemed to me that you could create multiple customer statement cycles for the same set of customers. It wasn’t clear to me that you have to assign a customer statement cycle to a customer in order for it to do anything. I was using the Prepare Statements (AR503000) screen and things seemed to be processing fine, but when I would go to Print Statements (AR503500) there was nothing to print for my statement cycle id. I was even able to delete my customer statement cycle in Statement Cycles (AR202800) after preparing the statement which indicated to me that no statement records were actually getting generated. However, once I finally put my statement in the General Info -> Statement Cycle ID field for a customer in the Customers (AR303000) screen, I was able to find records in Print Statements (AR503500) after preparing the statement. Bottom line, each customer can be on one statement cycle which makes sense. You wouldn’t want a customer to be getting statements on two different cycles simultaneously.
I’m starting to gain a little bit of momentum in getting through the Acumatica University courses correctly. Tonight I passed the F110 Advanced General Ledger (V3.0) course.
One of the things that stood out to me this time around is the hard closing of sub modules. The product that I currently use (Sage 500) is just now finally incorporating this into their current version (7.5). It’s nice that Acumatica is including this from the beginning.
I wanted to test that the hard closing of sub modules feature actually works, so I went into the demo company and closed everything in AP up through period 01-2013. After closing the periods, the Financial Periods (GL201000) screen looks like this for year 2013:
Then I tried to create a bill in the Bills And Adjustments (AP301000) screen with a date that falls in the closed 01-2013 period. To my surprise, I was able to post the bill, although I did get this warning:
As first I was disappointed and thought that the feature wasn’t working. However, I then remembered the Allow Posting to Closed Periods checkbox that is in the General Ledger Preferences (GL102000) screen. Apparently, this is checked by default in the demo company.
So, it looks like the feature is working (whew!). Also, it’s nice that Acumatica gives you the option to hard close sub modules or not. It’s up to you.
One of the reasons that I like posting videos that are zoomed in (rather than showing my entire computer monitor) is that I like consuming content on my iPhone while I’m on the go. I like listening to the Tech Tuesday series on the Acumatica University site, but I don’t like that the video portion is showing the full screen of a computer monitor because it makes it impossible to watch on my iPhone. In the case of Tech Tuesdays, I don’t think there is an alternative because they need to show the entire screen due to the large amount of information being conveyed (it works out though because I can listen on my phone while mowing the lawn without being tempted to watch the video). However, in other cases, it’s nice to be able to view the video on a phone. The point is, learning ERP on a phone is nice. You can learn while at the gym, while driving (audio only of course), taking a walk, mowing the lawn, etc.
This week I discovered two new ways to learn ERP stuff on my phone.
The first is a Podcast from a guy named Rick Nielsen. He has insightful things to say about implementing ERP. The Podcast is called Implement ERP FAST! and it’s available in the Podcast app on the iPhone.
The second thing I discovered is that I can listen to the new book on Data Warehousing that I downloaded to my Kindle app. I always knew that I could read books on the Kindle app, but I didn’t realize that I can listen to them too. The key is to use the iPhone VoiceOver feature by going to Settings -> General Accessibility -> VoiceOver and turning the feature off. In the new iOS 7, you can even make a shortcut to turning this feature on and off by going to Settings -> General Accessibility -> Accessibility Shortcut. When the feature is on, all you have to do is go to your Kindle app and swipe down with two fingers on the iPhone.
Since I finally passed F100, I decided to upgrade to version 4.1 before proceeding any further with the Acumatica courses. My experience with the upgrade was similar to my experience with the installation, very simple and straightforward.
There are a few new things in version 4.1 that caught my attention: [Read more…]
It took me a while to get through the exercises again because of family vacation and Summer house projects. Hopefully I’ll be able to tackle the next exam, F110 Advanced General Ledger (V3.0), in less time.
Acumatica is very good at integrating with Microsoft Excel. In this video I demonstrate the ability to export to Excel and import/update from Excel. I am using the Chart Of Accounts (GL202500) screen, but this feature is available on a number of different screens.
Update (May 31st, 2016): For a more detailed post on this topic, click here.
One of the most consistent things across ERP systems is the General Ledger Account Structure where the GL Accounts are given a certain structure according to your business needs. While this isn’t complicated, sometimes it’s nice to see how the screens look so you get a feel for the product. It’s kind of like a first date with your ERP system. So, with that in mind, here is a 4 minute video showing the relevant screens in Acumatica related to the General Ledger Account Structure setup.
I’m not positive what the length limits are, but, as far as I can tell from looking at the database structure, the maximum length of an ACCOUNT segment is 10 and the maximum length of a SUBACCOUNT segment is 30. As far as a limit on the number of segments within ACCOUNT or SUBACCOUNT, I’m not sure that there is one, theoretically. After recording the video I went back in and added a bunch more segments, all of length 30, to the SUBACCOUNT segment and didn’t get any errors (see screenshot below). I then defined some valid values for a few of the segments using the Segment Values (CS203000) screen and I still didn’t get any errors. I’m not sure why you would need so many segments, but it’s always nice that you could make things incredibly complicated if you wanted to.
A couple of weeks ago I took a trip home to meet my nephew who was just born a couple of months ago. My wife and daughter had flown out a week ahead of me so I made my trip solo.
My connecting flight in Philadelphia was supposed to take off around 9:00p, but we didn’t end up leaving until around 2:00a. Note to self, don’t take US Air ever again. We had two planes fail on us before the third one finally came through.
However, because I had my laptop with me, I was able to take advantage of the situation and continue my Acumatica learning. I found a comfortable seat in the corner of the terminal next to a power outlet, put my headphones in, and went to work. It really did make the whole experience less frustrating since I knew that I was being productive.
Note: If you are looking for an Acumatica partner in Philadelphia, checkout The LLB Group.
Sergey has a nice post up about how to easily translate all the field labels in Acumatica into another language:
I’ve never worked on an ERP translation project, but Sergey’s post makes translation in Acumatica look very simple.
I wanted to create a new company in Acumatica that would only be used for the F100 Hands-On Financials (V4.0) course that I am retaking, but I couldn’t remember how to do it. So, after figuring it out again, I thought I’d document it here so I can use these instructions next time I need to create a company. Please note, these are not official instructions, but I think they get the job done.
7. Click the Next > button
11. Login with Username: admin / Password: setup
It has been 5 days since I failed the examination, but I have been under the weather this week so I am just now getting around to starting the redo. I am starting by deleting the folders with the course material since I know that Acumatica University is frequently changing and the material might have changed since I last downloaded it. I plan to put in two Pomodoros tonight and then get back to painting the bathroom. Here we go!
I didn’t realize until reading this article today that NetSuite is so aligned with Oracle.
“A pioneer in the SaaS (software as a service) market, NetSuite has Ellison as an investor. Its software has typically run on the Oracle stack including the database.”
I don’t know that much about NetSuite, but this article helped me further differentiate between Acumatica and NetSuite. I already knew the following differences:
- Priced per user (NetSuite) vs. Unlimited users (Acumatica)
- SaaS option only (NetSuite) vs. SaaS and on-premise options (Acumatica)
- Forced upgrades (NetSuite) vs. Upgrade on your own schedule (Acumatica)
But, now I can add a new item to the list:
- Oracle Stack (NetSuite) vs. Microsoft Stack (Acumatica)
I definitely prefer the Microsoft stack. Microsoft knows business applications: SharePoint, SQL Server, Exchange, Lync, Word, Excel (with Power BI), PowerPoint, Project, Visio, Access, etc. They may not be the most powerful products in the market, but they are the easiest to use. You will always find an army of Microsoft professionals available because Microsoft understands the people side of software. It’s not just about the software, it’s about people using the software. Oracle has a good database product, but nothing beats Microsoft when it comes to the stack.
Props to Acumatica for going with the Microsoft stack.
Well, yesterday I failed the examination for the first Financial module course. I then failed the second examination. Before failing the third examination, I decided to stop, not wanting to waste the instructor’s time.
Basically, the examination consists of an Acumatica instructor checking my Acumatica version to see if I was able to perform all the exercises that were in the course material. They spot check different things to make sure that I followed the exercises to the letter. I thought I had completed the exercises correctly because they seemed easy enough, but the examination proved otherwise. If there is anything that is not exactly what they are looking for, you fail the exam. As the instructor put it, “this isn’t horseshoes,” being close doesn’t count.
Now, I get to re-perform the exercises. This time I am going to do the examination after each course instead of attempting them all at the end. There are six courses:
- F100 Hands-On Financials (V4.0)
- F110 Advanced General Ledger (V3.0)
- F120 Advanced Accounts Receivable (V3.0)
- F130 Advanced Accounts Payable (V3.0)
- F140 Advanced Cash Management (V4.0)
- F300 Inter-Company Accounting (V3.0)
It has been almost three months since I started the first financial module course. I honestly don’t remember that much from it. So, although I am definitely discouraged that I didn’t pass, it might be a good thing that I have to redo everything. Going through things a second time will help to reinforce what I learned.
This time around I am going to make a few changes:
- Take the examination for each course immediately after performing the exercises rather than wait until the end (it’s hard to remember what you did three months ago).
- Create a separate company for each course and name each company with the ID of the course (F100, F110, F120, F130, F140, F300). That way I can be sure that the only transactions to take place are due to the certification exercises.
- Apply the Pomodoro technique instead of staying up really late trying to finish a course.
This somewhat humiliating experience of failing the exam reminds me of my Aunt Carol. She failed Statistics twice in college before finally passing the course. She then went on to get her PhD and wound up teaching the course at the university. The point is that many times the harder it is to learn something, the better you understand it in the end. Especially with ERP, the tortoise wins the race. Failing the examination also helps remind me that my goal is to be APAL, not ANEXPERT. Even once I eventually pass the examination, my goal is to maintain a learner’s attitude towards the financial module. There are always new things to learn.
Let the Financial module course redo begin…
Today I am scheduled to review my Financial module exams with an Acumatica University Instructor. I did do a quick review of things yesterday to refresh my memory on everything that I learned. I have no idea what to expect. We’ll see how it goes…
Last week someone posted on the LinkedIn Acumatica User Group asking about how to create a rolling monthly financial report.
Now, it’s always dangerous to assume that you know what someone is asking for just by reading a couple of sentences, but I thought I would live dangerously and try to answer the question.
Disclaimer: This is my first time trying to write a financial report in Acumatica so MY METHOD MIGHT BE COMPLETELY WRONG. But, it does seem to work, and sometimes that is good enough. I have gone through part of the course on Acumatica reporting, but nothing so far that relates to the financial report writer. My method is based purely on dusting off my FRx knowledge and trying to make it work in Acumatica.
So, here it goes. In order to demonstrate the concept, I made a less than 8 minute long video on YouTube.
I realized that my Custom Vendors Report was not correct. This was due to the Business Account concept in Acumatica. I’m still not sure of the reason for it, but there is an entity called a Business Account (BIZACCT) that is the parent entity for both Customers and Vendors. If you go to the Segmented Keys (CS202000) screen, you can see that both Customers and Vendors share the same parent entity, BIZACCT.
What this means is that both customers and vendors store some information in the BIZACCT entity. When I designed my Custom Vendors Report, I didn’t realized that I actually made a report which displays Customers and Vendors. I only realized this when I dropped an extra field on the report: Vendor.CreatedByID. Once I dropped this field on the report, the report looked like this:
I immediately noticed that the create user (admin) only appeared next to some of the records. The reason is that only the vendors displayed the create user. The customers did not display a create user because I dropped a vendor-specific field (Vendor.CreatedByID) on the report. The fix? I just needed to go to File -> Build schema… and change the Join type from Left to Inner under the Relations tab.
Now my report only displays vendors. Consequently, the create user is now displayed for every record.
I barely made it through the first 66 pages of the 112 page training manual on Acumatica Report Designer because it was really dry and boring. To be honest, I didn’t read it carefully, but instead skimmed through.
The concepts are consistent with other report writing programs like SSRS and Crystal Reports and the training material is more like a reference manual than training material. However, I do think that I will be referring back to it in the future since it’s a good resource. For me, the best way to get comfortable with a new report writer is to start writing some reports!
That’s why I was happy to finally see on page 68 how to save and publish a report.
2. Go to File -> Save On Server… and give the report a name (I used Vendors.rpx). I also checked the Save as new version box which I’m hoping will keep a snapshot of this version of the report somewhere in the database (with Crystal Reports I always append the date to my versions which is a really duct tape way to do versioning).
3. Now for the cool part. The training course introduced me to the Site Map (SM200520) screen which is how you can make modifications to the Acumatica menu. In this case, all we need to do is add a new item to the menu for the new report. Since this is a vendor report (a really simple one), I decided to put it under Finance -> Accounts Payable -> Reports -> Balance. All I had to do was browse to the folder and then add a new entry (the highlighted one). You can click the image below to get a clearer picture.
I’m starting to warm up to Acumatica Report Designer after seeing how easy it is to publish a report and how nicely it integrates into the Acumatica application. I’m sure that SSRS reports still have their place, but a user would definitely notice that they are leaving Acumatica when running an SSRS report. Also, reports created with Acumatica Report Designer become just like any other screen as far as security (who has access) is concerned. On the other hand, SSRS security is maintained in an entirely separate area. Finally, when running a report created in Acumatica Report Designer, a user has no idea whether they are using a standard report or a custom one. This is exactly the kind of experience that I want a user to have when running reports.
On the topic of Acumatica reporting, I came across this nice little YouTube overview video from Acumatica.
I have to admit, at first I was disappointed to learn that Acumatica has its own reporting program. I was hoping that Acumatica would leverage SQL Reporting Services (SSRS) rather than create its own standalone report designer. The reason is that there is already so much functionality built into SSRS. I didn’t see why Acumatica would reinvent the wheel.
I don’t know the technology behind SSRS that well so I don’t know much about what would have been required to integrate it into the Acumatica framework. I just know that it’s a good report writing program with lots of cool features, it’s web-based, and it has a better future than Crystal Reports (in my opinion). Acumatica Report Designer reminds me a lot of SSRS and Crystal Reports.
There is a Data Access Class (DAC) layer in Acumatica that I don’t understand yet. The picture above is what I picture in my head. Now, as far as I can tell, the main advantage (from a reporting perspective) of leveraging the DAC layer is that the report might be able to control the content that gets presented to a user. For example, the report might show sales per branch with sales on the left and branches going across the screen as columns. If a person doesn’t have access to a specific branch, the report wouldn’t show them data for that branch. I’m not sure if this is how it works, but this is how I hope it works. It would be cool to have the same report display different data depending on who is running the report.
I think there is still a place for SSRS and Power Pivot reports using the traditional reporting method pictured above, but it would be really, really cool (assuming that the previous paragraph is true) if I could leverage the DAC layer even with SSRS and Power Pivot reports. Hopefully the S130 Reports Dashboards and Inquiries course will shed some light on the subject.
Acumatica gives every screen a unique ID. This might not seem like a big deal, but I think it is.
No matter how you determine the screen ID, the important point is that there is a screen ID. This might not seem that important, but it sure helps a lot when you need to refer to a specific screen. For example, the Accounts Payable and Accounts Receivable modules both have a screen with the name Recurring Transactions.
When you are referring to one of the screens in an email, it might be helpful to include the correct screen ID (the one for Accounts Payable or Accounts Receivable). Or, better yet, you could just include a link to the correct screen (replace localhost and AcumaticaERP with your paths of course):
http://localhost/AcumaticaERP/(W(1))/?ScreenId=AP203500. When a user finds the screen by entering the screen ID, they can also see the path to the screen because the navigation page adjusts to show which module and which area of the module the screen is in.
Again, this might not seem like a big deal, but I think that it helps a lot when writing documentation or communicating with colleagues about Acumatica. I often hear people get lazy when talking about screens in an ERP system and the name they use is not the actual screen name, but some creative variant. Having screen IDs helps to set the record straight and prevent misunderstanding, especially among new employees who might spend hours looking for a screen name that doesn’t exist before asking someone for help.
SAP employs the same technique for giving screens an ID (eg. FD01 or XD03). I’m glad that Acumatica does too.
Today I listed to the DN003 – Developer Network – Tech Tuesday course on my iPhone while vacuuming. I kept an eye on our one-and-a-half year-old daughter while my wife was at the gym, got some chores done, and learned some more about Acumatica. Now that’s some good APAL multi-tasking.
Tech Tuesdays are a conference call and screen-sharing session given every two weeks for developers. Most of the info is over my head, but I am able to still pickup some useful tips and tricks. The recorded sessions are available on the http://www.acumaticauniversity.com website. So far, they run about an hour.
It’s nice that the http://www.acumaticauniversity.com videos play on my iPhone. I’m able to go to the site on my phone’s browser, login, and see the full list of courses:
I can choose a course and play the audio and video right on my phone. The only downside is that it plays in the phone’s browser so I can’t lock the screen if I want it to keep playing. This causes the battery to drain a little faster than it normally would and I have to make sure not to accidentally touch the screen, but it’s not too big of a problem.
The convenience of having Acumatica videos available on my phone really helps to put the “perpetual” in Perpetual Acumatica Learner. I can even learn while vacuuming!
I thought I should make a post with short explanation of the term ERP.
ERP stands for Enterprise Resource Planning. It is a term that was coined by Lee Wylie at Gartner in 1990 as an extension of the term MRP (Material Requirements Planning). While MRP focusses on inventory ordering and supply chain management, ERP encompases the entire organization.
I use the term ERP to refer to any system that includes the accounting data. So, I even consider Quickbooks to be an ERP system. However, SAP or Acumatica are better examples of ERP. A true ERP system covers a lot more than accounting, such as:
- Customer Relationship Management (CRM)
- General Ledger
- Cash Management
- Accounts Payable
- Accounts Receivable
- Internal Accounting
- Overhead Cost Allocation
- Fixed Assets
- Sales Orders
- Purchase Orders
- Bar Coding
- Bills of Material
- Work Orders
- Shop Capacity Scheduling
- Quality Control
- Product Life-Cycle Management
- Human Resources
- Project Management / Contract Management
- Resource Planning
- EDI (Electronic Data Interchange) interfaces with customers and vendors for automatic ordering
- Customer and Vendor self-service portals
The Acumatica Report Designer is a stand-alone program that allows you to create reports in Acumatica.
Here was my first experience using the program.
3. Enter the path to your Acumatica installation, then enter your login info. Note that, if you have multiple companies as I do, you have to put the company name after the username (separated by @), otherwise you will get an error that says:
Error during login: PX.Data.PXUndefinedCompanyException: Unable determine proper company id for the request.
at PX.Data.PXDatabaseProviderBase.getCompanyID(String tableName, companySetting& setting)
at PX.Data.PXDatabaseProviderBase.getRestriction(String table, String alias, Boolean mainRestriction, Boolean isRightJoin, Nullable` 1 effectiveCid)
at PX.Data.PXDatabaseProviderBase.appendWhereClauseToSelect(String tableName, PXDataField pars, StringBuilder bld)
at PX.Data.PXDatabaseProviderBase.SelectSingle(Type table, IEnumerable` 1 joins, PXDataField pars)
at PX.Data.PXDatabase.SelectSingle[Table](PXDataField pars)
at PX.Data.PXDatabaseMembershipProvider.ValidateUserPassword(String username, String password, Boolean onlyAllowed)
at PX.Data.PXActiveDirectorySyncMembershipProvider.ValidateUser(String username, String password, String& providerLogin)
at PX.Data.PXActiveDirectorySyncMembershipProvider.ValidateUser(String username, String password)
at PX.Api.WebServiceBase.Login(String name, String password)
6. Note in the screenshot above that I selected the gl633500.rpx report. I knew that was the report that I wanted because I wanted to edit the Transactions for Account report in the Financial module. Since every screen in Acumatica has a unique ID, the report is actually just the ID for the screen in this case. You can determine the ID for a screen by putting your mouse over the screen name in the Navigation Pane or by clicking on the screen name, then looking at the address that appears on the top of your browser. As you can see in the screenshots below, both methods yield the result of GL633500.
7. Now I have a scary looking report that I can break if I’m not careful. Hopefully, after going through the 112 page S130 Reports Dashboards and Inquiries course, I will know what all the stuff below means.
While I’m waiting for my appointment with the Acumatica Instructor, I decided to continue my learning journey by taking the S130 Reports Dashboards and Inquiries course. In my opinion, reporting is one of the main reasons why people buy new ERP systems. Sure, it’s nice to have lots of information stored in your ERP system, but that information is only meaningful if you can create meaningful reports.
Also, the S130 Reports Dashboards and Inquiries course is the only one that makes sense at the moment. The Distribution learning module is not yet available, I’m not quite ready to go for the Projects or CRM modules, and I didn’t want to go through any of the boring Technical courses like installation, security management, upgrading, etc. For some reason, the S130 Reports Dashboards and Inquiries course is listed in the Technical category, but I consider it to be separate.
Today I scheduled my appointment with an Acumatica instructor to review the financial courses:
- F100 Hands-On Financials
- F110 Advanced General Ledger
- F120 Advanced Accounts Receivable
- F130 Advanced Accounts Payable
- F140 Advanced Cash Management
- F300 Inter-Company Accounting
They were very flexible with the time slots. I scheduled something for a week from Monday since my wife and daughter will be out of town on a trip. I’m scheduled to speak with the instructor after I get off work.
By the way, I posted this from my iPhone. We’ll see how it turns out.
I finally finished the course on multiple companies. There are two ways to configure multiple companies in Acumatica: separate branches and separate companies. It seems to me that what I typically think of as companies are actually going to be called branches in most cases in Acumatica.
If you configure companies:
- Each company is a separate legal entity
- The Due To / Due From Inter-Company transactions must be created manually
- You must create a separate company and import the data in order to do consolidated reporting
If you configure branches:
- The Currency, Fiscal Calendar, and Chart of Account Structure must be the same for all branches
- Customers, Vendors, and Employees are shared across all branches
- The Due To / Due From Inter-Company transactions are created automatically
- The branches could be the same or different legal entities
- Consolidated reporting is available
When using branches, there are different organizational examples to consider:
- (Example 1) A central headquarters with multiple locations or multiple branches that have separate operations. The accounting staff are located in the central headquarters. All transactions are posted in one ledger. The branches are not separate legal entities and no automatica inter-branch transactions are generated. Access rights can be maintained separately for each branch.
- (Example 2) Each branch is autonomous. Each branch is a legal entity. All transactions are posted to one ledger that is shared between the branches. Most vendors and customers are shared, but some are associated with a specific branch. Inter-branch transactions are automatically created. Some GL Accounts and GL Subaccounts are assigned to a specific branch.
- (Example 3) Combination of Example 1 and Example 2. Each legal entity has its own headquarters with separate locations/organizational units underneath. Each legal entity gets its own ledger. When transactions occur within a ledger, no inter-branch transactions are created. When transactions occur between ledgers, the inter-branch transactions will be generated by the system
I’ve been slacking lately. A few nights ago I spent the evening looking around the database tables to see what kind of reports I could create using Microsoft Power Pivot for Excel, but I neglected to look at any formal training material.
Last night I watched the video on inter-company transactions, but fell asleep near the end. Plus, I didn’t follow along and do the transactions in my copy of Acumatica so I need to watch the video again and this time complete the exercises.
We are going on a little vacation tomorrow so I won’t be able to finish off inter-company, the last piece of the Financial module training, until later next week. Then I will finally be able to schedule the Financial module exam with an Acumatica instructor.
I downloaded the WordPress app for my iPhone so I hope to make a couple of posts while we’re on vacation with thoughts about Acumatica.
Today I finished the F140 Advanced Cash Management (V4.0) course. Here are the main topics that were covered:
- You can enter your customer payments and then do a separate deposit where you select the payments that are going into that deposit. So, maybe you received four checks from customers today, but only three of them made it into the bank deposit for the day. No problem, you can select only three payments when you create today’s deposit transaction, then include the fourth payment on another deposit slip tomorrow.
- Payment Reclassification
- This feature allows you to enter payments that you received, but you’re not sure where they came from. They will accrue in a GL account of your choosing. Then, once you determine who they relate to, you can reclassify the payments which removes them from the GL account of your choosing and into the Accounts Receivable GL account.
- Bank Statement Upload
- This allows you to import transactions from your bank. You can either upload an OFX file (which most banks support) or a regular Excel file. If you upload a regular Excel file, you get to map the fields so it can pretty much be in any format. You can match the imported transactions to existing Acumatica transactions or create them in Acumatica if they don’t already exist.
- Bank Statement Reconciliation
- The Bank Statement Upload feature marks transactions as cleared. The Bank Statement Reconciliation feature is where you actually check off which items appear on a particular bank statement. However, you can sort by the cleared box so all the cleared transactions will appear together.
I’ve been trying to get in Acumatica learning here and there when I can, but I have a full-time job, a lovely wife, an adorable one-year-old daughter, and a son on the way. So, it’s hard to find time. When I do have time, I tend to get carried away and will work on Acumatica until the early hours of the morning. Then I’m exhausted the next day and it takes some time to recoup.
My new strategy is to take a more consistent, steady approach using the Pomodoro Technique. I just devote 25 minutes to Acumatica, then reassess if I should stop or put in another 25 minutes. My max is one hour per night. This works out well because the learning sections are small enough to be digested in 25 minute intervals. They say the tortoise wins the race. Let’s go tortoise!
Tonight I finished going through the videos and completing the training exercises for the F130 Advanced Accounts Payable (V3.0) course. Now I just need to go through the F140 Advanced Cash Management (V4.0) and F300 Inter-Company Accounting (V3.0), then I can contact an instructor to review my work.
The F130 Advanced Accounts Payable (V3.0) course was pretty straightforward. Here are the topics that were covered:
- AP Recurring Transactions
- Just like on the Accounts Receivable side of things, you can make a Bill recurring according to a schedule.
- ACH Payment Process
- You can have Acumatica automatically generate an ACH file for electronic payments that you want to submit to your bank. I didn’t go through the details of how to set this up, but it looks to be very flexible, just like the AR Credit Card Processing feature.
- AP Payment Processing Using Credit Card
- You can setup a credit card vendor so that when you pay Bills with your credit card, the payable amounts automatically get transferred to the credit card vendor. Then you can reconcile your statement before processing a payment to the credit card vendor.
- Credit Terms
- Lots of options here. You can setup due dates and discounts according to Fixed Number of Days, Day of Next Month, End of Month, End of Next Month, Day of the Month, Fixed Number of Days starting Next Month, or Custom buckets. You can also setup installment options on the credit term to split a payment into customized amounts spread out over a time period.
- Vendor Payment Lead Time
- You can enter a payment lead time per vendor. This is the amount of time it takes a payment to reach that specific vendor. This will be included in the due date and discount date calculations to ensure that your vendors receive your payment on time.
- Vendor Discount Processing
- If you make a payment in time, the system can automatically record the discount for you.
- 1099 Vendors
- Pretty standard functionality here. The system can track 1099 payments made to vendors and print a 1099 report at the end of the year.
For sure having Wiki pages available in Acumatica is A BIG DEAL.
Here are some things that you can do with the Wiki pages.
First, you can change existing Wiki pages. Since the entire help system in Acumatica is based on Wiki pages, there are already hundreds of pages with very useful content.
However, you might want to tailor some of that content to your organization specifically.
For example, maybe I am in the Vendors (AP303000) screen and I want to find out more information about the Status field. I can click the Help button in the upper right-hand corner of the screen and I’ll be taken to the Wiki help page for the Vendors (AP303000) screen. Here is a screenshot of the help on the Status field:
But, maybe I want to change some of the language to make things more specific to my organization. I can easily edit the Wiki help page and make some changes. Once I save the Wiki, anyone looking in Acumatica for help on the Status field in the Vendors (AP303000) screen will see my newly added information (I added a sentence at the end of the “On Hold” section).
What if you are too afraid to edit a Wiki help page because you don’t want to lose the nice looking out-of-the-box page from Acumatica?
Don’t worry, Wiki pages save each version, just like attached files do.
Simply click on the version that you would like to revert back to (1 in this case) and then click the Revert button in the screenshot above. This will restore the original Wiki page.
You can also view what the Wiki page looked like for a specific version by clicking the View Version button or compare the version to another version (changes, additions, deletions, etc. will appear similar to the way they do when you display changes in Microsoft Word) by clicking the Compare button.
Bottom line, feel free to go to town making modifications to your Wiki help pages. You can see who made each change, when they made them, and you can always revert back to the original if things get bad.
Also, you aren’t limited to editing existing pages. You can create your own library of Wiki pages for things that aren’t directly related to an Acumatica screen.
Maybe you’d like to create a Wiki page for the company organization chart or the procedure for requesting time off. The possibilities are endless.
You can even add a Wiki page to the Acumatica dashboard so that it is the first thing your employees see when they login to Acumatica.
Once again, I’d like to point out the power of having a web-based application. All the Wiki content that you create is just a hyperlink away from being accessed by another user. You can link to your content in an email, in a Microsoft Excel document, on a PowerPoint slide, etc.
Also, don’t forget, since Acumatica is licensed for unlimited users, you don’t have to be concerned about leveraging this feature for everyone in your organization.
Regarding how to apply Wiki pages in your organization, the possibilities really are endless.
A very nice feature in Acumatica is the ability to attach files. It’s surprising that more accounting systems don’t offer this feature. Actually, sometimes they claim to offer this feature, but they don’t actually store the files in the database (they just store a link to the file). Not only does Acumatica store the files in the database, but you can control who has access to the file, keep track of versions on the file, and do some other cool stuff. Suffice to say that this is much better than storing paper in a filing cabinet or storing scanned .pdf files on a windows shared drive by department, etc.
In Acumatica you can attached files to many different types of screens (maybe even all of them?). All you do is click the Files -> Add file… button in the upper right-hand corner of the screen.
Once there, you can either upload a new file or link to a file that has already been uploaded. It looks like the maximum file size is 25 MB on my demo system, but I’ll bet there is a setting somewhere that will allow you to increase the allowable size.
Once you upload some files to a screen, you can easily see how many files there are and what they are by clicking on the Files button again.
If you click on the of the [Edit] links in the screenshot above, you will be taken to the File Maintenance screen. Here you can view the versions of the file (if you replace the file with a new version, it keeps track of all the previous versions), who has access to the file, and even what screens in Acumatica are referring to the file. In the example below, I uploaded a file to an AP Bill, then I used the Link to existing file feature to add the file to another AP Bill. When you look at the File Maintenance screen you can see all the AP Bills that are referring to the file:
A HUGE advantage of storing files in Acumatica is that you don’t need to clutter up you email servers with large files. Simply upload a large file to the most relevant Acumatica screen, then send the web link around in email. This keeps your email profile from getting really big and it enhances security since email is not very secure. Anyone who wants to access your file will need the appropriate permissions in Acumatica to do so. And, since Acumatica is licensed for unlimited users, you don’t have to be concerned about leveraging this feature for everyone in your organization. A link would look something like this (you could make the link text whatever you want and still display the blue hyperlink to the file):
This is yet another benefit of having an ERP system that is web-based.
ps. (for the geeks)
It looks like the files get stored in the UploadFileRevision table. I ran “EXEC sys.sp_spaceused UploadFileRevision” and noted the table size to be about 209MB. Then I attached a 10MB file, ran the SQL command again and noted the table size to be about 219MB as expected.
I first noticed the difference in the navigation pane between version 3.0 and 4.0 since I’m watching training videos which were recorded in version 3.0, but I also wanted to point out that there is quite a difference between the buttons on the screens.
In version 3.0, the buttons were a lot more wordy and they took up more space (sorry for bad resolution, this is a screenshot from a video):
In version 4.0, the buttons take up much less space because the words have been removed. If you forget what a button does, you can hover your mouse over it and the button help will be displayed.
I’m currently going through the F130 Advanced Accounts Payable (V3.0) course and noticed that I have the same problem with the Recurring Transactions screen that I was having in the Accounts Receivable module. In Accounts Payable the screen ID is AP203500 and in Accounts Receivable the screen ID is AR203500.
The problem is that the bottom part of the screen is cutoff and I can’t add documents to the recurring schedule. Maybe it’s my browser (chrome)?
So, in order to get around this, I just used the Add to Schedule option on the document itself to add the document to the schedule.
But, I am wondering if I will see something else like this on another screen. We’ll see…
The F120 Advanced Accounts Receivable (V3.0) course was much more involved than the F110 Advanced General Ledger (V3.0) course. There are a lot more setup options to consider. I first tried to complete the exercises without watching the training videos, but I failed. I needed the training videos in order to see how to do all the steps in the process.
Here are the main topics covered in the training material:
- AR Recurring Transactions
- You can create a schedule and then add invoice templates to that schedule. Then when you run the General Recurring Transactions (AR504000) process, new invoices will be created from the templates according to the schedule.
- In the schedule you can setup an execution limit (how many times the schedule can be executed), a starting date, and an ending date.
- Then schedule is pretty flexible. Here are some examples of what you can setup:
- Every 3 days
- Every 2 weeks on Mondays and Thursdays only
- Every 2 months on the 15th of the month
- Every 4 months on the 3rd Saturday of the month
- AR Credit Verification
- You can create a credit limit and then a buffer amount so that you get warned when you’re over the credit limit, but future invoices won’t be placed on hold unless the buffer amount is exceeded.
- Credit Card Processing (by far the most involved section)
- I’m pretty impressed with the flexibility of this module. It seems like it’s designed to work with any credit card processing center. Most ERP systems force you to choose from a small list of vendors, if you even have any choice at all.
- Because the setup is a little involved, I was instructed me to go to http://demo.acumatica.com/ in order to complete the certification exercises because some of the setup had already been completed there.
- AR Direct Write-Off
- I wish Sage 500 ERP did a better job of this.
- You can use the Write Off Balances and Credits (AR505000) screen to view a list of all available invoices or filter by Customer/Branch/Write Off Limit, then select the individual invoices that you want to write off. Very easy.
- AR Overdue Charges
- Make sure the Accounts Receivable Preferences (AR101000) is setup properly.
- Setup an overdue charge using the Overdue Charges (AR204500) screen.
- Define a statement cycle utilizing an overdue charge using the Statement Cycles (AR202800) screen.
- Run the Calculate Overdue Charges (AR507000) screen to generate the overdue charge invoices.
- AR Statement Cycles
- Use the Statement Cycles (AR202800) screen to setup when the statements will go out and what the aging buckets will be.
- I’m not sure why you only get 4 aging buckets though. I was hoping there would be more. Some people like to go crazy with their buckets.
- AR Dunning Letters
- You can setup levels that trigger a dunning letter to be sent after an invoice is past due for a specific number of days. You could setup one level to trigger after 5 days overdue, another after 13 days overdue (bad luck), another after 17 days overdue, etc. There is no theoretical limit here on how many levels you can setup. I setup 20 levels just for fun, but you could make as many as you want as far as I can tell.
- I’m not positive, but I think you can tie different letter formats to different levels. So, a nice letter after 5 days past due and a threatening letter after 30 days past due.
Since I’m watching videos which were recorded using version 3.0, I noticed that the navigation pane on the left-hand side of the screen used to look like the Microsoft Dynamics ERP navigation panes in version 3.0.
Interesting move to go away from the ribbon look and feel that so many Microsoft-friendly products are moving towards.
Some brief comments about the Acumatica training videos on Acumatica University:
- I’m currently going through the F120 Advanced Accounts Receivable (V3.0) course which employs the most extensive set of videos that I’ve encountered so far. The lady who recorded the videos has an accent that takes a little while to get used to, but I really like her enthusiasm. Also, there is an advantage to her not having a stellar command of the English language because it forces her to read from a script (at least it seems like she is reading from a script). This results in her being able to communicate her point in a succinct way. Too many training videos these days are so wordy and slow moving because the person who recorded the video is making it up as they go along. Hopefully she will continue to be the presenter for the rest of the Financial module, but the videos were recorded back in September 2012 on version 3.0 so she might not be with Acumatica anymore.
- Because she is reading from a script (at least I think so), there are some nicely crafted statements. Here is my favorite quote so far from the F120 A/R Credit Verification (Video 2 of 7) video:
“His outstanding balance is over $10,000. We can see here that he violated at least the credit limit rule. Now let’s create an invoice for this customer and experience the system behavior towards the credit verification.”
It sounds like a “you have to experience it to believe it” pitch for an exotic vacation getaway. I can’t wait to experience the system behavior! Like I said, I appreciate the enthusiasm.
- My biggest gripe so far is the video quality. Sometimes it’s difficult to read what is on the screen. Maybe my screen resolution (1366×768) is too high?
Tonight I realized that I didn’t do my F110 Advanced General Ledger (V3.0) transactions in the demo company. Instead I did them in the blank company which was required to complete the F100 Hands-On Financials (V4.0) course. Oops…
So, I went back and installed the demo company in the same database as my original blank company. It’s nice to have the option to install multiple companies in the same database if you want. This makes consolidated reporting much easier.
Now I’m going back through the F110 Advanced General Ledger (V3.0) course and redoing my transactions in the demo company. Lesson learned. As I do this, I realized that I didn’t fully understand the allocations. You have to post an entry in order to put a balance in a source allocation account before you run the allocation. If there is no balance in the source account, you can run the allocation but nothing happens because there isn’t anything there to allocate. Rookie mistake.
I got a response from the email that I sent to [email protected] and the instructor recommended that I go through all the Financial courses, then we can look at everything in one sitting.
So, tonight I went through the F110 Advanced General Ledger (V3.0) course. Nothing too complicated here, just covered the following:
- Recurring Transactions
- Closing Process
Some of the screen names were a little different since this is a 3.0 course (no 4.0 available yet), but the screen IDs saved me. I still really like that you can refer to screens by their ID (eg. GL301000 instead of “Journal Transactions”).
I created an Excel Pivot Table with a list of all the Acumatica 4.0 screens.
In order to view the Pivot Table, please click here.
Here is my SQL code for the query that feeds the Pivot Table:
SELECT MainModule.Title[Main Module],SubModule.Title[Sub Module],
FROM dbo.SiteMap Screen
LEFT OUTER JOIN dbo.SiteMap Category ON Screen.ParentID = Category.NodeID
LEFT OUTER JOIN dbo.SiteMap Pane ON Category.ParentID = Pane.NodeID
LEFT OUTER JOIN dbo.SiteMap SubModule ON Pane.ParentID = SubModule.NodeID
LEFT OUTER JOIN dbo.SiteMap MainModule ON SubModule.ParentID = MainModule.NodeID
WHERE Screen.ScreenID IS NOT NULL AND Screen.ScreenID NOT LIKE '%000000'
AND MainModule.Title IS NOT NULL AND MainModule.Title<>'Acumatica Company'
-- AND Screen.ScreenID='WI000020'
ORDER BY MainModule.Position,SubModule.Position,Pane.Position,Category.Position,Screen.Position
I finally finished the data migration portion of the F100 Hands-On Financials (V4.0) course. Data migration is my least favorite portion of any implementation because:
- I think it’s boring
- It always takes much longer than you think it will
It took me a few late nights to finally get through this. There was some bad data in some of the import files that I had to cleanup in order for the import to go through successfully. When you import AP and AR open bills/invoices, the system automatically makes a journal entry which you then manually have to enter a reversing entry for. Also, the dollar amount of the imported items was $.02 different than what was in the Excel file which must have been due to rounding. I’m assuming that Acumatica put the errors in the import files intentionally so I could get some practice.
The next step is to schedule an appointment with an Acumatica instructor in order to review the setup configuration and data that I imported into the test company on my laptop. I sent an email to [email protected] requesting an appointment. We’ll see how long it takes for a response.
Tonight I went through pages 118-123 in the F100 Hands-On Financials (V4.0) course. Even though there were only a few pages, I spent quite a bit of time going through them (data import always takes a lot of time). The sample file that was created for import had some errors which made for good practice.
Overall, I like the import tool. First you create a mapping definition between your Excel file and the screen that you are importing into. Then you run the import. The errors appear next to each bad record, then you have the choice to fix the data in the screen or export it to Excel where you can make corrections and then re-import.
It’s a little bit of a pain to have to create a mapping definition between Excel and the Acumatica screen. Other import tools that I’ve seen just require Excel file to be in a certain format and then you import directly from Excel. However, the nice thing about creating a mapping definition is that you can re-use the definitions that you create. You can even schedule them to run on an interval, such as every night.
There are over 140 screens in the Finance area alone that can accept imports. That’s a lot of screens! Dynamics GP and Sage 500 ERP don’t have anywhere near that amount. I have to say though that I expected to see something like this from Acumatica because data integration is a key component of a typical web-based product philosophy.
You can also import for things other than Excel, such as another Microsoft SQL Server database, an ACH file from a bank, Salesforce, and XML. This makes the import routine very powerful as an integration tool since you always need to bring data in from multiple places when you are trying to make your ERP system the cornerstone of your business.
Next I will move on to importing other things such as Customers, a Trial Balance, etc. Hopefully I don’t get bogged down with errors as much as I did with the vendors. After I get through Data Migration, I will be finished with the F100 Hands-On Financials (V4.0) course.
Today I went through pages 69-117 in the F100 Hands-On Financials (V4.0) course. This section covered setting up the AP and AR modules. Lots of features to read through. I found myself using a couple of the keyboard shortcuts to save information and quickly transition to the next task. Here are a few things that stood out to me:
- Payment Lead Time – This is an AP feature that allows you to define the average number of days that a payment takes to reach a vendor. I assume that the Aging report will factor this when generating an aging. I haven’t seen this kind of feature before, although I’m not sure I would use it because lead times could vary greatly by vendor. We’ll see, maybe I will be able to override it at the vendor level.
- Default general ledger subaccounts – You have the ability to default GL accounts based on a combination of factors (4 bullets listed below). For example, if my subaccount structure was XX-YYY where XX is region (01 West, 02 Midwest, 03 South, 04 East) and YYY is department (000 General, 100 Sales, 200 Operations, 300 IT, 400 Finance, etc.), then I could put in a code of LL-200 to get the region from the customer/vendor and hardcode department 200 Operations. Or, I could put something like 03-EEE to hardcode region 03 South and get the department from the employee. I imagine that you could even put something like LE-LLE, although that wouldn’t make much sense. But, it’s nice that you can combine things.
- C – Expense account associated with branch
- E – Subaccount associated with employee
- I – Subaccount associated with non-stock item
- L – Subaccount associated with customer/vendor location
- Wiki help – I’m still very enthusiastic about this. In fact, it looks like the overview sections in the hands on exercises are captured directly from the Wiki help system. This is a good efficient use of the existing help. I’ve always been confused by software companies that create separate training material that walks you through each of the settings using different language than the help system. Hats off to Acumatica for not duplicating their effort here. I also tried making some modifications to the standard Wiki help and discovered (as expected) that is very easy to create custom tailored instructions. I can make slight modifications, remove entire sections, or insert blocks of customized help language. As I’ve said before, THIS IS A BIG DEAL.
The Cash Manager Configuration exercise occupied page 36 to 68 in the 157 page .pdf file that I downloaded from Acumatica University.
Nothing too earth shattering here. Lots of setup options: what payment terms are valid for which cash accounts, how to link a GL account to a cash account, whether or not an account is used in AP/AR/Remittance, and even whether or not to upload a check signature image.
There were a few mistakes in the training guide which gave the wrong screen ID or the wrong path to a screen, but nothing too major that I wasn’t able to figure out.
So far, the configuration setup reminds me of Dynamics GP because you have the ability to control things at a very granular level. This is a surprise to me because I expected that these kinds of options would come later in the life of the application. For example, the fact that you can associated which cash accounts a payment method can be used with is a feature that I didn’t expect to see.
Also, I noticed that the check form had the same screen ID format as the other screens. I was able to type in the screen ID using the trick listed here and it took me right to the check form. Yet another cool feature of a web-based application. I could reference a screen in an Excel file, a Word document, a SharePoint page, etc. and clicking the link would take me directly to my screen. I’ll experiment later, but I’m wondering if I might also be able to embed data in the URL so that a link would not only open a specific screen, but also fill in certain fields on that screen.
So far I have completed the training guide steps for a sample company’s General Ledger setup. This was the first 35 pages in the training guide.
I wanted to jot down a few comments/observations/etc.
- The buttons are a good size, but they are only icons so they don’t take up too much space. If you want to see the text for a button, just hover your mouse over it. The buttons look modern and remind me of the look and feel of Gmail.
- The navigation pane is on the left. You can easily drag it left and right in order to re-size it, or you can click the << icon to collapse it altogether and free up more space.
- The general modules are on the top of the screen and the detailed navigation within a module is within the navigation pane on the left.
- Every screen has its own screen ID! For example, the Journal Transactions screen ID is GL301000. I think that this is a big deal. I was first introduced to this idea with SAP because every SAP screen has a unique ID. This is important because it gives the user community an easy way to communicate, even if you have a customized system. When you refer to a screen, you don’t have to say, “The screen that you get to by clicking Finance on the top of the screen, then clicking the Work Area icon on the left-hand side, then clicking on Journal Transactions.” All you have to say is “screen GL301000” and everyone knows what you are talking about. In fact, you can easily navigate to a screen by putting the screen ID in the URL on top of your browser. For example, to browse to the Journal Transactions screen, simply click on http://localhost/AcumaticaERP/?ScreenId=GL301000. If you want to navigate to another screen, simply replace GL301000 in the URL with the screen ID of the screen that you are trying to reach.
- The help file uses a Wiki concept WHICH IS A BIG DEAL. Many times in the past I have wanted to change what the help file in an ERP system says about a field on a screen. Maybe we want to add more explanation about a field. Maybe we want to put in a link that takes you to a page on our company SharePoint site. Maybe we want to hijack a field, rename it, and use it for a completely different purpose. This is yet another advantage of a web-based application. It doesn’t use a locked down Windows help file. It uses a Wiki which can be customized to look exactly the way we want it to look. This can help tremendously with getting all your existing users on the same page and getting new users up to speed. There is no more need to develop a documentation guide outside of your ERP application. Now you can develop the guide directly within the application and your documentation guide will know what screen the user was on when they asked for help. Did I mention that this is A BIG DEAL?
- The screens respond very fast. I was expecting there to be a lag when clicking around, especially since I am running all the server components on a laptop, but it turns out that the speed is very good.
- I was able to complete some of the setup steps by importing from Excel. This is a very nice feature. Just open the Excel file, map the Excel columns to the Acumatica fields, and click import. Very easy to use and very powerful.
- I was able to connect to the Acumatica installation on my laptop from my iPhone. I could login and could click on some of the links, but the navigation pane wasn’t working for me. I’ll look into that later.
I’m sure that I will have a lot more to say as time goes on, but I wanted to jot down my initial impressions.
When I first tried to connect to the Acumatica URL (http://localhost/AcumaticaERP for me), I got this error:
At first I thought I should go back and revise my Installation Success post, but, after some quick Googling, I realized that this was a problem with my IIS installation. I’m not very familiar with IIS so it came as no surprise to me to I missed something during the IIS installation. The simple instructions here solved the problem for me:
Now I am able to login successfully using the default user (admin) and password (setup). It forced me to change my password which is good. There are a lot of configuration options to look through…
Wow, that was a very pleasant experience. The whole installation process only took about 15 minutes and the wizard was very intuitive. Also, I took a look back at the installation file and noticed that it is less that 148 MB. Yes, you heard that right: 148 MB!
This is a very big deal. The size of the installation file and the smooth installation process tells me that the code base is very efficient and I’m dealing with a modern ERP application. I didn’t have to go setup any ODBC connections, install any patches to fix some bug caused by an old known issue from the 1980s, or make a dangerous security change to my laptop to compensate for a poorly written installation routine.
Suffice to say, the PAL is off to a great start with Acumatica!
After 6 patient days of waiting, I finally have my hands on the installation media. I’m going to attempt to do the installation on my own by going through the 52 page installation guide.
First, a review of the computer that I will install Acumatica on and a little overview of the requirements listed in the installation guide. Yes, I know that Acumatica is a “cloud” application, but the “cloud” is just a bunch of computers (nothing too special). What makes a “cloud” application special, in my opinion, is that it is web-based. This allows you to interact with the application on multiple devices and, even more powerful, to more easily integrate the application with other applications. So, I’m not at all concerned that I will be installing Acumatica on my local computer. I still get to harness its web-based capabilities. Also, going through the installation process will help me to learn some of the technical aspects of Acumatica.
Taking a look at the system requirements, I’m going to need the following:
- A web browser. I’ll be using Google Chrome which is supported (version 23 or later)
- A windows operating system. A few different ones are supported, including Windows 7 which I’ll be using.
- 4 GB RAM recommended. Looks like I’m ok here.
- 500MB of available internal hard disk space. This won’t be a problem for me, plenty of space available on this laptop.
- Microsoft .NET framework version 4.0 or later. Hmmm, I don’t really know what I have (.NET always confuses me). I’ll give the installation a try and see if it fails.
- Microsoft Internet Information Services (IIS) 7.0 or later, depending on the underlying operating system. I think I’m ok here. I turned on IIS using these simple instructions.
- Microsoft Windows Installer version 3.1 or later. I’ll ignore this since I’m pretty sure I have it.
- Critical system patches and updated for Microsoft products. Ok, this looks like something the lawyers put in. I’m going to ignore this too.
- There are a few different versions of Microsoft SQL Server that are supported. I have SQL Server 2008 R2 installed on the laptop.
Sorry about all the technical stuff. I didn’t intend for this post to get so technical. Basically, there isn’t anything weird in the requirements list. This is all standard Microsoft stuff and the technology is very modern.
Now I’m going to tackle the installation…
I will be starting with the F100 Hands-On Financials (V4.0) course. Tonight I downloaded the material needed to complete the course. I will go through the 157 page .pdf file and setup a company from scratch. I like the from scratch approach. In the past I have worked with demo data when learning an ERP product, but it was hard to pay attention since the data was already created for me. I’m hoping that building a company from scratch will help me to stay more engaged during the learning process.
After going through the material, I am supposed to take an exam and then review the exam with an instructor. I like that I will be getting some human interaction during the process. This should be interesting…
However, before I begin the course, I need to get my hands on an installation of Acumatica. I sent an email off to the [email protected] email address to see if they can help. Maybe they will have a pre-built virtual machine?
Today I received my invitation email with a link to create an account on the Acumatica University training platform! This gives me access to an entire library of training courses. I like the online format. With the previous ERP products that I learned, I had to dust off photocopies of old training manuals. Having the information online is a big improvement.
Taking a quick glance, it appears that there are 41 courses available. I’ll be checking with ARCVAR to see which one they would like me to start on first.
I currently work for an engineering/logistics/manufacturing company in Cleveland, OH. We use Sage 500 ERP and I am part of a team of four dedicated to customizing the product, writing reports, analyzing data, etc. Bottom line: I don’t have a working installation of Acumatica.
I was able to connect with A Really Cool VAR (ARCVAR). They are going to help me get the product installed and get me access to the training material. In exchange, I will help produce training documentation for them. I am at a disadvantage since I will be working in a more theoretical environment, but at least I have something to get my hands on.
Props to ARCVAR for going along with my unorthodox proposal. For now I’m going to keep their identity a secret. That way I can write whatever I want without incriminating them.
That said, please note that everything written on this site is from ME ONLY. NOTHING here represents the views of Acumatica or ARCVAR.
An expert (ANEXPERT for short) is a person who has a lot of experience in the ERP arena, but they consider their experience to be a complete body of work. They often begin their introductions by touting their decades of experience. A person might not even realize that they consider themselves to be ANEXPERT. However, you can usually tell by their tone of voice and body language.
The problem with ANEXPERT is not the amount of knowledge that they have. The problem is with their mindset. Their vast amount of knowledge could be helpful if they were a PAL, but, instead, it hinders them because they are not adopting a learner’s mindset.
Every company is unique. When you bring someone in to look at your business, you are hiring a professional learner who will study your unique situation. If you hire ANEXPERT, you risk getting a cookie cutter ERP implementation that is pulled out of that person’s prior project archive and dusted off to look like something new for your company.
So what do I mean by a Perpetual Acumatica Learner (APAL for short)?
Perpetual can be defined as: “never ending or changing”
This blog will approach Acumatica from a learner’s perspective. Currently, I hardly know anything about Acumatica. However, as time goes on and I acquire more and more Acumatica knowledge, I might be tempted to fall into the mindset of ANEXPERT. This is dangerous. Even the most seasoned ERP veterans should always maintain a learner’s mindset.
APAL never arrives, but is always on a journey. It defines a mindset more than defining a person. Be careful, you might be in APAL mindset one minute, but quickly slip into ANEXPERT mindset the next. This blog is an attempt to maintain and promote the mindset of APAL.
So, APAL is a constant reminder to me that I want to remain a perpetual Acumatica learner, never ending, never changing, always hungry for more.
I have decided to begin writing a blog about learning Acumatica. This is going to be quite a learning experience, both with learning Acumatica and with trying to communicate via a blog. It could be a failure or a success. We’ll see how it goes…
I first became interested in Acumatica sometime last year (can’t remember exactly when). It was mentioned on the Panorama Consulting Solutions blog (which I follow). As I began to learn more and more about Acumatica, I became more and more intrigued. Here is a product that takes advantage of deployment via a web browser, yet they aren’t going to tie you into a SAAS solution (not yet at least :)).
My career began at Deloitte in the audit department, but I quickly found my way into the ERP arena which I found to be a good blend of my accounting and technology interests. I am 31 years old now and pretty clear that I want to continue to focus my career on ERP for the foreseeable future. My experience so far has mostly been related to the following three ERP products:
- Microsoft Dynamics GP (formerly Great Plains)
- Microsoft Dynamics SL (formerly Solomon)
- Sage 500 ERP (formerly MAS 500, formerly formerly something else, maybe Acuity?)
I have also had experience with SAP, QAD, and Quickbooks while working for a company that had gone through two acquisitions (hence the simultaneous use of three products). It was a headache, but a good learning experience.
I ruled out pursuing SAP because I like to work with medium sized business and SAP is more for larger corporations. The three products mentioned above have a good history, but I’m not so sure about their future. Dynamics SL is the most ignored product in the Dynamics SL family and Sage 500 ERP has always been on the back burner in the Sage product portfolio. Dynamics GP is going strong, but it was created in the 1980s and stands on a very antiquated technology foundation.
Everyone is clear that web browser solutions are the thing of the future. They allow you to use multiple devices (computer, tablet, phone, etc.). They allow you to tap into the Cloud as a deployment option. They also allow you to deploy your solution to other locations without any kind of technology prerequisites.
If I were 51, I would probably ride into the sunset of my career on Dynamics GP. But I’m not 51, I’m 31, and I have time to watch a product develop.
There are other web-based ERP products out there (like NetSuite and Intacct), but I’ve decided to invest my time with Acumatica. It’s somewhat of a gamble because the ERP arena has always been uncertain. It takes a long time for an ERP product to mature. During that time the development of the product can easily be frustrated or altogether halted by changes in the company structure, acquisitions, or breakthrough changes in technology. It is a gamble, but an exciting one. I’m going to keep my eye on Acumatica as it grows and matures. Hopefully, I will be able to grow and mature along with it.