Goldmine - Pull data from another database to display on contact record

I am looking for a way to view data from another database on my Goldmine contact record.  For example, we use Dynamics GP.  i would like to see in Goldmine the YTD Sales for a contact in Goldmine.  I have a way to link between the two databases using the Customer ID field.
jnikodymAsked:
Who is Participating?
 
stevengraffConnect With a Mentor Commented:
We do this all the time in GoldMine. You'd use the GM+View tab (which is like a web page, driven by (any data you choose from) the current contact. You'd configure an Active Server Page, or, what we use, a product called dbnetgrid.

The biggest challenge, sometimes, is writing the SQL query(s) that will display the data you want.
0
 
PortletPaulCommented:
I am not knowledgeable in either Dymanics GP or Goldmine; this is just an observation.

The question assumes you can find an expert in both those products who can propose the details of this solution. The number of folk with such a mixture of expertise will not be huge.

You may want to elaborate on "I have a way to link between the two databases using the Customer ID field." e.g.
What field is this in Dynamics GP?

Are you permitted to query against Dynamics GP directly from within Goldmine?
have you asked?
Most IT shops I know would veto this immediately as it may impact the performance/stability of 2 critical systems.

If you can't query directly, can you implement "an interface"?
if so, what is the preferred technical approach?
(eg. old fashioned file based batch, web service, other)

To me these are rhetorical questions as I probably won't contribute past this point, but I'd suggest this question needs some more detail.
0
 
PortletPaulCommented:
ah well I'll definitely bow out now (humble pie and all that) - cheers.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
stevengraffCommented:
@PortletPaul, your comments were all "on." I am a GoldMine guy, and, you'll note, I've called out the need for a GP guy to help with the queries. Also, a saving grace of the OP is that he used the word "view," meaning we won't be doing any unsavory integrity-breaking updates via the back-end.
0
 
GMGeniusCommented:
If GP is SQL backend then it is possible as steven has stated using GM+View

I have done this sort of thing on many occasions with GoldMine , I have views into Sage 50 (ODBC) and Sage 200 (SQL backend)

All it takes is some digging into the SQL data and you will be able to sort it.
0
 
GMGeniusCommented:
Just to explain a little, just build either an ASP.NET or PHP or infact any other web style page(s) that pull data (the page should accept a passed value in the URL for the account record link)

Then just pull the data using what ever method of data access you want to use and show this in the page

Some Examples attached
Sage50-1.gif
Sage200-2.jpg
0
 
jnikodymAuthor Commented:
I have never built a ASP.NET page, so i think the dbnetgrid may be the way to go for me.  I tried installing dbnetgrid, but once installed i click on the web developer link and it opens in a browser with an error.  Did i miss something on the install?
0
 
jnikodymAuthor Commented:
Getting the below error when i click on the Web Designer in dbnetsuite

error.gif
0
 
stevengraffCommented:
Sorry I can't be of more use to you here... best bet is to contact the dbnetgrid support folks. I have noticed their installations to be "quirky."

You may also want to have a gander at Iron Speed which, to my eye, is somewhat similar.
0
 
jnikodymAuthor Commented:
Ok, i spoke with the dbnetgrid support people and got it installed.  Can someone give me some direction on how to connect a goldmine contact record to a sql view?
0
 
stevengraffCommented:
First, create the query or view you need of your external (GP?) database. Then, ensure that you can access it using dbNetgrid using IE. You'll want to be sure you can use a query string in your url, something like

http://myserver/dbnetgrid/mydoco/?customerid=12345678

Once you've got that bit working, you can set up your gm+view tab so that it's popping the customer id value in automatically, something like

http://myserver/dbnetgrid/mydoco/?customerid=&key5

Your existing GoldMine should have some examples of this, perhaps for doing a Google map. The process is the same.

Good luck.
0
 
jnikodymAuthor Commented:
I have the view created in sql, but i'm confused on the query string you list above.  Can you explain what part of the query string is the view name?  Do i need to set up the connection to my sql database in the dbnetgrid connection manager first?
0
 
stevengraffCommented:
yes, you do, as per my previous post.
0
 
jnikodymAuthor Commented:
does the dbnetgrid need to be installed on the sql server?
0
 
stevengraffCommented:
No, though it could be.
0
 
jnikodymAuthor Commented:
I'm having trouble figuring out the dbnetgrid portion of this.  I have my sql view, but i can't seem to get the connection string correct.
0
 
stevengraffCommented:
Yeah, that can be a challenge ok! Why not post it, maybe there's something "obvious" amiss in it.
0
 
jnikodymAuthor Commented:
My database name is CRAFT.  The view i want to access is named CUSTOMERS.  I want to display the customer info for whatever record i am viewing in Goldmine.

I've tried, http://laptop-39/dbnetgrid/CRAFT/?customerid=12345678
0
 
stevengraffCommented:
Looks good on the url side, assuming your dbnetgrid file is programmed to pick up the data in your url.

Have you gotten it to respond, first, without being driven by the customerid? That would be a pre-requisite, in my mind.
0
 
stevengraffCommented:
You've programmed your dbnetgrid document with something like

select * from customers where customerid = '12345678'

right? I mean you'd want to make sure it produces some kind of result with it hardcoded.
0
 
jnikodymAuthor Commented:
No, i can't get it to display the entire view in a browser.  I get the below.  I don't know where to call out the view in the url.
error.gif
0
 
jnikodymAuthor Commented:
Here is a screenshot of my dbnetgrid window

dbnet.gif
0
 
stevengraffCommented:
Sure, you've got to get your iis problem worked out first. I'm not an expert at that, I just keep googling and fiddling 'til something useful happens :)
0
 
jnikodymAuthor Commented:
ok, after playing around with iis and dbnetgrid i am able to access the below link which shows my entire view.  Now, how do i get it setup to only display a specific customer?

http://laptop-39/DbNetSuite/DbNetGrid/customers.aspx
0
 
stevengraffCommented:
Great! Now you want it to be able to do:

http://laptop-39/DbNetSuite/DbNetGrid/customers.aspx/?custid=12345678

right?

It's pretty simple, though I must confess I'd have to do some digging to find it... I don't know it off the top of my head. It's one of those things that web programmers consider "basic," so somewhat challenging to track down. Try googling... if you don't find it, let me know, and I'll see if I can dig it up for you tonight or tomorrow.

Obviously, you'll alter your query to have a "where" clause, i.e.  "where custid = '" + custid or something like that.

Good luck!
0
 
jnikodymAuthor Commented:
Do i do this within the dbnetgrid software or is it something i need to change in my .aspx file?  If you could help me out that would be great.  Thank you
0
 
stevengraffCommented:
The change will need to be made in your aspx file.

First, modify your query to include a "wherepart" (I think), and add the clause "custid = '12345678' "

For test purposes, you are initially hard-codeing the custid.

When you see the right result in your web page, then you'll need the logic that grabs the string from the url, and you'll change your wherepart clause to say "custid = '" + custid + "'"

It's impossible to read the single and double quotes above, so be sure to copy and paste so you can see what's going on. Also, I don't know the exact syntax, I just know you need to do a concatenation of a string and a field, with the field being in single-quotes.
0
 
jnikodymAuthor Commented:
below is my aspx file text.  Where would i add the code?

<%@ Register TagPrefix="DNL"  Namespace="DbNetLink.DbNetSuite.UI" Assembly="DbNetLink.DbNetSuite" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head />
  <body>
    <form runat="server">
      <DNL:DbNetGrid runat="server" ID="CustomersGrid" FromPart="Customers" ConnectionString="craft" Theme="Classic">
        <GridColumns>
          <DNL:GridColumn Filter="True" Label="Customer  Number" ColumnExpression="[Customer Number]" PrimaryKey="True" />
          <DNL:GridColumn Label="Customer  Name" ColumnExpression="[Customer Name]" />
          <DNL:GridColumn Label="Address 1" ColumnExpression="[Address 1]" />
          <DNL:GridColumn Label="Address 2" ColumnExpression="[Address 2]" />
          <DNL:GridColumn Label="City" ColumnExpression="City" />
          <DNL:GridColumn Filter="True" Label="State" ColumnExpression="[State]" />
          <DNL:GridColumn Label="Zip" ColumnExpression="Zip" />
          <DNL:GridColumn Label="Phone 1" ColumnExpression="[Phone 1]" />
          <DNL:GridColumn Label="Total  Sales LTD" ColumnExpression="[Total Sales LTD]" />
          <DNL:GridColumn Label="Total  Sales LYR" ColumnExpression="[Total Sales LYR]" />
          <DNL:GridColumn Label="Total  Sales YTD" ColumnExpression="[Total Sales YTD]" />
        </GridColumns>
      </DNL:DbNetGrid>
    </form>
  </body>
</html>
0
 
stevengraffCommented:
Confirm this with dbnetgrid examples...

<DNL:DbNetGrid runat="server" ID="CustomersGrid" FromPart="Customers" ConnectionString="craft" Theme="Classic">

would become

<DNL:DbNetGrid runat="server" ID="CustomersGrid" FromPart="Customers" WherePart="custid='12345678'" ConnectionString="craft" Theme="Classic">

and, then, ultimately,

<DNL:DbNetGrid runat="server" ID="CustomersGrid" FromPart="Customers" WherePart="custid='" + custid + "'"  ConnectionString="craft" Theme="Classic">

and you'll need to do further research or wait 'til tonight to get more info on getting the /?custid=12345678 from the url into the custid variable in your script.
0
 
jnikodymAuthor Commented:
Thank you.  Let me know when you can get me that last part on getting the  /?custid=12345678 from the url into the custid variable in the script.
0
All Courses

From novice to tech pro — start learning today.