1. Full Integration of Custom Fields and Custom Tables 2013 User’s Conference Adam Ploshay Rebecca Swords

2. Introduction Hi! How’s it going? Did you see that ludicrous display last night?

3. 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

4. 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

5. General Configurations -> Custom Fields Configuration -> Custom Tables Configuration

6. General Configurations -> Custom Fields Configuration -> Custom Fields Configuration

7. Custom Tables: Templates • Use to populate custom table • May require re-launch before configuring

8. 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…

9. 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

10. 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)

11. 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!

12. 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

13. 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

14. 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*

15. 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>

16. Tree Menu Use | between tree levels: <menu type=“tree”> %campus% | %alertGroup% </menu>

17. Description: Help Text <description><![CDATA[ Customize help text here!<br /><br /> Use HTML tags to add formatting.]]> </description>

18. 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"/>

19. 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

20. 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>

21. 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

22. 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>

23. 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>

24. 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

25. 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

26. 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

27. TOTALLY PLAUSIBLEEXAMPLE:adding Pizza Delivery Zone to jbAddress

28. 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>

29. 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

30. 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'

31. 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)

32. 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

33. 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>

34. 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>

35. 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>

36. 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

37. 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>

38. 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>

39. 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.

40. Q & A • Questions (from you folks) • Answers (from us)