Solved

Updating GP Dynamics SQL Server Customer Master RM00101 Through ODBC Linked Tables in Access??

Posted on 2011-09-10
11
1,617 Views
Last Modified: 2013-12-11
I have a Access Front-End application that connects to Dynamics SQL Server using ODBC Linked Tables like RM00101, RM20101, SOP30200, SOP30300 to create various custom Receivables and Sales analysis/reports.  This application was built for a client and has been running for several years.  The client uses the two USERDEFINED fields on the customer record in the Customer Master table (RM00101) to store some status types of the accounts and this is updated monthly from within the Access Front-End using a query.  Works great and never had any problems.

The client is doing an overhaul of all the customers in the RM00101 table (I exported it to Excel) and now have a need to programmatically update the following fields using a SQL query in Access on the linked table without having to use the Great Plains GUI to call up 10,000 customers manually.

CUSTCLAS, INACTIVE and HOLD

My question is, will updating the three fields above without going through the GUI cause other problems within Dynamics??  My concern is there could be other form events that are triggered from the GUI update to change other tables, etc. and just running a SQL query on the customer master could leave some things out of sync within Dynamics.  Probably not but I don't want to run a update on those fields then have the endusers getting all sorts of error messages when navigating Dynamics with the GUI.

The INACTIVE and HOLD seems like they would be restricted to just the Customer Master for obvious reasons but the CUSTCLAS could very well be populated in several other tables.

Any input will be greatly appreciated.

Thanks,

ET



0
Comment
Question by:Eric Sherman
  • 6
  • 5
11 Comments
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
ID: 36515878
Updating HOLD should be no problem.

Updating INACTIVE may be an issue if you want to inactivate customers that have any unposted or open transactions.  While SQL will let you do this, the GUI would not, and this may have unpredictable results for the users.  I have not done this myself, but I am thinking if you verify that the customers you want to make inactive do not have any records in the RM10201, RM10301, RM20101 and SOP10100 tables, you should be ok to make them inactive.  Of course if you are going the other way, making customer active, that should not cause any issues.

For CUSTCLAS, if all you are looking to do is simply update the Customer Class ID, not make any other changes to the customer's setup based on the Class ID, then there should not be a problem.  In the GUI, when you update a customer's Class ID you are prompted with a dialog box asking if you want to update the customer's information with the settings from the class.  Updating the Class ID in SQL is the equivalent of saying No to this question.  I have done this numerous times for companies that only use the Class ID for grouping or reporting purposes.  
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 36515913
Thanks a million Victoria for the quick response.  I use to implement Great Plains years ago starting with the older GPA DOS version then on to the earlier versions of Dynamics before the merger with MS.  Therefore, I'm kind of familiar with their table structures and GUI's.  Your comments definitely clears some things up.

INACTIVE -  I can/will confirm any account that's being inactivated does not have any open or unposted transactions.

CUSTCLAS - This one concerned me the most because after reviewing the Field Info in Dynamics, it shows where this field is used in multiple tables (see pic below).  I think this client uses the CUSTCLAS field for other activities and if I remember correctly it's tied to their Pricing, Price List, etc. for SOP.  Therefore, just changing it on the Customer Master record in RM00101 may cause some Invoicing problems down the road.  If that's the case, then it's probably best to update that field using the GUI.  I support this client "Remotely" for custom applications and their IT Dept. / Dynamics support is also outsourced.  The objective is not to cause any issues with Dynamics as their's no one in-house to handle/resolve them.

Thanks,

ET
   
custclas.PNG
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 36515927
<<<<<In the GUI, when you update a customer's Class ID you are prompted with a dialog box asking if you want to update the customer's information with the settings from the class.  Updating the Class ID in SQL is the equivalent of saying No to this question.>>>>>

I guess the real question is what information is it updating the customer to from the class when you select Yes in the dialog box???


Thanks,

ET

0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 36515936
ET,

For Class ID - none of those are 'real' tables except RM Customer MSTR (RM00101) and Sales Prospect Master (SOP00200).  SOP00200 is not used by most companies, but even if it is used here, you're updating customers, not prospects, so I cannot see any problems being caused by this.  Of course it is ultimately up to you and your customer as to what you are comfortable with, but I have done this exact type of update many times for various customers with no issues whatsoever.  

Victoria
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 36515943
ET,

What's updated from the Class ID to the customer is whatever is different.  Anything that is on the class that is not on the customer (and that is possible to update) will get updated.  For example, if the Class ID has Payment Terms of 'Net 30' and the customer has Payment Terms 'Net 15', saying Yes changes the customer's terms to Net 30, saying No leaves the terms as Net 15.  

If this is a concern, then the real question I would have is - why are they looking to update the Customer Class ID?  
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Author Comment

by:Eric Sherman
ID: 36516619
Ok, thanks for you input and that clears up the questions that I had.  I just didn't want to get something out of sync in Dynamics causing some problems when the users open the GUI, etc.

<<<<<why are they looking to update the Customer Class ID?>>>>  

I think they have some Pricing tied to the Customer Class.


Now that it's more clear exactly what the update of customer information is doing when changing the CUSTCLAS and the accounts have no unposted or open transactions .... I think the SQL update will not cause any problems.

Again, thanks for your input .... Much Appreciated!!!

ET
0
 
LVL 19

Author Closing Comment

by:Eric Sherman
ID: 36516624
Thanks for your valuable input.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 36516696
Just to follow up, if they are looking to update the customer's Price Level (which can be updated with the class), then changing the class in SQL, while not causing any problems, will also not do what they want.  Might be worth a conversation to find out exactly what they are looking to accomplish.  :-)
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 36516736
Exactly ... and that's a question I will present to the client to get confirmation.  If the Price Level is the reason for changing the CUSTCLAS then they will be better off changing that through the GUI.

On another note ... It's been years since I've opened an Dynamics GUI but I got stuck on the Sales Batches Window.  If I select a batch with transactions ... isn't there a way to see all the transactions on the batch without printing a Batch List or Edit List???

Say if I wanted to call up a batch and change a specific transaction ... not enter a new one on the batch.

Thanks,

ET
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 36516757
ET,

If they want to change the price level for the customer, you can do that in SQL also, by changing the PRCLEVEL field in RM00101.

For you other question, the short answer is that you can open the Sales Transaction Entry window, click the looking glass next to the Doc Number and search for your transaction by any criteria you want...batch, customer, etc.  If you need more on that, it may be better to start another question, just to keep these topics separate.
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 36516802
Yes, I may have to update the CUSTCLAS and PRCLEVEL fields on the customer card.   Thanks!!!

Appreciate the input on the Batch question.  Thanks again.

I agree ... if needed ... I will open a new question.

You've been a great help.

ET Sherman
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you need to import sales transactions from another system into Microsoft Dynamics GP as Sales Order Invoices, you may need to store some additional data related to the customer, the transaction, or the line items.  Even if your company is manuall…
As an accountant it is essential that I am able to provide accurate and timely information to management and staff.  One of the challenges that I have faced is the need to report on a time period, whether it be a month, quarter, or year, and wanting…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now