The following computed column function examples show the original input and the output expected when data is imported to match a specific requirement in a target system configuration.
For information on using functions in the Computed column, see Use the Computed column.
Function name |
Computed column sample |
Input values and output results |
---|---|---|
AddDefaultUOMIfMissing |
func.AddDefaultUOMIfMissing("DEVTag", "DEVWeight", [TAG_WEIGHT]) |
INPUT If the values for [TAG_WEIGHT] are "123 " and "456" OUTPUT The outputs are 123 kg and 456 kg, as the default UOM is kg based on the SI in the schema |
ConCat |
func.Concat({"A","B","C"}) |
INPUT If the values are "A", "B", and "C" OUTPUT ABC |
ConvertToHash |
func.ConvertToHash([Name]) |
INPUT If the value for [Name] is “ABC Industries” OUTPUT The output is "170C0B71E9F68CC294A6614BAE3E7347C208EA48" |
DateTimeColumn |
func.DateTimeColumn([InputColumn],"Input value format","") |
INPUT Converts date or date time to invariant format to work with SmartPlant Foundation and Data Validator. For example, the Input Value Format "dd/MMM/yyyy-HH:mm:ss:fff" OUTPUT Output as invariant date format and time "yyyy/MM/dd-HH:mm:ss:fff"’ |
DateTimeColumn |
func.DateTimeColumn([InputColumn], "Input value format", "TimeZone") |
INPUT Converts date time to invariant format date time and time zone. For example, for the Input Value Format and Time Zone "dd/MMM/yyyy-HH:mm:ss:fff","Central Standard Time" OUTPUT Output as invariant date time format and includes time zone. For example "yyyy/MM/dd-HH:mm:ss:fff","GMT" |
Decode |
Func.Decode([Input column], {"IfValueMatches1","SetValueTo1","IfValueMatches2","SetValueTo2"}, "DefaultValue") |
INPUT Looks at the input string and allows you to replace values; Func.decode([inputcol], {"N","North","S","South","E","East","W","West"},"Default") OUTPUT If input is N it is replaced by North, and so on. If input is not N, S, W, or E it returns to the default value |
Divide |
func.Divide("100","10") |
INPUT Divides the numerator by the denominator; func.Divide(100,10) OUTPUT If 100 is the numerator and 10 is the denominator, 10 is the output |
GetDefaultUOMIfMissing |
func.GetDefaultUOMIfMissing("DEVTag", "DEVWeight", [TAG_WEIGHT_UOM]) |
INPUT If the values for [TAG_WEIGHT_UOM] is "kg" and "" OUTPUT The outputs are "kg" and "kg" assuming that the default UOM is kg |
GetDocumentRevisionsCountFromTargetSystem |
func.GetDocumentRevisionsCountFromTargetSystem(“#SPFDesignDocMaster,.Name=[DocumentName]”,”true”) |
INPUT Takes the QueryDef for the unique identification of the document master, and a true/false flag to determine the search configuration. For example, the input can be Name = Document1 and Ignore configuration = true. OUTPUT Returns the number of revisions available for Document1 in all configurations. If the Ignore configuration value is set to “false”, then the count of revisions available for Document1 that are specific to the configuration selected during job creation is considered. |
GetFileName |
func.GetFileName([FilePath]) |
INPUT Returns the file name from a file path. For example, if the file path value is C:\Folder1\SubFolder1\File1.txt. OUTPUT File1.txt |
GetJobDetails |
func.GetJobDetails([PROPERTY_NAME]) |
INPUT Takes the property name from the job as input. For example, VTLJobWorkflowName. OUTPUT Returns the value associated with the specified property. For example, Import Validate Export. |
GetMajorRevisionCodeFromTargetSystem |
func.GetMajorRevisionCodeFromTargetSystem([REV_SCHEME], [REV_ CODE]) |
INPUT Takes the document revision scheme and revision code. For example, REV_CODE=1A, REV_SCHEME=RS_Rev1A OUTPUT Returns the document major revision code specified in the input. For example, Major revision code = 1 |
GetMinorRevisionCodeFromTargetSystem |
func.GetMinorRevisionCodeFromTargetSystem([REV_SCHEME], [REV_ CODE]) |
INPUT Takes the document revision scheme and revision code. For example, REV_CODE=1A, REV_SCHEME=RS_Rev1A OUTPUT Returns the document minor revision code specified in the input. For example, Minor revision code = A |
GetParentObjectClassDef |
func.GetParentObjectClassDef([Tag],[TagClass],[ParentTagName], {"SPXTagInstrument", "SPXTagPipingComponent"}) |
INPUT Takes the object name, object class definition, parent object name, and possible parent object class definitions. For example, ObjectName = "Tag201" ObjectClassDef = "SPXTagEquipment" ParentObjectName = "Tag101" PossibleParentObjectClassDef = {"SPXTagInstrument", "SPXTagEquipment"} OUTPUT Returns the class definition of the parent object. For example, checks if Tag101 is provided in the object name column of the input CSV file. If provided, gets the ObjectClassDef value for Tag101. If not provided, queries for Tag101 and SPXTagInstrument in the target system; If found, returns SPXTagInstrument. If not found, queries for Tag101 and SPXTagEquipment; If found, returns SPXTagEquipment. If the object is still not found, a blank value is returned. |
GetTargetSystemValueIfEmptyReturnDefault |
func.GetTargetSystemValueIfEmptyReturnDefault( "true",”This is default tag description”, "#DEVTag,.Name=[TAG_NAME]",".Description") |
INPUT Replaces input values with the value from the target system, and if the object identified by the QueryDef parameter is not available, returns the default value specified in the input string. For example, TAG_NAME = "Tag01" OUTPUT This goes to the target system and finds the DEVTag with the name "Tag01" and returns its description. If the DEVTag, "Tag01" is not available in the target system, then the description, "This is default tag description" specified in the input string is returned. |
GetValueFromServerManagerSettings |
func.GetValueFromServerManagerSettings (“SubmissionsShareDirectory”) |
INPUT Finds the input string from the properties in the Settings node for a site in the Server Manager. For example, SubmissionsShareDirectory. OUTPUT Returns the value associated with the specified property. For example, C:\ Server Files\Web_Sites\CRServer\Files\Submissions. |
GetValueFromTargetSystem |
func.GetValueFromTargetSystem("true","#DEVTag,.Name=[TAG_NAME]",".Description") |
INPUT Replaces input values with the value from the target system. For example, TAG_NAME = "Tag01" OUTPUT This goes to the target system and finds the DEVTag with the name "Tag01" and return its description. If "Tag01" is not found in the target system, then "NULL" value is returned. |
IndexOf |
func.IndexOf("ABCDEF","C") |
INPUT Finds the index of the search string within the input string. For example, func.IndexOf("ABCDEF","C") OUTPUT The output is 2 because counting begins at zero |
InvertedFilter |
func.InvertedFilter([PROPERTY_NAME],{"DateTime", func.DateTimeColumn([PROPERTY_VALUE], "dd-MM-yyyy", "UTC")}) |
INPUT Checks for the Property name “DateTime”, in an Inverted csv formatted file and if it matches the column name (“DateTime”) then the DateTime column value is evaluated based on the defined function DateTimeColumn. OUTPUT Output as invariant date and time format "yyyy/MM/dd-HH:mm:ss:fff"’ |
Join |
func.Join({"T","A","G"},",") |
INPUT Joins an array of strings separated by a user-defined separator. For example, func.Join({"T","A","G"},",") OUTPUT T,A,G |
Left |
func.Left([TAG_NAME],"5") |
INPUT Adds a number of input string characters to the output string starting from the left, matching the number specified. For example, [TAG_NAME] = PSS-105 OUTPUT PSS-1 |
Length |
func.Length([TAG_NAME]) |
INPUT Length of the input string. For example, [TAG_NAME] = PSS-105 OUTPUT 7 |
Minus |
func.Minus("10","2") |
INPUT Subtracts the second value from the initial value. For example, 10 - 2 OUTPUT 8 |
Multiply |
func.Multiply({"4","2"}) |
INPUT Multiplies two values. For example, 4 x 2 OUTPUT 8 |
PadLeft |
func.PadLeft([TAG_NAME],"A","10") |
INPUT Takes the given input string [TAG_NAME] and adds padding to the left using a padding character (A) to match the string length given in the function (10). For example, TAG_NAME = PSS-105 OUTPUT AAAPSS-105 |
PadRight |
func.PadRight([TAG_NAME],"A","10") |
INPUT Takes the given input string [TAG_NAME] and adds padding to the right using a padding character (A) to match the string length given in the function (10). For example, TAG_NAME = PSS-105 OUTPUT PSS-105AAA |
RegexMatch |
func.RegexMatch([TAG_NAME], "[A-Z]") |
INPUT If uppercase characters in the tag name, returns = True, if no uppercase characters, returns = False. For example, TAG_NAME = TAG01 OUTPUT TAG_NAME = TAG01 returns = True |
RegexMatch |
func.RegexMatch([TAG_NAME], "[^A-Z]") |
INPUT If no uppercase characters in tag name, returns = True. If uppercase characters exist returns = False. (^ = string may not contain alphabetic characters A - Z). For example, [TAG_NAME] = tag01 OUTPUT TAG_NAME = tag01 returns = True |
RegexReplace |
func.RegexReplace([TAG_NAME]," \s+"," ") |
INPUT Uses the second parameter to find the characters to replace and replaces them with the third parameter specified. [TAG_NAME] = "A string with too many spaces in" OUTPUT Where "\s+" means any number of spaces and we are replacing them with one space, so the output is "A string with too many spaces in" |
Replace |
func.Replace([TAG_NAME],"P","E") |
INPUT [TAG_NAME] = P-105 OUTPUT E-105 |
Right |
func.Right([TAG_NAME],"3") |
INPUT Adds a number of input string characters to the output string starting from the right, matching the number specified [TAG_NAME] = PSS-105 OUTPUT 105 |
Right |
func.Right([TAG_NAME],"5") |
INPUT Adds a number of input string characters to the output string starting from the right, matching the number specified [TAG_NAME] = PSS-105 OUTPUT S-105 |
Split |
func.Split("TAG_NAME","_","1") |
INPUT Takes a string and splits it on the given character. The output returned is determined using the integer value provided For example, in the string, TAG_NAME, to get NAME as the output, first argument is the string itself, second argument is the “_” character in the string that is used for splitting, and the third argument is the integer value of the index, that is “1”. OUTPUT The output is NAME because counting begins at zero |
SplitAlphaNumericSequence |
func.SplitAlphaNumericSequence([REV_CODE],"True","1") |
INPUT Takes a string value, a true/false flag to determine if the primary sequence should be returned and a default value to be used in case the value is empty. Splits the string based on the change in alphanumeric characters and returns the first or second part of the string depending on the true or false flag [REV_CODE]="A1" OUTPUT "A" |
SubString |
func.SubString("[TAG_NAME]","0","3") |
INPUT Takes part of a string, starting from a given starting index character number, and continues for a given number of characters OUTPUT TAG |
Sum |
func.Sum({"1","2","3"}) |
INPUT Adds the elements of an integer array, so 1 + 2 + 3 OUTPUT 6 |
ToLower (Lowercase) |
func.ToLower([TAG_NAME]) |
INPUT [TAG_NAME] = PSS-105 OUTPUT pss-105 |
ToUpper (Uppercase) |
func.ToUpper([TAG_NAME]) |
INPUT [TAG_NAME] = pss-105 OUTPUT PSS-105 |
Translate |
func.Translate([TAG_NAME], "TAG-", "tag") |
INPUT This takes the first character of the StringToReplace parameter and replaces it with the first character of the ReplacementString. It then takes the second character of the StringToReplace parameter and replaces it with the second character of the ReplacementString and so on. If the StringToReplace is longer than the ReplacementString then the extra characters are replaced with blanks [TAG_NAME] = TAG-PSS-123 OUTPUT tagPSS123 |
Trim |
func.Trim([TAG_NAME],"E") |
INPUT Trims a character value. [TAG_NAME] = EIS-PSS-105E OUTPUT IS-PSS-105 |
TrimEnd |
func.TrimEnd([TAG_NAME],"0") |
INPUT Trims a character value from the end. [TAG_NAME] = 0001-PSS-10500 OUTPUT 0001-PSS-105 |
TrimStart |
func.TrimStart([TAG_NAME],"0") |
INPUT Trims a character value from the start. [TAG_NAME] = 0001-PSS-10500 OUTPUT 1-PSS-10500 |
YMDColumn |
func.YMDColumn([InputColumn],"Input value format") |
Takes date value of dd/MMM/yyyy and converts to yyyy/MM/dd INPUT 30/Jan/2014 OUTPUT 2014/01/30 |