Category Archives: Uncategorized

Trouble installing or uninstalling MS Dynamics GP.

Posted on by
2

On some 64 bit computers the MS Dynamics GP install will hang.  Typically you cannot uninstall the program.  Running setup.exe again will launch but when clicking on the install MS Dynamics nothing happens and the screen clears.

In this case you have a partial install.  Attempts to load a service pack result in messages like “Property invalid country code”, and installation of the service pack hangs.

Although you might think this is a rights issue with the user not having Admin rights or a .net issue it most likely is not.  First you should properly uninstall MS Dynamics GP.

Download the MS Fixit program from this link.  This is a download of the pointer to run the program from the MS server.  So you must be online to run the tool.  You cannot download the tool and run it offline. http://support.microsoft.com/mats/Program_Install_and_Uninstall

Follow the prompts to Uninstall the program.  Do not try to fix the MS Dynamics installation.  Uninstall first.

Rerun the MS Dynamics install, right click on the setup.exe and run as Administrator.

MS Dynamics GP 2010 new features in Payables.

Posted on by
Reply

The Select Checks screen has been modified to present a more standard GP look for selecting and inserting ranges.  You can insert ranges for Vendor ID, Vendor Name, Class ID, Payment Priority, Voucher Number, Document Number, and Discount Date/Due Date.

Click Build Batch.  Then you can select insert a range again and click Add To Batch.  Continue adding to the batch, then click Edit Check Batch to review.

You can also exclude inactive vendors in lookups.  Click the lookup, then right click in the middle section of the lookup screen, select Exclude Inactive Vendors.  To make this the default anywhere this lookup is used click on Set as Default View.

There is a new option to exclude vouchers in the select checks process with expired discounts.  This comes in handy were pay runs need to be done to meet a discount date.  You can set the option in payables setup.

Once set, in the Select Checks process if the Discount Date/Due Date range is selected, vouchers with expired discounts would not be included as in the example below.

 

 

 

Sampe VBA Excel Macro with loop to update MS Dynamics GP table.

Posted on by
Reply

Here is a full example of the VBA sample code with a loop.  This VBA code loops through an Excel spreadsheet and inserts records in the MS Dynamics GP Payroll Posting Accounts table UPR40500. In this case we only insert accounts for Gross Pay.

The VBA sample code also create a record set named (rs) from the GL00100 table that will provide the GL account index for the full account.  The full GL account is matched to the record in the GL00100 table to get the account index.

Comment lines begin with an apostrophe (‘).

Public Sub GetAccounts()

‘Define ADO connection object and recordset object
Dim cn As ADODB.Connection, rs As ADODB.Recordset

‘set cn (connection) to hold database connection string
‘set rs (recordset) to hold the results for the retrieved record set ‘from SQL
Set cn = New ADODB.Connection
cn.Open “Provider=’SQLOLEDB’;Data Source=’dh-PC’;Initial Catalog=’TWO’;” & _
“Integrated Security=’SSPI’”
Set rs = New ADODB.Recordset

‘strSQL will hold the select SQL statemment to run
‘rs.open opens the record set with strSQL, and connection cn
Dim strSQL As String

‘ declare other variables
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer
Dim strFullAcct As String
Dim rsFullAcct As String

‘Read cells from payroll accounts sheet starting in row 2
Dim i As Integer
i = 2
strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Let strSQL = “select * from GL00100″
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
‘Use a MsgBox to display a messge and field contents for debuging, ‘remove the apostrophe in from the MsgBox statement below to see ‘how it works.

‘MsgBox strSQL

‘Process all the rows in the spreadsheet
Do Until IsEmpty(Cells(i, 1))

‘step through the record set and perform required actions on the records
‘reference the fields using the column name from the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
strFullAcct = strDept & strMainAcct & strPosition
rsFullAcct = Left(rs!ACTNUMBR_1, 3) & Left(rs!ACTNUMBR_2, 4) & Left(rs!ACTNUMBR_3, 2)
If strFullAcct = rsFullAcct Then intID = rs!ACTINDX
‘move to the next record in the recordset
rs.MoveNext
Loop

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID

‘adjust department and position code strings to proper length
strDept = Right(strDept, 2)
strPosition = Right(strPosition, 2)

‘create the sql string to insert the record into the Payroll posting accounts table
strSQL = “INSERT INTO UPR40500 (DEPRTMNT,JOBTITLE,PAYROLCD,UPRACTYP,ACTINDX) VALUES”
strSQL = strSQL & “(” & strDept & “,” & strPosition & “,’” & strPayCd & “‘,” & strPayType & “,” & strAcctIndx & “)”
‘MsgBox strSQL
cn.Execute strSQL

i = i + 1
strDept = “”
strPosition = “”
strPayCd = “”
strMainAcct = “”
strPayType = 0
strAcctIndx = 0
strFullAcct = “”
rsFullAcct = “”

strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Loop

MsgBox “Import Completed”

‘close the open record set
rs.Close:   Set rs = Nothing
‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub

MS Dynamics GP Outlook is crashing GP when using Citrix

Posted on by
Reply

This appears to be caused by a conflict between a GIF image file and a DirectX file. Only certain version of DirectX cause this problem. The problem file is named “Icmsg.gif”. Go to the C:\Program Files\Microsoft\Dynamics\GP\Background\Images\ directory, and rename the file “Icmsg.gif” to something else. The full path would be “C:\Program Files\Microsoft Dynamics\GP\Background\Images\Icmsg.gif”.

The file was causing a Dexterity Runtime error and GP would not launch. The Icmsg.gif file is the Outlook mail icon on the home page.

MS Dynamics GP how to setup a variable allocation account.

Posted on by
Reply

With fixed allocation accounts the distribution to the gl account is based on a fixed percentage.  With variable allocation accounts you can use statistics like head count, square feet, gallons, etc. to calculate how amounts are distributed to gl accounts.  The accounts used to calculate the breakout are called breakdown accounts.

Example: Assume we have a building with the following square footage and we have entered a journal entry for the unit accounts and posted.  Total 229,000 square feet.

100-9010-00 Administration  2000 sq ft

200-9010-00 Accounting  1000 sq ft

300-9010-00 Sales  5000 sq ft

400-9010-00 Service  20000 sq ft

500-9010-00 Training    1000 sq ft

600-9010-00 Receiving 200,000 sq ft

Go to Cards – Financial – Variable Allocation

Setup a new GL variable allocation account 000-7000-00 Electricity, click based on Year to Date as our square footage of the building is assumed to be unchanged for the year.  Click in Distribution accounts, enter 100-6190-00 as the first account.  While still on the distribution account we just entered click in the first box for the Breakdown account and enter 100-9010-00 as the account. Repeat this for each distribution account and each breakdown account.  In this example we will have only one breakdown account for each distribution account.

The accounts will match up like this.  The sq footage in each breakdown account will be divided by the total 229,000 sq foot to give the percentage for the breakout.

Distribution Acct Breakdown Acct Sq Foot Breakout%
100-6010-00 100-9010-00 2000 0.008733624
200-6010-00 200-9010-00 1000 0.004366812
300-6010-00 300-9010-00 5000 0.021834061
400-6010-00 400-9010-00 20000 0.087336245
500-6010-00 500-9010-00 1000 0.004366812
600-6010-00 600-9010-00 200000 0.873362445
       
    229000  

To see the result in action we then enter a journal entry to 000-7000-00 our Electricity account for $50,000 and post. Go to Transactions – Financial – General, enter a batch id.  I created a batch called DH.  Notice I clicked the box Break Down Allocation.

I then click Save to save the general ledger transaction in the batch.  Once I have saved in the batch I can click the printer icon to the right on the screen to see the batch edit.  I don’t have to actually post the transaction to see what the result will be.  Using batches in this case can be useful.  Here is the batch edit showing what the results will be.

This is a simple example and I hope this clears up Variable Allocation accounts for you.

You can have multiple breakdown accounts per distribution account line.

Breakdown accounts do not have to be only unit accounts.  Breakdown accounts can be other posting accounts. Keep in mind that if you have marked Trx Period on the Variable Account setup screen and your using regular dollar posting accounts as breakdown accounts then your breakdown amounts may not exist until you post gl batches or make journal entries for the period.

Incorrect Item cost on a new Purchase Order in MS Dynamics GP.

Posted on by

Whey does the wrong cost show up on a Purchase Order?

In MS Dynamics GP there are several screens where you will see the cost of inventory:

1) Cards – Inventory – Item

  • Standard Cost
  • Current Cost

2) Utilities – Inventory – Adjust Costs

  • Cost (which can be changed per line item for each receipt or adjustment)

3) Cards – Inventory – Item – (Go to button) Vendors, click Assign, then select the Vendor

  • Originating Invoice Cost

 

Which cost is used when you enter a Purchase Order?  Logically you might think the Current Cost from the Item Card might be used, or perhaps the last cost from the PO receipt line you see in the  Adjust Costs screen. 

The answer is the PO entry screen will always use the cost from the Originating Invoice Cost field regardless of how the Current Cost is set or modified in the other screens.

So for example I setup a new item and make the Current Cost $10.00.  I then receive a PO for a vendor at $10.o0.  In Current Cost, Originating Invoice Cost, and the Cost in the receipt line in Adjust Costs the cost is listed as $10.00.  I then go into the Item Card and change the Originating Invoice Cost to $11.00.  When I do a new PO for the vendor the cost on the line item displays as $11.00.

This is important especially if you buy from multiple vendors.  Last year let’s say you bought the same item as above for $8.00 from an old vendor.  If today you do a new PO for the vendor from last year the cost will default to $8.00.  Thus you have the opportunity to process and post the receipt and invoice with the wrong cost.

SRS MS Dynamics GP reports not visible in Custom Reports

Posted on by

SQL Reporting Services reports are normally visible in the Navigation Pane, under Home, in Report Shortcuts, Custom Reports.

When a user in Dynamics GP clicks on Custom Reports the repots list should populate in the Custom Report List to the right. 

In this case the user clicks Custom Reports and nothing happens, there is no error, and the reports do not populate the list.

This problem is related to security.  The relationship between active directory, sql, SRS, and MS Dynamics GP is a complicated one.

This solution assumes you have SRS, Active Directory, MS Dynamics GP setup properly, and you have run the MS SQL Reporting Servies wizard for GP and it has installed properly and been configured.  Furthermore, the computer that the user is on has not been excluded or blocked by IIS, and that the computer hasn’t been blocked by a firewall issue.

Delete the GP user in MS Dynamics GP.  Setup a new user.  Give them the proper access to companies and security.  At this point you can assign Power User rights and test.   Lauch Dynamics GP and login as the new user.  Click Custom Reports.  The list should populate.

If this works you are probably good.  Change security for the user.  Have them log in and test.  Keep an eye on it because sometimes it will break again, and you may have to repeat the process.

If this does not work you have a different issue with SRS, IIS, active directory, etc.