FastNET Functions
From Agility
Contents
Introduction
Within the framework there are several places where the user may specify a function instead of a variable.
e.g. String Expressions. Queries used in the Query Builder
Date and Time
| Function | Parameters | Description |
|---|---|---|
| Now | ([precision]) | Current date and current time (DateTime). The optional precision parameter is a string containing one of the values: y, m, d, h, mm, s, ms. This parameter specifies the precision of the result. For example if “d” is specified, all date elements smaller than day will be set to 0, which is equivalent to Today() function. |
| Today | () | Current date only, time 00:00 (DateTime) |
| BeginOfMonth | (Date) | First day of the optional Date or current Date month, time 00:00 (DateTime) |
| EndOfMonth | (Date) | First day of next month after optional Date or current Date, time 00:00 (DateTime) |
| DaysRange | (Number of Days) | “Now” plus or minus a number of days (DateTime) |
| DateAdd | (Start Date, Period, Number) | This function adds /subtracts from the startdate then number of periods defined. Period must be one of “second, minute, hour, day, week, month, year”. Number may be negative if required. |
| DateDiff | (Period, StartDateTime, EndDateTime) | This function adds /subtracts the EndateTime from the StartDateTime then returns the number of periods between the two.. Period must be one of “second, minute, hour, day, week”. Returned number may be negative.
Pre 4.1.4 use the syntax “HOURS”, both supported after 4.1.4 release. |
| TODDiff | (Period, StartDateTime, EndDateTime) | This function works only on the time element of a datetime field. The date part of the filed is totally is ignored.
It returns the difference between Start Time of day and End Time of day. Period must be one of “second, minute, hour”. If the Start Time is greater than the end time it will assume that it has gone over midnight and calculate the difference accordingly. e.g. TODDIFF(‘Min’ , 12/12/2012 13:10 , 12/01/2000 19:00) will return 350. TODDIFF(‘Min’ , 12/12/2012 23:10 , 12/01/2000 08:00) will return 530. |
| BeginOfDay | () | |
| EndOfDay | () | |
| CalculateEndDate | (Start Date, PriorityCode|PriorityID, dueMinutes) | Calculates the end date based on Start Date, Priority Code and the due time in minutes. If the proirity has got a calendar assigned, the calendar working hours will be taken into account during calculation. |
| CalculateCalendarBasedEndDate | (Start Date, CalendarCode|CalendarID, dueMinutes) | Calculates the end date based on Start Date, Calendar Code and the due time in minutes. Calendar working hours as well as exceptions specified within the given callendar will be taken into account during calculation. |
User Information
| Function | Parameters | Description |
|---|---|---|
| UserID | () | ID value of the currently logged in user |
| UserName | () | Name of current user |
| UserInfo | (“FieldName”) | Extracts the data form the field in the upUser record. For the currently logged in User. This can also be used to provide the value of any attribute of Active Directory if AD integration is enabled. |
| UserInfo | (“FieldName”,UserID) | Extracts the data form the field in the upUser record, for the user identified by UserID parameter. |
| UserBelongsToGroup | (UserID, GroupName) | Returns true if the user with passed id belongs to the group with the passed name. otherwise returns false. |
| IsNamedLicenseUser | (UserID) | Returns True value if the user using the Named License; otherwise False. |
| LicenseUsersCount | (UserID) | Returns number of own licenses depended of the license type. |
| GetUserCount | (UserID) | Returns number of users currently logged into system depended of owning the license type. |
| CheckUserLimit | (UserID, Percentage)
Returns True value when number of currently logged users is less or equal than value passed in Percentage argument; otherwise False. It is depend of license type which is owning by the currently logged user. |
String Manipulation
| Function | Parameters | Description |
|---|---|---|
| StrLen | (“string”) | Returns the length of a string. In example on left it will return 6. |
| Replace | (‘Regexp’,’ReplaceExp’,’ReplaceIn’) | Replace ReplaceIn string using search defined by Regexp with ReplaceRegexp regular expression. |
| Translate | Arguments[] | Translates first argument passed into the language according to the logged user’s profile language setting. First argument is the string to be translated with replacement marks; the rest of the arguments is used to replace replacement marks after the string is translated.
e.g. simple string TRANSLATE (“Hello World”) or with substitution TRANSLATE ( “Work Order {0} is Overdue”, woJob.JobCode) |
| Split | (StringToSplit, SplittingCharacter) | Returns an arrays of strings resulting from splitting the StringToSplit into parts according to SplittingCharacter divisor |
| Format | (FormatString,StringToBeFormatted) | Performs string formatting as per definition of .NET String.Format function |
| FORMATWITHCULTURE | (CultureName,FormatString,StringToBeFormatted) | As above with additional argument allowing to pass specific Culture Name like en-GB or en-US etc. |
Form Information
| Function | Parameters | Description |
|---|---|---|
| FormURL | (string FormName, string/datakey BusinessData_DataKey, string FilterExpression | Calculates URL to form specified by form name. If BusinessData_DataKey is specified it will be used as data id to open in the form. If it is null, FilterExpression string can be used. This filter expression will be used to identify the data record to show in the form.
FormUrl("AGWODet") - open new AGWODet form FormUrl("AGWODet",DataKeyString("sxp","woJobTask",inPurchaseOrder.JobTaskID)) FormUrl("AGWODet",null," woJobTask.JobTaskID = "+ inPurchaseOrder.JobTaskID) |
| FormID | (String) | Returns the integer value representing the ID of the Form with name paseed as an argument. |
| CurrentFormState | () | Valid states are:-
|
| Lookup | (‘BusinessObjectName’, ‘lookupTable’, ‘KeyFieldName’, KeyFieldValue, ‘ValueExpression’ [, ExtendedFilter] [,SkipFieldValueIfNull]) |
|
| Rownum | (TableName, datakey) | Returns the number of saved rows in the specified data table in a current dataset incremented by 1. the datakey supplied is the key of the parent record of the child rows which are being counted. |
| DataRowState | (TableName, PrimaryKeyValue [, PrimaryKeyValue1 …] | Returns one of the strings representing the data row state: Added, Modified, Deleted, Unchanged
|
| ADUserAttrValue | (AttributeName) | Returns the value of the Active Directory Attribute passed as a parameter. |
| TimeSpan | (int Minutes,string Format) | Returns time span as string formatted according to set format in parameter Format. For example: 6h 50m or 7d 6h
When parameter Format is not set then default firstElement = hour and secondElemet = minute. Examles :
|
| RegisterValue | (ValueName,StringValue) | Returns the value name which can be read by Agility during processing. Used Internally by ajax.aspx. |
Misc Information
| Function | Parameters | Description |
|---|---|---|
| LastValue | Fields | Returns the contents of the last field which has a value entered. e.g. lastvalue ( “A” , “B” ,””,”D” ,””) will return the value “D”. |
| SysParam | ( ParameterFullName ) | Return value of system parameter. The system parameters are defined in system user. |
| SysParam | (UserID,ParameterFullName) | Return value of the User parameter.
For example to set only certain users to be able to set Autodeployment settings on the labour resources 1) Set up a parameters set for the user called System. 2) Add a Bool Parameter called AutodeploymentInUse 3) On the labour resources form for the AutoDeployment Tab remove the existing check for the global parameter and replace it with the following. 4) SYSPARAM(UserID(),"System\AutodeploymentInUse") = True 5) Only users with this custom parameter will see the Autodeploy settings. Note: This could also be done with a group permission. See UserBelongsToGroup above. |
| DataRowState | (TableName, PrimaryKeyValue1,…) | Depending on state of row selected by TableName and PrimaryKeyValues function returns Added, Changed, Unchanged and Deleted. Function can select rows only from dataset of current business object. |
| DataKeyString | (DatabaseName, TableName, RecordID) | Returns a full string representation of internal FastNET DataKey structure. DatabaseName is a symbolic database name as specified in the config file, e.g. “sxp” – for Agility db. TableName – the name of the table the record belongs to, RecordID – a value of the ID field of the record.
Parse (type,format,stringToParse) Returns the result of parsing the ToParse string into the type described by type, according to the format passed in the format parameter. |
| ExecSQL | Arguments[] | Returns true if the execution of parameterized query is successful. First function arguments have to be defined as type of String. The first argument is taken as parameterized query string. Parameters name must be defined as @p1, @p2, ….@pN and number of parameters values must be matched with query parameters.
Example: Expression: EXECSQL("select TOP(1) JobID FROM woJob WHERE (AssetID = @p1) AND (PriorityID = @p2) AND (JobTypeID = @p3) AND (FullDescription LIKE @p4) AND (JobStatusID = @p5)", woJob.AssetID, woJob.PriorityID, woJob.JobTypeID, woJob.FullDescription, woJob.JobStatusID) Is evaluate for example as follows: exec sp_executesql N'select TOP(1) JobID FROM woJob WHERE (AssetID = @p1) AND (PriorityID = @p2) AND (JobTypeID = @p3) AND (FullDescription LIKE @p4) AND (JobStatusID = @p5)',N'@p1 int,@p2 int,@p3 int,@p4 nvarchar(4000),@p5 int',@p1=171,@p2=5019,@p3=5004,@p4=N,@p5=5019 |
| MonitorTableChanges | (ParentDataKeyString, ChildTable, ColumnList) | Function returns boolean value which indicates changes in the given table. The first argument is a textual representation of parent DataKey. Value of this argument can be obtained by using DATAKEYSTRING function. The second argument points to table which data are monitored. The third argument specified the pipe separated column list which values need to be monitored.
Example: MONITORTABLECHANGES(DATAKEYSTRING("sxp", "pmBooking", pmBooking.BookingID), "pmBookingResource", "AttendeeEmail|AttendeeType|StartDate") Returns true if value of one of these columns AttendeeEmail, AttendeeType, StartDate in pmBookingResource has been changed or new record have been added or any existing record have been deleted. |
Agility Use Only
| Function | Parameters | Description |
|---|---|---|
| AssetInfo | (AssetID,”Fieldname”) | Gets the appropriate field from the pmAsset table whose record ID is contained in the AssetID field. Note the use of Quotes around the fieldname. |
| Format | (FormatString, FormatParameter[]) | Formats supplied format parameters according to the format string.
Note that the formatting is defined as per the “string.format function in c#. e.g. FORMAT('{0:dd\/MM\/yyyy hh\:mm tt}' , woJob.StartDate) |
| GetCurrentEmployeeID | () | “Agility” only function which Returns the ID of the Labour record associated with the currently logged in user. |
| CurrencyToNative | (CurrencyID,ValueToConvert,Type) | Convert value(ValueTo Convert) in not native currency(CurrencyID) to value in native currency and returns in format depending on sets for type(Type). |
| NativeToCurrency | (CurrencyID,ValueToConvert,Type) | Convert value(ValueTo Convert) in native currency to value in other currency(CurrencyID) and returns in format depending on sets for type(Type). |
| DatePart | (“Period Date”, DateTime Value) | Returns an integer that represents the specified datepart of the specified date. Works similar to SQL function DATEPART. First argument is name of period: "YEAR","MONTH", "WEEK","DAY","HOUR","MINUTE" or "SECOND" or “DOW” or “DOY”.
“DOW”=Dayof Week 0=Sunday, 6 = Saturday. “DOY”=Day of Year. Second argument is DateTime value. |
| GetNextValue | ( “SequenceName”) | Gets the next value from Agility Sequence table. For use with user define numbering of documents e.g. Work Orders , Purchase Orders, Document number etc. |
GPS Utilities
| Function | Parameters | Description |
|---|---|---|
| CONVXTOLATITUDE | (x, y, z) | Converts OSGB36 x value to latitude in geodetic coordinates. As parameters, it expects 3 double variables to represent x, y and z values in OSGB36 system respectively. |
| CONVYTOLONGITUDE | (x, y, z) | Converts OSGB36 y value to longitude in geodetic coordinates. As parameters, it expects 3 double variables to represent x, y and z values in OSGB36 system respectively. |
| CONVZTOELIPSOIDAL | (x, y, z) | Converts OSGB36 z value to height in geodetic coordinates. As parameters, it expects 3 double variables to represent x, y and z values in OSGB36 system respectively. |
| CONVLATITUDETOX | (Latitude, Longitude, Height) | Converts geodetic latitude to x value in Cartesian coordinates. As parameters, it expects 3 double variables to represent latitude, longitude and height values in geodetic system respectively. |
| CONVLONGITUDETOY | (Latitude, Longitude, Height) | Converts geodetic longitude to y value in Cartesian coordinates. As parameters, it expects 3 double variables to represent latitude, longitude and height values in geodetic system respectively. |
| CONVELIPSOIDALTOZ | (Latitude, Longitude, Height) | Converts geodetic height to z value in Cartesian coordinates. As parameters, it expects 3 double variables to represent latitude, longitude and height values in geodetic system respectively. |
| CONVLATITUDETOEASTING | (Latitude, Longitude, Height) | Converts Geodetic latitude to x value in OSGB36 coordinates. As parameters, it expects 3 double variables to represent latitude, longitude and height values in geodetic system respectively. |
| CONVLONGITUDETONORTHING | (Latitude, Longitude, Height) | Converts Geodetic longitude to y value in OSGB36 coordinates. As parameters, it expects 3 double variables to represent latitude, longitude and height values in geodetic system respectively. |
Database Queries
| Function | Parameters | Description |
|---|---|---|
| SecureSelectStar | (string DatabaseName,string TableName, int Userid)
Returns string containing the SQL SELECT statement returning ALL COLUMNS of the given table. SQL includes all security rules for a specific user for that table name. | |
| SecureSelectPrimaryKey | (string DatabaseName,string TableName, int Userid) | Returns string containing the SQL SELECT statement PRIMARY KEY column of the given table. SQL includes all security rules for a specific user for that table name. |
Standard .NET static functions
It is possible to call all public static .NET standard functions available like a normal functions. It's name has to contain full namespace:
System.Guid.NewGuid()- .NET static method for generating guid valueSystem.String.Format("test {0}", pmAsset.Code)- .Net static method for formating strings.
Calling export from string expression
Function doExport allows to call predefined in system export. Export is identified by Code. Additionally named parameters can be passed to export. Passed to export parameters are accessible as variables. Returned value is always string.
doExport(Code [NamedParameter1=val1, NamedParameter2=val2,…])
Example string expression:
doExport(“LabourKPIExport”, LabCode=emLabour.LabourCode)
Example export definition:
<export>
<contents>
<data type="template-html">
<div>LabourCode: { #LabCode }</div>
</data>
</contents>
</export>
Filter Expression functions
There are special function to help build dynamic filter expressions.
| Function | Parameters | Description |
|---|---|---|
| IF | (<condition>, <ifTrueExpression>, <ifFalseExpression>) | depending of <condition> boolean value function will include <ifTrueExpression> or <ifFalseExpression> in the filter expression. The <condition> could by constant, variable or function call only. Both expression are both filter expressions. This function is internal part of Filter Expression and is not available in String Expression.
for example:
|
| IsEqual | (<argument1>,<argument2>) | Checks if <argument1> is equal <argument2>
for example:
|
| Negation | (<booleanArgument>) | Calculates boolean negation of <booleanArgument>
for example:
|
