full integration of custom fields and custom tables n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Full Integration of Custom Fields and Custom Tables PowerPoint Presentation
Download Presentation
Full Integration of Custom Fields and Custom Tables

Loading in 2 Seconds...

play fullscreen
1 / 40

Full Integration of Custom Fields and Custom Tables - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Full Integration of Custom Fields and Custom Tables' - paki-garrett


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
full integration of custom fields and custom tables

Full Integration of Custom Fields and Custom Tables

2013 User’s Conference

Adam Ploshay

Rebecca Swords

introduction
Introduction

Hi!

How’s it going?

Did you see that ludicrous display last night?

agenda
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
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
slide5

General Configurations

-> Custom Fields Configuration

-> Custom Tables Configuration

slide6

General Configurations

-> Custom Fields Configuration

-> Custom Fields Configuration

custom tables templates
Custom Tables: Templates
  • Use to populate custom table
  • May require re-launch before configuring
custom tables custom reports and search
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
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 extensions1
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
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
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
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
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*
slide15
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
Tree Menu

Use | between tree levels:

<menu type=“tree”>

%campus% | %alertGroup%

</menu>

description help text
Description: Help Text

<description><![CDATA[

Customize help text here!<br /><br />

Use HTML tags to add formatting.]]>

</description>

slide18
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
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
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
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 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
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
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
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
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
data objects xml sample table mod w sql changes
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
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
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
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
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
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
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
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
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
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
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
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.
slide40
Q & A
  • Questions (from you folks)
  • Answers (from us)