Adimpact.com

From the category archives:

GoldMine Tips

Yes, I am still alive! I know its been a while since our last blog post, but I’ll try to make it up to you in the near future! I received an email question on the lookup.ini and how to use it to deal with ranges. Adidas Gazelle Homme So, for example, we want to assign our sales rep by the employee count of a contact. chaussures louboutin soldes There’s some good basic information on how to create and where to find the lookup.ini in GoldMine’s help file so we won’t go too much into detail on that. asics pas cher Basically, it’s a file that tells GoldMine to auto-populate some fields based on others. For our example here we’re assuming we have a custom field, UEES, # of Employees, that tracks a contact’s number of employees. adidas neo femme We then want to auto-populate the Key4, Account Manager, field based on that. nike free 4.0 donna Lookup INI 1 Our criteria is going to be: <= 99: Mike 100 – 499: Bill > 500: Sam The basic syntax we’re going to be using here is iif(Expression,True,False). louboutin pas cher So if the expression is true, it returns the middle section of the formula, and false the end. asics kayano 21 homme We’re then going to nest some additional iif in the false area so we can do multiple evaluations. Nike Air Max 2016 Homme Here’s the code and a screen shot of what the lookup.ini would look like for this: iif(contact2->uees <= 99, ‘Mike’, iif(contact2->uees >= 100 .and. Adidas Zx 750 Homme contact2->uees <= 499, ‘Bill’, iif(contact2->uees >= 500,’Sam’,’Error’))) Lookup INI 2 I’ve been doing some cool things with Dashboards recently, I think we’ll try to feature some of them in the next posts.

It’s bound to happen, cheap bns gold you’ll come across a dreaded duplicate contact in your GoldMine! GoldMine Duplicate 1 From the Search Center, Nike Air Max Thea Homme Bleu though, Adidas Zx Flux Homme Pas Cher it’s easy to take care of. Nike Air Huarache Femme Simply select each of the records by far left box. nike air jordan 5 uomo GoldMine Duplicate 2 Then go to Tools | Data Management | Merge/Purge Records | Merge Tagged Records. Kanken No 2 GoldMine Duplicate 3 GoldMine will consolidate the records into one, asics gt soldes merging the history, Nike Air Max 95 Homme pending, adidas gazelle femme opportunities, Nike Air Max 1 Homme etc.

I received this question from a long time blog reader, “Do you have a SQL Query that can list the Companies where there has been NO CHANGE, no pending record entered or History Record in the last year?” Well, no I don’t, asics gel pas cher but let’s see what we can do! So here’s the query:

SELECT c1.company, c1.contact,  Nike Free Run 3.0 femme c1.phone1, c1.address1,  asics gel lyte 3 c1.city,  louboutin chaussures c1.state,  Parajumpers Femme Californian Joanna c1.zip, c1.key1, c1.accountno FROM contact1 c1 WHERE lastdate < getdate() - 365 AND c1.accountno NOT IN (SELECT accountno FROM cal WHERE createon >= getdate() - 365) AND c1.accountno NOT IN (SELECT accountno FROM conthist WHERE createon >= getdate() - 365) AND c1.key1 <> 'Unqualified' ORDER BY c1.company, c1.contact 

Ignored Accounts SQL So a couple of things to note. Air Jordan 1 bambini You’ll notice I’m using getdate() – 365 to automatically go back one year, you could replace this with a date, Adidas Zx 500 Homme e.g. < 1/1/2015, adidas hamburg homme if you want to hard code the dates. Also, I am excluding any contact marked Unqualified in the key1 field for those contacts that you do not want to contact often.

Anytime you’re using a system like GoldMine, it’s a good idea to evaluate how often a field is populated and the different values that are being put in. asics noir soldes For example, here we have our Industry field. Nike Air Max 90 Femme Industry Are people using it? If so how many records do we have for the different industries? Fortunately this is an easy question to answer with a pretty simple SQL Query. chaussures timberland soldes First, however, you’ll need to do a Right-Click | Properties on the field to get the field name. This is how it’s referenced in the database. New Balance 574 femme Industry Properties So our SQL Query to analyze this will be:

SELECT key3, count(*) as '#' FROM contact1 GROUP BY key3

Simply copy and past the SQL Query into Tools | SQL Query and hit query. adidas stan smith homme bleu You’ll see a result like this. adidas gazelle femme You’ll notice there’s two blank areas, one for blanks and one for nulls, but essentially they’re the same thing. New Balance 1600 homme Query You can switch out the fields just be sure to change the name after the select as well as after the group by. chaussures louboutin You can also use this for other tables, for example contact2, for custom fields, just change the table name after the FROM.

GoldMine’s relationship tree is a great tool to see all of the contacts at a particular company. Adidas Zx Flux Homme Rouge However, adidas tubular enfants creating them and keeping them up to date can be a chore. GoldMine Relationship Tree 1 However, Nike Roshe Run Homme there’s a nice tool within GoldMine’s data management utilities to do this for you. New Balance 999 femme Go to Tools > Data Management > Create Default Relationship Trees. adidas zx 850 homme GoldMine Relationship Tree 2   Normally you’ll be building the relationship trees based on the Company field. ugg chaussons You can also have GoldMine create subsections (folders) based on a secondary field, bottes ugg in the example I chose State, Adidas Superstar Femme Rose and finally, Nike Air Max Command Homme I like to replace all existing relationships, Nike Air Max Command Homme that way all the relationship trees are rebuilt.

In our last post we talked about automatically creating Relationship Trees to link all of the contacts at a single company together. new balance soldes There will come a time, though, nike air jordan 7 uomo when you want to update all of the contacts within that Relationship Tree. chaussure homme louboutin For example, Nike Air Max 2011 Femme if one of those organizations becomes a customer. adidas Powerlift 3 Femme First, bottes ugg go to the top level of the organizational chart, louboutin femme and then change the value of the field you’ll want to replicate. Canada Goose Homme GoldMine Replicate 1 Then Right-Click on the top entry and choose Replicate data… GoldMine Replicate 2 Then choose the field or fields to replicate.

If you’ve been using GoldMine for a while you’re probably very familiar with previewing a filter. Here we have a list of all of our Web Import records. ugg 2017 Filter Drill Down 1 What you may not have noticed is the Drill Down button in the lower-right, and what it’s for. final fantasy 14 gil for sale So, nike air max flyknit donna once you have a filter like this previewed, Adidas Zx Flux Femme Leopard go back to the Filters tab, asics gel lyte pas cher and select another filter. new balance france I’ve selected California. Nike Air Zoom Pegasus 33 femme Filter Drill Down 2 Go back to Preview and click Drill Down. Asics Gel Lyte 3 Homme Filter Drill Down 3 You’ll see GoldMine has combined the two filters, nike kwazi homme so we’re only seeing records in California that are Web Import records. If this is a useful filter for you, Nike Air Max 90 Femme click Save to save the combined filter as anew filter.

3-25-2009-11-03-06-pm

Spring is finally here and it’s time for some Spring cleaning on our GoldMine database!

Here’s a SQL Query that’ll show you any duplicate email addresses you have in GoldMine. Air Jordan 14 Homme This is probably one of the most important queries you can run to ensure that any email blasts you do via GoldMine don’t result in duplicate emails being sent to the same person.

SELECT   c1.contact   ,           Adidas Yeezy 550 Homme c1.company   ,           Adidas Powerlift 3 Homme cs.contsupref,           Adidas NMD Femme c1.accountno FROM     contact1 c1          INNER JOIN contsupp cs          ON       c1.accountno      = cs.accountno WHERE    ISNULL(cs.contsupref,  timberland soldes hommes '') <> '' AND      cs.contsupref IN          (SELECT  contsupref          FROM     contsupp          WHERE    CONTSUPP.rectype = 'P'          AND      CONTSUPP.Contact = 'E-mail Address'          GROUP BY contsupref          HAVING   COUNT(*) > 1          ) ORDER BY c1.contact

Simply copy and paste into GoldMine’s SQL Query window and hit query.

How to Effectively Use ‘Dummy’ Uses in GoldMine I got a great question on this from Susan Hallam from Hallam Internet, basically how to do marketing calendars or other public calendars in GoldMine. nike air jordan 3 homme One of the great things about GoldMine is that its licensing is based on concurrent users, or who is actually logged in. asics soldes So that gives us a lot of flexibility in creating ‘dummy’ users to use for a variety of reasons. adidas nmd r1 homme However, for our topic today, let’s say we have a marketing calendar that includes email blasts, attendance at conferences and the like. nike dynamo free ps We can create a user in GoldMine called ‘Market’, the username is limited to 8 characters, but we can put ‘Marketing Calendar’ as the full name. Soldes Louboutin Dummy Users 1 You’ll then be able to access the Marketing Calendar from the calendaring module by changing the user. buy gil online Dummy Users 2 When scheduling activities on the calendar I’d recommend using Events since they can show up as multi-day activities, for things like conferences, trade show booths, etc. Adidas Zx 500 Og Femme Since the Events probably aren’t related to any specific contact just be sure to uncheck the Linked box when scheduling. asics gel kinsei donna Also, be sure the user for the Event you’re scheduling is Market.

Over the years, Parajumpers Masterpiece Passport I’ve post many SQL Queries that GoldMine users have found very useful. To see the entire list check out the SQL Category on the right or just use this link: http://www.thegoldmineblog.com/?cat=8 For any of those, Nike Cortez femme here’s how to export those results. Nike Roshe Run Femme Right-click in the results area, New Balance 533 homme then choose Output To and Excel. While you can output the results to Word or the Clipboard, new balance femme you’ll find the formatting off… asics gel lyte v homme Excel works the best. Nike Blazer femme From there you can massage the data, Adidas Zx Flux Homme print, save, email, Asics Gel Lyte 3 Homme etc. GoldMine Output SQL Query The only times you’ll run into trouble is if the SQL Query includes notes, bottes ugg those tend not to transfer well.

GoldMine’s alarms, New Balance Homme in conjunction with scheduled activities, are a great way to remind yourself, Asics Gel Lyte 5 Femme Kaki or someone else, Adidas Superstar Femme Rouge to do something. Nike Air Huarache Femme Rose GoldMine Alarms 1 Unless you turn your alarms on by default for certain activities, Parajumper Femme Alaska say Next Actions and Calls, new balance soldes though, Kanken Kids you have to remember to check the box on the activity for the alarm to be turned on for the activity. GoldMine Alarms 2 To set certain activity types to have their alarm defaulted to on simple go to Tools | Options then the Alarms tab. Nike Air Max Thea Homme Bleu Then click on More Options. Nike Air Max Thea Homme Rouge Here you can choose which activities will have their alarms on by default.

I’ve gotten a couple of emails in the last week that go something like this: “Hi Chad… I am having a bit of an issue that the Goldmine folks couldn’t help me with after over an hour on the phone. Adidas Zx Flux Homme Noir All I need is a report showing Completed appointments (and calls if possible) during a certain date range with columns: userid, company, contact, date.” It’s pretty simple. Here’s the SQL Query you’ll need for something very similar, I’ll add a few more columns that I think many people will want. nike air jordan 13 uomo To change the date range change the first (beginning date) and second date (ending date) in the FROM clause.

 SELECT   conthist.userid                                            ,           Nike Blazer Femme CONVERT(VARCHAR(10), conthist.ondate, 101) AS activity_date,          conthist.ref                                               ,          CASE                   WHEN srectype = 'A'                   THEN 'Appt'                   WHEN srectype = 'C'                   THEN 'Call'                   WHEN srectype = 'O'                   THEN 'Other'                   WHEN srectype = 'T'                   THEN 'Action'          END AS activity_type,           Nike Air Max 97 Homme contact1.company    ,           nike kwazi femme contact1.contact    ,          conthist.actvcode   ,          conthist.resultcode FROM     contact1          INNER JOIN conthist          ON       contact1.accountno = conthist.accountno          AND      conthist.ondate BETWEEN '1/1/2010' AND      '1/31/2013' WHERE    srectype IN ('A',                        Adidas Superstar Femme Blanche 'C',                        Adidas Ultra Boost Femme 'O',                        Nike Air Huarache Homme 'T') ORDER BY userid,