How to create a Query Lookup Filter in DejaOffice PC CRM

From CompanionLink Support
Jump to: navigation, search

Using DejaOffice PC CRM you can now create Advanced Lookup Filers using SQL Queries.

Where to find the Lookup Filters

Findqueries.png

The Advanced Lookup filters can be found in the Following DejaOffice PC CRM Menu:

  1. Open DejaOffice PC CRM and select Contacts.
  2. Select the Category Filter Icon (or use the 3 bars in the top right and select Category Filter).
  3. In the bottom left, select the "Queries" option, this will open the Query Filter Menu.


Note: Using the Category Filter is still the easiest way to Filter records in DejaOffice PC CRM.















Creating a Lookup Filter

To create new filters, from the Query Filter Panel:

  1. Select "+" to create a new Lookup Filter.
  2. In the top box, set a Name for your Lookup.
  3. Select the Query Box and right Click to get a list of the DejaOffice PC CRM Fields for the Lookup and select the Field you want to Query on.
  4. Add the SQL lookup command. (See Below for a examples). If you right click in the command box it will list all DPC fields you can use.
  5. Finally Add the text you are wanting to lookup. (IE: The Last Name, First Name, Phone number, etc that you want to display).
  6. Click Ok to Save and your filter will be stored for use at any time in the future.


QueryCreate.png
  1. Example: clxcategory LIKE 'Business' AND addrCity1 LIKE 'Seattle'. This will filter the DPC contacts list to display only contacts with the Category of "Business" and in the City of "Seattle".
  2. Example: createDate > '1580198400000'. This will filter the DPC contacts list to display only contacts Created after start of 2020. To add a Date to a Query, right click the query box and select "Insert Date Value".
  3. Example: companyName = 'ACME' AND addrCity1 LIKE 'Atlanta'. This will filter the DPC contacts list to display only contacts in the ACME company in the city of Atlanta.
  4. Example: phoneValue1 LIKE '%67' OR phoneValue2 LIKE '%67' . This will filter the DPC contacts list to display only contacts where their first two phone number fields end in 67, you can repeat this for all 10 phone fields if you'd like although ensure there is no trailing OR after the last phone value.
  5. Example: length(companyName)>0. This filter will display all contacts with a Company Name field that is greater than 0. IE Only records with a company name.
  6. Example: birthday > '19580101' AND birthday < '20100101'. This filter would show contacts with Birthday's between the dates of Jan 1st 1958 and Jan 1st 2010.


  • Note: There are many SQL Commands, but for query purposes you should only need AND, =, >, <, %, and LIKE.



Using an Existing Lookup Filter

After creating Lookup Filters, to enable or disable them:

Usefilter1.png
  1. Open DejaOffice PC CRM and select Contacts.
  2. Select the Category Filter Icon (or use the 3 bars in the top right and select Category Filter).
  3. In the bottom left, select the "Queries" option, this will open the Query Filter Menu.


From this menu you an enable or disable any created filers. To clear a lookup filter, select the Category Filter Icon at the top and uncheck any selected Filters. To return to Category Filters, select "Categories" in the bottom left of the Filter Menu.

  • Note: Currently you can only Filter on a Lookup Query OR on a Category, not both at the same time.

DejaOffice PC Field Names

If you open a new Query filter you can right click the Query box and select any field to add to the Query.

  • Note: this list is not complete and may or may not be up to date, for best results open your Database file in something like SQLite Browser
  • In addition not all internal fields are exposed in the display

Contacts

  1. autoid INTEGER "autoid" INTEGER NOT NULL
  2. anniversary TEXT "anniversary" TEXT DEFAULT
  3. birthday TEXT "birthday" TEXT DEFAULT
  4. children TEXT "children" TEXT DEFAULT
  5. companyName TEXT "companyName" TEXT DEFAULT
  6. displayText TEXT "displayText" TEXT DEFAULT
  7. externalId TEXT "externalId" TEXT
  8. extraDetails TEXT "extraDetails" TEXT DEFAULT
  9. firstName TEXT "firstName" TEXT DEFAULT
  10. id TEXT "id" TEXT
  11. imAvatarLoc TEXT "imAvatarLoc" TEXT DEFAULT
  12. jobTitle TEXT "jobTitle" TEXT DEFAULT
  13. lastName TEXT "lastName" TEXT DEFAULT
  14. middleName TEXT "middleName" TEXT DEFAULT
  15. nickname TEXT "nickname" TEXT DEFAULT
  16. notes TEXT "notes" TEXT DEFAULT
  17. prefix TEXT "prefix" TEXT DEFAULT
  18. spouse TEXT "spouse" TEXT DEFAULT
  19. suffix TEXT "suffix" TEXT DEFAULT
  20. url TEXT "url" TEXT DEFAULT
  21. clxcategory TEXT "clxcategory" TEXT DEFAULT
  22. modifiedPC INTEGER "modifiedPC" INTEGER DEFAULT 0
  23. modifiedHH INTEGER "modifiedHH" INTEGER DEFAULT 0
  24. clxdeleted INTEGER "clxdeleted" INTEGER DEFAULT 0
  25. addrCity1 TEXT "addrCity1" TEXT DEFAULT
  26. addrCountry1 TEXT "addrCountry1" TEXT DEFAULT
  27. addrCustomLabel1 TEXT "addrCustomLabel1" TEXT
  28. addrFreeformAddress1 TEXT "addrFreeformAddress1" TEXT DEFAULT
  29. addrLabel1 INTEGER "addrLabel1" INTEGER DEFAULT 0
  30. addrState1 TEXT "addrState1" TEXT DEFAULT
  31. addrStreetAddress1 TEXT "addrStreetAddress1" TEXT DEFAULT
  32. addrZipCode1 TEXT "addrZipCode1" TEXT DEFAULT
  33. addrCity2 TEXT "addrCity2" TEXT DEFAULT
  34. addrCountry2 TEXT "addrCountry2" TEXT DEFAULT
  35. addrCustomLabel2 TEXT "addrCustomLabel2" TEXT
  36. addrFreeformAddress2 TEXT "addrFreeformAddress2" TEXT DEFAULT
  37. addrLabel2 INTEGER "addrLabel2" INTEGER DEFAULT 0
  38. addrState2 TEXT "addrState2" TEXT DEFAULT
  39. addrStreetAddress2 TEXT "addrStreetAddress2" TEXT DEFAULT
  40. addrZipCode2 TEXT "addrZipCode2" TEXT DEFAULT
  41. addrCity3 TEXT "addrCity3" TEXT DEFAULT
  42. addrCountry3 TEXT "addrCountry3" TEXT DEFAULT
  43. addrCustomLabel3 TEXT "addrCustomLabel3" TEXT
  44. addrFreeformAddress3 TEXT "addrFreeformAddress3" TEXT DEFAULT
  45. addrLabel3 INTEGER "addrLabel3" INTEGER DEFAULT 0
  46. addrState3 TEXT "addrState3" TEXT DEFAULT
  47. addrStreetAddress3 TEXT "addrStreetAddress3" TEXT DEFAULT
  48. addrZipCode3 TEXT "addrZipCode3" TEXT DEFAULT
  49. customName1 TEXT "customName1" TEXT DEFAULT
  50. customValue1 TEXT "customValue1" TEXT DEFAULT
  51. customName2 TEXT "customName2" TEXT DEFAULT
  52. customValue2 TEXT "customValue2" TEXT DEFAULT
  53. customName3 TEXT "customName3" TEXT DEFAULT
  54. customValue3 TEXT "customValue3" TEXT DEFAULT
  55. customName4 TEXT "customName4" TEXT DEFAULT
  56. customValue4 TEXT "customValue4" TEXT DEFAULT
  57. customName5 TEXT "customName5" TEXT DEFAULT
  58. customValue5 TEXT "customValue5" TEXT DEFAULT
  59. customName6 TEXT "customName6" TEXT DEFAULT
  60. customValue6 TEXT "customValue6" TEXT DEFAULT
  61. customName7 TEXT "customName7" TEXT DEFAULT
  62. customValue7 TEXT "customValue7" TEXT DEFAULT
  63. customName8 TEXT "customName8" TEXT DEFAULT
  64. customValue8 TEXT "customValue8" TEXT DEFAULT
  65. customName9 TEXT "customName9" TEXT DEFAULT
  66. customValue9 TEXT "customValue9" TEXT DEFAULT
  67. emailCustomLabel1 TEXT "emailCustomLabel1" TEXT DEFAULT
  68. emailLabel1 INTEGER "emailLabel1" INTEGER DEFAULT 0
  69. emailValue1 TEXT "emailValue1" TEXT DEFAULT
  70. emailCustomLabel2 TEXT "emailCustomLabel2" TEXT DEFAULT
  71. emailLabel2 INTEGER "emailLabel2" INTEGER DEFAULT 0
  72. emailValue2 TEXT "emailValue2" TEXT DEFAULT
  73. emailCustomLabel3 TEXT "emailCustomLabel3" TEXT DEFAULT
  74. emailLabel3 INTEGER "emailLabel3" INTEGER DEFAULT 0
  75. emailValue3 TEXT "emailValue3" TEXT DEFAULT
  76. imCustomLabel1 TEXT "imCustomLabel1" TEXT DEFAULT
  77. imLabel1 INTEGER "imLabel1" INTEGER DEFAULT 0
  78. imServiceProtocol1 INTEGER "imServiceProtocol1" INTEGER DEFAULT 0
  79. imValue1 TEXT "imValue1" TEXT DEFAULT
  80. imCustomLabel2 TEXT "imCustomLabel2" TEXT DEFAULT
  81. imLabel2 INTEGER "imLabel2" INTEGER DEFAULT 0
  82. imServiceProtocol2 INTEGER "imServiceProtocol2" INTEGER DEFAULT 0
  83. imValue2 TEXT "imValue2" TEXT DEFAULT
  84. imCustomLabel3 TEXT "imCustomLabel3" TEXT DEFAULT
  85. imLabel3 INTEGER "imLabel3" INTEGER DEFAULT 0
  86. imServiceProtocol3 INTEGER "imServiceProtocol3" INTEGER DEFAULT 0
  87. imValue3 TEXT "imValue3" TEXT DEFAULT
  88. phoneCustomLabel1 TEXT "phoneCustomLabel1" TEXT DEFAULT
  89. phoneLabel1 INTEGER "phoneLabel1" INTEGER DEFAULT 0
  90. phoneValue1 TEXT "phoneValue1" TEXT DEFAULT
  91. phoneCustomLabel2 TEXT "phoneCustomLabel2" TEXT DEFAULT
  92. phoneLabel2 INTEGER "phoneLabel2" INTEGER DEFAULT 0
  93. phoneValue2 TEXT "phoneValue2" TEXT DEFAULT
  94. phoneCustomLabel3 TEXT "phoneCustomLabel3" TEXT DEFAULT
  95. phoneLabel3 INTEGER "phoneLabel3" INTEGER DEFAULT 0
  96. phoneValue3 TEXT "phoneValue3" TEXT DEFAULT
  97. phoneCustomLabel4 TEXT "phoneCustomLabel4" TEXT DEFAULT
  98. phoneLabel4 INTEGER "phoneLabel4" INTEGER DEFAULT 0
  99. phoneValue4 TEXT "phoneValue4" TEXT DEFAULT
  100. phoneCustomLabel5 TEXT "phoneCustomLabel5" TEXT DEFAULT
  101. phoneLabel5 INTEGER "phoneLabel5" INTEGER DEFAULT 0
  102. phoneValue5 TEXT "phoneValue5" TEXT DEFAULT
  103. phoneCustomLabel6 TEXT "phoneCustomLabel6" TEXT DEFAULT
  104. phoneLabel6 INTEGER "phoneLabel6" INTEGER DEFAULT 0
  105. phoneValue6 TEXT "phoneValue6" TEXT DEFAULT
  106. phoneCustomLabel7 TEXT "phoneCustomLabel7" TEXT DEFAULT
  107. phoneLabel7 INTEGER "phoneLabel7" INTEGER DEFAULT 0
  108. phoneValue7 TEXT "phoneValue7" TEXT DEFAULT
  109. phoneCustomLabel8 TEXT "phoneCustomLabel8" TEXT DEFAULT
  110. phoneLabel8 INTEGER "phoneLabel8" INTEGER DEFAULT 0
  111. phoneValue8 TEXT "phoneValue8" TEXT DEFAULT
  112. phoneCustomLabel9 TEXT "phoneCustomLabel9" TEXT DEFAULT
  113. phoneLabel9 INTEGER "phoneLabel9" INTEGER DEFAULT 0
  114. phoneValue9 TEXT "phoneValue9" TEXT DEFAULT
  115. phoneCustomLabel10 TEXT "phoneCustomLabel10" TEXT DEFAULT
  116. phoneLabel10 INTEGER "phoneLabel10" INTEGER DEFAULT 0
  117. phoneValue10 TEXT "phoneValue10" TEXT DEFAULT
  118. urlLabel1 TEXT "urlLabel1" TEXT DEFAULT
  119. urlURL1 TEXT "urlURL1" TEXT DEFAULT
  120. urlLabel2 TEXT "urlLabel2" TEXT DEFAULT
  121. urlURL2 TEXT "urlURL2" TEXT DEFAULT
  122. urlLabel3 TEXT "urlLabel3" TEXT DEFAULT
  123. urlURL3 TEXT "urlURL3" TEXT DEFAULT
  124. pictureLocSquare BLOB "pictureLocSquare" BLOB
  125. recordType INTEGER "recordType" INTEGER DEFAULT 1
  126. pictureFile TEXT "pictureFile" TEXT
  127. phoneQuickdial1 TEXT "phoneQuickdial1" TEXT
  128. phoneQuickdial2 TEXT "phoneQuickdial2" TEXT
  129. phoneQuickdial3 TEXT "phoneQuickdial3" TEXT
  130. phoneQuickdial4 TEXT "phoneQuickdial4" TEXT
  131. phoneQuickdial5 TEXT "phoneQuickdial5" TEXT
  132. phoneQuickdial6 TEXT "phoneQuickdial6" TEXT
  133. phoneQuickdial7 TEXT "phoneQuickdial7" TEXT
  134. phoneQuickdial8 TEXT "phoneQuickdial8" TEXT
  135. phoneQuickdial9 TEXT "phoneQuickdial9" TEXT
  136. phoneQuickdial10 TEXT "phoneQuickdial10" TEXT
  137. pictureFileSquare TEXT "pictureFileSquare" TEXT
  138. version INTEGER "version" INTEGER DEFAULT 0
  139. urlLabelValue1 TEXT "urlLabelValue1" TEXT DEFAULT
  140. urlLabelValue2 TEXT "urlLabelValue2" TEXT DEFAULT
  141. urlLabelValue3 TEXT "urlLabelValue3" TEXT DEFAULT
  142. fullName TEXT "fullName" TEXT DEFAULT
  143. multiCategory TEXT "multiCategory" TEXT DEFAULT
  144. private INTEGER "private" INTEGER DEFAULT 0
  145. wirelessID TEXT "wirelessID" TEXT DEFAULT
  146. ringtoneID INTEGER "ringtoneID" INTEGER DEFAULT 0
  147. modifiedForAndroid INTEGER "modifiedForAndroid" INTEGER DEFAULT 0
  148. androidAccountName TEXT "androidAccountName" TEXT DEFAULT
  149. androidAccountType TEXT "androidAccountType" TEXT DEFAULT
  150. multiContactIds TEXT "multiContactIds" TEXT DEFAULT
  151. multiContactNames TEXT "multiContactNames" TEXT DEFAULT
  152. hasNote INTEGER "hasNote" INTEGER DEFAULT 0
  153. hasHistory INTEGER "hasHistory" INTEGER DEFAULT 0
  154. department TEXT "department" TEXT DEFAULT
  155. addrLong1 REAL "addrLong1" REAL DEFAULT 0
  156. addrLat1 REAL "addrLat1" REAL DEFAULT 0
  157. addrLong2 REAL "addrLong2" REAL DEFAULT 0
  158. addrLat2 REAL "addrLat2" REAL DEFAULT 0
  159. addrLong3 REAL "addrLong3" REAL DEFAULT 0
  160. addrLat3 REAL "addrLat3" REAL DEFAULT 0
  161. phoneDisplay TEXT "phoneDisplay" TEXT DEFAULT
  162. addrMapType1 INTEGER "addrMapType1" INTEGER DEFAULT 0
  163. addrMapType2 INTEGER "addrMapType2" INTEGER DEFAULT 0
  164. addrMapType3 INTEGER "addrMapType3" INTEGER DEFAULT 0
  165. addrMapFile1 TEXT "addrMapFile1" TEXT DEFAULT
  166. addrMapFile2 TEXT "addrMapFile2" TEXT DEFAULT
  167. addrMapFile3 TEXT "addrMapFile3" TEXT DEFAULT
  168. addrMapFileOther1 TEXT "addrMapFileOther1" TEXT DEFAULT
  169. addrMapFileOther2 TEXT "addrMapFileOther2" TEXT DEFAULT
  170. addrMapFileOther3 TEXT "addrMapFileOther3" TEXT DEFAULT
  171. hasAttachment INTEGER "hasAttachment" INTEGER DEFAULT 0
  172. nextActionTextA TEXT "nextActionTextA" TEXT DEFAULT
  173. nextActionTextD TEXT "nextActionTextD" TEXT DEFAULT
  174. nextActionTextT TEXT "nextActionTextT" TEXT DEFAULT
  175. nextActionIDA INTEGER "nextActionIDA" INTEGER DEFAULT 0
  176. nextActionIDD INTEGER "nextActionIDD" INTEGER DEFAULT 0
  177. nextActionIDT INTEGER "nextActionIDT" INTEGER DEFAULT 0
  178. nextActionTimeA INTEGER "nextActionTimeA" INTEGER DEFAULT 0
  179. nextActionTimeD INTEGER "nextActionTimeD" INTEGER DEFAULT 0
  180. nextActionTimeT INTEGER "nextActionTimeT" INTEGER DEFAULT 0
  181. searchName TEXT "searchName" TEXT DEFAULT
  182. searchCompany TEXT "searchCompany" TEXT DEFAULT
  183. multiContactEmails TEXT "multiContactEmails" TEXT DEFAULT
  184. createDate INTEGER "createDate" INTEGER DEFAULT 0
  185. assistantName TEXT "assistantName" TEXT DEFAULT
  186. hoursJSON TEXT "hoursJSON" TEXT DEFAULT
  187. timezone TEXT "timezone" TEXT DEFAULT
  188. userID INTEGER "userID" INTEGER DEFAULT 0
  189. customName10 TEXT "customName10" TEXT DEFAULT
  190. customValue10 TEXT "customValue10" TEXT DEFAULT
  191. customName11 TEXT "customName11" TEXT DEFAULT
  192. customValue11 TEXT "customValue11" TEXT DEFAULT
  193. customName12 TEXT "customName12" TEXT DEFAULT
  194. customValue12 TEXT "customValue12" TEXT DEFAULT
  195. customName13 TEXT "customName13" TEXT DEFAULT
  196. customValue13 TEXT "customValue13" TEXT DEFAULT
  197. customName14 TEXT "customName14" TEXT DEFAULT
  198. customValue14 TEXT "customValue14" TEXT DEFAULT
  199. customName15 TEXT "customName15" TEXT DEFAULT
  200. customValue15 TEXT "customValue15" TEXT DEFAULT
  201. customName16 TEXT "customName16" TEXT DEFAULT
  202. customValue16 TEXT "customValue16" TEXT DEFAULT
  203. customName17 TEXT "customName17" TEXT DEFAULT
  204. customValue17 TEXT "customValue17" TEXT DEFAULT
  205. customName18 TEXT "customName18" TEXT DEFAULT
  206. customValue18 TEXT "customValue18" TEXT DEFAULT
  207. customName19 TEXT "customName19" TEXT DEFAULT
  208. customValue19 TEXT "customValue19" TEXT DEFAULT
  209. customName20 TEXT "customName20" TEXT DEFAULT
  210. customValue20 TEXT "customValue20" TEXT DEFAULT
  211. multiUserID TEXT "multiUserID" TEXT DEFAULT

Tasks

  1. autoid INTEGER "autoid" INTEGER NOT NULL
  2. id TEXT "id" TEXT
  3. alarmDateTime INTEGER "alarmDateTime" INTEGER DEFAULT 0
  4. completed INTEGER "completed" INTEGER DEFAULT 0
  5. externalId TEXT "externalId" TEXT
  6. location TEXT "location" TEXT DEFAULT
  7. note TEXT "note" TEXT DEFAULT
  8. originalStartTimestamp INTEGER "originalStartTimestamp" INTEGER DEFAULT 0
  9. priority INTEGER "priority" INTEGER DEFAULT 2
  10. subject TEXT "subject" TEXT DEFAULT
  11. startTimestamp INTEGER "startTimestamp" INTEGER DEFAULT 0
  12. clxcategory TEXT "clxcategory" TEXT DEFAULT
  13. modifiedPC INTEGER "modifiedPC" INTEGER DEFAULT 0
  14. modifiedHH INTEGER "modifiedHH" INTEGER DEFAULT 0
  15. clxdeleted INTEGER "clxdeleted" INTEGER DEFAULT 0
  16. recordType INTEGER "recordType" INTEGER DEFAULT 3
  17. multiCategory TEXT "multiCategory" TEXT DEFAULT
  18. status TEXT "status" TEXT DEFAULT
  19. percentComplete INTEGER "percentComplete" INTEGER DEFAULT 0
  20. priorityString TEXT "priorityString" TEXT DEFAULT
  21. startDate2 INTEGER "startDate2" INTEGER DEFAULT 0
  22. private INTEGER "private" INTEGER DEFAULT 0
  23. multiContactIds TEXT "multiContactIds" TEXT DEFAULT
  24. multiContactNames TEXT "multiContactNames" TEXT DEFAULT
  25. wirelessID TEXT "wirelessID" TEXT DEFAULT
  26. project TEXT "project" TEXT DEFAULT
  27. rrule TEXT "rrule" TEXT
  28. recurrenceStartDate INTEGER "recurrenceStartDate" INTEGER DEFAULT 0
  29. parentId TEXT "parentId" TEXT
  30. lastSnooze INTEGER "lastSnooze" INTEGER DEFAULT 0
  31. originalAlarm INTEGER "originalAlarm" INTEGER DEFAULT 0
  32. firstInstanceDate INTEGER "firstInstanceDate" INTEGER DEFAULT 0
  33. lastInstanceDate INTEGER "lastInstanceDate" INTEGER DEFAULT 0
  34. locLong REAL "locLong" REAL DEFAULT 0
  35. locLat REAL "locLat" REAL DEFAULT 0
  36. alarmTone TEXT "alarmTone" TEXT DEFAULT
  37. locName TEXT "locName" TEXT DEFAULT
  38. locCompany TEXT "locCompany" TEXT DEFAULT
  39. locStreet TEXT "locStreet" TEXT DEFAULT
  40. locCity TEXT "locCity" TEXT DEFAULT
  41. locState TEXT "locState" TEXT DEFAULT
  42. locZip TEXT "locZip" TEXT DEFAULT
  43. locCountry TEXT "locCountry" TEXT DEFAULT
  44. locMapType INTEGER "locMapType" INTEGER DEFAULT 0
  45. locMapFile TEXT "locMapFile" TEXT DEFAULT
  46. locMapFileOther TEXT "locMapFileOther" TEXT DEFAULT
  47. hasAttachment INTEGER "hasAttachment" INTEGER DEFAULT 0
  48. completedDate INTEGER "completedDate" INTEGER DEFAULT 0
  49. specialType INTEGER "specialType" INTEGER DEFAULT 0
  50. createDate INTEGER "createDate" INTEGER DEFAULT 0
  51. activityType TEXT "activityType" TEXT DEFAULT
  52. activityTypeID INTEGER "activityTypeID" INTEGER DEFAULT 0
  53. userID INTEGER "userID" INTEGER DEFAULT 0
  54. notify INTEGER "notify" INTEGER DEFAULT 0
  55. multiUserID TEXT "multiUserID" TEXT DEFAULT '
Retrieved from "https://www.companionlink.com/support/wiki/index.php?title=How_to_create_a_Query_Lookup_Filter_in_DejaOffice_PC_CRM&oldid=18157"