Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
As many of you know, on the Customer Card you have the option to set up your customers as Open item or Balance forward. A few differences about the types are mentioned in KB871990 below.
Balance Forward Customers vs. Open Item Customers
However, there are times when the type was selected by mistake, or later, as time goes by, your company chooses to make a change to the other type for whatever reason. Most of the time you can’t just flip the option via the front end in GP unless certain requirements are met. As you all know anything is possible to change with SQL, but most times if you are unable to change via the front end, there is good reason to not just change it via the backend. I’d like to explain some of the reasons and provide some things to take into consideration.
One of the requirements of changing the balance type in the front end is as follows:
You cannot change the balance type if there are unposted, posted or historical transactions for the customer. You receive the error below when you try to change it on the Customer Maintenance Options card if you haven’t met the requirements.
"You cannot change the balance type if there are unposted, posted or historical transactions for the customer".
If you don’t want to remove historical data, you can look at doing this via the backend, but it’s at the customer own risk.
Again, the recommended way to solve this would be to either set up a new customer and use that moving forward or to reduce the customer balance to zero, move all documents to history by running Paid Transaction Removal, remove all historical data for the customer and then change the balance type of the Customer Card. The disadvantage to the latter option is that you lose all your data.
There are some options via SQL, but again you take this at your own risk. The steps do not take into consideration 3rd party tables, customizations etc. But, I wanted to share this information because we know people are sometimes just flipping the flag in the backend and not realizing the implications of doing this, so this is what I wanted to shed some light on.
Below are the two options on how to change the balance type and things to consider under each.
How To Change Balance Forward Customers To Open Item When Using Microsoft SQL
This can be done using a SQL Statement in SQL Query Analyzer. However, before proceeding, be sure to read the entire section:
NOTE: BALNCTYP 1 is for balance forward customers BALNCTYP 2 is for open item customers in the RM00101 table.
1. Verify your customer's balance has been brought down to zero first. This can be done at Cards - Sales - Summary. Select the customer ID in order to check their balance.
2. If the customer's balance is zero, you can proceed to change the balance type by running the SQL Scripts to change the balance type. If it is not, proceed to step 3.
a. Verify your customer is set to a Balance type of Balance Forward. The BALNCTYP column should show 1. Replace 'XXX' with the customer ID.
SELECT CUSTNMBR, BALNCTYP FROM RM00101 WHERE CUSTNMBR = 'XXX'
b. Then update the customer to an Open Item customer by running the following statement.
UPDATE RM00101 SET BALNCTYP = 0 WHERE CUSTNMBR = 'XXX'
*This is all that needs to be done*
3. If the customer's balance is not zero:
a. Enter a credit document or debit document to bring it to zero and let it flow through GL. (Note – step c will have you bring the balance of the customer back up.
b. Now, update the customer's balance type.
c. Repost a credit/debit to the customer to bring the balance back to what it was prior to the change. NOTE: If you did not post through GL in step 3a, then don’t post this document through to GL either so it’s a wash.
Listed below are the ramifications for not bringing the customer's balance down to zero first. Following the steps above, will prevent ramifications 1 and 2 from occurring.
1. The first issue arises if the BBF customer has an outstanding bbf amount. If you change that customer to an Open Item customer, you are unable to apply a payment to the bbf amount. That is to say, if the BBF customer had an outstanding bbf of $35 when you make the change you cannot apply a $35 cash receipt to the bbf, because it will not show up in the apply window.
2. The second issue arises from the HATB and outstanding bbf amounts. The HATB is built from a temp table; however, that temp table looks at the Apply Tables for much of its information. The problem comes into play because the invoices that make up the $35 bbf were moved to history during PTR/Consolidation process. They are moved to history but have no apply records, which is normal for BBF customers. However, when you print out the HATB when they are Open Item customers it will look as those invoices are still outstanding because they have no apply info. This is a problem because now their Aged Trial Balance and HATB do not match and the HATB and GL will not tie out.
How To Change Open Item Customers To Balance forward Customer When Using Microsoft SQL
Changing the customer's balance type from Open item to Balance forward. This type of change can be done through the backend using Query analyzer. When you go from Open item to Balance forward all you need to do is write a simple update statement and run it against your company database.
Example: If you would like to update only one customer you could use the following script. (Replace XXX with your customer number)
UPDATE RM00101 SET BALNCTYP = '1' WHERE CUSTNMBR = 'XXX'
From all the testing we have done in the past, making the change in the RM Customer Master didn't seem to cause problems with reports, the PTR process and generating bbf's. There have also been no reports of issues from customers after they made the change other than the one note below. However, it's always best to test this out in your own environment in a TEST company. Make sure you can enter new transactions after the change and processes all run smoothly. Ensure your data is in the right buckets on reports (current vs noncurrent) after running reconcile/PTR etc.
NOTE: If you are using national accounts – you will not be able to make the change. You will receive the error trying to update in SQL if the customer you are updating is set up in National accounts as a Parent or Customer under (Sales >> Cards >> National Accounts).
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "RM_Customer_MSTR_NA". The conflict occurred in database "TWO", table "dbo.RM00101".
The statement has been terminated.
A reminder, to become a member of a national account, a customer must meet the following criteria:
•The customer must be an open item customer.
•The customer can be part of only one national account, as a parent or as a child.
So, when you try to update it to Balance Forward customer it errors out because it must be Open Item if it sees a partner/child relationship for that customer.
Hopefully this provides some good insight and helps you if/when it comes time to having to change the Customer balance types.
Angela Ebensteiner | Sr. Technical Lead, Microsoft Dynamics GP