SBM ModScript, Part 2 - Transition Related Items

1 Likes
over 3 years ago

In part 1 we reviewed what ModScript is. Let's start looking at some examples:

In Use Case 1, the ModScript will run in the Post Transition context of an item's Close transition. The item might be selected in a multi-relational field on a separate item, and if all items in that multi-relational field are closed, we'll transition the container item. This is similar to what can be done with the SBM Sub-Tasks feature, but we'll do it via scripting because there could be some extra logical detail that Sub-Tasks couldn't check for but scripting could (and it gives us a showcase for ModScript features). The full application can be found here: Container.zip. This script uses the algorithms "drop_while()" and "any_of()", to see more information on algorithms, see Part 5. This script also uses "from_json()", to see more about ChaiScript's JSON utility functions, see Part 4.

 

// set up some constants for use later in the script
add_global_const( "USR_CONTAINER", "CONTAINER_TBL_NAME" );
add_global_const( "RELATED_ITEMS", "CONTAINER_FIELD" );

// Get the container application table id and add it as a global
global CONTAINER_TBL = Ext.TableId(CONTAINER_TBL_NAME);

// define a function that trims commas and returns the new string
def TrimCommas( s ) {
/*	ChaiScript engine automatically returns whatever occurs on the last line of a 
	function. We could add a "return" statement here if desired for clarity.
	This may be a little hard to follow, but we call drop_while on our string to remove 
	the starting commas, then the return value is reversed, we do drop_while again to 
	trim the back, then reverse again.
	These "fun" statements are anonymous functions known as lambdas */
	reverse( drop_while( reverse( drop_while( s, fun(x){ return x == ','; } ) ), 
		fun(x) { x == ','; })); 
}

// Find the multi-relational field
var relational = Ext.CreateAppRecord( Ext.TableId("TS_FIELDS"), 
				FieldTypeConstants.MULTIPLE_RELATIONAL );
relational.ReadByColumnAndColumn("TABLEID", CONTAINER_TBL, "DBNAME", CONTAINER_FIELD );

// Create a list, as it is possible that our item is in more than one container item
var containerList = Ext.CreateAppRecordList( CONTAINER_TBL );

/* Yes, ModScript has multi-line comments!

 Read the list of items that contain this item. 
 Use SQL binding by passing a Vector of Pair objects, each with a data type and a value.
 Vectors can be created on the fly using [ ... ] syntax */
containerList.ReadWithWhere(
	"TS_ID in (select TS_SOURCERECORDID from TS_USAGES where TS_FIELDID=? and TS_RELATEDRECORDID=?)",
	[ Pair(DBTypeConstants.INTEGER, relational.GetId()), 
	  Pair(DBTypeConstants.INTEGER, Shell.Item().GetId()) ] );

// loop through the resulting list, using the "for each" syntax (just a ":")
for( containerItem : containerList ) {
	// for each item, read the contents of the relational field, check the items, 
	// transition if necessary
	
	// Get the field value from the containing item. GetFieldValue() returns a Variant,
	// so use to_string() to get it as a string.
	// In 11.4, we have GetFieldValueString(), GetFieldValueInt(), etc, for getting field 
	// values as the desired type.
	var fieldVal = containerItem.GetFieldValue(CONTAINER_FIELD).to_string();
    
	// remove the current item from the comma-separated list of items 
	var regex = Regex();
	regex.Compile( ",${Shell.Item().GetId()}," );
	fieldVal = regex.ReplaceAll( fieldVal, "," );

	// trim outside commas off of list
	fieldVal = TrimCommas( fieldVal );
	
	// turn comma separated list into Vector of values. 
	// ChaiScript can do this with "from_json" if we use array syntax [ ... ]
	// ChaiScript has in-string processing, use ${ ... } inside a string and the stuff 
	// inside the braces will be processed by the engine and put inline in the string.
	var itemIDs = ("[${fieldVal}]").from_json();

	// Loop through those other contained items to see if they are inactive too.
	// We could do another for-each loop here, but let's use an algorithm instead.
	if ( !any_of( itemIDs, 
			fun( itemID ) { // return true if item is active
				var contained = Ext.CreateProjectBasedRecord( CONTAINER_TBL );
				return contained.Read(itemID) && 
					contained.GetFieldValue( "ACTIVEINACTIVE" ) == 0;
			} ) ){
		// no items found that are active, we need to transition this container item
		containerItem.QuickTransition( "CONTAINER.CLOSE", true );
	}
} 

 

SBM ModScript Table of Contents

Comment List
Anonymous
  • Being new to Modscript, Chaiscript, and/or C++ this was difficult to follow. I generally get the gist of it, though I couldn't describe how most of it works... not sure if this is explained in further detail throughout the series or not.
  • My SQL is correct, we are looking for items, which have the Multi-Relational field specified, in which the current item is selected. So, our current item is the related record, and we are looking for the source records. If, instead, we had a multi-relational field in the current record that pointed to the items we needed to transition, we could have pulled the TS_IDs of the selected items straight from that field. We would have needed to parse the comma-separated-integer list, but we could have done that. You should always prefer the usages tables to searching the text field using '%,123,%'. Any text search that starts with a wildcard cannot take advantage of indexes, where-as you should find the usages tables properly indexed. When the query cannot use an index, you have huge performance penalties from full-table-scans. This not only slows down your query, but slows down the entire system. It can lock the table while the query runs, preventing other processes from making updates. Another best-practice to at least consider is the usage of "with(nolock)" hints on SQL written for MSSQL (not Oracle). This can prevent the query from locking the table while running the query. On the negative side, it can read non-committed changes (which might get rolled back). As it has both a benefit and a drawback, use the hints with care. A final best practice, when it comes to SQL, is to consider adding indexes specifically for your queries. Scripts sometimes get run frequently, and the entire system might run better if your script SQL is well-indexed. As a side note, SBM strives to add indexes for the queries we write, but sometimes we miss some indexes that we should have added. Also, with the schema of aux/primary tables being fairly custom, and with reports and feeds being able to add SQL filters that we cannot foresee, it is a good idea to look at your database to identify SQL queries that use a lot of resources, and try to provide indexes to improve those queries. Also, if you find indexes that you think we should be adding by default, please let us know. When it comes to updating the Usages tables, if you make updates using AppRecords, AppScript/ModScript should update the usages properly (I think). If you make changes directly to the database somehow, the usages tables will not get updated and that could be really bad. Please let me know if you see places where SBM objects were used to update fields, but the usages did not get updated properly.
  • Don: Should line 34:
    "TS_ID in (select TS_SOURCERECORDID from TS_USAGES where TS_FIELDID=? and TS_RELATEDRECORDID=?)"
    be:
    "TS_ID in (select TS_RELATEDRECORDID from TS_USAGES where TS_FIELDID=? and TS_SOURCERECORDID=?)"
    ?? And more of a general question: Do you recommend using the "Usage" tables ("ts_MultiUserUsages" , "ts_SelectionUsages", "ts_Usages") to iterate related items from a Multi-User/Group/Selection/Relational field instead of querying the Multi-X varchar field directly with something like: Where ts_MultiSomethingField Like '%,' + Cast(tbl.ts_Id as VarChar) + ',%' (not directly related to this example): When will the AppScript or ModScript engine update the appropriate "Usage" table? For example, a script modifies a varchar Multi-Relational field in the current item to add or remove one or more TS_IDs; when the script or transition completes does the script engine update TS_SELECTIONUSAGES to properly reflect the items removed from or added to the multi-rel field?
Related Discussions
Recommended