panSL language reference. Table of contents.
Functions may be used with PropertyTypes of DataType Formula.
This page lists all functions. You may also look up functions by FunctionCategory.
There are two possible styles for using functions:
1) Traditional spreadsheet style with function-name followed by arguments in parantheses
Example:
Year_of_birth = Year(Date_of_birth)
Left_part_of_name = Left(Name,5)
Length_of_name = Length(Name)
Year_of_birth = Date_of_birth.Year()
Left_part_of_name = Name.Left(5)
Length_of_name = Name.Length()
Year_of_birth = Date_of_birth.Year
Length_of_name = Name.Length
Field name | Description |
Abs | Category OtherMathematicalFunctions Abs(number)returns the absolute value of number Example: Abs(-10)returns 10. |
Acos | Category TrigonometricFunctions |
Asin | Category TrigonometricFunctions |
Atan | Category TrigonometricFunctions |
Average | Category StatisticalFunctions See Avg(number1, number2, ..., numberN) |
Avg | Category StatisticalFunctions Avg(number1, number2, ..., numberN)returns the average of the given numbers. Example: Avg(1, 2, 6)returns 3. |
CompareTo | Category StringFunctions CompareTo(string1, string2)or string1.CompareTo(string2) returns -1 if string1 is "before" string2 alphabetically, 1 if string1 is "after" string2 alphabetically and 0 if the strings are equal. Example: "Ann".CompareTo("John")returns -1. |
Concat | Category StringFunctions Concat(string1, string2, ...)concatenates strings. Example:
Concat("John" ," ", "Smith")returns "John Smith" Note that the + Operator may also be used instead of Concat like
See also Join(collection, separator) |
Concatenate | Category StringFunctions See Concat(string1, string2, ...) |
Contains | Category StringFunctions, LogicalFunctions Contains(string1, string2)or string1.Contains(string2) returns "true" if string1 contains string2, otherwise it returns "false". Example: "John Smith".Contains("hn")returns "true". "John Smith".Contains("HN")returns "false" (comparision is case-sensitive). "John Smith".ToUpper.Contains("HN")returns "true". |
Cos | Category TrigonometricFunctions |
Cosh | Category TrigonometricFunctions |
Count | Category StatisticalFunctions Count(String1, String2, ..., StringN)returns N. Example:
Count(1, 2, 6)returns 3. |
CountNeg | Category StatisticalFunctions CountNeg(number1, number2, ..., numberN)returns the count of absolute negative numbers (absolute negative number = number less than zero). Example: CountNeg(1, -3, -6, 0)returns 2. |
CountPos | Category StatisticalFunctions CountPos(number1, number2, ..., numberN)returns the count of absolute positive numbers (absolute positive number = number greater than zero). Example: CountPos(1, 3, -6, 0)returns 2. |
Created | Category DateFunctions, panSLFunctions Created may be used in two ways: A) Createdreturns date and time the CurrentEntity was created. B) Created(identifier)or identifier.Createdreturns date and time the Property identified by identifierwas created. Example: Createdmight for instance return "2012-12-09 10:07:33" Name.Createdmight for instance return "2012-12-09 10:07:33" Name.Created.DatePartmight for instance return "2012-12-09" Name.Created.TimePartmight for instance return "10:07:33" |
Creator | Category panSLFunctions See ImpersonationEntity |
CurrentEntity | Category panSLFunctions Refers to the entity currently in scope. This Function is usually superfluous. For instance, given an entity Person with Name, it is not necessary to write CurrentEntity.Name. If is sufficient to just specify Name. |
DatePart | Category DateFunctions DatePart(date)or date.DatePartreturns the date part (that is, the date without any time information) for date. Example: "2012-12-09 10:07:33".DatePartreturns "2012-12-09" |
Day | Category DateFunctions Day(date)or date.Dayreturns the day in the month of date Example: "2012-12-09".Dayreturns 9. "12/09/2012".Dayreturns 9. |
DayOfWeek | Category DateFunctions DayOfWeek(date)or date.DayOfWeekreturns the weekday which date falls on. Example: "2012-12-09".DayOfWeekreturns "Sunday". "12/09/2012".DayOfWeekreturns "Sunday". Use Weekday(date)if you, instead of the name of the day, need the number of the day (0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday) |
DayOfYear | Category DateFunctions DayOfYear(date)or date.DayOfYearreturns the day of the year (1 to 365 / 366) for date. Example: "2012-02-03".DayOfYearreturns 34. "3/2/2012".DayOfYearreturns 34. |
E | Category ConstantFunctions, OtherMathematicalFunctions Ereturns the value of E (approximately 2.71828) |
EndsWith | Category StringFunctions, LogicalFunctions EndsWith(string1, string2)or string1.EndsWith(string2) returns "true" if string1 ends with string2, otherwise it returns "false". Example: "John Smith".EndsWith(" Smith")returns "true". "John Smith".EndsWith(" smith")returns "false" (comparision is case-sensitive). "John Smith".ToLower.EndsWith(" smith")returns "true". |
EntityIdentification | Category panSLFunctions By entity identification is meant (for a given Root PropertyType) a string of the values of PropertyTypes specified with IdentificationUsefulness Essential If no PropertyTypes have IdentificationUsefulness Essential then all PropertyTypes of DataType ShortText (the default DataType) are used. EntityIdentification is the same value that a typical panSL Implementation will use for link text when creating hyperlinks to an Entity. EntityIdentification may be used in two ways: A) EntityIdentificationreturns entity identification for the CurrentEntity. B) EntityIdentification(identifier)or identifier.EntityIdentificationreturns entity identification for the Property identified by identifier. Example:
In the example Summary1 and Summary2 will return the same values. (If no PropertyTypes are designated Essential in the panSL schema then a typical panSL Implementation like MyLittleDatabase will use for EntityIdentification all ShortText fields. In the case above the default EntityIdentification would become First_name + " " + Last_name). |
Equals | Category StringFunctions, LogicalFunctions Equals(string1, string2)or string1.Equals(string2) returns "true" if string1 equals string2, otherwise it returns "false". Example: "John Smith".Equals("John Smith")returns "true". "John Smith".Equals("john smith")returns "false" (comparision is case-sensitive). "John Smith".ToLower.Equals("john smith")returns "true". For numbers the equals-operator = may be used, like 5 = 2 |
Exp | Category OtherMathematicalFunctions Exp(number)returns the natural exponentatial of number Example: Exp(1)returns E (approximately 2.71828) Exp(0)returns 1. |
ForEach | Category panSLFunctions ForEach iterates through a collection of objects, doing something with each object and returns a new collection of objects. ForEach(collection,object => expression)or collection.ForEach(object => expression) returns a new collection consisting of the value of expression for each object in the original collection object represents the successive objects from collection as the iteration proceeds Example (given a schema with PropertyType Phone_number and Cardinality ZeroToMany): Phone_number_without_leading_digit = Phone_number.ForEach(x => x.Substring(1)) ForEach can be used for summing up data from related entities like this example where activities are registred for different projects but usually a simpler construction may be used:
In the example Total_project_cost1 demonstrates use of ForEach but Total_project_cost2 gives just the same result in a simpler manner. |
Hyperlink | Category panSLFunctions Hyperlink may be used in five different ways: A) Hyperlinkcreates a clickable hyperlink to the CurrentEntity. B) Hyperlink(entityidentifier)or entityidentifier.Hyperlinkcreates a clickable hyperlink to the Property entityidentifier. C) Hyperlink(entityidentifier,linktext)or entityidentifier.Hyperlink(linktext)creates a clickable hyperlink to the Property entityidentifier with linktext. D) Hyperlink(url)or url.Hyperlinkcreates a clickable hyperlink to url. Note the meaning of D). It could be natural to assume that D) creates a hyperlink to the CurrentEntity with a specified link-text. E) Hyperlink(url,linktext)or url.Hyperlink(linktext)creates a clickable hyperlink to url with linktext. Example: Hyperlinkcreates a clickable hyperlink to the CurrentEntity. Car_ownership.RelatedEntity.Hyperlinkcreates a clickable hyperlink to the related Entity for Car_ownership (given a schema with relation between Owner and Car). Car_ownership.RelatedEntity.Hyperlink("This car's owner")creates a clickable hyperlink to the related Entity for Car_ownership (in this case the owner of a car). "http://panSL.org".Hyperlinkcreates a clickable hyperlink to the http://panSL.org. "http://panSL.org".Hyperlink("The great new schema language")creates a clickable hyperlink to the http://panSL.org with The great new schema language" as link-text. |
If | Category LogicalFunctions, panSLFunctions If(expression, resultIfTrue, resultIfFalse) evaluates expression and returns appropriate result. Example: If(Income < 1000, 10%, 15%) returns 10% if Income is less than 1000. If income equals 1000 or more then the function returns 15%. |
IfNot | Category LogicalFunctions, panSLFunctions IfNot(expression, resultIfFalse, resultIfTrue) evaluates expression and returns appropriate result. Example: IfNot(Income < 1000, 15%, 10%) returns 15% if Income is less than 1000. If income equals 1000 or more then the function returns 10%. |
ImpersonationEntity | Category panSLFunctions By ImpersonationEntity is meant the Entity that created a given entity. The alias "Creator" may be used. ImpersonationEntity may be used in two ways: A) ImpersonationEntityreturns impersonation entity for the CurrentEntity (returns the creator of the CurrentEntity) B) ImpersonationEntity(identifier)or identifier.ImpersonationEntityreturns impersonation entity for the Property identified by identifier. Example: ImpersonationEntity.EntityIdentificationreturns EntityIdentification for the entity that created the CurrentEntity. ImpersonationEntity.EntityLinkreturns a hyperlink complete with database-name pointing to the entity that created the CurrentEntity. ImpersonationEntity.ImpersonationEntity.EntityIdentificationreturns EntityIdentification for the entity that created the entity that created the CurrentEntity. |
ImpersonationProperty | Category panSLFunctions See ImpersonationEntity |
IndexOf | Category StringFunctions IndexOf(string1, string2)or string1.IndexOf(string2) returns the position of the first occurrence of string2 within string1. First position is 0. Example: "ABCDEF".IndexOf("BCD")returns 1. See also Contains(string1, string2) |
Insert | Category StringFunctions Insert(string1, position, string2)or string1.Insert(position, string2) inserts string2 into string1 at position (0 = first position). Example: "ABCDEF".Insert(3, "xy")returns "ABCxyDEF". |
Join | Category StringFunctions Join(collection, separator)or collection.Join(separator) joins together strings in collection separated by separator. Example (given a schema with PropertyType called Phone_number with Cardinality ZeroToMany):
See also Concat(string1, string2, ...) |
LastIndexOf | Category StringFunctions LastIndexOf(string1, string2)or string1.LastIndexOf(string2) returns the position of the last occurrence of string2 within string1. First position is 0. Example: "AxBx".LastIndexOf("x")returns 3. |
Left | Category StringFunctions Left(string,n)or string.Left(n)returns first n characters of string If string is shorter than n then the whole string is returned. Example
"ABCDEF".Left(3)returns "ABC". |
Len | Category StringFunctions Len(string)or string.Lenreturns length of string Example: "ABC".Lenreturns 3. |
Length | Category StringFunctions See Len(string) |
Ln | Category OtherMathematicalFunctions Ln(number)returns the natural logarithm of number Example: Ln(E)returns 1. Ln(1)returns 0. |
Log10 | Category OtherMathematicalFunctions Log10(number)returns the logarithm to base 10 of number Example: Log10(10)returns 1. Log10(1)returns 0. Log(number)may be used instead of Log10(number). |
Lower | Category StringFunctions Lower(string)or string.Lowerreturns lower case version of string Example: "AbcDef".Lowerreturns "abcdef" |
Max | Category StatisticalFunctions Max(number1, number2, ..., numberN)returns the greatest number among the numbers given. Example: Max(1, 3, -6, 0)returns 3. |
Mid | Category StringFunctions Mid may be used in two ways: A) Mid(string,pos)or string.Mid(pos)returns all characters from string starting at position pos (1 = first position) If pos is longer than the length of string then an empty string is returned. B) Mid(string,pos,n)or string.Mid(pos,n) returns n characters from string starting at position pos (1 = first position) If the part of string after pos is shorter than n then the whole string is returned. Use Substring(string,pos)/ Substring(string,pos,n)if you prefer to count 0 as first position. Example: "ABCDEFG".Mid(2)returns BCDEFG. "ABCDEFG".Mid(2,3)returns BCD. "ABCDEFG".Mid(4,3)returns FG. |
Min | Category StatisticalFunctions Min(number1, number2, ..., numberN)returns the smallest number among the numbers given. Example: Min(1, 3, -6, 0)returns 3. |
Month | Category DateFunctions Month(date)or date.Monthreturns the month of date Example: "2012-12-09".Month |
Now | Category DateFunctions Nowreturns the current date and time. A possible use for Now is to specify Default=Nowas a DefaultFormula for a Property. Note: If you want to know when an Entity was created in an ordinary Formula you may use CurrentEntity.Created. Many panSL implementations will cache Formula evaluations. If you use Now in an ordinary Formula it will not necessarily be recognized as changing all the time. |
Null | Category ConstantFunctions Null returns a null-value (nothing). Null is usually treated as equivalent to an empty string (the literal string ""). (Null is not to be confused by the Simplification Nothing.) |
PadLeft | Category StringFunctions PadLeft(string1, length, string2)or string1.PadLeft(length, string2) adds repeated instances of the first character in string2 to the left of string1 until a length of length has been attained. Example: "ABCDEF".PadLeft(10, " ")returns " ABCDEF". See also Space(integer) |
PadRight | Category StringFunctions PadLeft(string1, length, string2)or string1.PadLeft(length, string2) adds repeated instances of the first character in string2 to the right of string1 until a length of length has been attained. Example: "ABCDEF".PadRight(10, " ")returns "ABCDEF ". Space(integer) |
panCR | Category ConstantFunctions Returns a string consisting of the Carriage return character (ASCII value 13) See also panCRLF |
panCRLF | Category ConstantFunctions Returns a string consisting of the Carriage return character (ASCII value 13) plus Linefeed character (ASCII value 10). Use this Function if you want to generate new lines (separate lines) in your Functions. Example: Address = Street_name + Street_number + panCRLF + Postal_code + " " + City |
panLF | Category ConstantFunctions Returns a string consisting of the Linefeed character (ASCII value 10) See also panCRLF |
panQuote | Category ConstantFunctions Returns a string consisting of a quote ". Example: "They said: " + panQuote + "Come!" + panQuotereturns They said: "Come!" |
panTAB | Category ConstantFunctions Returns a string consisting of the Tab character (ASCII value 9). |
PI | Category ConstantFunctions, OtherMathematicalFunctions PIreturns the value of PI (approximately 3,14159265) |
PrimaryKey | Category panSLFunctions PrimaryKey may be used in two ways: A) PrimaryKeyreturns the primary key of the CurrentEntity. B) PrimaryKey(identifier)or identifier.PrimaryKeyreturns the primary key of the Property identified by identifier. B) is a somewhat contrived example but many Implementations of panSL will actually create unique identifiers for every Property created, not only Entities. Example: PrimaryKeymight for instance return 42 Name.PrimaryKeymight for instance return 43 |
Quarter | Category DateFunctions Quarter(date)or date.Quarterreturns the quarter (1-4) for date. Example: "2012-02-03".DayOfYearreturns "Q1". "3/2/2012".DayOfYearreturns "Q1". "2012-12-09".DayOfWeekreturns "Q4". "12/09/2012".DayOfWeekreturns "Q4". |
Rad | Category ConversionFunctions Rad(AngleInDegrees)or AngleInDegress.ToRadreturns AngleInRadians. Converts degrees to radians. It is equivalent to AngleInDegrees/360*2*PI Note that all TrigonometricFunctions expect angles in radians. Example: Rad(360)returns approximately 6.28 (2*PI) Rad(90)returns approximately 1.57 (PI/2) |
RelatedEntity | Category panSLFunctions RelatedEntity(identifier)or identifier.RelatedEntityreturns the related entity. Only valid when identifier refers to a Property of PropertyType Relation. Example:
In a typical panSL Implementation if will often be unnecessary to manually construct information with RelationEntity as the Implementation will usually automatically link to related entities using RelatedEntity.EntityIdentification. |
Remove | Category StringFunctions Remove(string,position)or string.Remove(position) removes characters from string from position (0 = first position) Example: "ABCDEF".Remove(3)returns "ABC". |
Replace | Category StringFunctions Replace(string1, string2, string3)or string1.Replace(string2, string3) replaces all occurrences of string2 within string1 by string3. Example: "ABCDEF".Replace("CD", "xy")returns "ABxyEF". |
Right | Category StringFunctions Right(string,n)or string.Right(n)returns last n characters of string If string is shorter than n then the whole string is returned. Example: "ABCDEF".Right(3)returns "DEF". |
Sgn | Category OtherMathematicalFunctions Sgn(number)returns the sign of number as -1, 0 or 1. Example: Sgn(-10)returns -1 Sgn(0)returns 0 Sgn(7)returns 1 |
Sign | Category OtherMathematicalFunctions See Sgn(number) |
Sin | Category TrigonometricFunctions |
Sinh | Category TrigonometricFunctions |
Space | Space(integer)returnes integer number of spaces. Example: Space(3returns " " (three spaces) Note: Space(integer)is equivalent to "".PadLeft(integer, " ") |
Split | Category StringFunctions Split(string1, string2)or string1.Split(string2) splits string1 into elements separated by string2. Example: "John Smith".Split(" ")returns the array ("John", "Smith"). |
Sqrt | Category OtherMathematicalFunctions Sqrt(number)returns the square root of number Example: Sqrt(9)returns 3. |
SquareRoot | Category OtherMathematicalFunctions See Sqrt(number) |
StartsWith | Category StringFunctions, LogicalFunctions StartsWith(string1, string2)or string1.StartsWith(string2) returns "true" if string1 starts with string2, otherwise it returns "false". Example: "John Smith".StartsWith("John")returns "true". "John Smith".StartsWith("john")returns "false" (comparision is case-sensitive). "John Smith".ToLower.StartsWith("john")returns "true". |
Substring | Category StringFunctions Substring may be used in two ways: A) Substring(string,pos)or string.Substring(pos)returns all characters from string starting at position pos (0 = first position) If pos is equal to or longer than the length of string then an empty string is returned. B) Substring(string,pos,n)or string.Substring(pos,n)returns n characters from string starting at position pos (0 = first position) If the part of string after pos is shorter than n then the rest of the string after pos is returned. Use Mid(string,pos,n)if you prefer to count 1 as first position. For Java-programmers: Note that n is number of characters, not end-position. Example: "ABCDEFG".Substring(2)returns CDEFG. "ABCDEFG".Substring(2,3)returns CDE. "ABCDEFG".Substring(5,3)returns FG. |
Sum | Category StatisticalFunctions Sum(number1, number2, ..., numberN)returns the sum of the given numbers. Example:
Sum(1, 2, 3)returns 6. |
SumNeg | Category StatisticalFunctions Same as Sum(number1, number2, ..., numberN)but sums only negative numbers. |
SumPos | Category StatisticalFunctions Same as Sum(number1, number2, ..., numberN)but sums only positive numbers. |
Tan | Category TrigonometricFunctions |
Tanh | Category TrigonometricFunctions |
TimeOfDay | Category DateFunctions TimePart(date)or date.TimePartreturns the time part (that is, the time of the day) for date. Example: "2012-12-09 10:07:33".TimeOfDayreturns "10:07:33" |
TimePart | Category DateFunctions See TimePart(date) |
ToLower | Category StringFunctions See Lower(string) |
ToLowerCamelCase | Category StringFunctions ToLowerCamelCase(string)or string.ToLowerCamelCasereturns lower camel case version of string Example: "Alice had a little lamb".ToLowerCamelCasereturns "aliceHadALittleLamb" Note that characters other than A-Z, a-z, 0-9 and _ are removed. |
ToLowerCase | Category StringFunctions See Lower(string) |
ToPascalCase | Category StringFunctions ToPascalCase(string)or string.ToPascalCasereturns Pascal case version of string Example: "Alice had a little lamb".ToPascalCasereturns "AliceHadALittleLamb" Note that characters other than A-Z, a-z, 0-9 and _ are removed. |
ToRad | Category ConversionFunctions See Rad(AngleInDegrees) |
ToString | Category StringFunctions ToString(object, format)or object.ToString(format) convert object to a string as specified by format. Example of some valid format specifiers: If object is a Date: "yyyy-MM-dd HH:mm:ss", "MM/dd/yy". If object is a Decimal (Double): "0.00". If object is an Integer: "### ### ##0". The AgoRapide and MyLittleDatabase Implementations of panSL support the same format specifiers for ToString as used in the Microsoft .NET architecture (that is format specifiers as used with C# and Visual Basic among other). For more information, see Formatting Types at http://msdn.microsoft.com. |
ToUpper | Category StringFunctions See Upper(string) |
ToUpperCase | Category StringFunctions See Upper(string) |
Trim | Category StringFunctions Trim(string)or string.Trimremoves leading and trailing white space from string Example: " AbcDef ".Trimreturns "AbcDef" See also TrimLeft(string)and TrimRight(string) |
TrimEnd | Category StringFunctions See TrimRight(string) |
TrimLeft | Category StringFunctions TrimLeft(string)or string.TrimLeftremoves leading white space from string Example: " AbcDef ".TrimLeftreturns "AbcDef " See also Trim(string)and TrimRight(string) |
TrimRight | Category StringFunctions TrimRight(string)or string.TrimRightremoves trailing white space from string Example: " AbcDef ".TrimRightreturns " AbcDef" See also Trim(string)and TrimLeft(string) |
TrimStart | Category StringFunctions See TrimLeft(string) |
Trunc | Category OtherMathematicalFunctions Trunc(number)removes the fractional part of number Example: Trunc(9.3)returns 9. Trunc(-7.3)returns 7. |
Truncate | Category OtherMathematicalFunctions See Trunc(number) |
Upper | Category StringFunctions Upper(string)or string.Upperreturns upper case version of string Example: "AbcDef".Upperreturns "ABCDEF" |
URLEncode | Category StringFunctions URLEncode(string)or string.URLEncodeencodes string for use in a URL. Example: "cash & carry".URLEncodereturns "cash+%26+carry". Hyperlink("http://www.google.com/search?q=" + Name.URLEncode , "Google '" + Name + "'") creates a Hyperlink for looking up Name with the search engine Google (given that the Schema contains a PropertyType named Name). |
Value | Category StringFunctions, ConversionFunctions Value(string)or string.Valuereturns value of string Example: "42".Valuereturns 42. Note that this function is somewhat superfluous as strings are always automatically converted to numbers as needed. |
Weekday | Category DateFunctions Weekday(date)or date.Weekdayreturns the weekday which date falls on. 0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday Example: "2012-12-09".DayOfWeekreturns 0. "12/09/2012".DayOfWeekreturns 0. Use DayOfWeek(date)if you, instead of the number of the day, need the name of the day |
Year | Category DateFunctions Year(date)or date.Yearreturns the year of date Example:
"2012-12-09".Yearreturns 2012. "12/09/2012".Yearreturns 2012. |
panSL language reference. Table of contents.
More samples available at http://panSL.org/samples/.
Last updated 2012-05-02