Differences

This shows you the differences between two versions of the page.

--- office:customer_relations_management:user_preferences [2011/04/29 16:33]
markphillips
+++ office:customer_relations_management:user_preferences [2018/12/23 12:11] (current)
@@ Line 14: / Line 14: @@
 {{:office:customer_relations_management:preferences.png|}}
+===== Reporting on the Fields =====
+You may want to request help from CCS Staff, this section involves SQL Coding which most users are not familiar with. To use this feature, you would need to access [[office:reporting:pivot_reporting|Pivot Reporting]]\\
+If you wanted to pull a report that showed when a patient first signed up, you would use a statement like this:\\
+SELECT clFirstName,ClLastName,FieldRepMemo as SignUpDate
+FROM tblClients
+INNER JOIN tblProductOrders
+ON tblProductOrders.ClientID=tblClients.ClientID
+AND EventSubject='Primary Center Designation Date'
+WHERE iSDATE(FieldRepMemo)<>0 AND  Coalesce(ClLogon, '')<>''
+AND FollowUp>=GetDate()
+**The Parts of the statement are as follows:**\\
+**These are the fields to show. The first and last names of the patient. And the Date. FieldRepMemo is the field where the custom entries are always saved.**\\
+SELECT clFirstName,ClLastName,FieldRepMemo as SignUpDate\\
+**These are the the database table the information is stored in.**\\
+FROM tblClients\\
+INNER JOIN tblProductOrders\\
+ON tblProductOrders.ClientID=tblClients.ClientID\\
+**This is the criteria. In single quotes is the name of the custom field.**\\
+AND EventSubject='Primary Center Designation Date'
+**This is more criteria. In this case it says there must be a logon, the custom must be a date, and the followup date is greater than or equal to today.**
+WHERE iSDATE(FieldRepMemo)<>0 AND  Coalesce(ClLogon, '')<>''
+AND FollowUp>=GetDate()
+The report would yeild something like:
+|John | Smith | 01/20/2011|
+|Mary | Jane  | 04/20/2011|
+|Jim  | Chough| 09/10/2011|

Trace:

Differences

Navigation

Search

Toolbox