Solved

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

Posted on 2014-09-17
30
385 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
ID: 40329274
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
ID: 40329320
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
ID: 40329322
ah well I'll definitely bow out now (humble pie and all that) - cheers.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 11

Expert Comment

by:stevengraff
ID: 40329335
@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
ID: 40329884
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
ID: 40329969
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
ID: 40330251
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
ID: 40330320
Getting the below error when i click on the Web Designer in dbnetsuite

error.gif
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 40331105
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
ID: 40332823
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
ID: 40333159
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
ID: 40333278
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
ID: 40333288
yes, you do, as per my previous post.
0
 

Author Comment

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

Expert Comment

by:stevengraff
ID: 40333364
No, though it could be.
0
 

Author Comment

by:jnikodym
ID: 40333524
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
ID: 40333651
Yeah, that can be a challenge ok! Why not post it, maybe there's something "obvious" amiss in it.
0
 

Author Comment

by:jnikodym
ID: 40339370
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
ID: 40339409
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
ID: 40339462
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
ID: 40339590
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
ID: 40339610
Here is a screenshot of my dbnetgrid window

dbnet.gif
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 40339614
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
ID: 40339853
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
ID: 40339875
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
ID: 40339891
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
ID: 40339912
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
ID: 40340149
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
ID: 40340185
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
ID: 40340248
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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