Where Did My TRANS Go? (R18 Computed Columns Tips and Tricks)

1. Where Did My TRANS Go?(R18 Computed Columns Tips and Tricks) Presented by: Kathy Schroer (kis@datatel.com) Senior Consultant, Business Automation

2. R18 Computed Column Update R18 Computed Column forms and process Accessing data in another file Calling a Subroutine Referencing File Suite data Other Notable Functions Where can I go for help? Questions Agenda

3. R18 Computed Column Update • 31424.37 – Released 6/6/2007 • Delivered a new computed column bundle record (“COLLEAGUE”) in preparation for delivery of C# computed column support to be delivered on 23856.67 • 29110.15 – Released 6/11/2007 • Improved DMI Memory Management Release • New version of C# computed column generator released; not functional until delivery of 23856.67 to call the new generator

4. R18 Computed Column Update • 23856.67 – RELEASED 9/6/2007 • Full support of computed column generation on all databases. • Enhancements to Envision to support automatic generation and deployment of computed columns in SQL Server. • The C# generator will replace the need for manually written T-SQL scripts in SQL Server.

5. R18 Computed Column Update • 23856.67 (cont’d) • Computed Column Functions now working/available: • - SWAP - REMOVE • - DCOUNT - SETDATE • - INSERT - SOUNDEX • - ISALPHA - IF/ELSE and Concatenation • - MATCH - Nested IF/ELSE statements • MAX - TOARRAY issues • - MIN - INSTANCEOF

6. R18 Computed Column Update • 27201.85 – Future • FOR_<text> INSTANCE_OF – additional keyword to be added to Envision Basic for IS-type subroutines that call file suites • AnswerNet doc includes a list of workarounds for Unidata sites • 29362.82 – Future • S.GET.INDEX replacement logic for IS-type subroutines • AnswerNet doc includes a list of workarounds and logic for replacement

7. R18 Computed Column forms and process • All computed columns are written in the Envision Tool Kit by Application • Demand Columns • Demand Processes • Comp(uted) Column Code • Logical File Name • Bundle = USER • Conversion String • Single/Multivalue • Skip Database Gen • External References • Generate?

8. Use of Computed Columns • Run-Time (Rules and Communication Management) • Must be defined in the CDD through Envision • Does NOT need to be defined in the database • Envision Selects • Must be defined in the CDD through Envision • Must be defined in the database • Ad-hoc Queries • Does NOT need to be defined in the CDD through Envision • Must be defined in the database

9. Computed Column Checklist • Every statement ends in “;” • All CDD elements referenced in the computed column code are listed in the Demand Columns window • All subroutines referenced in the computed column code are listed in the Demand Processes window • The value returned by the computed column must match the Conversion format specified on DCC (e.g. Integer = MD0)

10. Computed Column Checklist (cont’d) • The value returned by the computed column must match the Single/Multivalue designation specified on DCC • Names of CDD elements, files, and subroutines must adhere to naming conventions (no dots, proper case, v & vl) • The computed column must reference a file in the correct application (cc in the PERSON file must be created in CORE)

11. Computed Column Checklist (cont’d) • Computed Columns and Subroutines referenced by the computed column must generate without issues

12. Accessing data in another file • Data from co-files (files with the same key) can be referenced directly • Data from files with Envision-defined pointers can be referenced directly • Data from other files can be referenced by creating a key relationship

13. Accessing data in another file Return the Colleague ID, the Last Name from PERSON and Student Load from STUDENT.TERMS

14. Accessing data in another file Demand Columns: STUDENTS.ID LAST.NAME STU.TERMS STU.ACAD.LEVELS STTR.STUDENT.LOAD Computed Column: key xKeyStudentTerms for file StudentTerms; xKeyStudentTerms = vStudentsId:"*": vlStuTerms[vlStuTerms.Size()]:"*": vlStuAcadLevels[1]; return vStudentsId:" ":vLastName:" ":vSttrStudentLoad;

15. Accessing data in another file

16. Calling a Subroutine • Subroutines called by computed columns at the database level (Envision Selects and Ad-hoc Queries) MUST be IS-Type subroutines • Subroutines called by computed columns in Run-Time are NOT required to be IS-Type subroutines • Subroutines called by computed columns may only have one output argument • The output argument must be the first argument

17. Calling a Subroutine (cont’d) • Referenced subroutines must be listed in the Demand Processes on DCC • Subroutines called within a computed column always return a single valued string-type result

18. IS-Type Subroutines • Any subroutines referenced by an IS-type subroutine must also be IS-type subroutines • Cannot reference variables stored in COMMON (e.g. CRNT.FA.YEAR) • Debug statements must be enclosed in “$IF APPSERVER THEN” logic • Must use Envision Basic syntax (no UniBasic commands – OPEN, READ, etc.)

19. IS-Type Subroutines (cont’d) • Cannot process an active select list (e.g. FOR_EACH SELECTED) • Cannot update the database

20. Calling a Subroutine Return the age return SCalcAge(vHrperId);

21. Referencing File Suite Data • From a file suite file of the same suite (year) – same rules as referencing data between any files (co-file/pointer/key variables) • From a file suite file of a different suite (year) or from a non-file suite file – use InstanceOf function • Using a subroutine – subroutines that reference file suites can only be used in Run-Time (not at the database level)

22. Referencing File Suite Data Return total awarded amount from 2007 for this APPLICANT

23. Referencing File Suite Data string xYear = "2007"; real xAwarded = vSaAwarded.InstanceOf(xYear); return xAwarded;

24. Other Notable Functions • Sum() • Size() • Concatenation • Array Extraction • ToString(“pattern”) • ToDelimitedString(“delimiter”) • ToArray(“delimiter”)

25. Sum() Add all numeric values in an array to create a single value result: real xResult = 0; real[] xlArray = {1, 2, 4}; xResult = xlArray.Sum(); return xResult; return value is 7

26. Size() Returns the number of values in an array: int xResult = 0; real[] xlArray = {1, 2, 4}; xResult = xlArray.Size(); return xResult; return value is 3

27. Concatenation • Concatenation operates on string data only. • Single valued concatenation: string xResult; xResult = vLastName:”, “:vFirstName; return xResult; return value is “Smith, Joe” • Array concatenation – at least one of the values must be an array: string[] xlResult; xlResult = ArrayCat(vlStuTerms,” – “); return xlResult; return value is {2007/SP - , 2007/SU - , 2007/FA - }

28. Array Extraction To extract one value from an array: string xResult; xResult = vlAppApplications[vlAppApplications.Size()]; return xResult; APP.APPLICATIONS = {3094, 8076, 17943} return value is 17943

29. ToString(“pattern”) Converts a number to a string with a specified format: string xResult1; xResult1 = vBirthDate.ToString(“DYMD”); return vLastName:” – “:xResult1; return value is “Smith – 1984 04 24”

30. ToDelimitedString(“delimiter”) Converts an array to a string with the delimiter argument between each array element string xResult; xResult = vlMailingCcNotRecvd.ToDelimitedString(“-”); return xResult; MAILING.CC.NOT.RECVD = {APP, TR1, TR2} return value is: APP-TR1-TR2

31. ToArray(“delimiter”) Converts a string to an array with the delimiter as the start of each array element: string[] xlSsnParts; xlSsnParts = vSsn.ToArray(“-”); return xlSsnParts; SSN = 123-45-6789 return value is: {123, 45, 6789}

32. Where can I go for help? • Datatel’s R18 Resource Center: http://www.datatel.com/support/r18.cfm • R18 Programmer Buddy – weekly one-on-one consultations with Datatel custom programmers • Envision Computed Column Commands Reference – available on the Datatel web site

33. Where can I go for help? (cont’d) • Creating Computed Columns (R18) – offered Online, Regionally and At Datatel • Onsite Courses and Consulting or Phone Consulting – contact Client Services Initiatives in either of these ways: • call 1.800.DATATEL and select option #4 • send an e-mail to services@datatel.com

34. Questions? Kathy Schroer Senior Consultant Business Automation kis@datatel.com