Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
ranger 7/5/2012 2:32:14 PM

after business portal install, views on database failing

hi all,

after installing the business portal, i am noticing that some of the view in our gp database no longer run.  specifically, the purchasing views...

for the sake of this post, let's just use the view called "PurchaseOrders".  this is a union query with the working and history tables in it.  In the pm00200 table, there is a field called "ACNMVNDR".  after the business portal installs, it added some fields for "drillback" of which plays off this field.

           'Account Number With Vendor For Drillback' = 'dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=HOU1-SVRDBS1&CompanyID=RNGR&ProductID=0' + dbo.dgppAccountIndex(1, ['PM Vendor Master File'].[ACNMVNDR])

as you can see, it is using a function called dbo.dgppAccountIndex(), which takes two parameters which are both integer datatypes.

When i run the view, it gives me this:  "Conversion failed when converting the varchar value 'Customer #905      ' to data type int"

So i realized it was this function, since the data in this field has alpha-numeric values, it is a string...the function wants two integers..

Why is this?  What are these drillback columns for?  Why would the GP database define the field as a char(21), and this installation wants it to be a number?


Any ideas what's going on here?





Version: GP 2010
Section: Dynamics GP, SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables