Cornerstone Update Transaction

1
Cornerstone Update Transaction
Templates for Business Inc.

• Building a Fully Metadata Driven and Localized
  Application

Prepared by Sergio Del Rio Date June 7th, 2006
2
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

3
Agenda Time Permitting

• Sort
• Distinct
• Aggregate Functions
• Cross Resource Joins

4
Introduction

• RETS Update Transaction
• Existing Implementations
• Process Flow Overview
• Login
• Get Metadata
• General Search
• Update
• New Listing
• Existing Listing

5
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

6
Process Flow - Login

• Standard RETS Login
• Not currently updated to RETS 1.5/1.7
• Cornerstone Role Restrictions
• Contact MRIS for additional privileges
• Interactive Update
• Must login as Agent
• Bulk Update
• Will require special Login
• Change tracking based on Login

7
Process Flow Get Metadata

• Recommend Daily Updates
• Basic Metadata
• Presentation Metadata
• Localization Metadata

8
Process Flow General Search

• Select Existing Listings in System
• Edit Existing Listing
• Copy Existing Listing
• Update Metadata - Copy on Cloning Flag
• External Validation
• Agent Lookup
• School Lookup
• Tax Lookup
• Streets / Zip Code Lookup

9
Process Flow Existing Listing

• Select with ForUpdate1
• Use ListingKey for Search
• Limit Number of Checked Out Listings
• Role Based

10
Process Flow New Listings

• Update Auto Populate Step
• Use validate-flag 1
• Mandatory Fields
• Identified by - Update Metadata AutopopRequired
• Currently
• County
• Property Type

11
Process Flow Validate Step

• Update validate-flag 2
• Execute after all Mandatory Fields identified in
  the Update Metadata are filled in by user
• Execute after all Validation Expressions are
  executed by the client
• Returns Error/Warning Block
• Data Not Saved in Database
• If not already done, automatically executes
  Update Auto Populate Step

12
Process Flow Save Step

• Update validate-flag0
• Data Saved in Database if
• No Error block is returned
• No Un-Answered Warnings are returned
• If not already done, automatically executes
  Update Validate Step
• Listing Number Generated
• Listing Date Generated
• Listing immediately visible to users

13
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

14
RETS Resources

• Updateable Resources
• Property ClassALL
• OpenHouse ClassOPEN_HOUSE
• External Validation Resources
• MRIS_ALL_ACTIVE_LIST_AGENT
• MRIS_ALL_ACTIVE_ALT_AGENT
• MRIS_ALL_ACTIVE_SALE_AGENT
• MRIS_ALL_SCHOOL_ELEM
• MRIS_ALL_SCHOOL_HIGH
• MRIS_ALL_SCHOOL_MIDDLE
• MRIS_ALL_CITY_ZIP_CODE
• MRIS_ALL_MRIS_TAX
• Images
• At this time we do not support the addition of
  Images

15
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

16
Update Transaction Extensions

• Errors in Warning Block Definition Fixed
• Return Data Sizes Increased
• Transaction ID Defined

17
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

18
Metadata Extensions

• Existing Metadata
• METADATA-CLASS
• METADATA-TABLE
• METADATA-LOOKUP_TYPE

19
Metadata Extensions

• Newly Implemented Metadata
• METADATA-UPDATE (Extended)
• METADATA-UPDATE_TYPE (Extended)
• METADATA-EDITMASK
• METADATA-FOREIGNKEYS (Extended)
• METADATA-VALIDATION_LOOKUP
• METADATA-VALIDATION_LOOKUP_TYPE (Extended)
• METADATA-VALIDATION_EXTERNAL (Extended)
• METADATA-VALIDATION_EXTERNAL_TYPE (Extended)
• METADATA-SEARCH_HELP
• METADATA-UPDATE_HELP
• METADATA-VALIDATION_EXPRESSION (Extended)

20
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

21
Validation Expressions

• Additional Special Operand Token
• .RECORDSTATUS. ? NEW, UPDATE
• Validation Expression BNF Grammar
• Makes Validation Expressions implementable
• Extended Metadata
• Event Types
• Validation Expression Types

22
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

23
Presentation Metadata

• Allows for 100 Metadata Driven Client
• Grouping of Related Fields
• New Metadata
• METADATA-COLUMN_GROUP_SET
• METADATA-COLUMN_GROUPS
• METADATA-COLUMN_GROUP_CONTROL
• METADATA-COLUMN_GROUP_TABLE
• METADATA-COLUMN_GROUP_NORMALIZATION

24
Keystone Implementation Overview

25
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

26
Localization

• Allows for the Development of Dynamic
  Regionalized Applications
• What can be Localized?
• Columns can be Hidden
• Picklist Items can be Hidden
• Column Labels can be Changed
• Picklist Labels can be Changed
• New Metadata
• METADATA-LOCALE_LOOKUP
• METADATA-LOCALE
• METADATA-LOCALE_TABLE_SUPPRESSION
• METADATA-LOCALE_TABLE_TRANSLATION
• METADATA-LOCALE_LOOKUP_INFORMATION
• METADATA-LOCALE_LOOKUP_TYPE_SUPPRESSION
• METADATA-LOCALE_LOOKUP_TYPE_TRANSLATION

27
Localization - Future

• Validation Expressions
• Can currently localize rules using the Expression
  Language
• Will allow a cleaner implementation in the near
  future

28
Agenda

• Introduction
• Process Flow
• RETS Resources
• Update Transaction Extensions
• Metadata Extensions
• Validation Expressions
• Presentation Metadata
• Keystone Implementation Overview
• Localization
• JRETSC

29
JRETSC

• Java RETS Client Library
• Completely Abstracts all RETS Transactions
• Higher Developer Productivity
• Similar Interface than JDBC
• Abstract API Design
• Allows for Multiple Implementations
• Will support RETS 2.0 in future with no API
  changes

30
JRETSC RetsConnection

• Instantiate RetsConnection Object
• Class driverClass
• String className
• com.mris.platinum.client.direct.DirectConnecti
  on
• try
• driverClass Class.forName(className)
• this.retsConnection
• (RetsConnection) driverClass.newInstance()

31
JRETSC Login

• Execute the RetsConnection login() Method
• RetsOffice retsOffices
• retsOffices this.retsConnection.logIn(this.serv
  erUrl,
• 
  this.userAgent,
• 
  this.agentLogin,
• 
  this.agentPassword)
• // If more than one office returned, user should
  pick one.
• if (retsOffices.length gt 1)
• this.retsConnection.setOffice(retsOffices0
  )

32
JRETSC Search

• Using SearchHandler
• RetsSearch retsSearch
• retsSearch this.retsConnection.getSearchHandler(
  )
• retsSearch.initSearch(Property,
• ALL,
• true, true)
• // retsSearch.executeSearch((ListingKey123456789
  0))
• retsSearch.executeSearchForUpdate((ListingKey123
  4567890))

33
JRETSC Search

• Retrieving Results
• RetsResultSet retsResultSet
• RetsResultRow retsResultRow
• while (retsSearch.hasMoreSets())
• retsResultSet retsSearch.getNextSet()
• while (retsResultSet.hasMore())
• retsResultRow retsResultSet.next()
• System.out.println(retsResultRow.getString(List
  ingKey))

34
JRETSC Update

• Using UpdateHandler
• RetsUpdate retsUpdate
• RetsUpdateResult retsUpdateResult
• retsUpdate this.retsConnection.getUpdateHandler(
  )
• retsUpdate.initUpdate("Property", "ALL",
  "Change")
• retsUpdate.addUpdateField("ListingKey",
• retsResultRow.getString("ListingKey"))
• retsUpdate.addUpdateField(ListPrice,
  1500000.00)
• retsUpdateResult retsUpdate.executeUpdate(RetsVa
  lidateFlag.SAVE)

35
JRETSC Update

• Retrieving Update Results
• RetsUpdateError retsUpdateErrors
• retsUpdateErrors retsUpdateResult.getRetsUpdateE
  rrors()
• if ((retsUpdateErrors ! null)
  (retsUpdateErrors.length gt 0))
• for (int i 0 i lt retsUpdateErrors.length
  i)
• if (retsUpdateErrorsi.isWarning())
• System.out.println("WARNING "
  retsUpdateErrorsi.toString())
• else
• System.out.println("ERROR "
  retsUpdateErrorsi.toString())

36
JRETSC Logout

• Simple Method Call
• this.retsConnection.logOut()

37
Agenda

• Sort
• Distinct
• Aggregate Functions
• Cross Resource Joins

38
Sort

• New Search Option
• Sort field-namesort-dir (,field-namesort-di
  r)
• Valid sort-dir Values
• ASC
• DES
• Role Based Sorting Limits
• Necessary for Performance and Memory Usage
• Example
• SortListPriceASC

39
Agenda

• Sort
• Distinct
• Aggregate Functions
• Cross Resource Joins

40
Distinct

• New Search Option
• Distinct 0 1
• Default is 0 All Records
• When set to 1 Only Returns Distinct Records
• Uses Sort Extension
• Example
• Distinct1

41
Agenda

• Sort
• Distinct
• Aggregate Functions
• Cross Resource Joins

42
Aggregate Functions

• Extension to Select Clause
• Sort Extension becomes Group By
• Columns in Select that are not Aggregated are
  automatically added to Sort
• Supported Functions
• SUM
• AVG
• COUNT
• MIN
• MAX
• MEDIAN
• STDDEV

43
Aggregate Functions

• Example
• SelectCounty,AVG(ListPrice)
• Returns the Average ListPrice grouped by County
• SelectAVG(ListPrice)SortCountyASC
• Returns the Average ListPrice grouped by County
• Return Data Format - COMPACT
• Functional Results are Always at End of standard
  RETS Data Row
• Column Name will be Function(Field)
• Example
• ltCOLUMNSgt\tCol1\tCol2\tAVG(Col3)\tlt/COLUMNSgt
• ltDATAgt\tData1\tData2\tData3\tlt/DATAgt

44
Aggregate Functions

• Return Data Format XML
• New Element ltAggregateFunctionsgt
• Returned immediately after top level XML Node
• Columns returned with Function Attribute
• Example
• ltREDATAgt
• ltMRISPropertiesgt
• ltResidentialPropertygt
• lt/ResidentialPropertygt
• lt/MRISPropertiesgt
• ltAggregateFunctionsgt
• ltCol3 FunctionAVGgt
• lt/Col3gt
• lt/AggregateFunctionsgt
• lt/REDATAgt

45
Agenda

• Sort
• Distinct
• Aggregate Functions
• Cross Resource Joins

46
Cross Resource Joins

• Based on Foreign Key Metadata
• Two kinds of Joins
• Normal Join
• Exists Only Join
• METADATA-FOREIGNKEYS
• X-MRIS_OneToManyFlag
• TRUE Only for Exists Only Joins
• FALSE Exists for Normal Joins
• Driving Resource and Class specified as normal
  Search Options

47
Cross Resource Joins

• New Search Option
• Join ForeignKeyIDTag (,ForeignKeyIDTag)
• Tag Use
• Becomes a Label used to identify Fields in the
  Select and DMQL Options
• Example
• Join1234567890ListingAgent
• DMQL(ListingAgent.LastNameSmith)
• SelectListingID,ListPrice,ListingAgent.FirstName

48
Cross Resource JoinsReturn Data Format

• COMPACT
• Uses additional ltDATAgt groupings for each Join
  used after driving Resource lt/DATAgt
• Each Join identified by ForeignKeyTag Attribute
• Example
• ltCOLUMNSgt\tCol1\tCol2\tCol3\tlt/COLUMNSgtltCOLUMNS
  ForeignKeyListingAgentgt\tCol1\tCol2\tlt/COLUMNSgt
  
• ltDATAgt\tData1\tData2\tData3\tlt/DATAgtltDATA
  ForeignKeyListingAgentgt\tData1\tData2\tlt/DATAgt

49
Cross Resource JoinsReturn Data Format

• XML
• New Element ltJoingt
• Returned immediately after top level XML Node
• Each Join identified by ForeignKeyTag Attribute
• If no data exists for Join, only returns begin
  and end ltJoingt XML Attributes
• Example
• ltREDATAgt
• ltMRISPropertiesgt
• ltResidentialPropertygt
• lt/ResidentialPropertygt
• lt/MRISPropertiesgt
• ltJoingt
• ltActiveAgent ForeignKeyListingAgentgt
• lt/ActiveAgentgt
• lt/Joingt
• lt/REDATAgt

50
Testing Support

• Test Server
• http//ptest.mris.com6103/ptest/login
• Contact MRIS for User ID and Password
• E-Mail rets_at_mris.net
• Provide MRIS with UserAgent for Update
  application
• RETS Forum
• http//forums.mris.com/forums/index.cfm?forumID3
• Also Accessible through http//www.mris.com
• MRIS Update Transaction

51
Questions and Answers