AppRecord

Description

AppRecord is the base object type for the majority of SBM objects. An AppRecord can represent any row of any table from the current SBM database. The table is specified by a numeric table ID which must be supplied when the AppRecord is created. Thus, AppRecord objects must be created using Ext.CreateAppRecord() and cannot be created with CreateObject(). For details on Ext.CreateAppRecord, refer to Ext.CreateAppRecord( tableId [, recType] ). For details on working with SBM database records, refer to Working with SBM Database Records.

After an AppRecord has been created for a specific table, its methods can retrieve data from any row of that table. However, the AppRecord can never be used with a different table; you must create a separate AppRecord object for that purpose.

When discussions of AppRecord objects refer to the "table ID of the current item," it means the table ID given when the AppRecord was created. AppRecord subtypes have hard-coded table IDs and can thus be created without supplying the table ID. For example, a User object is an AppRecord subtype that is always associated with the TS_USERS table. Thus, the table ID of any User object is the TS_ID associated with the TS_NAME of "Users" in the TS_TABLES table. The "Ext" object provides functions to look up table names and IDs.

Inheritance

AppRecord does not inherit from any object type.

Methods

• Add() (On-premise only): This method adds a new row to a table. After creating an AppRecord from the desired table and setting any desired field values, use this method to add the record as a new row in its table. This is not for use with Primary tables, because those items must go through a Submit transition. However, it is possible to make a copy of a Primary table item using this method.

  Note: Changes to cached items written with the Add() method are not immediately available to other SBM server computers that are connected to the same database. The other computers see these changes when the cache periodically synchronizes with the database. The time interval for cache updates can be configured and defaults to twenty seconds.

  Input: N/A

  Output: N/A

  Return: int – The TS_ID of the record added, which is unique to this table. Zero means the record could not be added due to an error.

  Example:

  var tableId = Ext.TableId("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var myRecord.SetFieldValue("CONTACTLASTNAME", "Smith");
  var ret = myRecord.Add();
  if (ret == 0){
    Ext.LogErrorMsg("Error adding Contact.");
  }

• Delete() (On-premise only): On most tables, this method removes the record from the database. On tables such as States and Users, the records are marked as deleted but remain in the database.

  CAUTION:
  This method immediately deletes the record. The user is not asked to verify deletion.

  Input: N/A

  Output: N/A

  Return: bool. True if the record is deleted from or marked as deleted in the database.

  Example:

  var tableId = Ext.TableId("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var whereClause = "TS_CONTACTFIRSTNAME = 'Joe'";
  if (myRecord.ReadWithWhere(whereClause)) { 
    if (!myRecord.Delete()) {
  		  Ext.LogErrorMsg("Error Deleting Contact record.");
  	 }
  	 Ext.WriteStream( "joe was deleted" );
  }

• Fields(): AppRecords from an Auxiliary or Primary table have a variable field list. That is, their fields can be defined using SBM Composer. The Fields() method returns a VarFieldList object containing all such fields. For AppRecords from tables without a variable field list, this method returns the global constant Nothing. See the VarFieldList object for details on manipulating field data.

  Input: N/A

  Output: N/A

  Return: VarFieldList – List of fields from this AppRecord.

• GetDisplayName(): Every database table has a display name format. SBM Composer enables you to define display name formats for Primary and Auxiliary tables. For other tables, the display name is found in the TS_NAME column.

  Input: N/A

  Output: N/A

  Return: string – This record's display name, formatted according to table settings.

• GetFieldValue(name, value): Gets the value of any field. If the calling AppRecord is an item from a Primary or Auxiliary table, its variable field list is searched. Otherwise, the schema for its table is searched.

  Input: name (string) – The name of the field whose value will be retrieved from the variable field list or system field list. System table field names must be entirely in lowercase characters or they will not be found. Field names for variable fields should be provided in uppercase characters for database column names or in lowercase or mixed case characters for display names (for Title, for example).

  Output: value (string) – The field's value, converted to a string. The value is returned as it is stored in the database. For example, for selection fields, the return value is the database ID for the selection.

  Note: If the field is a Multi-User field for which the Groups and Users selection mode is enabled, the value can include both pure integers (TS_IDs from the TS_USERS table) and group identifiers (TS_IDs from the TS_GROUPS table). The latter are in the form G1, G2, …

  Return: bool – True if the named field was found successfully.

  Example:

  //Create and retrieve Project record
  
  var tableId = Ext.TableId ("TS_PROJECTS");
  var myRecord = Ext.CreateAppRecord (tableId);
  var ok = myRecord.Read("Image Builder");
  if ( ok != 0 ) {
  	 ok = myRecord.GetFieldValue( "parentid", value ); 
  }
  if ( ok != 0 ) {
  	Ext.LogInfoMsg ("Parent ID of Image Builder is " &&& value);
  } 
  else {
  	Ext.LogErrorMsg( "Unable to get Parent ID of " &&&
   "Image Builder project" );
  }

  Note: A GetFieldValue method is also included in the SBM JavaScript Library.

• GetId(): Retrieves the current AppRecord's TS_ID. Note that TS_ID is uninitialized on a newly-created object. This method will only return a meaningful result if the calling object has been read from or written to the database.

  Input: N/A

  Output: N/A

  Return: (int) – TS_ID of the calling AppRecord.

  Example:

  //Create a Contact record
  var tableId = Ext.TableId ("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var whereClause = "TS_CONTACTFIRSTNAME like 'Joe'";
  var ContactTSID; 
  if(myRecord.ReadWithWhere(whereClause)) {
  	//Read the TS_ID of the record
  	ContactTSID = myRecord.GetId();
  }

• GetName(): Gets the calling AppRecord's "name" system field. For more details on the "name" system field, refer to Working with SBM Database Records.

  Input: N/A

  Output: N/A

  Return: (string) – A text string from the associated Name field.

  Example:

  //Create a Contact record
  var tableId = Ext.TableId ("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var whereClause = "TS_CONTACTFIRSTNAME like 'Joe'";
  if(myRecord.ReadWithWhere(whereClause)) {
  	/*On the contact record, this will return back
  		the contact's full name */
  	name = myRecord.GetName();
  }

• GetRecTableId(): Returns the calling AppRecord's table ID.

  Input: N/A

  Output: N/A

  Return: (int) – The calling AppRecord's table ID.

  Example:

  var tableId = Ext.TableId ("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var result = myRecord.GetRecTableId();
  if(result != tableId) { 
  	Call Ext.LogInfoMsg("Error was expecting 'Contacts' to be table " +
      tableId);
  }

• HasVariableDBFields(): Tests whether the calling AppRecord is from a table with variable fields.

  Input: N/A

  Output: N/A

  Return: (bool) – True if the calling AppRecord's table has variable fields.

• IsFieldEqual( fieldNameOrId, value ): Tests for equivalence between the specified field and a value formatted as a string.

  Input: fieldNameOrId (string or integer) – If a non-numeric string, the name of the field to be tested. Otherwise, it is converted to a number and taken as the TS_ID of the field to be tested.

  Input: value (string) – The value to be compared to. The field's value will be formatted as a string and compared to this value.

  Output: N/A

  Return: (int) – Zero if the field value does not match, otherwise non-zero.

• IsLocked(): Tests whether the calling AppRecord has been locked, meaning it is in use by another user. This means IsLocked() should return True if another user is using the item. If the item is locked by the current user who is calling IsLocked(), the return is False, which indicates that no one else can prevent an update to the item that was just locked.

  Input: (bool) – Without an input parameter ( or with "False" as the input ), IsLocked() means "Does someone else have this record locked?" With "True" as the input parameter (IsLocked(True)), the meaning is "May I (as the current user) update this record?"

  Output: N/A

  Return: (bool) – True if the record is locked by another user.

• Lock( [stealLock] ) (On-premise only): Locks the calling AppRecord, so other users will not attempt to update it.

  Input: stealLock (bool) – Optional, defaults to False. If True, any existing lock on this record will be broken, and changes made by the former lock holder are lost.

  Output: N/A

  Return: (bool) – True if the record is successfully locked.

• Read(recordIdOrName): Look up a row of this AppRecord's table. Depending on the parameter's type (numeric vs. string), a different type of lookup is performed.

  Input: recordIdOrName (string or number) – If this is a number or string that ChaiScript can convert to a number, it is taken as the value to search for in the TS_ID column. All other parameter types are converted to a string and searched for in the "name" system field.

  Output: N/A

  Return: bool – True if the TS_ID or name was found, in which case the calling AppRecord object becomes a copy of that row in the table.

  Example:

  //Create a Contact record
  var tableId = Ext.TableId ("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  if(! myRecord.Read("Smith")) { 
  	Ext.LogErrorMsg("Error looking for Contact with " +
  	"TS_CONTACTLASTNAME of 'Smith'");
  }

• ReadWithWhere(WhereClause [, Vector<Pair> params]) (On-premise only): Used to find a record by passing in a string containing a SQL "where" clause, not including the keyword "where". All fields will start with "TS_" and normal SQL syntax will apply. The calling object will become a copy of the first record found. This is the most general way of finding particular AppRecords.

  Input: whereClause ( string ) – The SQL "where" clause to find the specific record in the table, not including the keyword "where."

  Input: params ( Vector ) – Params is an optional Vector storing SQL bind parameters, where each entry is a Pair, where the first value is the parameter type and the second value is the value to bind to the SQL parameter.

  Output: N/A

  Return: (bool) – True if the method found the record, in which case the calling object becomes a copy of the record found. If more than one record is found, the first one is copied to the calling object.

  Example:

  var record = Ext.CreateAppRecord(4);
  Var whereClause = “TS_CONTACTFIRSTNAME like ‘Joe’”;
  myRecord.ReadWithWhere(whereClause);

  Example:

  var record = Ext.CreateAppRecord(4);
  record.ReadWithWhere("TS_Name = ?", [Pair(12,"EVERYONE")]);

• SetFieldValue( name, value ) (On-premise only): Sets the value of any field. If AppRecord is calling an item from a Primary or Auxiliary table, its field in its variable field list is set. Otherwise, the schema for its table is set. System table field names must be entirely in lowercase characters or they will not be found. Field names for variable fields should be provided in uppercase characters for database column names or in lowercase or mixed case characters for display names (Title, for example).

  CAUTION:
  It is strongly recommended that you consult the SBM schema document before using the SetFieldValue method.

  Input: name (string) – The name of the field whose value will be set in the variable field list or system table field list.

  Input: value (string or Number) – The new value for the field. The value must be specified as it is stored in the database. For example, for selection fields, the value must be the database ID for the selection.

  Tip: For elapsed-time fields, the value that you retrieve from a GetFieldValue() call is not compatible with a call to SetFieldValue(), because a standalone number in an elapsed-time field is interpreted as hours rather than seconds. To get around this, provide SetFieldValue() with the string "00:00:" plus the number of seconds retrieved by GetFieldValue(). For example:

  "00:00:" & elapsedTime

  SBM converts the seconds into the correct hours, minutes, and seconds for you.

  Return: bool – True if the field value was successfully set.

  Note: A SetFieldValue method is also included in the SBM JavaScript Library.

• SetName(newName) (On-premise only): Sets the "name" value of the calling object. For system tables, this is the "TS_NAME" column. For Primary and Auxiliary tables, this is the column corresponding to the "name" system field. For more details on the "name" system field, refer to Working with SBM Database Records.

  Input: newName (string) – Text to be used as the new name field.

  Output: N/A

  Return: N/A

  Example:

  //Create a Company record
  var myRecord = Ext.CreateAppRecord( Ext.TableId ( "TS_COMPANIES" ));
  var whereClause = "TS_NAME like 'ABC'"
  var bOk = myRecord.ReadWithWhere( whereClause );
  if( bOk = true ) { 
  	//Set the name of the company to "XYZ"
  	myRecord.SetName("XYZ");
  	//Commit change to database
  	 myRecord.UpdateWithLock( true );
  }

• Unlock( [currentUserOnly] ) (On-premise only): Unlocks the calling AppRecord, signaling that it is available for other users.

  Input: currentUserOnly (bool) – Optional, defaults to False. If True, only the current user's locks are unlocked. If False, any user's locks on this record are removed.

  Output: N/A

  Return: (bool) – True if the record was successfully unlocked or was not locked to begin with.

• Update() (On-premise only): This method replicates the functionality of the system Update transition. The values of all fields in the calling AppRecord are written to the database. Any fields that the current user has privileges to update may be modified and the Update method commits them to the database. This method modifies an existing record, based on the TS_ID of the calling AppRecord. To add a new record, see the Add() method.

  Note: During Post-Transition, Post-State, and Pre-State scripts, it is not necessary to call Shell.Item.Update because the system always calls Update after the script finishes. However, if you create any other AppRecord objects, you will need to call Update on those each of those objects; the system always calls Update only for Shell.Item.

  Input: N/A

  Output : N/A

  Return: (bool) – True if the record is updated in the database. The same cache rules apply as in Add().

  Example:

  // Create a Contact record
  var tableId = Ext.TableId ("TS_CONTACTS");
  var myRecord = Ext.CreateAppRecord(tableId);
  var whereClause = "TS_CONTACTFIRSTNAME like 'Joe'";
  if( myRecord.ReadWithWhere(whereClause) ) {
  	// Get a lock, set the value, update and unlock
  	var locked = myRecord.Lock();
  	myRecord.SetFieldValue("CONTACTLASTNAME", "Smith");
  	myRecord.Update();
  	var unlocked = myRecord.Unlock();
  }

• UpdateWithLock( [stealLock] ) (On-premise only): Updates the record that has been previously locked with Lock() function.

  Input: stealLock (bool) – Optional parameter that allows the user to skip the Lock function and steal the lock during the update. Defaults to False. If True, any other user's existing locks will be broken, causing that user's changes to be lost.

  Output: N/A

  Return: (bool) – True if the record was successfully updated.

• ReadByColumn( columnName, value ): Reads any record type by a column value. "value" must be an integer type, a string type, or a float type. The database column will be expected to be of that same type, so a string with the value "1" will not work when reading an integer column. This can be worked around using the CInt, CStr, and CDbl functions. If the table is cached by Application Engine, the record may be read from the cache instead of the database.

  Example:

  var tableId = Ext.TableId( "TS_USERS" );
  var record = Ext.CreateAppRecord( tableId );
  if( record.ReadByColumn( "LOGINID", "joe" )){
      var name = record.GetName();
      Ext.WriteStream( "User is " + name  );
  }

• ReadByColumnAndColumn( columnName, value, column2Name, value2 ): Reads any record type by two column values. The first column and value must be an integer type. The second column can be integer or string. The database column will be expected to be of that same type, so a string with the value "1" will not work when reading an integer column. This can be worked around using the CInt, CStr, and CDbl functions. If the table is cached by Application Engine, the record may be read from the cache instead of the database.

  Example:

  var tableId = Ext.TableId ( "USR_APP_NAME" );
  var record = Ext.CreateAppRecord( tableId );
  var read = record.ReadByColumnAndColumn( "SUBMITTER", 27, "TITLE", "myTitle" );
  if(read){
      var name = record.GetName();
      var itemId = record.GetId();
      Ext.WriteStream( "The item is " + name + " " + itemId );
  }

• GetUUID(): Returns the item's UUID, if applicable.

  Example:

  var itemUUID = record.GetUUID();

• ReadByUUID(): Reads the item using the UUID column, if it exists. If the table is cached by AE, the record may be read from the cache instead of the database.

  Example:

  var uuidRead = record.ReadByUUID(itemUUID);

• GetSchemaColumns(): Returns a vector of SchemaColumn objects. Will not include fields for VarRecord or ProjectBasedRecord objects.

  Properties:

  • string name: Schema column name, usually lower case with no TS_ prefix.
  • long type: Matches a value from DBTypeConstants for data type.
  • long length: Length of fixed length text column (database may be larger to support Unicode character expansion), or size of integer column, or 0 if not needed by type.

  Example (includes example of Schema Data):

  var tableId = Ext.TableId( "TS_USERS" );
  var record = Ext.CreateAppRecord( tableId );
  var schemaColumns = record.GetSchemaColumns();
  var schema = Variant();
  for( schema : schemaColumns ){
  Ext.WriteStream( schema.name + " " + schema.type + " " + schema.length + ", ");
  }