8 Replies Latest reply on Dec 29, 2016 3:13 PM by GZimme

    Primary Email in Crystal report

    GZimme Apprentice

      How do I add the primary email address in a Crystal report?  I need to display contact1 and contact2 data but not sure how to get the Contsupp table email address.

      I know I need to add both tables in crystal but not sure on the "code" to show the email address.  Thanks in advance for any help.

        • 1. Re: Primary Email in Crystal report
          John Neighbors Expert

          I tend to setup this SQL view using SQL Server Management Studio, and then LINK this view just like a table using Crystal Reports. It makes it a whole lot easier.

           

          --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

          --- vw_Email_Address VIEW

          --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

          if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_Email_Address]') and OBJECTPROPERTY(id, N'IsView') = 1)

          drop view [dbo].[vw_Email_Address]

          GO

           

          SET QUOTED_IDENTIFIER OFF

          GO

          SET ANSI_NULLS ON

          GO

           

          /*

           

          --=-=-=-=-=-=-=-=-=-=-=-=

          -- Synopsis

          --=-=-=-=-=-=-=-=-=-=-=-=

           

          Simplifies reporting of GM email addresses (E-mail Address details).

           

          --=-=-=-=-=-=-=-=-=-=-=-=

          -- Change Log

          --=-=-=-=-=-=-=-=-=-=-=-=

           

          2016-12-28 jsn    View creation.

           

          */

           

          CREATE VIEW [vw_Email_Address]

          AS

           

          select ACCOUNTNO

          , isnull(contsupref,'')+isnull(address1,'') as 'EMAIL'

          , SUBSTRING(isnull(zip,''),2,1) as 'PRIMARY'

          , LINKACCT

          , ADDRESS2 as 'Name'

          , isnull(MERGECODES,'') as 'MERGECODES'

           

          from contsupp

          where rectype = 'P' and u_contact = 'E-MAIL ADDRESS'

           

          GO

           

          SET QUOTED_IDENTIFIER OFF

          GO

          SET ANSI_NULLS ON

          GO

           

          GRANT SELECT ON [dbo].[vw_Email_Address] TO [public]

          GO

           

          Hope this feedback helps.

          • 2. Re: Primary Email in Crystal report
            GZimme Apprentice

            Jim,

            THANKS...  clarifying question:   Do I just copy this entire code into a sql query and run it?

             

            And then the vw_Email_Address "Table/View" can be accessed via Crystal?!?!

             

            This will be quite nice.

            • 3. Re: Primary Email in Crystal report
              GZimme Apprentice

              Jim,

              THANKS.  I just ran the sql script and it created the view.  I will try the Crystal report later today.

              • 4. Re: Primary Email in Crystal report
                John Neighbors Expert

                Was out of pocket for a bit, but, as you've now seen, YES, that's what you do.  Good Luck! :-)

                • 5. Re: Primary Email in Crystal report
                  John Neighbors Expert

                  By the way, recognize this includes ALL email addresses, both primary AND non-primary.  Therefore, any given CONTACT1 record could have MULTIPLE of these email address records reported by this view.

                   

                  With the view created, simply run:

                   

                  select * from vw_email_address

                   

                  ...and notice the Primary field.  If Primary = '1' then that email address is the one flagged as Primary; otherwise, it is NOT a primary email address.

                   

                  Again, hope this helps.

                  • 6. Re: Primary Email in Crystal report
                    GZimme Apprentice

                    Helpful.. however, I get more records than I want.  I only want one contact record and if it has an email address then it will display.  What I'm getting is a record for each email address... so it appears like I have duplicate contacts in GoldMine.  And if there is no email address then I get no contact1 data on my report.   Here's my DB Expert in Crystal...  I guess I can't attach a pic here.... so here's the linking order in Crystal:

                    Contact1.accountno -->Contact2.accountno

                    Contact1.accountno-->vw_Email_Address.accountno

                     

                    My end result is that I need any new contacts created(I use parameter field to look at createon) in GoldMine by date range.  I show a couple contact1 fields and one contact2 fields.  I need the primary email address to display if it's there.  This is not a report just for email addresses.  It's primarily a report for contact1/2 data and to show the email address if present.

                     

                    THANKS for any additional help.

                    • 7. Re: Primary Email in Crystal report
                      John Neighbors Expert

                      Gary,

                       

                      This is simply a matter of knowing how to "instruct" Crystal Reports to do precisely what you want.  Yes, the VIEW I shared shows all email addresses, as that allows maximum flexibility with reporting.  You simply need to use it however your needs dictate.  I'm short on time and it's been a while since I did this specific thing with Crystal, so I'm a bit rusty on the details.

                       

                      Due to my own time constraint, let me offer this simple, slightly alternative approach:

                       

                      1) First, make a slightly different version of the VIEW that ONLY returns PRIMARY email addresses.

                       

                      Rather than "vw_Email_Address", you might name it something like "vw_Email_Address_Primary_Only".

                       

                      For the details of the script I provided above, change the WHERE clause from this:

                       

                           where rectype = 'P' and u_contact = 'E-MAIL ADDRESS'

                       

                      To this:

                       

                           where rectype = 'P' and u_contact = 'E-MAIL ADDRESS' and SUBSTRING(isnull(zip,''),2,1) = '1'

                       

                      Then, change your Crystal Report to using the new vw_Email_Address_Primary_Only view rather than the original vw_Email_Address view.

                       

                      2) THEN, when linking this NEW "primary only" view to the CONTACT1 table...

                       

                      Since having a primary email address is OPTIONAL, you want the LINK between CONTACT1 and the VIEW to be a LEFT OUTER JOIN, such as this:

                       

                       

                      This tells Crystal that you want to include CONTACT1 no matter if there is an associated entry in the VIEW or not.

                       

                      Hope this feedback helps.

                      1 of 1 people found this helpful
                      • 8. Re: Primary Email in Crystal report
                        GZimme Apprentice

                        John,

                        PERFECT... i actually understand all that AND I agree that this should do exactly what I need.

                        You're the best.  Greatly appreciated.