Changes in Charts, Vendors, Customers & Items in Dynamics GP (Video)

Did you know that Dynamics GP offers the ability to change and merge accounts, items, vendors, and customers to clean up your data for more efficient use? We will show how this is done as well as tips on large scale changes.

Transcript below:

– [Katie] Hello, everyone. Thank you for joining us today for a Dynamics GP Coffee Break, Changes in Charts, Vendors, Customers, and Items in Dynamics GP. My name is Katie and I’ll be the facilitator today. If you have any questions during the presentation, please type them into the question area in the control panel on the right-hand side of your screen and we’ll answer questions at the end.

This session will be recorded and posted on our blog as well as shared with you later this week. Now, I would like to introduce our presenter today, Don McNulty, who is a solutions architect at Encore. Don, I’ll pass it on to you.

– [Don] Thanks. So, today what we’re going… Can you hear me okay?

– Yep.

– Okay, good. Perfect. All right. So today what we’re going to talk about is changes in master file. These include the charts, the vendors, the customers, and the items. So, we got master records that we can change quite easily. Customers, vendors, GL accounts, and items are the main four master files that GP has.

There are additional tools as well which allow you to change things like territories and a bunch of other stuff which I’ll discuss very briefly. There’s two ways of doing these particular changes, we can do them individually one by one or we can do mass changes. Okay?

And then there’s some requirements that are required for each of these different locations depending on what it is that you’re doing, what are the limitations, and what can we do, and what can’t we do. So first off, it comes from what they call a PSTL or professional services tool library. This is something that is part of Great Plains and as of 2013 R2, it became free to all users inside GP, so you can download this and install it without registration keys.

So, inside here what we have is a list of all of the different professional services tools that are available. I’ve highlighted the couple that are in here that I’m going to be using. The customers and the vendors have actually been moved right into GP underneath utilities and I’ll show you those, and things like that. Now, in order to use some of these tools, you might need to be either Sa or the database owner.

And it depends on the tool that you use as to whether or not you require exclusive access. The GL chart of accounts, for instance, because it’s fundamental to all tables inside GP, you must be the only person in the database and you must be logged in as either the database owner or the systems administrator.

Not a big deal. Usually we do these things after hours and things like that. So that’s what it looks like. We pop it into the PSTL tools. Usually it sits down at the bottom here. It may not show on your screen. You have to drag this part up to take a look at it, and then we can take a go from there.

Now, additional features besides the standard items, customers, vendors and chart, under the master record, you can change sites, so you can change… If you don’t like your warehouse sites where you want to combine multiple sites that you no longer use into a history site, you can do so to reduce the size of the inventory files.

You can combine sales people together. You can modify salespeople. You cannot combine salespeople. I’m not sure why they don’t have a combined, but they only have a modify. Territories you can combine and modify. Fixed assets you can modify only, you cannot combine.

And then there is an employee modifier, but that only works on U.S. payroll, okay? It does not work on Canadian payroll. Under the transactions, there’s some really cool tools inside PSTL that you might be interested in. The biggest one and the most used one is the RM Unapply Historical. Once you’ve applied a transaction in receivables and you’ve moved it to history, there is no way you can bring that document back in order to reapply it correctly.

You can go into professional service tools, and you can unapply those historical documents. There’s a couple of checkboxes that you can use. The minimum check means that you can set 25 doses at minimum check and we will not write a check for less than 25 bucks. SOP-POP check so that when you’re entering your sales order processing documents, that you must put in a purchase order POP number or purchase order number from the customer.

You can do a Doc Date Verify so you can say, “Do not allow dates that are outside a certain range.” That’s really nice from the point of view so you don’t end up keying in something like 2204 instead of 2024, something like that. And then lastly, there’s an item reconciler. This is almost popular.

The item reconciler runs about 10 times faster than the regular item reconciler. It only does what’s required and works much, much faster than the one that’s in the utilities. They’re inside here and these will be coming out as a slide deck to you. There is documentation on all of these features as well as a whole bunch. All the ones that you saw on that previous slide, they’re recorded inside here. And if you don’t have a copy of it, you can always download it from this location here as well.

So, I’ve provided those as part of the slide deck. Okay, so let’s go talk about what we’ve got. So, demos. So, basically what we have here is we have a list of different things. So, I’m going to start with the chart of accounts. So, I need to change my chart of accounts.

So inside here, I just hit the PSTL, I come up to here, I hit this one here, it pops up this screen here. Once this has been loaded, I can run any of these particular ones just by selecting a check account modifier combiner, and I can hit next. Now, when I hit next, it opens up this box here. Now, I’m logged in as Sa.

If I was not logged in as Sa, it would tell me that I’m not logged in as Sa, so it would tell me that I’m at… or that I’m not the only person in this particular company’s database. So I can select by a range, so I can go from starting number two to any number and I can change different field types, or I can do it by account or I can import. So when I do it by account, I can just quickly take a quick look inside here, and I can grab…say I grab my sales discount available, and I could take in like so, and then if I was to hit the convert, everything that was in 1205 would automatically become in 1206.

So, this changes everything. It’s as if it was always 1206. It changes the chart of accounts, it changes all posted transactions, all historical posted transactions, all transactions in GL, AR, AP, inventory, fixed assets, everywhere inside GP, it will change it. It does not change third-party products 100% of the time.

It does a lot of third-party products, those that have followed a standard coding for their account index and things like that. If they’ve used the same name, it will do them. But if the writer of the third-party code decided to use a different name for a field or put it in a weird place or a word structure, it will not change it.

Now, one of the things that it does here you have to be careful of is there are two places that will cause issues for you. If you are using this to move segments around, so you’ve gone, “Okay, I’m going to put my first segment to my third segment or my third segment to my first segment,” or whatever it is that you decide you want to do, you have to be careful about the overlays inside GL, inside GP.

So, Canadian payroll, for instance, has an overlay by department. That department overlay is not changed by this and has to be done manually after the fact. Same with sites. Sites have overlays. If you change a department code and you move it out, you need to change the site that might affect that particular department or location.

Just something to think about while you’re doing it. Now, the other thing you can do, of course, is that you can click on the, let me just close this for a second. Hit cancel. I’m going to go into next inside here, and I’m going to hit the import button, and then it gives me a Validate and Modify. So, this is how I would do a mass number. So what I did is I ran out these three accounts. This is just a smart list chart of accounts.

I dumped it out, did this this morning. I’m going to grab these three accounts and I copy them into here. Okay? I now change them to 1111, 1121, 1131, so they’re going to move by one up so that they’re unique numbers. I then have to save this as a tab-delimited form. Anything that’s inside the professional service tools library expects tab-delimited.

Okay? So, the tab-delimited form means that you just go file and you go save as, and when you go browse, you find the file and we go down to here and we look inside here for the tab-delimited text, like so. Like that.

So then we can save that as a tab-delimited text. And when I save that as a tab-delimited text, it will then save it where I need it to be. Then what I can do is I can hit the Validate button because I’ve already done this before, so let me just go back here. When I click on the Validate button, it asks me which one. There it is there, there’s my GLAcctChg. And we go Screen and it tells me which one is the invalid account.

So, it finds the account and then finds the appropriate checks to make sure the first one exists and the second one exists so there are no invalid accounts. So, I could now hit the Modify and it would run my spreadsheet. This spreadsheet can be as large as you need.

Obviously if you do hundreds of accounts, it’s going to take quite a bit of time, so make sure you’ve scheduled enough time and you’ve done this in a test environment to make sure that it does what you want. You have to be careful that if you have account A… Sorry. If you have account A and account B and account C, and you want A to become B and you want C to become or B to become C and C to become D, if you were to run this as a spreadsheet, you would end up with a single account because A would move into B, then all of B, all the A which has now been emerged into B would become C.

All of C would then go into D. If you wanted to actually do this, what you would have to do is you would have to do this like that. So, C would become D which frees up C, B can then become C as a new account. A becomes B as a new account and everybody’s happy.

You just have to be aware that this is something that may occur. Okay? So, that’s how a GL change works. It changes absolutely everything in the GL. Your financial statements are not changed. So management reporter, jet reports, whatever you’re using for your financial statements will not change as a result of this change.

Okay? So you’ll need to rewrite those in advance. So ordinarily what we do is just with a big chart change, we run it in a test environment, you rewrite your financials. When you’re ready with your financials, we go back and do it in the live company. Could be done at any time. On a single account because somebody has made a mistake and entered, you know, meals and entertainment twice, they entered it as 7701, 7700 and 7702, you could easily just take the two and combine them both together, and then you’d only have a single account which is what you are looking for.

Or you’ve got a department wrong, put it into department 100 and it should have gone to department 200, you can change that as well. Well, those are the types of things that you can do within the account modifier. Okay, next. So, underneath sales, and at the bottom here of the utilities, you’ll see customer combiner and modifier.

You’ll also see exactly the same thing underneath the vendor. Now, I’m going to deal with the vendors today simply because that’s what I decided to do. Customers and vendors work exactly the same. The screens themselves are identical. So, if I look at this screen and I look at this screen, there is no difference between these two screens. The only difference here is whether or not you want to do different balance types which you have a section in customers that allows you to do that.

Now, oh, I should have mentioned in the general ledger, the one that… There’s two caveats to it. One, you cannot change an allocation account to a posting account or combine them together. You cannot change a unit account into a posting account. You cannot change a balance sheet account into an income statement account or an income statement into a balance sheet account.

Okay? So, you do need to be aware that those particular pieces must be the same between the destination of the account as well as the start one. The same is true here. In the customer type, I cannot combine a balance forward into an open item and I cannot take an open item and put it into a balance forward. But on the vendor combiner, I can just quickly inside here, I can go into here and I could take AUSTRALIA and I could type in a new name, A-U-S-T-R-A-L-0-0-0-1, like so.

I could do that. Destination vendor’s not found because of the Vendor Combiner. So if I click on my modifier… Okay, let me just go Ctrl+X, change it to the modifier, yep. And I grab my AUSTRALIA again and then I go Ctrl+V and now it’ll be happy.

And it allows me to add as many as I wish to do at once. This is extremely good in vendors and customers because what we want to do is this… Ordinarily, it’s because somebody’s added a new customer.

One of the other ones that is of interest to us is inside here I have ELECTRONIC003 Hill’s Brokerage Service. You know, well, I don’t want Hill’s Brokerage Service to get that. I want it to be Hill’s Brokerage Service. So, I grab this one and then I can type in HILLSBROK like so, and now it become Hills Brokerage. So if a company’s changed its name or been… so XYZ becomes ABC Corp, you can move it so that it sorts it properly inside your vendor.

If you wish to combine two together, you can do so. You can go AUSTRALIA could go into ELECTRONIC003 or into HILLSBROK. You can’t do them both at the same time, you must do it as two separate runs. Okay? Now I’m going to click this and remove all, yes. I can also, with the Vendor Modifier, I can do a lookup and I can go into here and I can grab a VendorChg, and when I hit Validate, it populates this particular screen and it populates it with three separate ones that I put into my spreadsheet.

And again, my spreadsheet was just… I grabbed this. I grabbed the information from here, and I copied it into here so that I’d have it. Now, I had to use HILLSBROK001 because if I used one here and I hit tab, it will tell me that it’s a duplicate. That means that I already have a one.

So, I probably would want to combine the two together instead of modifying them but I’m just for the demo purposes, I can show you this one right here and it gives you these errors and things like that. When this screen is accepted, we can then go on from there like so. So once we’ve done that, we could then hit the process and these three would permanently change to that.

This changes all purchase orders, anything to do with purchasing and anything to do on all the transactions including like fixed assets inside where you’ve linked up your vendors into your purchasing amounts. This also works in accounts receivable, accounts receivable, it does it for all the RM transactions as well at history and open, as well as all the sales order processing documents.

These two, especially sales order processing, if you’ve got a large sales order processing database can take a long time to do. There’s a lot more work to be done in the customer and the vendor than there is in the GL because the GL in a lot of places is stored as an index number, so we don’t need to worry as much about it. Okay?

So that’s there. Now, when we get to items, which is the next one that I’m going to talk about is we have a number of different things, number of caveats. There’s very few caveats inside here and they’ve put it into here. They have also added inside here, underneath project accounting, they have project code modifier. So, I can modify the contract, the contract ID, project number, project ID and the cost category ID.

For those of us using project accounting, this is extremely useful in work because if we get things in the wrong location, we want to be able to move them and revamp our projects to meet new criteria. Easy to do. There is no import.

You have to do them one by one at this point, okay? But they are now available directly underneath utilities. Used to be a separate load that you had to do but they have now bundled it as part of that. That I believe requires 18.4 or 18.5 or better before it shows up on your screen here. This is 18.6 that we’re looking at today, which is the most current version. So I’m going to go back to the PSTL tools inside here and I’m going to go to the Item Number Modifier, and I want to talk about the Item Number Modifier.

So inside here I got a start number and end number. And again, I’ve got that same issue inside here. I can take my items. Now, there are a few caveats to it that you need to make sure that you’ve checked. The items that get moved from one item to another item, we can do whatever we want. We can take an existing item and change it to a new item.

On the Combine, there’s some serious limitations that may cause you some grief. First off is the number of decimals for the item must be identical, both on the dollar value and on the unit. The unit of measure must be the same. Okay?

So, things that you cannot change inside inventory. So when I look at inventory and I look at an item… And I bring up one of my favorite items. We’ll just bring up the two here. So, you’ll see here the Valuation Method is shaded. That Valuation Method must be the same between the multiple ones you’re bringing together.

So, I cannot bring an actual cost item into a FIFO Perpetual or a FIFO Perpetual into an average cost or into a periodic. I cannot change the Valuation Method. I cannot change the inventory type, so I can’t take a service part and make it an item. I can’t take an item and make it a service part.

Unit of Measure Schedule, I need to have… The thing about the Unit of Measure Schedule is that it locks down this. The base unit of measure must be the same and the quantity decimal places must be the same. So, this one’s 0 and Each. So, therefore everything must be 0 and an Each. So, I can’t bring a piece or a pound into an Each. And the other thing I cannot do is I cannot bring in serialized numbers into non-serialized numbers or non-serialized numbers into serialized numbers.

So, those are the types of things that you have to be aware. What it does is it tries and it comes back and it gives you an error message and it tells you what is wrong. There’s about five things that are in there. It’s all described well inside the manual for the PSTL tools as to what it is that you need to do. So, when I’m doing that, I just wanted people to be aware that it’s not as simple with the items, and it’s one of the reasons why it’s still in the PSTL tools.

It requires a Sa in order to run. So, the Combiner does exactly the same thing. It looks exactly the same screen. You’ve got starting number with a description then a combined item, and then you can do a Validate and an Import. Again, Validate and Import requires a tab-delimited form. Column A is the existing number, column B is where it’s going and all those things must match, although they’re not recorded on the spreadsheet at all.

It just verifies as it does it. If it tries to do one that is not matching in the right locations, it will error it out and give you a list, so that’s fine. So, it’s very much an iterative process. You tend to run it and then keep on going from there. Other tools inside here, these are the other tools.

So this Doc Date Verify allows you to turn this on and you can turn this on and these boxes here are the ones that you can turn on and off and it verifies the doc date. That will verify the add items in POP and SOP, and then the Decimal Place Tool, so it keeps that correct. In your items inside here, there’s your Site Combiner and your Site Modifier, so you can modify your sites.

If you’ve got a large number of descriptions that you wish to change, you can use the Item Description Modifier. What that allows you to do is since you start with the item, start with the item number and the ending description and then we’ll push it through based on an import file so you can put in what you want it to be from what it was. This one here is the minimum PO receipt and the minimum check.

You just turn them on here like so. Now, if you turn them on, it’s only turned on for the workstation that you are working on. So, if you want this across the firm, you must go into each workstation and load professional services tools and then turn it on accordingly. So, there’s those ones inside here.

One that I didn’t mention before is this GL Master Triggers as well as the RM Master Triggers and the PM Master Triggers. That’s the chart of accounts. If you put it into one GL code into one company, it will actually add it to all of your companies if you’ve got lots of companies, same thing with a vendor so that your vendor lists are identical. So that’s something that you can work on from there.

Okay? This one up here, this Update User Date turns on and off the verify that the date has been changed the next time you’re in. So, if you leave it running overnight, you know, it comes up with a message saying, “Would you like to change your user date?” and then it stops working. Because of it, this turns that off so that you can run things in the background as well.

There’s some other tools inside here which are quite nice. There’s a Fixed Asset Modifier up in here so you can modify your fixed assets. Okay. So, I think I’m about on time here. I am, 9:26, 9:24, that’s perfect.

So, does anyone have any questions?

– Thanks, Don. If anyone does have questions, if you could please enter them into the question area in the control panel on the right-hand side of your screen. First up here I have, does it work to merge an account into an already existing account with history to clear out an old account sequence or segments?

– Okay. So, does it work on old accounts? Yes. Doesn’t matter whether… They don’t need to be cleared out, it merges things together, so it takes all the transactions and puts them in as if they’d always been posted to the same GL code. So, if you’ve got old, unused accounts and you want to repurpose them and use them for something else that’s now current, you would still retain that old history.

If you combine accounts together, the history gets combined together into a single account. So, I’ve got a lot of people who’ve got departments that they no longer care about, but they’ve got history 5, 6, 7, 8, 9, 10 years ago. So what they do is they combine all of the income statement into a single account and all of the balance sheet into a single account so you’ve only got the two accounts left in your chart rather than hundreds.

The other thing that you can do is if you’ve got lots of accounts that you need to delete, you can take accounts that you’ve cleared out, merge them all together, write journal entries to zero them all out, get rid of the history and then delete just single and double accounts, which makes it a little quicker from that point of view.

So, that’s something that you can do as well. I hope that answers the question. If it doesn’t, please send me a note individually.

– Next here, Don, when you do Vendor Modifier or Combiner, does it combine all vendor details including EFT details?

– It combines all the details. The question is it combines based on the address code, because the EFT is stored against the address code. If the address that you’re adding…

If you’ve got a address 1 and an address… If they both have address ID 1, then there’ll be no overwrite. But if you have address 1 and address 2, then they’ll both be there. So, it depends on how your data is set up. Your EFT information is stored against a vendor address combination.

Vendor address is where you get it from. It is not stored against the vendor master, it’s stored against the vendor address master because you could have multiple EFTs within the same vendor based on different addresses. So that address… if that address code… If I use main for my address code across all my vendors, and I’ve only got mains, if I combine them together, I can’t have two mains when I’m finished.

I’ll only have one main. So, the one main would mean that you’d only have one EFT left out of the two that were there originally. Okay?

– Excellent.

– And the same is true for AR as well on the ship to addresses, ship to and bill to. If you’re combining two customers together if they have different ship to’s, the ship to’s will all come through, but if they’re the same, only one comes through and that I believe remains at the… I’ll have to double-check, but I’m pretty sure it remains at the original.

It does not overwrite. Okay?

– Thanks, Don. I have one last question here. Is the Site Modifier where you would also modify the default site ID?

– The default site… Well, the default site… there’s a couple of default sites, right? There’s a default site on sales order processing under sales order processing itself. That one you’d have to change manually, and your default site on your customers is not changed. At least I do not believe it’s changed.

I’ve never actually checked because I have very few clients who actually use a default site. So when I go into my sales and I look at a customer, where is it? This site ID, it’s also based on the item site combination so that conversation we just had about the EFT would also hold here anything that was in here.

Now, whether that site ID changes when I do the Site Modifier, I’ve not actually verified that. I believe it does. So, if I had put in here, I’d put in my 01-N and I made it 01-NE instead, I believe it would’ve changed inside here.

It changes all fields with site as far as I’m aware. But I would verify that in a test before I actually went ahead and did a large site. Okay?

– I think, Don, more specifically for this one, they’re wondering about the inventory section. So the default for the item inquiry.

– Okay, so underneath your inventory… So, when you’re in here, so there is no default on the item inquiry. So when I go to the item inquiry, if I just go to an item inquiry itself and I go into here, and I grab an item inside here like so, inside here I can pick whatever…

These items I have to select the site on the inquiry. So, the item itself doesn’t default that way. So, there’s no default from that point of view. There’s no… so if I go like so, so if I go into the item and I go to options, there’s no default site attached to it.

I’m not sure where they’re asking. If they can send me a more detailed information, I can take a look at it.

– That’s good. We are at time for today. So, if you did have any additional questions, you can enter them into the sidebar and we will will get back to you later today by email. But we do have a few upcoming Dynamics GP Coffee Breaks scheduled. I’ll add the link to our events page in the chat if you’d like to register for those sessions.

I’ll also add the link where you can set your email preferences and opt into our monthly and quarterly emails so you can stay in the know about upcoming events and see our latest articles. All right, thank you, Don, for the information. Thank you, everyone, for joining us today. Have a good rest of your day.

– Thanks all.

Dynamics GP Coffee Break | Setting up ACH and EFT in Dynamics GP

This webinar will go through the basics of setting up ACH / EFT for accounts payable and accounts receivable. We'll walk you through the settings and the default setups.

May 21
9:00 am – 9:25 am

Register here

Webinar - Setting up ACH and EFT in GP

May. 21
9:00 am – 9:25 am