Thoughts on technology and innovation
A system administrator can now create a custom (user-defined) tab that contains both standard iMIS fields and custom user-defined fields for the Details pane of the Manage Customers window. This tab can, for example, contain a mix of: • fields from the Name table or the Name_Fin table; • fields from any user-defined tables that apply to all customer types; • SQL formula fields based on these tables (example: calculate the member’s Age on this tab instead of showing the BIRTH_DATE field). Notes on This Feature: 1. Only one custom tab can be created. 2. To protect data integrity, some fields are always READ ONLY. These include: • Phone or fax numbers and email addresses that are used in synchronization rules; • Fields subject to User Defined Company Flow Down; • SQL formula-based fields; • The Name_Fin.BILL_TO field; • The Name.COUNTY field if auto-population of the county name is enabled; • System-generated fields (such as Name.FULL_NAME or Name.LAST_UPDATED). To create a SQL Field on your Custom Tab: 1. Open the Custom Tab Designer Window (Customer | Set up module | Additional Windows | Custom Tab).2. Click the Create SQL Field button.3. Enter a Prompt for the field. 4. Enter a valid SQL formula in the SQL String field. 5. Click the Add SQL button.How iMIS Processes the SQL Formula: In this sample scenario, your formula string is:
If you have defined user-defined tables UDTable1, UDTable2, then the select statement that will be generated to populate the tab for ID #100225 is:
Select DATEDIFF(year,BIRTH_DATE,GETDATE()) from Name,Name_Fin,Name_Demo,UDTable1, UDTable2 where Name_Demo.ID=*Name.ID and UDTable1.ID=*Name.ID and UDTable2.ID=*Name.ID and Name_Fin.ID=*Name.ID and Name.ID='100225'
NOTE: If the string is not properly structured so that it can be executed in this select statement, you will receive an error "Invalid SQL formula!" Excerpted from ASI's iMIS 10.5.28.02 Release Notes.