?
Solved

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

Posted on 2014-09-17
30
Medium Priority
?
398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 13
  • 2
  • +1
30 Comments
 
LVL 49

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 2000 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 49

Expert Comment

by:PortletPaul
ID: 40329322
ah well I'll definitely bow out now (humble pie and all that) - cheers.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

718 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