
Full Integration of Custom Fields and Custom Tables 2013 User’s Conference Adam Ploshay Rebecca Swords
Introduction Hi! How’s it going? Did you see that ludicrous display last night?
Agenda • Introduction • Agenda (this slide) • Using Custom Table Builder • Configuration • Templates • Custom Reports, Search • E-Form Extensions • Alerts • Modifying XML Data Objects • Data object overview • XML-only modifications • Data object changes that also require database access • Creating entirely new tables or views • Q & A
Custom Tables: Configuration • Add info to student record (tied to idnumber) • Up to 20 tables, 50 fields/table • Specify: • Label • Description • Data type • Referenced code table • May require clearing code cacheor relaunch
General Configurations -> Custom Fields Configuration -> Custom Tables Configuration
General Configurations -> Custom Fields Configuration -> Custom Fields Configuration
Custom Tables: Templates • Use to populate custom table • May require re-launch before configuring
Custom Tables:Custom Reports and Search • Requires closing, relaunchingsunapsis • After that, they work just like any other table This sounds so easy, it makes me almost as happy as this turtle. Almost…
Custom Tables: E-Form Extensions • Case study: using custom fields to control e-form access • Override isSupported • Return true or false: is the person who is logged in allowed to access services with this extension? • Query jbCustomFieldsXX
Custom Tables: E-Form Extensions • Can be used in any part of the extension: • appendFormData – append custom data to an e-form • modifyService – conditionally show or hide fields based on values of custom data • verifyFormAction – ensure that form data is compatible with custom data (or have an error message if not)
Custom Tables:Alerts • Any idnumber-bearing table can be queried for custom alerts • Some resources for writing custom alerts: • AbstractCustomFieldMatchAlertService.cfc • For logic based on a particular field value match • See: ExampleCustomFieldMatchAlertService.cfc • AbstractCustomFieldRangeAlertService.cfc • For logic based on particular field value range • See: ExampleCustomFieldRangeAlertService.cfc • AbstractCustomTableMatchAlertService.cfc • For logic based on particular table/view presence/absence • See: ExampleCustomTableMatchAlertService.cfc • BlankAlertService.cfc • For anything!
Data Objects • Used to describe table data and how it’s displayed • Can configure (among other things): • Help text • Field labels and order • Field visibility • Field constraints • Record selection structure (“menu”) • Official Sunapsis files: ioffice/xml/dataobjects_XXX.xml Do NOT edit the Sunapsis files. • Create your own (Edit these): ioffice/xml/dataobjects_XXX_institution.xml • Your institutional XML takes precedence
IMPORTANT Notes! A friendly reminder from your neighborhood license agreement: “Licensee may not modify the System other than to extend the System through the pluggable component infrastructure for table views, alerts, reports, e-mail services, and e-forms.” • We strongly encourage you to make backups before applying upgrades • You should also review your custom XML and database changes after any update • We don’t support your database changes • We don’t guarantee that the database structure won’t change • We do reserve the right to laugh at you if you break something :D
Parts of a Data Object <dataObject> Attributes: • name* – database table name • label* – desired window title • module – use “sunapsis” Elements: • menu • description • primary key* • foreign key* • datums* • constraints*
Menu <menu> • How record list appears, which data is included • Attribute: • type - “tree” or “list” • Use % around field names to put in record value <menu type=“list”> %label% (%tableName%) </menu>
Tree Menu Use | between tree levels: <menu type=“tree”> %campus% | %alertGroup% </menu>
Description: Help Text <description><![CDATA[ Customize help text here!<br /><br /> Use HTML tags to add formatting.]]> </description>
Keys Configure primary and foreign keys, as they are in the database. <primaryKey>recnum</primaryKey> Use self-reference if there is no foreign key: <foreignKeydataObject="configCustomTables" key="recnum"/>
Datum • Order of datums matters • Attributes: • key* – column name in database • label* – label displayed in Sunapsis • type* – data type • length – maximum number of characters (no more than DB limit) • data – required, recommended, optional • display – show the field, or not • sort – “asc” or “desc”, records are sorted by this field • encrypt – if field should be encrypted (special requirements) • Element: reference – for fields to reference a code table
Datum with Reference • Display a descriptive value rather than a code (ex: “Mexico” vs. “MX”) • Reference attributes: • dataObject – table that is being referenced (no self-reference) • valueKey – code column • displayKey – description column <datum key="country" label="Country" type="string" length="5"><referencedataObject="viewCodeCompleteCountryList"valueKey="code" displayKey="description"/></datum>
Constraints • Add constraints or data to fields • Run on submit • Attributes: type, key • Elements: compare, constant • Examples (see dataobjects_xml_overview for more): • RegularExpressionConstraint • CompareDatesConstraint • ConditionalRequiredConstraint • AssignUserFullNameConstraint
Constraint Examples <constraint type="AssignUsernameConstraint" key="username"/> <constraint type="RegularExpressionConstraint" key="sevisid"><constant>N\d{10}</constant></constraint> <constrainttype="ConditionalRequiredConstraint" key="alertGroup"><compare>type</compare><constant>A</constant></constraint> <constraint type="CompareDatesConstraint" key="prgStartDate"><compare>prgEndDate</compare></constraint>
Complete Data Object <dataObject name="jbPassport" label="Passport" module="sunapsis"><menu>%cpass%: %passiss% - %passexp%</menu><description><![CDATA[ Passport info, see also <a href="40">this KB article</a>. ]]></description><primaryKey>recnum</primaryKey><foreignKeydataObject="jbInternational" key="idnumber"/><datums><datum key="lastname" label="Last Name" type="string" length="50" data="recommended"/><datum key="firstname" label="First Name" type="string" length="50" data="recommended"/><datum key="midname" label="Middle Name" type="string" length="50"/><datum key="cpass" label="Passport Country" type="string" length="5"><referencedataObject="codeCountry"valueKey="code"displayKey="description"/></datum><datum key="passnum" label="Passport Number" type="string" length="255" data="recommended"encrypt="true"/><datum key="passiss" label="Issue Date" type="date" sort="desc"/><datum key="passexp" label="Expiration Date" type="date" data="recommended"/><datum key="datestamp" label="Last Updated" type="datestamp"/><datum key="recnum" label=" " type="integer"/><datum key="idnumber" label=" " type="integer"/></datums><constraints/></dataObject>
Changing the General Configurations Menu • Official Sunapsis (don’t edit): display.xml • Institution specific (edit this): display_institution.xml • <generalCodesDisplay> - menu configuration • <set label=“Menu Title”> • <dataObject> attributes • reference – table name (data object name) • view – multi or single (multiple records allowed) • width, height, preferredHeight – configure dimensions
Changing the Individual Display • Modifications to Record Management • Can only add, not remove • Example use case: add a custom view of student data that should be available from their record
Data Objects XML:Table/View Mods w/SQL changes • So, you’re thinking of adding new fields to an existing table/view? • Considerations: • Might break current code (!) • Might break future updates (!!) • Will require reviewing, merging XML from future updates (!!!) • Generally, DON’T DO IT. I DON’T KNOW WHY WE’RE EVEN TELLING YOU ABOUT IT. Might seem desirable if you need new data in a 1-1 relationship with an existing table/view. But road to hell is(1) paved with good intentions, and (2) has an on-ramp any time you say “I know a shortcut!” • Changes Needed: • SQL: New fields needs to allow NULLs, or set default values • This is so that you avoid breaking existing INSERT code • XML: Copy into your _institution XML, add new fields • This is necessary for your field to show up in the user interface • Even if they have display=“false”, they should still be recorded here for the sake of documentation
TOTALLY PLAUSIBLEEXAMPLE:adding Pizza Delivery Zone to jbAddress
Data Objects XML:SAMPLE Table Mod w/SQL changes • EXAMPLE: Add a “pizza delivery zone” field on jbAddress: • SQL: • XML: • Please • Don’t • Do • This ALTER TABLE dbo.jbAddress ADD pizzaDeliveryZone INT NOT NULL CONSTRAINT DF_jbAddress_pizzaDeliveryZoneDEFAULT (0) <dataObject name="jbAddress" label="Addresses" module="sunapsis"> …<datums> …<datum key="pizzaDeliveryZone“label="Pizza Delivery Zone"type="integer"/> …</datums><constraints/></dataObject>
Data Objects XML:Adding new tables/views: Overview • Considerations: • If it is an idnumber-bearing table, use the Custom Tables Configuration, instead. • Better yet, have someone else use it! • This is useful for views, and non-idnumber tables • Changes Needed: • Create the table/view, in the database • Update user permissions, in the database • Create the XML representation of the table/view • Relaunchsunapsis
Data Objects XML:Adding new tables/views: SQL • 3 SQL Steps: • 1: Create your table/view • Format: <prefix><institution name><description>Example: codeIUBPizzaTopping • See spreadsheet of standard table name prefixes • 2: Update user permissions • Additional configuration needed for a non-standard table prefix. Don’t use those. • 3: Generate skeletal XML description • Copy resulting message text (2nd tab) EXEC dbo.spIOfficeRoleUpdate --updates user permissions EXEC dbo.util_GetColumns 'someTableNameGoesHere'
Data Objects XML:Adding new tables/views: XML • 2 XML Steps: • 4: Paste skeletal XML into <prefix>_institution.xmlfile, modify • Label, (Module), Menu, Description • primaryKey, foreignKey • Datums: Required Fields • Key: pre-set • Label: will need to be changed • Type: pre-set, may need to be changed (for labels, and for nvarchar or datetime fields) • Datums: Optional Fields That May Be Already Set • Length: no restriction by default, pre-set for nvarchar/varcharfields • Datums: Other Optional Fields • Sort: none/asc/desc (note importance of datum order) • Data: optional/required/recommended • Display: true/false • (Module) • Encrypt: false/true • Note that encrypted fields need a database type of nvarchar(255), regardless of XML type • Datums: References • If your table has lots of references, consider adding a '1' argument to the util_GetColumns call for longForm • Datums: Constraints • (see list) • Optionally add XML-only datums: header, paragraph • 5: Add into display_institution.xml (if applicable)
Data Objects XML:SAMPLE Table Addition: SQL 1/2 • SQL Steps: • 1: Create your table • 2: Update user permissions CREATE TABLE [dbo].[codeIUBPizzaTopping]( [recnum] [int] IDENTITY(1,1) NOT NULL, [code] [nvarchar](5) NOT NULL, [description] [nvarchar](20) NOT NULL, CONSTRAINT [PK_codeIUBPizzaTopping] PRIMARY KEY CLUSTERED ( [recnum] ASC )WITH (PAD_INDEX = OFF, … ) ON [PRIMARY] EXEC dbo.spIOfficeRoleUpdate --updates user permissions
Data Objects XML:SAMPLE Table Addition: SQL 2/2 • SQL Steps: • 3: Generate skeletal XML description • Copy message text: • a EXEC dbo.util_GetColumns 'codeIUBPizzaTopping' <dataObject name="codeIUBPizzaTopping" label="codeIUBPizzaTopping" module=""><menu></menu><description><![CDATA[codeIUBPizzaTopping]]></description><primaryKey>recnum</primaryKey><foreignKeydataObject="codeIUBPizzaTopping" key="recnum"/><datums><datum key="recnum" label=" " type="integer" display="false"/><datum key="code" label="code"type="string" length="5"/><datum key="description" label="description"type="string" length="20"/></datums><constraints/></dataObject>
Data Objects XML:SAMPLE Table Addition: XML 1/2 • XML Steps: • 4: Paste skeletal XML into code_institution.xmlfile, modify • a <dataObject name="codeIUBPizzaTopping" label="Pizza Toppings“ module="sunapsis"><menu>%description% (%code%)</menu><description><![CDATA[Lists Aver’s Pizza toppings.<br/>NOTE: All other pizza places are inferior and therefore irrelevant. ]]></description><primaryKey>recnum</primaryKey><foreignKeydataObject="codeIUBPizzaTopping" key="recnum"/><datums><datum key="recnum" label=" " type="integer" display="false"/><datum key="code" label="Code" type="string" length="5"/><datum key="description" label="Description" type="string" length="20"/></datums><constraints/></dataObject>
Data Objects XML:SAMPLE Table Addition: XML 2/2 • XML Steps: • 5: Add into display_institution.xml(optionally) • a • a • a <?xml version="1.0" encoding="iso-8859-1"?><displayxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ...><individualDisplay> ... </individualDisplay><communityDisplay> ... </communityDisplay><sevisBatchDisplay> ... </sevisBatchDisplay><domCodeDisplay> ... </domCodeDisplay><generalCodesDisplay><set label="IU Specific: Pizza Logistics"><dataObject reference="codeIUBPizzaTopping"view="multi"width="600" height="200"preferredHeight="200"/></set></generalCodesDisplay></display>
Data Objects XML:SAMPLE View Addition: SQL 1/2 • SQL Steps: • 1: Create your view • 2: Update user permissions CREATE VIEW [dbo].[viewStudentRecordQuotedUniversityID] AS SELECT jbInternational.idnumber ,'`' + jbInternational.universityid AS universityid FROM jbInternational EXEC dbo.spIOfficeRoleUpdate --updates user permissions
Data Objects XML:SAMPLE View Addition: SQL 2/2 • SQL Steps: • 3: Generate skeletal XML description • Copy message text: • a EXEC dbo.util_GetColumns 'viewStudentRecordQuotedUniversityID' <dataObject name="viewStudentRecordQuotedUniversityID“label="viewStudentRecordQuotedUniversityID"module=""><menu></menu><description><![CDATA[viewStudentRecordQuotedUniversityID]]></description><datums><datum key="idnumber" label=" " type="integer" display="false"/><datum key="universityid" label="universityid"type="string" length="11"/></datums><constraints/></dataObject>
Data Objects XML:SAMPLE View Addition: XML 1/2 • XML Steps: • 4: Paste skeletal XML into code_institution.xmlfile, modify • a <dataObject name="viewStudentRecordQuotedUniversityID“label="Quoted University ID" module="sunapsis"><menu></menu><description><![CDATA[Lists the University ID preceded by a backquote (`) to preventautomatic numeric conversion in Excel. ]]></description><datums><datum key="idnumber" label=" " type="integer" display="false"/><datum key="universityid" label="University ID" type="string" length="11"/></datums><constraints/></dataObject>
Data Objects XML:SAMPLE View Addition: XML 2/2 • XML Steps: • 5: Add into display_institution.xml(optionally) [this space intentionally left blank] • Nope! Not applicable in this case. Just need it for custom reports.
Q & A • Questions (from you folks) • Answers (from us)