The purpose of this article is to guide the user in using the SDML (Syclo Data Markup Language) or also known as an Agentry flunky as a foreach function in an SQL transaction step.
To properly explain the flunky <<foreach>> function, an example will be described herein.
insert into <<local.exchangeTable>> (SycloIncidentID, personID, lastUpdate, action)
<<foreach objects "<<local lastUpdate=<<if <<eq <<my.lastUpdate format=\"%Y\">> "0000" >> \"'01/01/1970 00:00:01'\" else \"<<my.lastUpdate>>\" >> >>
select '<<my.object.SycloIncidentID>>', <<local.PersonID>>, <<local.lastUpdate>>, null
The steps above is how a <<foreach>> function works in Agentry. The SQL step will try to insert into a local exchange table different parameters where the values are based on the results returned from a foreach function against the current object.
The keyword above is foreach "object" in the collection to select certain values.
- The '<<my.object.SycloIncidentID>>' is <<my.object.projectKey>>' (i.e. SycloIncident is the Project and SycloIncidentID is the key property)
- The '<<local.PersonID>>' is a local variable that calculated the PersonID
ProjectKey is the Key property of the object ‘Project’ which is the current referenced object. So Project is the name of the object and projectKey is the key property of it.
For the Foreach object, you can only use the current collection.
Additional Note: When a transaction is done locally without a key property setup yet, it is recommended to set the @id_temp to a local value (ex: Local_1 where Local_ is the prefix and the 1 is an incremental value of a locally created transaction), and then use a different script that will search each object into the database using the local flunky (ex: search a backend exchange table to retrive the actual property ID that maps to Local_1 - This type of design is seen in older Syclo products Work Manager for IBM 5.2 and earlier).