Solved

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

Posted on 2014-09-17
30
374 Views
Last Modified: 2014-10-10
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.
0
Comment
Question by:jnikodym
  • 13
  • 13
  • 2
  • +1
30 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 11

Accepted Solution

by:
stevengraff earned 500 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
ah well I'll definitely bow out now (humble pie and all that) - cheers.
0
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
@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
 
LVL 12

Expert Comment

by:GMGenius
Comment Utility
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
 
LVL 12

Expert Comment

by:GMGenius
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
Getting the below error when i click on the Web Designer in dbnetsuite

error.gif
0
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
yes, you do, as per my previous post.
0
 

Author Comment

by:jnikodym
Comment Utility
does the dbnetgrid need to be installed on the sql server?
0
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
No, though it could be.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
Yeah, that can be a challenge ok! Why not post it, maybe there's something "obvious" amiss in it.
0
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
Here is a screenshot of my dbnetgrid window

dbnet.gif
0
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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
 
LVL 11

Expert Comment

by:stevengraff
Comment Utility
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
 

Author Comment

by:jnikodym
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The need for Texting We all wear many hats. Part of this article's focus is the marketing hat... and another part is the consumer hat. Those of us who are consumers frequently are either ambivalent toward, or hiding from... those of us who are mark…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

10 Experts available now in Live!

Get 1:1 Help Now