10 Replies Latest reply on Jun 8, 2017 3:41 PM by RSanch

    SQL Filter within Goldmine

    RSanch Rookie

      I am looking to create a list of filters for my employees to use on their own to track their progress or work.

       

      I would like to create a GM Filter to show all pending callbacks for that employee. Currently this is what I have below.

       

      WHERE c1.U_KEY4 = 'BM' AND c2.CALLBACKON <= GETDATE ()

       

      Currently I am trying to call every contact record that has a KEY4 value of the employees initials (in this case BM) and if that has a pending callback (c2.callbackon). However it is not returning the correct value reflected in the activity list for all open calls.

        • 1. Re: SQL Filter within Goldmine
          RSanch Rookie

          To give an idea, if I run that filter i get a value of 129. But if i go to Open activities > Calls > And show ALL OPEN for that user it gives 38.

          I believe this is due to the fact that KEY4 on contact records may not be the best tracking mechanism, however that is how we know which account belongs to which employee.

          • 2. Re: SQL Filter within Goldmine
            DCastell Expert

            Filters only filter contacts, not activities.  If the user needs to track/view/manage activities, the best place to do it is the activity list. 

             

            As you've noted, the CALLBACKON field (and other similar extrapolated fields) is not reliable for this purpose. 

             

            The users may also get good use out of some of the built-in dashboards for viewing/slicing/dicing their activities/sales/etc.

            • 3. Re: SQL Filter within Goldmine
              BPayne Rookie

              Doug, why not create queries instead of filters? I have had some success creating queries for users and showing them how to change a key value (like a date) to get the correct results. This gets around the limitation of filters and provides an easy way to list the appropriate contacts while allowing the user to click any record in the result set to get that contact. You can also include contact information (name, phone, etc.) in the result set from the query.

              • 4. Re: SQL Filter within Goldmine
                RSanch Rookie

                We use SQL queries for management, some I've created and a few Doug has even helped me with. The filters are mainly for the sales rep to track and monitor their own work. For instance another Filter we have is for "No Call Backs Scheduled"

                 

                The code is the following:

                 

                WHERE c1.U_KEY4 = 'BM' AND  (c2.CALLBACKON = '' OR c2.CALLBACKON <= ' ' OR c2.CALLBACKON IS NULL

                 

                This basically takes the KEY4 field on the contact record and if it has the initials BM it will show the results of all accounts with BM that have NO call back pending. However this one isn't working as well. Again its more of a tracking tool for the sales rep. I prefer them to use the activity list for pending callbacks, but some filters are useful to show which accounts they need to call.

                 

                Any ideas?

                • 5. Re: SQL Filter within Goldmine
                  BPayne Rookie

                  My thoughts:

                  1. The success of your filter is dependent upon the c2.CALLBACKON field getting populated every time a call activity is created/modified. Since I do not know how this field is populated, it does not seem likely this will work every time.
                  2. If the c2.CALLBACKON field is populated correctly, your filter does not need the c2.CALLBACKON = '' because c2.CALLBACKON <= '' already takes care of this.
                  3. I think you would be more consistent (assuming a callback is a calendar event of type C) if you created a query like shown below.

                   

                  SELECT ACCOUNTNO, CONTACT

                   

                  FROM dbo.CONTACT1

                   

                  WHERE U_KEY4 = 'BM' AND ACCOUNTNO NOT IN (SELECT ACCOUNTNO FROM dbo.CAL WHERE RECTYPE='C')

                   

                  • 6. Re: SQL Filter within Goldmine
                    DCastell Expert

                    Queries are fine (I create many for many purposes) but if the users simply need to manage/view/work with their activities, I fail to see the need for them when the activity list is specifically designed for the purpose.

                     

                    Maybe Raven could explain why the activity list is not sufficient for the needs of the users?

                    • 7. Re: SQL Filter within Goldmine
                      BPayne Rookie

                      Okay, maybe I misunderstood. I was basing my answer on the filter that Rachel shared. This filter's WHERE clause would show all contacts that do not have a call scheduled. How would one use the activity list to identify one's contacts that are not on the list? If she just needs to manage the outstanding calls, the activity list is definitely the way to go.

                      • 8. Re: SQL Filter within Goldmine
                        RSanch Rookie

                        Activity list doesn't show the accounts with out callbacks pending. Basically the filter needs to pull the initials from one field on the record, see if theres a call back pending (callbackon) if there is... then do nothing, because it would be in the activity list at that point. If not, return the results of the contact record.

                         

                        = True, and no pending callbacks = true then show the results. Other wise, yes it would be in the activity list.

                        • 9. Re: SQL Filter within Goldmine
                          DCastell Expert

                          Fair enough.  Maybe I missed that part of the original request.  So, then, take the SQL query Brian provided, query it, then, with the results still on the screen, go to the Groups tab and build a new group based on the query results.  Once that's done, go schedule a call for the whole group. 

                           

                          Problem solved.  BM can then proceed to manage those calls from th activity list.

                          1 of 1 people found this helpful
                          • 10. Re: SQL Filter within Goldmine
                            RSanch Rookie

                            Thanks guys - appreciate your help as always.