Solved

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

Posted on 2011-09-10
11
1,646 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I've just fixed a problem with Sage 50 Accounts 2011 crashing when creating a new Company.  I've wasted several hours on it so I thought I'd share the solution with others.  Remarkably, the same laptop had what appears to be exactly the same …
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

21 Experts available now in Live!

Get 1:1 Help Now