Business Central – SIFT Technology, NAV Conversions, and Performance

Mature man getting a headache from taxes and billsWe’re in the process of moving a client from a 3.6(!) database to Business Central 18 (Spring 2021). In the database, there is a custom table that has several Flow Fields defined as sums of the Amount fields in G/L Entries and the Detailed Customer Ledger Entry table.

Just for the giggles, you should know that the history goes back to 2003, and there are 1MM+ records in the tables, but not many more than 1MM records. To make it a little more interesting, we didn’t do the original customization, so we’re figuring out the customization as we go.

Fighting the Performance Battle

We’ve converted the data, refactored everything to an AL extension, and are testing reports. The problem came when we hit a couple of reports based on a table added in the extension. The table has 4 flow fields. And the reports take forever to run (hours in one case). They are slow on a local copy of the old database, but they are much slower on BC18. Much slower. Like ten times slower.

So we looked at the old code. As it turns out, the flow fields were based on data from the base Business Central table as well as data from the extension. Previous customization in C/AL (3.6) had added a key on all of these fields. As you may know, table extensions cannot use fields from the base table as part of keys.

Because flow fields rely on keys, performance took a huge hit.

The Solution

the solution was actually fairly straightforward. Since we needed the data from the base table in the extended table, we created triggers to copy the data. We were then able to build keys (SQL indexes) on the data in the table extension.

    trigger OnBeforeInsert()
    trigger OnBeforeModify()
    trigger OnBeforeRename()
    procedure CopyFields()
        "ext G/L Account No." := "G/L Account No.";
        "ext Document Date" := "Document Date";
        "ext Posting Date" := "Posting Date";
        "ext Amount" := Amount;

The result was dramatic: a report that took 2.5 hours to run before the change ran in 15 minutes after the change. It still seems like we can optimize this a bit, but there also a tremendous number of calculations happening in the report.

Tech Support Woes

As both a provider of tech support and a consumer, I understand both ends of the conversation. I still tell the story of the customer who called and said, “My computer monitor just went black. Poof.” 

I asked, “On the power button over there to the right on the computer tower, there is a light. Is it on?”

“No,” they said.

“Could you press the power button?”

“Sure,” they said. I heard a computer whirring to life in the background. There was an uncomfortable silence.

“I bet you were taking a disk out of the drive, weren’t you? The eject button is right above the power button,” I offered.

“Thanks, that got it.” was the reply. Click.

And then there’s this, from yesterday. We are working with tech support for an extension to the eCommerce platform, Magento. We got the following email, “It seems that we do not have access to your Magento admin any longer (link to the picture above). Please update the access data so our Tech Team could look into the details and assist you. (Link to the secure site for submission of the credentials.)”

CAPTCHA is the little utility that asks you to select all the cars from a checkerboard of nine pictures. Irritating, I know, but it prevents hundreds of bogus registrations. I’d guess that the Tech Team at this vendor can’t distinguish cars from traffic lights.

More to the point, we’ve observed that this particular vendor likes to gaslight us. With 35+ years of experience, I’ve probably got more years’ experience in IT than most of the Tech Team has drawn breath. But this brings up a bigger issue.

It’s a common thing in the tech world–or so it seems–to gaslight end users who aren’t technical (act like they are crazy or don’t know what they’re talking about when they report an issue). Don’t get me wrong, there are users who can’t change the battery in a wireless mouse. There are also times when the data provided isn’t adequate to solve the problem, or when the problem comes and goes without leaving definite traces or symptoms.

We teach our Tech Support employees to respect and understand our customers. I’ve learned a lot in 35 years by listening to our customers. Some days I thought their way was better; some days I thought my way was. We always worked out a solution by combining ideas.

That’s the way it should be: IT folks should help end users figure out how to use their technology better. That’s what we do at Data Guidance Group.

My two cents.


SolarWinds Security Product Breached

DHS reported a breach of several government agencies, apparently by Russian hackers. The full article is here. More details on the technical implications of this are here.

Many government agencies use SolarWinds products; any organization relying should follow the detailed developments closely.

The most technically interesting and sophisticated item in this report has been the hackers’ ability to bypass two factor authentication as discussed in the second article above.

No Action Required iPhone Hack

For most people, the thought of having their computer or smart phone taken over is frightening. Our standard advice to prevent hacks: do not open anything from someone you do not know. Do not open anything you were not expecting. Do not open anything that looks suspicious.

Wi-Fi iPhone Hack

For this newest iPhone hack, you don’t have to do a thing. Take a look at the video in the article below to see a rapid, frightening hack:

Just to check your “hackability,” let me ask this: Did you at least think about it before you clicked that link?

Protecting Yourself

There is redemption in the article: keep your devices updated; this prevents the hack. I know there can be irritating changes in iOS with new versions, but nothing like the irritation of being hacked.

PPP Loan – IRS Releases Rulings on Deductability of Expenses

When the CARES Act that created the PPP (Paycheck Protection Program) was written, Congress included a provision that specified that PPP proceeds that were ultimately forgiven under the program were not taxable. The IRS has issued a series of Revenue Rulings (starting with 2020-32) that state that expenses paid with tax-free income are non-deductible.

Many people believe that this does not represent the intent of the legislation because–in effect–it adds the amount of the loan to the forgiven borrower’s bottom line. By denying the deductability of the expenses paid with the loan, the IRS accomplishes the same tax effect as including the proceeds of the loan in income.

Now the IRS has released ruling 2020-27 stating that even if the loan is not forgiven until 2020, the expenses are not deductable in 2020.

If you have questions about this, you should consult your tax advisor. For safety’s sake, it is important to plan for the extra income in 2020.

How to Try Microsoft Dynamics 365 Business Central

Microsoft D365 Business Central looks like a very busy web site when you login in. There is a learning curve; finding features and reports goes faster if you have a bit of help. We released an 11 minute video this week to help navigate through the system. A few features covered are discussed below.

Selecting Business Central Role Centers

The first page that comes up when you log in to Business Central is called a Role Center. There are different pre-built roles in Business Central like “Business Manager” or “Accountant.” Each of these has different Role Center links that make it easier for someone in that role to get to the functions they need. To see and change a user’s Role, click on the gear icon in the upper right corner and choose, “My Settings.”

Dynamics 365 Business Central Role Center screen capture.

Managing Multiple Companies

You can have as many companies as you want to set up in Business Central. Change companies on the “My Settings” page just like the Role. The license that installs with Business Central (the trial license) requires that company names begin with CRONUS.

Menus, Tabs, and Actions

The Role Center for Dynamics 365 Business Central also has a drop down menu at the top. Quick links (like Customers, Vendors, and Items above) take you directly to needed data. Actions are like quick links; actions preceeded by + will add a new item. So the Action labeled “+Sales Quote” creates a new sales quote.

More Information on the Video

The video covers this and more. Included are suggestions for using the sample data. For example, Blue, Red, and blank locations (warehouses) are good choices because they don’t require you to use the Warehouse Shipping and Receiving functions in the system to create and post Sales Orders and Purchase Orders. The full video runs 11 minutes and provides an introduction to finding what you need. We are planning a follow up video on making design changes in BC.

The Business Central video is available on YouTube. If you’d like to set up a trial of BC, you can do that here. More information is available on our website.


PPP Changes Signed

Perhaps a blog about IT and business software isn’t the place for news about the Paycheck Protection Program. We’ve always focused on business applications of technology, though. In this case, the PPP is an important tool for business continuity. So we want to make sure that our readers at least have occasional information about what’s going on.

Here’s an article from Forbes discussing the changes to the PPP that were signed into law last week.

Congress Proposes PPP Changes

It’s hard to tell exactly what Congress will be able to pass, but it seems that there is bipartisan support for helpful revisions to the Paycheck Protection Program. Since the PPP loans had different disbursement dates, the conservative answer is to plan using the currently available program parameters. Here’s a recent Forbes article on proposals in Congress.

E-commerce Site Sales Increase by Double Digits

According to a New York Times article, Amazon sales are up 26% from a year ago in the First Quarter of 2020. Motley Fool’s stock recommendation service doubled down on their Image from shopping site - view cart icon.recommendation of Amazon as a buy. Etsy, a marketplace for small handicraft and other sellers, also reported a 34.7% increase in marketplace revenue. eBay is pretty much even for the 1st Quarter, with a 1% decline in marketplace revenue.

As large as these companies may seem, they have one thing in common: many of the sales made on these platforms are made and fulfilled by small companies. The anecdotal information we have is also encouraging: a $5 million retail distribution company is up 40% this year over last, with most of the increase coming from online. Another small retailer client reported a 49% increase in online sales.

Coronavirus is devastating sectors of the economy, but it may also offer an opportunity. If your business is not currently online, now might be a good time to test the water. Let us know if you would like some suggestions.


PPP Loan Forgiveness Application Out — Watch for Gotchas!

Checking calculations on a spreadsheet.You can download the PPP Forgiveness Application form here. Here are a few things we see (this isn’t professional advice, just a few points to think about):

  • The period over which you collect expenses (payroll and allowed expenses) is 8 weeks, starting either from the date you received the funds, or the first day of the following pay period (Alternate Payroll Covered Period). Whichever you choose (and it can make a difference, so you need to do the calculation with both dates), you can include expenses paid or incurred. You’ll have to study the rules for the difference, but you can’t count expenses twice.
  • Since you based the amount of the loan of the average monthly payroll cost times 2.5, many businesses will not get forgiveness of the entire loan amount. Example: suppose you have a payroll of $20,000 per month, all salaries paid on the 15th and end of month ($10,000 per payroll). You request $50,000 in PPP loan (2.5 x $20,000). Now you calcuate 8 weeks of salary. You’ll probably have only 3 actual payroll dates in the 56 day (8 week) period. If you hold one period of salary, you have (apparently) incurred another payroll worth of expense. That means you have payroll expense of $40,000, but you have to come up with $10,000 of rent, mortgage interest, etc. during that 8 weeks.
  • Owing money isn’t really an issue. The interest rate is 1%.
  • Be careful what you spend the money on. If you deposit it into your general operating account and pay bills, you may not be using it for “covered expenses,” but you agreed (and signed) that you would only use it for “covered expenses.”
  • One last item: there’s this nasty little signoff on the last page: “does not exceed eight weeks’ worth of 2019 compensation for any owner-employee or self-employed individual/general partner, capped at $15,385 per individual.” This may further limit the compensation you are allowed to include.

Lastly, all of this is changing. Several organizations including the AICPA and NFIB are pointing out that this math doesn’t work for all businesses. So expect to see some changes.