We use cookies to improve your experience on our website.

Andosi Blog

Exploring Great Design

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much easier. Simple table level backups with T-SQL.

The following will select all of the data in SOP10100 into a new SOP10100_Bkup_03012009 table:

select *
into SOP10100_Bkup_03012009
from SOP10100

Remember that inserts, updates, and deletes often fire off events that could alter data in other tables. This won't backup those dependent tables so be mindful of the potential that a simple update to one table

Read more

I neglected to mention another potential good reason to Customize GP; control your support costs, increase your IT resource capacity, and increase user satisfaction. There are routine problems that are easily resolved but often require the assistance of a system administrator. For example, records locked by other users, orphaned TempDB.dbo.DEX_LOCK records, can be a nuisance. This typically grows more common as the concurrent user count increases in Citrix environments. These records are necessary to prevent multiple users from accessing the same transactions at the same time. However, when they are not properly cleared they prevent users from accessing transactions even

Read more

While customization, when not properly planned and justified, can be problematic there are good reasons to customize your system to create long-term, sustainable competitive advantage and build discipline in your organization. I will follow this up with some specific examples of customizations I have built for these reasons and others.

1. Leverage your people for more value added activities. I'm not opposed to relying on people for some things but I don't see the value in having them enter and process data that the system could process unassisted. I've seen too many Controllers that function more like glorified GL Clerks.

Read more


This requirement came up last week and after Mariano explained how to do it, I went ahead and did it... for fun. Sick, I know.

I posted an example of using the DUOS a couple of year ago and used that, with very few tweaks, after using Modifier to add a Check Box control to the Customer Maintenance window to store the RequiredPO flag in the DUOS (SY90000) as Mariano suggested. If you have problems converting that example to this scenario let me know and I will e-mail you the code.

After that, all you have to do is add

Read more

GP does a good job of giving you the ability to create business alerts to keep you informed of events that have or may occur based on conditions in your database. Sometimes, the need to create an alert outside of the functionality in GP does, amazingly, come up. Just today a post at http://groups.google.com/group/microsoft.public.greatplains/topics and a response by Polino @ DynamicsAccounting.net drove me to create a sample business alert using only T-SQL that you could use as a starting point to developing your own.

This alert, if scheduled to run periodically, will e-mail a list of users that have been

Read more

A request to restrict SOP Lookup Widnow to only return Documents for which the User is responsible came up yesterday on the http://groups.google.com/group/microsoft.public.greatplains/topics. This is a somewhat common request that is easily addressed using VBA. This example only works for v10 but you could easily convert it to previous version by following the steps in http://mbsguru.blogspot.com/2006/04/ado-connections-through-vba-in.html.
This example assumes that the UserIDs are stored in the Sales Person ID field in Customer Maintenance and that relationship is used to determine Customer Ownership during order entry.

1. Add "Sales Document Numbers" window to Visual Basic. 2. Add the "Document Number"

Read more

I love the GP Newsgroup. Helping others with their problems helps me learn more about GP. Here's what I taught myself today in response to a newsgroup post.

 
Run this against your company database to select the users which users have SOP Documents locked:
 
select s.SOPNUMBE, a.USERID
from tempdb.dbo.DEX_LOCK l
inner join DYNAMICS.dbo.ACTIVITY a
on l.session_id = a.SQLSESID
inner join SOP10100 s
on l.row_id = s.DEX_ROW_ID
and l.table_path_name = DB_NAME() + '.dbo.SOP10100'

This blog has been relocated from http://mbsguru.blogspot.com/ with authorization.

Read more

We're going to change some Item Numbers in GP. I know, I can use PS Tools to do this but to use that you have to turn off your replication. Long story short, I don't wanna.

Anyway, I had to figure out which tables to update so I wrote a query that would return to me all of the tables, with data, that have an ITEMNMBR column:

select distinct o.Name
from SysColumns c
inner join SysObjects o
on c.id = o.id
inner join SysIndexes i
on c.id = i.id
where c.name = 'ITEMNMBR'
and o.xtype = 'u'
and rowcnt <>

Read more

I think this is pretty much common knowledge but it is a simple change you can make with VBA to ensure your inventory adjustments post to the GL. Paste this code below behind the Inventory Batch Entry window to mark the checkbox to post to the GL by Default and then prompt the user before posting without posting to the GL. This demonstrates a couple of handy VBA techniques you can use to alter and control the behavior of GP (forgive the formatting).

Option Explicit
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

Private Sub Comment_AfterGotFocus()

'Mark the Post

Read more

Schedule this script to run periodically against your company database to delete empty SOP batches automatically. It will check to verify that there aren't any transactions in the batch and that there is not a batch activity record first.

DECLARE @INTERID varchar(10),
@CMPNYNAM varchar(31)

SET @INTERID = DB_Name()
SELECT @CMPNYNAM = CMPNYNAM from DYNAMICS.dbo.SY01500 where INTERID = @INTERID

DELETE SY00500
where BCHSOURC = 'Sales Entry'
and BACHNUMB not in (select BACHNUMB from SOP10100)
and BACHNUMB not in (select BACHNUMB from DYNAMICS.dbo.SY00800 where CMPNYNAM = @CMPNYNAM and TRXSOURC = 'Sales Transaction Entry')

This blog has been relocated from http://mbsguru.blogspot.com/ with authorization.

 

Read more

I'm not real committed to this blog as you can tell. But, I do have something to offer. Here's the first of some simple tips I might share ;)

Displaying a message box to a user and controlling their actions might be the easiest way to change the functionality of GP. You display the message box so they aren't confused when you alter the standard functionality of GP. This came up recently in a GP newsgroup post. Below is an example of forcing the user to enter a tracking number before clicking the Ready To Close buttin on RMA Entry

Read more

While I am not a fan of using the DUOS with the same methods described in the manual, using ADO with RetrieveGlobals is better and easier in my opinion, I did do so in one instance to store an EDI Account Number for each customer. I added a field to Customer Maintenance and named it "strEDIAccount". Then I added the code below to my VBA project to store the date in and retrieve it from the DUOS (SY90000) table. This isn't much different than the sample code in the manual but it is a real world example. You should be

Read more

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

Read more

This has come up time and time again on message boards I frequent on the web. People are always asking how they can access SQL Tables and other objects through VBA to extend GP. It's pretty simple to add a field to a window with Modifier or create a user form in VBA but many seem to wonder how to access the database to really make things happen.

You can find all you need to know on Customer/PartnerSource. I'm only disclosing the same information here and a little of what I have learned to complement that information.

When Microsoft Dynamics

Read more

Let's review a 3rd Party addon for Great Plains today. We recently purchased Liaison Messenger http://www.liaisonsc.com/liaisonmessenger.html to automate business form generation and distribution to customers and vendors. Specifically, our ROI will come from automating invoice printing and e-mailing to customers as we generate hundreds of invoices everyday. The cost of the time required to process those invoices and the postage/supplies necessary get them distributed justified the investment in such a system. In time we'll do the same for customer statements and vendor POs. I would recommend that everyone check out this product.

I'll give Messenger 4 out of 5 stars.

Read more

I've been a Dynamics GP resource since the late 90s. Before that, I was an accountant with a fortune 500 company running Great Plains Accounting Software. Now, I manage an IT Department for a global organization that has standardized on Dynamics GP.

I'm here in part out of inspriration by Doug's keynote at Convergence 2006 in Dallas to further contribute unselfishly to the Dynamics GP community. I'm not out to sell anything but rather simply share my knowledge of Dynamics GP and absorb knowledge from others doing the same. I'll give you the good, the bad, and the ugly from

Read more