KEP4RG4&-%X-%TO GDPR Info NEW Wizard Systems 0 7 frame 4 0 0 1151 645 0 0 1 4 4 4 26 0 Malformed Emails frame_1 1 Tables, Views, Customisation and Record Count frame_2 1 Export Assistant frame_3 1 Wizard Systems Website frame_4 1 GDPR Compliance frame_5 1 Resources frame_6 1 frame_1 frame 2 0 0 1135 607 0 0 1 179 0 0 0 frame_1_1 frame_1_2 frame_1_1 frame_1 1 0 0 1135 177 0 0 1 0 frame_1_2 frame_1 1 0 0 1135 426 0 0 1 0 frame_2 frame 4 0 0 1135 607 0 0 0 39 4 4 26 0 Record Counts frame_2_1 1 Custom SQL views frame_2_2 1 Email Centre Analysis frame_2_3 1 Pending Activities by User frame_2_4 1 frame_2_1 frame_2_1 2 0 0 1119 569 0 0 0 35 0 0 0 frame_2_1_1 frame_2_1_2 frame_2_1_1 frame_2_1_1_1_1 1 0 0 1119 33 0 0 0 0 frame_2_1_2 frame_2_1_1_1_1 1 0 0 1119 532 0 0 0 0 frame_2_2 frame_2 2 0 0 1119 569 0 0 0 28 115 30 0 frame_2_2_1 frame_2_2_2 frame_2_2_1 frame_2_2 1 0 0 1119 26 0 0 0 0 frame_2_2_2 frame_2_2 1 0 0 1119 539 0 0 0 0 frame_2_3 frame_2 2 0 0 1119 569 0 0 0 38 0 0 0 frame_2_3_1 frame_2_3_2 frame_2_3_1 frame_2_3 1 0 0 1119 36 0 0 0 0 frame_2_3_2 frame_2_3 1 0 0 1119 529 0 0 0 0 frame_2_4 frame_2 1 0 0 1119 569 0 0 0 0 frame_3 frame 2 0 0 1135 607 0 0 0 166 0 0 0 frame_3_1 frame_3_2 frame_3_1 frame_3_1 1 0 0 1135 164 0 0 0 0 frame_3_2 frame_3_1 1 0 0 1135 439 0 0 0 0 frame_4 frame 1 0 0 1135 607 0 0 0 0 frame_5 frame 2 0 0 1135 607 0 0 0 330 468 265 0 frame_5_1 frame_5_2 frame_5_1 frame_5 2 0 0 1135 328 0 0 0 96 463 46 0 frame_5_1_1 frame_5_1_2 frame_5_1_1 frame_5_1 1 0 0 1135 94 0 0 0 0 frame_5_1_2 frame_5_1 1 0 0 1135 230 0 0 0 0 frame_5_2 frame_5 1 0 0 1135 275 0 0 0 0 frame_6 frame 1 0 0 1135 607 0 0 0 0 6 frame_1_2 21 0 1134 425 21 0 1134 425 0 0 100 100 556 0 30004 11245 I2R1XES(Y)]G6X{ Table View1 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3F(YB1]%TO Account Number Account Number 0 0 1 256 Account Number Email Email 1 0 1 265 Account Number Recid Error Desc Error Desc 1 0 0 256 Mergecodes Mergecodes 1 0 0 256 0 0 frame_1_1 20 70 629 90 20 70 629 90 0 0 80 20 529 1852 16642 2381 I2R2676$RW[Q6X{ Label2 0 This is a free dashboard supplied by Wizard Systems Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_1_1 20 14 629 50 20 14 629 50 0 0 80 20 529 370 16642 1323 I2R3D1N(*+8G6X{ Label3 0 Bad Email Addresses Window WindowText -21 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 0 frame_1_1 20 98 629 118 20 98 629 118 0 0 80 20 529 2593 16642 3122 I2R3HK2%V C?6X{ Label4 0 For amendments or other custom dashboards please contact us on 01454 316800 Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_1_1 20 154 636 174 20 154 636 174 0 0 80 20 529 4075 16828 4604 I2R3L11#7R`H6X{ Label5 0 To use this Dashboard click the Email Address in the list below to navigate to the record that needs correcting To use this Dashboard click the Email Address in the list below to navigate to the record that needs correcting Window Blue -11 0 0 0 400 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 9 frame_1_1 637 0 952 175 637 0 952 175 0 0 300 300 16854 0 25188 4630 I2R3V3S$$.K{6X{ Web View6 0 http://www.wizard-systems.co.uk/uploads/1/2/7/4/12746493/1395395086.png 0 0 0 frame_1_1 20 126 263 146 20 126 263 146 0 0 80 20 529 3334 6959 3863 I2RCJ0Z ]+=96X{ Label7 0 www.wizard-systems.com Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 6 frame_2_1_2 0 0 1123 537 0 0 1123 537 0 0 100 100 0 0 29713 14208 I4R7T1J%C9.#6X{ Table View8 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3E#H_Q-%TO Count Count 1 0 0 256 Table Name Table Name 1 0 0 256 Table Type Table Type 1 0 0 256 0 0 frame_2_1_1 13 -2 377 26 13 -2 377 26 0 0 80 20 344 -53 9975 688 I4RBRLA)\ERA6X{ Label10 0 How Many Records Do You Have? Window WindowText -21 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 6 frame_3_2 0 0 1132 439 0 0 1132 439 0 0 100 100 0 0 29951 11615 IF8YRTD#ZXHT6X{ Table View13 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3E$_?Y-%TO COMPANY COMPANY 1 0 1 256 ACCOUNTNO CONTACT CONTACT 1 0 0 256 CONTACTTYPE CONTACTTYPE 1 0 0 256 LASTNAME LASTNAME 1 0 0 256 DEPARTMENT DEPARTMENT 1 0 0 256 TITLE TITLE 1 0 0 256 PHONE1 PHONE1 1 0 0 256 ADDRESS1 ADDRESS1 1 0 0 256 ADDRESS2 ADDRESS2 1 0 0 256 ADDRESS3 ADDRESS3 1 0 0 256 CITY CITY 1 0 0 256 STATE STATE 1 0 0 256 ZIP ZIP 1 0 0 256 DEAR DEAR 1 0 0 256 EMAILID EMAILID 1 0 0 256 EMAILMERGECODES EMAILMERGECODES 1 0 0 256 MERGECODES MERGECODES 1 0 0 256 ACCOUNTNO ACCOUNTNO 1 0 0 256 PHONE2 PHONE2 1 0 0 256 PHONE3 PHONE3 1 0 0 256 0 2 frame_3_1 21 84 377 105 21 84 377 105 0 0 100 23 556 2223 9975 2778 IF8Z1CD% MAD6X{ Drop-down list14 0 0 1 KEP4R3E!T^]!%TO Group_name Group_recid 0 frame_3_1 21 35 581 55 21 35 581 55 0 0 80 20 556 926 15372 1455 IF91G2E%=D?86X{ Label15 0 To export from this export assistant dashboard first create a GoldMine Contact Group. Select this Window Blue -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_3_1 21 56 581 76 21 56 581 76 0 0 80 20 556 1482 15372 2011 IF91I6X(QI<G6X{ Label16 0 group using the dropdown list below. Within the table right click and select ‘Output to’ Window Blue -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_3_1 21 0 427 28 21 0 427 28 0 0 80 20 556 0 11298 741 IF91KHQ%;M/E6X{ Label17 0 Export Assistant by Wizard Systems Window WindowText -19 0 0 0 400 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 9 frame_3_1 588 0 931 161 588 0 931 161 0 0 300 300 15558 0 24633 4260 IF91QUZ$,_X{6X{ Web View18 0 http://www.wizard-systems.co.uk/uploads/1/2/7/4/12746493/1395395086.png 0 0 0 frame_3_1 21 119 574 139 21 119 574 139 0 0 80 20 556 3149 15187 3678 IF91X3O&88:B6X{ Label19 0 Wizard System can proivide service or training to edit the look and feel of this Dashboard Window Crimson -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_3_1 21 140 574 160 21 140 574 160 0 0 80 20 556 3704 15187 4233 IF923Q8$6!@;6X{ Label20 0 Please call our sales team for more details Window Crimson -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 9 frame_4 0 0 1141 607 0 0 1141 607 0 0 300 300 0 0 30189 16060 IGWW7JV#4&SO%TO Web View21 15 http://www.wizard-systems.co.uk 0 0 6 frame_2_2_2 0 0 1123 540 0 0 1123 540 0 0 100 100 0 0 29713 14288 IPEQNRY#J9-5%TO SQLViews 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3D*WE0?%TO view_name View_name 1 0 0 256 IsIndexed IsIndexed 1 0 0 256 IsIndexable IsIndexable 1 0 0 256 0 2 frame_2_2_1 0 0 203 21 0 0 203 21 0 0 100 23 0 0 5371 556 IPERBNE)UJ`I%TO DatabseList 0 0 1 KEP4R3D%\(^@%TO Text Value 6 frame_2_3_2 0 0 1123 532 0 0 1123 532 0 0 100 100 0 0 29713 14076 IPESC9A%L;`?%TO Table View23 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3C$ORD?%TO userid userid 1 0 0 256 name name 1 0 0 256 Inbox Inbox 1 0 0 256 Trash Trash 1 0 0 256 Outbox Outbox 1 0 0 256 Draft Draft 1 0 0 256 0 6 frame_2_4 19 85 938 399 19 85 938 399 0 0 100 100 503 2249 24818 10557 K6UBKZK$QHU$6X{ Table View29 0 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3C&\<,/%TO userid Userid 1 0 0 256 Name Name 1 0 0 256 Calls Calls 1 0 0 256 Appts Appts 1 0 0 256 Actions Actions 1 0 0 256 Other Other 1 0 0 256 Lit Fulfilment Lit Fulfilment 1 0 0 256 Events Events 1 0 0 256 To Do To Do 1 0 0 256 Sales Sales 1 0 0 256 Sales (Not Linked to Ops) Sales (Not Linked to Ops) 1 0 0 256 Sales (Linked to Ops) Sales (Linked to Ops) 1 0 0 256 0 0 frame_2_4 32 26 924 46 32 26 924 46 0 0 80 20 847 688 24448 1217 K6UBSIS&>{/-6X{ Label30 0 Are your users completing their activities? Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 0 frame_2_4 35 56 924 76 35 56 924 76 0 0 80 20 926 1482 24448 2011 K6UBUSJ!4UVW6X{ Label31 0 Old un-completed activites can slow GoldMine down. Call us if your users need some training. Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 6 frame_5_2 0 0 1136 262 0 0 1136 262 0 0 100 100 0 0 30057 6932 K7ZTG0C(9>[N6X{ Table GDPR CONTACTS 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3B%E=#(%TO Type Type 0 0 0 256 accountno accountno 0 0 0 256 HistoryRecid HistoryRecid 0 0 0 256 Contact Contact 1 0 1 257 accountno ContactRecid Company Company 1 0 1 256 accountno Company Ondate Date 1 0 0 256 Ref History Reference 1 0 1 264 accountno HistoryRecid Actvcode Actvcode 1 0 0 256 Resultcode Evidence Code 1 0 0 256 ContactRecid ContactRecid 0 0 0 256 createon createon 1 0 0 256 0 0 frame_5_1_1 0 0 469 35 0 0 469 35 0 0 80 20 0 0 12409 926 K7ZTGEG*9*JW6X{ Label36 0 GDPR Compliance Records Window WindowText -19 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 0 frame_5_1_1 476 0 945 20 476 0 945 20 0 0 80 20 12594 0 25003 529 K7ZTLAU*!=]Z6X{ Label37 0 Reference = GDPR Compliance - Code = GDP - Result GD1 - GD6 Window WindowText -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma 0 0 0 0 0 6 frame_2_3_1 1319 255 1419 297 1319 255 1419 297 0 0 100 100 34899 6747 37544 7858 K80109U%MDNH6X{ Table View32 0 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R3B$ V^O%TO Total Total 1 0 0 256 0 6 frame_5_1_2 0 1 1136 228 0 1 1136 228 0 0 100 100 0 26 30057 6033 K802H6U&#F2<6X{ Table GDPR STATS 15 Window WindowText Black -11 0 0 0 400 0 0 0 0 0 0 5 0 Tahoma KEP4R39#QF,)%TO Count Count 1 0 0 256 Description Description 1 0 0 256 0 10 frame_6 7 112 1015 161 7 112 1015 161 0 0 60 20 185 2963 26855 4260 K87DPWU(MR-26X{ Link34 0 The ICO is the UK's independent body set up to uphold information rights https://ico.org.uk/ 1 0 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 10 frame_6 7 175 315 224 7 175 315 224 0 0 60 20 185 4630 8334 5927 K87E6UG*$*;/6X{ Link35 0 Report a Concern https://ico.org.uk/concerns/ 1 0 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 0 frame_6 7 7 775 42 7 7 775 42 0 0 80 20 185 185 20505 1111 K87EA7Y(3ES_6X{ Label38 0 Useful Resources and Web Sites Window Orchid -19 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 10 frame_6 7 49 1015 98 7 49 1015 98 0 0 60 20 185 1296 26855 2593 K87EDJB%7;^T6X{ Link37 0 Getting ready for the GDPR https://ico.org.uk/for-organisations/resources-and-support/data-protection-self-assessment/getting-ready-for-the-gdpr/ 1 0 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 10 frame_6 7 238 315 287 7 238 315 287 0 0 60 20 185 6297 8334 7594 K87EFGY*EZ:W6X{ Link38 0 UK Government GDPR Bill https://www.gov.uk/government/collections/data-protection-bill-2017 1 0 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 10 frame_6 7 301 315 348 7 301 315 348 0 0 60 20 185 7964 8334 9208 K87EV62 +KA,6X{ Link39 0 ePrivacy Regulations https://ico.org.uk/for-organisations/guide-to-pecr/ 1 0 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 9 frame_6 322 175 1064 447 322 175 1064 447 0 0 300 300 8520 4630 28152 11827 K87FDWX$\,,P6X{ Web View40 0 https://twitter.com/iconews 0 0 10 frame_6 7 357 315 408 7 357 315 408 0 0 60 20 185 9446 8334 10795 K8F65RO$<]JC6X{ Link40 0 All 99 Articles https://gdpr-info.eu/ 1 1 Window Blue Blue -19 0 0 0 700 0 1 0 0 3 2 1 34 Tahoma 0 0 0 0 0 3584 0 frame_5_1_1 7 35 889 63 7 35 889 63 0 0 80 20 185 926 23521 1667 LEPP30J!XORF6X{ Label39 0 This Dashboard may not show your compliant and non-compliant records because you have not configured your GoldMine system to track this information. Call us for training on how to configure GoldMine to track GDPR Compliance. Window Crimson -11 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 0 frame_5_1_1 7 63 888 91 7 63 888 91 0 0 80 20 185 1667 23495 2408 LEPP6D8$L;.Q6X{ Label40 0 Call us for training on how to configure GoldMine to track GDPR Compliance. Window Crimson -11 0 0 0 700 0 0 0 0 3 2 1 34 Tahoma 0 0 0 0 0 2 1 13 IF8Z1CD% MAD6X{ 5 0 7 IF8YRTD#ZXHT6X{ KEP4R3E$_?Y-%TO CONTACT_GROUP_EXPLICIT_FILTER 1 IF8Z1CD% MAD6X{ Value IPERBNE)UJ`I%TO 5 0 7 IPEQNRY#J9-5%TO KEP4R3D*WE0?%TO DB 1 IPERBNE)UJ`I%TO Value K802H6U&#F2<6X{ 5 0 7 K7ZTG0C(9>[N6X{ KEP4R3B%E=#(%TO TYPE 1 K802H6U&#F2<6X{ Description 321 2 0 EMPTYCGRECID000 2 Wiz_GDPR_Evidence_Count_KEP4R39#QF,)%TO KEP4R39#QF,)%TO 0 0 0 0 0 select count(*) 'Count', 'Total Primary Contacts' [Description] from contact1 union all select count(*) 'Count', 'Total Additional Contacts' [Description] from contsupp where rectype = 'C' union all --'No Evidence for Primary Contact' Select count(*) 'Count', 'No Evidence for Primary Contact' 'Description' from contact1 where accountno not in (select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate > getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4)) union all --'No Evidence for Additional Contact' Select count(*) 'Count', 'No Evidence for Additional Contact' from contsupp where rectype = 'C' and recid not in (select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and ch.resultcode like 'GD%' and ch.ondate > getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C') union All -- Primary Contacts with Evidence select count(*) 'Count', 'Evidence for Primary Contact' 'Description' from contact1 where accountno in (select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate > getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) ) union all -- Additional Contacts with Evidence Select count(*) 'Count', 'Evidence for Additional Contact' from contsupp where rectype = 'C' and recid in (select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate > getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C') union all --Primary Contacts with Evidence (Expired) select count(*) 'Count', 'Evidence for Primary Contact (Expired)' 'Description' from contact1 where accountno in (select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate < getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where c1.accountno not in ( select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) ) ) union all --Additional Contacts with Evidence (Expired) Select count(*) 'Count', 'Evidence for Additional Contact (Expired)' from contsupp where rectype = 'C' and recid in (select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate < getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C' and cs.recid not in ( select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) )) 1 EMPTYCGRECID000 2 Wiz_noHistPend_Count_KEP4R3B$ V^O%TO KEP4R3B$ V^O%TO 0 0 0 0 0 Select count (Accountno) Total From Contact1 Where Accountno not in (SELECT DISTINCT Accountno FROM CAL) and Accountno not in (SELECT DISTINCT Accountno FROM Conthist) 1 CLOSEDACTCG0001 2 WIZGDPR_Evidence_KEP4R3B%E=#(%TO KEP4R3B%E=#(%TO 0 c1 ACCOUNTNO 1 ACCOUNTNO c1_2 2 Company 3 2 Contact h RESULTCODE 1 Evidence Code h REF 1 Reference h ACTVCODE 1 ACTVCODE h ONDATE 1 ONDATE h_ondate_year 2 Activity_year h ACCOUNTNO 1 Built By Wizard System 0 0 0 AND h SRECTYPE 1 EQUAL TO O 1 AND h REF 1 BEGINS GDPR Compliance 0 AND h ACTVCODE 1 EQUAL TO GDP 0 ACTIVITY_USER_GROUP 1 AND 0 0 0 h.[USERID] IN ( <<~USER_GROUP_MEMBERS>> ) 1 CONTACT_GROUP_EXPLICIT_FILTER 1 AND EQUAL TO 0 0 1 c1.ACCOUNTNO IN (SELECT ACCOUNTNO FROM {{contact_db}}CONTGRPS WHERE USERID IN ( SELECT RECID FROM {{contact_db}}CONTGRPS WHERE RECID = '<<VALUE>>' )) 1 CONTACT_GROUP_VIA_MACRO 1 AND EQUAL TO 0 0 1 <<~CONTACT_GROUP>> 1 CONTACT_FILTER 1 AND EQUAL TO 0 0 0 <<~CONTACT_FILTER>> 1 CONTACT_ACCOUNT 0 AND EQUAL TO c1 ACCOUNTNO 1 0 0 1 CONTACT_RECID 0 AND EQUAL TO c1 RECID 1 0 0 1 OPP_RECID 0 AND EQUAL TO op RECID 1 0 0 1 OPP_USER_GROUP 1 AND 0 0 0 op.[USERID] IN ( <<~USER_GROUP_MEMBERS>> ) 1 TYPE 1 AND EQUAL TO 0 IMPOSSIBLEVALUE 1 1 type = '<<VALUE>>' 0 0 select * from ( select 'Total Primary Contacts' 'Type', c1.accountno, '' 'HistoryRecid', c1.recid 'ContactRecid', c1.Contact, c1.Company, c1.createon, null [Ondate], '' [Ref], '' [Actvcode], '' [Resultcode], 'Primary Contact' 'Contact Type' from contact1 c1 union all Select 'Total Additional Contacts', cs.accountno, '' 'HistoryRecid', cs.recid 'ContactRecid', Cs.Contact, c1.Company, c1.createon, null [Ondate], '' [Ref], '' [Actvcode], '' [Resultcode], 'Additional Contact' 'Contact Type' from contsupp cs join contact1 c1 on cs.accountno = c1.accountno where cs.rectype = 'C' union all select 'No Evidence for Primary Contact' 'Type', c1.accountno, '' 'HistoryRecid', c1.recid 'ContactRecid', c1.Contact, c1.Company, c1.createon, null [Ondate], '' [Ref], '' [Actvcode], '' [Resultcode], 'Primary Contact' 'Contact Type' from contact1 c1 where accountno not in (select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate > getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4)) union all --No Evidence Additional Select 'No Evidence for Additional Contact', cs.accountno, '' 'HistoryRecid', cs.recid 'ContactRecid', Cs.Contact, c1.Company, c1.createon, null [Ondate], '' [Ref], '' [Actvcode], '' [Resultcode], 'Additional Contact' 'Contact Type' from contsupp cs join contact1 c1 on cs.accountno = c1.accountno where cs.rectype = 'C' and cs.recid not in (select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and ch.resultcode like 'GD%' and ch.ondate > getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C') union all --Primary Contacts select 'Evidence for Primary Contact' 'Type', c1.accountno, ch.recid 'HistoryRecid', c1.recid 'ContactRecid', c1.Contact, c1.Company, c1.createon, Ch.ondate, ch.ref, ch.actvcode, ch.resultcode, 'Primary Contact' 'Contact Type' from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) union all -- select 'Evidence for Primary Contact (Expired)' 'Type', c1.accountno, ch.recid 'HistoryRecid', c1.recid 'ContactRecid', c1.Contact, c1.Company, c1.createon, Ch.ondate, ch.ref, ch.actvcode, ch.resultcode, 'Primary Contact' 'Contact Type' from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate < getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where c1.accountno not in ( select c1.accountno from contact1 c1 join conthist ch on c1.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(c1.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) ) union all --Addtional Contacts select 'Evidence for Additional Contact' 'Type', cs.accountno, ch.recid 'HistoryRecid', cs.recid 'ContactRecid', Cs.Contact, c1.Company, c1.createon, Ch.ondate, ch.ref, ch.actvcode, ch.resultcode, 'Additional Contact' 'Contact Type' from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C' union all --Evidence for Addtional Contact Expired select 'Evidence for Additional Contact (Expired)' 'Type', cs.accountno, ch.recid 'HistoryRecid', cs.recid 'ContactRecid', Cs.Contact, c1.Company, c1.createon, Ch.ondate, ch.ref, ch.actvcode, ch.resultcode, 'Additional Contact' 'Contact Type' from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate < getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4) where cs.rectype = 'C' and cs.recid not in ( select cs.recid from contsupp cs join contact1 c1 on c1.accountno = cs.accountno join conthist ch on cs.accountno = ch.accountno and resultcode like 'GD%' and ch.ondate >= getdate() -730 and left(cs.contact,len(substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4))) = substring(ref,charindex('oc:',ch.ref)+3,len(substring(ref,charindex('oc:',ch.ref),80))-4)) ) Contacts where 1 = 1 <<TYPE>> order by Company, contact 1 EMPTYCGRECID000 2 Email Analysis_K7RZZKT(EX8N6X{_KEP4R3C$ORD?%TO KEP4R3C$ORD?%TO 0 0 0 0 0 select Distinct userid, u.name, (select count(*) from Mailbox with (NOLOCK) where [FOLDER] = 'X-GM-INBOX' and mb.userid = mailbox.userid ) 'Inbox', (select count(*) from Mailbox with (NOLOCK) where [FOLDER] = 'X-GM-TRASH' and mb.userid = mailbox.userid ) 'Trash', (select count(*) from Mailbox with (NOLOCK) where [FOLDER] = 'X-GM-OUTBOX' and mb.userid = mailbox.userid ) 'Outbox', (select count(*) from Mailbox with (NOLOCK) where [FOLDER] = 'X-GM-DRAFTS' and mb.userid = mailbox.userid ) 'Draft' from mailbox mb with (NOLOCK) left join users u on mb.userid = u.username order by userid 1 EMPTYCGRECID000 2 Wizard Pending by User_K7RZZKS#@1>66X{_KEP4R3C&\<,/%TO KEP4R3C&\<,/%TO 0 0 0 0 0 select distinct cl.userid, u.Name, (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'C' and cl.userid = c.userid ) 'Calls', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'A' and cl.userid = c.userid ) 'Appts', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'T' and cl.userid = c.userid ) 'Actions', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'O' and cl.userid = c.userid ) 'Other', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'F' and cl.userid = c.userid ) 'Lit Fulfilment', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'E' and cl.userid = c.userid ) 'Events', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'D' and cl.userid = c.userid ) 'To Do', (select count(*) from Cal c with (NOLOCK) where left(rectype,1) = 'S' and cl.userid = c.userid ) 'Sales', (select count(*) from Cal c with (NOLOCK) where (left(loprecid,1) = ' ' or isnull(loprecid,'') = '') and left(c.rectype,1) = 'S' and cl.userid = c.userid ) 'Sales (Not Linked to Ops)', (select count(*) from Cal c with (NOLOCK) where left(loprecid,1) <> ' ' and isnull(loprecid,'') <> '' and left(c.rectype,1) = 'S' and cl.userid = c.userid ) 'Sales (Linked to Ops)' from cal cl left join users u on cl.userid = u.username order by cl.userid select accountno, COMPANY, Contact, (select COUNT(*) from CAL where ACCOUNTNO = contact1.ACCOUNTNO) 'Count' from CONTACT1 where ACCOUNTNO in ( select top 5 accountno from CAL where accountno > '' group by accountno order by COUNT(*) desc) 1 EMPTYCGRECID000 2 List SQL Databases_K7RZZKU(J3<%6X{_KEP4R3D%\(^@%TO KEP4R3D%\(^@%TO 0 0 0 0 0 SELECT name [Text], name+'.sys.views' [Value] FROM master.dbo.sysdatabases WHERE dbid > 6 order by 1 1 EMPTYCGRECID000 2 List SQL Views_K7RZZKV&1:F#6X{_KEP4R3D*WE0?%TO KEP4R3D*WE0?%TO 0 0 0 0 DB 1 EQUAL TO 0 sys.views 1 1 <<VALUE>> 0 0 SELECT name AS view_name ,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexed') AS IsIndexed ,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexable') AS IsIndexable FROM <<DB>> order by 1 1 CONTGROUPSCG001 2 Contact Group List_K7RZZKV%A]<G6X{_KEP4R3E!T^]!%TO KEP4R3E!T^]!%TO 0 1 2 Group_name 4 2 Group_recid 0 0 0 0 SELECT ' (Not set)' as [Group_name], '<<__INACTIVE_STATE__>>' as [Group_recid] UNION SELECT cg.REF as [Group_name], cg.RECID as [Group_recid] FROM <<CONTGRPS_TABLE>> cg WHERE cg.ACCOUNTNO like '*M_ %' AND cg.REF > '' ORDER BY 1 1 EMPTYCGRECID000 2 WS Table Record Count_K7RZZKW&{I,Q6X{_KEP4R3E#H_Q-%TO KEP4R3E#H_Q-%TO 0 0 0 0 0 Select count(*)'Count' , 'Cal' 'Table Name','Calendar' 'Table Type' from Cal union all Select count(*), 'Cal','Calendar - Excluding Email Headers' 'Table Type' from Cal where left(rectype,1) <> 'M' union all Select count(*), 'Cases','Cases' 'Table Type' from Cases union all Select count(*), 'Contact1','Company/ Primary Contact Information' 'Table Type' from Contact1 union all Select count(*), 'Contact2','User Defined Field Data' 'Table Type' from Contact2 union all Select count(*), 'ContGrps', 'Group Records' from Contgrps union all Select count(*), 'ContGrps', 'Group Headers' from Contgrps where accountno like '*M%' union all Select count(*), 'ContGrps', 'Group Members' from Contgrps where accountno not like '*M%' union all Select count(*), 'Conthist', 'History Data' from Conthist union all Select count(*), 'Contsupp', 'Additional Contacts' from Contsupp where rectype ='C' union all Select count(*), 'Contsupp', 'Linked Documents' from Contsupp where rectype ='L' union all Select count(*), 'Contsupp', 'Referrals' from Contsupp where rectype = 'R' union all Select count(*), 'Contsupp', 'Additional Contacts' from Contact1 union all Select count(*) ,'Contsupp' , 'Detail Record - '+Contact 'Name/Desc' from Contsupp where rectype = 'P' group by contact union all Select count(*), 'Forms', 'Template Records' from forms union all Select count(*), 'Lookup', 'F2 Pick Lists' from Lookup union all Select count(*), 'Mailbox', 'Email Bodies' from mailbox union all Select count(*), 'Opmgr', 'Opportunites' from opmgr where left(rectype,1) ='O' union all Select count(*), 'Opmgr', 'Projects' from opmgr where left(rectype,1) ='P' union all Select count(*), 'Report32', 'Reports' from report32 union all Select count(*), 'Users', 'User Records' from users 1 EMPTYCGRECID000 2 WizFreeExport_K7RZZKW&5!;]6X{_KEP4R3E$_?Y-%TO KEP4R3E$_?Y-%TO 0 0 0 0 CONTACT_GROUP_VIA_MACRO 1 AND EQUAL TO 0 0 0 '<<~CONTACT_GROUP>>' 0 CONTACT_RECID 1 AND EQUAL TO 0 0 0 c1.recird='<<Value>>' 0 CONTACT_ACCOUNT 1 AND EQUAL TO 0 0 0 c1.accountno='<<Value>>' 0 CONTACT_GROUP_EXPLICIT_FILTER 1 AND EQUAL TO 0 0 1 c.ACCOUNTNO IN (SELECT ACCOUNTNO FROM {{contact_db}}CONTGRPS WHERE USERID IN ( SELECT RECID FROM {{contact_db}}CONTGRPS WHERE RECID = '<<VALUE>>' )) 0 MergeCode 1 AND EQUAL TO 0 0 0 0 0 SELECT C.*, W.WEBSITE, C2.*, (LEFT([Contact], CASE WHEN CHARINDEX(' ', [Contact]) > 0 THEN CHARINDEX(' ', [Contact]) -1 ELSE LEN([Contact]) END)) as First FROM (SELECT C1.ACCOUNTNO+C1.RECID AS CUSTID, 'PRIMARY' AS CONTACTTYPE, C1.ACCOUNTNO, C1.COMPANY, C1.CONTACT, C1.LASTNAME, C1.DEPARTMENT, C1.TITLE, C1.SECR, C1.PHONE1, C1.PHONE2, C1.PHONE3, C1.FAX, C1.EXT1, C1.EXT2, C1.EXT3, C1.EXT4, C1.ADDRESS1, C1.ADDRESS2, C1.ADDRESS3, C1.CITY, C1.STATE, C1.ZIP, C1.COUNTRY, C1.DEAR, C1.SOURCE, C1.KEY1, C1.KEY2, C1.KEY3, C1.KEY4, C1.KEY5, C1.STATUS, --CAST(C1.NOTES AS VARCHAR(1000)) NOTES, ltrim(isnull(C1.MERGECODES,'')) MERGECODES, C1.CREATEBY, C1.CREATEON, C1.CREATEAT, C1.OWNER, C1.LASTUSER, C1.LASTDATE, C1.LASTTIME, C1.RECID, ISNULL(PE.EMAILID,'n/a') EMAILID, ISNULL(PE.EMAILMERGECODES,'') EMAILMERGECODES FROM CONTACT1 C1 WITH (NOLOCK) LEFT JOIN (SELECT ACCOUNTNO, ISNULL(CONTSUPREF,'')+ISNULL(ADDRESS1,'') [EMAILID], MERGECODES AS EMAILMERGECODES FROM CONTSUPP WHERE RECTYPE = 'P' AND CONTACT = 'E-MAIL ADDRESS' AND SUBSTRING(ZIP,2,1) = '1' AND (LINKACCT IS NULL OR LINKACCT = ' ') ) PE ON C1.ACCOUNTNO = PE.ACCOUNTNO union SELECT CSC.ACCOUNTNO+CSC.RECID AS CUSTID, 'ADDITIONAL' AS CONTACTTYPE, CSC.ACCOUNTNO, C1.COMPANY, CSC.CONTACT, LTRIM(RIGHT(CSC.CONTACT,CHARINDEX(' ',REVERSE(CSC.CONTACT)))) AS LASTNAME, NULL AS DEPARTMENT, CSC.TITLE, NULL AS SECR, CASE ISNULL(CSC.PHONE, '') WHEN '' THEN C1.PHONE1 ELSE CSC.PHONE END AS PHONE1, C1.PHONE2, C1.PHONE3, C1.FAX, CASE ISNULL(CSC.PHONE, '') WHEN '' THEN C1.EXT1 ELSE CSC.EXT END AS EXT1, C1.EXT2, C1.EXT3, C1.EXT4, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.ADDRESS1 ELSE CSC.ADDRESS1 END AS ADDRESS1, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.ADDRESS2 ELSE CSC.ADDRESS2 END AS ADDRESS2, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.ADDRESS3 ELSE CSC.ADDRESS3 END AS ADDRESS3, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.CITY ELSE CSC.CITY END AS CITY, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.STATE ELSE CSC.STATE END AS STATE, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.ZIP ELSE CSC.ZIP END AS ZIP, CASE ISNULL(CSC.ADDRESS1, '') WHEN '' THEN C1.COUNTRY ELSE CSC.COUNTRY END AS COUNTRY, CSC.DEAR, C1.SOURCE, C1.KEY1, C1.KEY2, C1.KEY3, C1.KEY4, C1.KEY5, C1.STATUS, --CAST(CSC.NOTES AS VARCHAR(1000)) NOTES, Ltrim(isnull(CSC.MERGECODES,'')), C1.CREATEBY, C1.CREATEON, C1.CREATEAT, C1.OWNER, CSC.LASTUSER, CSC.LASTDATE, CSC.LASTTIME, CSC.RECID, ISNULL(AE.EMAILID,'n/a') EMAILID, ISNULL(AE.EMAILMERGECODES,'') EMAILMERGECODES FROM CONTSUPP CSC WITH (NOLOCK) JOIN CONTACT1 C1 on C1.Accountno = CSC.ACCOUNTNO LEFT JOIN (SELECT LINKACCT, ISNULL(CONTSUPREF,'')+ISNULL(ADDRESS1,'') [EMAILID], MERGECODES AS EMAILMERGECODES FROM CONTSUPP WHERE RECTYPE = 'P' AND CONTACT = 'E-MAIL ADDRESS' AND LINKACCT IS NOT NULL AND LINKACCT <> ' ' ) AE ON CSC.RECID = AE.LINKACCT WHERE CSC.RECTYPE = 'C' ) C JOIN CONTACT2 C2 on C2.Accountno = C.ACCOUNTNO LEFT JOIN ( SELECT ACCOUNTNO, CASE ADDRESS1 WHEN NULLIF(ADDRESS1,null) THEN CONTSUPREF+ADDRESS1 ELSE CONTSUPREF END AS WEBSITE FROM CONTSUPP WHERE RECTYPE = 'P' AND CONTACT = 'WEB SITE' AND SUBSTRING(ZIP,2,1) = '1' ) W ON C.ACCOUNTNO = W.ACCOUNTNO Where 1=1 <<CONTACT_GROUP_EXPLICIT_FILTER>> 1 EMPTYCGRECID000 2 WSBadEmail_K7RZZKW&[_)%6X{_KEP4R3F(YB1]%TO KEP4R3F(YB1]%TO 0 0 0 0 0 select Accountno as [Account Number],Mergecodes, Len(isnull(contsupref,'') + isnull(address1,'')) AS [Email Length], isnull(contsupref,'') + isnull(address1,'') 'Email', Case When isnull(contsupref,'') + isnull(address1,'') is null then 'NULL Email is invalid' When charindex(' ', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex('/', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex('=', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex(':', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex(';', isnull(contsupref,'') + isnull(address1,'')) <> 0 then 'invalid character' When len( isnull(contsupref,'') + isnull(address1,''))-1 <= charindex('.', isnull(contsupref,'') + isnull(address1,'')) then 'check for %._ at end of string' When isnull(contsupref,'') + isnull(address1,'') like '%@%@%'or isnull(contsupref,'') + isnull(address1,'') Not Like '%@%.%' then 'Check for duplicate @ or invalid format' End 'Error Desc' from contsupp where rectype= 'P' and contact = 'E-mail Address' and Case When isnull(contsupref,'') + isnull(address1,'') is null then 0 When charindex(' ', isnull(rtrim(contsupref),'') + isnull(rtrim(address1),'')) <> 0 or charindex('/', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex(':', isnull(contsupref,'') + isnull(address1,'')) <> 0 or charindex(';', isnull(contsupref,'') + isnull(address1,'')) <> 0 then 0 When len( isnull(contsupref,'') + isnull(address1,''))-1 <= charindex('.', isnull(contsupref,'') + isnull(address1,'')) then 0 When isnull(contsupref,'') + isnull(address1,'') like '%@%@%'or isnull(contsupref,'') + isnull(address1,'') Not Like '%@%.%' then 0 Else 1 END = 0 1