Function.Data.General

From GAB Help
Jump to: navigation, search

Available commands

Contents

Function.Data.DataTable.AddColumn

Syntax

Function.Data.DataTable.AddColumn(datatable name [string], column name [string], column data type [string])

Function.Data.DataTable.AddColumn(datatable name [string], column name [string], column data type [string], default value [any])

Function.Data.DataTable.AddColumn(datatable name [string], column name [string], column data type [string], default value [any], maximum length [long])

Arguments

datatable name

string

column name

string

column data type

string

default value

any

maximum length

long

Examples

F.Data.DataTable.Create("Instructors", True)
F.Data.DataTable.AddColumn("Instructors", "Name", "String")
F.Data.DataTable.AddColumn("Instructors", "Age", "Long", 99)
F.Data.DataTable.AddColumn("Instructors", "School", "String", "N/A", 20)

Remarks

  • Note: The specified default value should match the data type of the column being created


Function.Data.DataTable.AddDisplayPartColumn

This function looks up short part numbers in a datatable column and places the part (and optionally revision) in other columns in the datatable. The target part (and optionally revision) columns will be automatically created within the datatable, if they are not present. If the overload containing only the target part column name is used, the long part number and long revision will be concatenated into the target part number field.

Syntax

Function.Data.DataTable.AddDisplayPartColumn(datatable name [string], part type [enum integer], short part column name [string], long part column name [string], long revision column name [string])

or

Function.Data.DataTable.AddDisplayPartColumn(datatable name [string], part type [enum integer], short part column name [string], long part column name [string])

Arguments

datatable name

string

part type

enum integer

The Part Type enumerable values are as follows:

  • 0 - Standard part number
  • 1 - Customer's part number
  • 2 - Manufacturer's part number
  • 3 - User's part number
short part column name

string

long part column name

string

long revision column name

string

Examples

Remarks


Function.Data.DataTable.AddExpressionColumn

This function creates a datatable column of the name and data type specified, filled with data computed using the given expression.

Valid data types are: Boolean Date Float String Long


Expression notes (from MSDN):

Expression Syntax

When you create an expression, use the ColumnName property to refer to columns. For example, if the ColumnName for one column is "UnitPrice", and another "Quantity", the expression would be as follows: "UnitPrice * Quantity"

When you create an expression for a filter, enclose strings with single quotation marks: "LastName = 'Jones'"

If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.

And Between Child False In Is Like Not Null Or Parent True

If a column name satisfies one of the above conditions, it must be wrapped in either square brackets or the "`" (grave accent) quotes. For example, to use a column named "Column#" in an expression, you would write either "[Column#]":

Total * [Column#]

or "`Column#`":

Total * `Column#`

If the column name is enclosed in square brackets then any ']' and '\' characters (but not any other characters) in it must be escaped by prepending them with the backslash ("\") character. If the column name is enclosed in grave accent characters then it must not contain any grave accent characters in it. For example, a column named "Column[]\" would be written:

Total * [Column[\]\\]

or

Total * `Column[]\`


User-Defined Values User-defined values may be used within expressions to be compared with column values. String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character). Date values should be enclosed within pound signs (#) or single quotes (') based on the data provider. Decimals and scientific notation are permissible for numeric values. For example:

"FirstName = 'John'"

"Price <= 50.00"

"Birthdate < #1/31/82#"

For columns that contain enumeration values, cast the value to an integer data type. For example:

"EnumColumn = 5"


Parsing Literal Expressions All literal expressions must be expressed in the invariant culture locale. When DataTable parses and converts literal expressions, it always uses the invariant culture, not the current culture.

String literals are identified when there are single quotes surrounding the value. For example:

'John'

Boolean literals are true and false; they are not quoted in expressions.

Integer literals [+-]?[0-9]+ are treated as System.Int32, System.Int64 or System.Double. System.Double can lose precision depending on how large the number is. For example, if the number in the literal is 2147483650, DataSet will first attempt to parse the number as an Int32. This will not succeed because the number is too large. In this case DataSet will parse the number as an Int64, which will succeed. If the literal was a number larger than the maximum value of an Int64, DataSet will parse the literal using Double.

Real literals using scientific notation, such as 4.42372E-30, are parsed using System.Double.

Real literals without scientific notation, but with a decimal point, are treated as System.Decimal. If the number exceeds the maximum or minimum values supported by System.Decimal, then it is parsed as a System.Double. For example:

142526.144524 will be converted to a Decimal.

345262.78036719560925667 will be treated as a Double.


Operators Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

When you create comparison expressions, the following operators are allowed: < > <= >= <> = IN LIKE

The following arithmetic operators are also supported in expressions: + (addition) - (subtraction)

  • (multiplication)

/ (division) % (modulus)

String Operators To concatenate a string, use the + character. The value of the CaseSensitive property of the DataTable determines whether string comparisons are case-sensitive

Wildcard Characters Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

"ItemName LIKE '*product*'" "ItemName LIKE '*product'" "ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.


Parent/Child Relation Referencing A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.

When a child has more than one parent row, use Parent(RelationName).ColumnName. For example, the Parent(RelationName).Price references the parent table’s column named Price via the relation.

A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.

If a table has more than one child, the syntax is: Child(RelationName). For example, if a table has two child tables named Customers and Orders, and the DataRelation object is named Customers2Orders, the reference would be as follows: Avg(Child(Customers2Orders).Quantity)


Aggregates The following aggregate types are supported: Sum (Sum) Avg (Average) Min (Minimum) Max (Maximum) Count (Count) StDev (Statistical standard deviation) Var (Statistical variance)

Aggregates are ordinarily performed along relationships. Create an aggregate expression by using one of the functions listed earlier and a child table column as detailed in Parent/Child Relation Referencing that was discussed earlier. For example: Avg(Child.Price) Avg(Child(Orders2Details).Price)

An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price": Sum(Price)

If you use a single table to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the column.

If a table has no rows, the aggregate functions will return null.

Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown in the following section.

An aggregate can only be applied to a single column and no other expressions can be used inside the aggregate.


Functions The following functions are also supported:

CONVERT Converts particular expression to a specified .NET Framework Type.

Syntax Convert(expression, type)

Arguments expression -- The expression to convert. type -- The .NET Framework type to which the value will be converted.

Example Expression="Convert(total, 'System.Int32')"

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only. Most of these primitive data types map directly to GAB counterparts.

LEN Gets the length of a string

Syntax LEN(expression)

Arguments expression -- The string to be evaluated.

Example Expression="Len(ItemName)"


ISNULL Checks an expression and either returns the checked expression or a replacement value.

Syntax ISNULL(expression, replacementvalue)

Arguments expression -- The expression to check. replacementvalue -- If expression is null, replacementvalue is returned.

Example Expression="IsNull(price, -1)"


IIF Gets one of two values depending on the result of a logical expression.

Syntax IIF(expr, truepart, falsepart)

Arguments expr -- The expression to evaluate. truepart -- The value to return if the expression is true. falsepart -- The value to return if the expression is false.

Example Expression = "IIF(total>1000, 'expensive', 'dear')


TRIM Removes all leading and trailing blank characters like \r, \n, \t, ' '

Syntax TRIM(expression)

Arguments expression -- The expression to trim.


SUBSTRING Gets a sub-string of a specified length, starting at a specified point in the string.

Syntax SUBSTRING(expression, start, length)

Arguments expression -- The source string for the substring. start -- Integer that specifies where the substring starts. length -- Integer that specifies the length of the substring.

Example Expression = "SUBSTRING(phone, 7, 8)"

Syntax

Function.Data.DataTable.AddExpressionColumn(datatable name [string], column name [string], column data type [string], expression [string])

Arguments

datatable name

string

column name

string

column data type

string

expression

string

Examples

Remarks


Function.Data.DataTable.AddRelation

This function creates a logical relationship between two DataTables. The relationship can be created on one or more parent columns, to the appropriate child column(s). Multiple columns are delimited with a *!*.

Syntax

Function.Data.DataTable.AddRelation(parent datatable [string], parent columns [string], child datatable [string], child columns [string])

Function.Data.DataTable.AddRelation(parent datatable [string], parent columns [string], child datatable [string], child columns [string], relationship name [string])

Arguments

parent datatable

string

parent columns

string

child datatable

string

child columns

string

relationship name

string

Examples

 'Creating parent datatable
Function.Data.DataTable.Create("Runner", True)
Function.Data.DataTable.AddColumn("Runner", "RunnerName", "String")

'Creating 1st child datatable
Function.Data.Datatable.AddTable("Runner", "RunningLog")
Function.Data.DataTable.AddColumn("Runner$RunningLog", "Runner", "String")

'Adding relations between tables
Function.Data.DataTable.AddRelation("Runner", "RunnerName", "Runner$RunningLog", "Runner")

Remarks


Function.Data.DataTable.AddRow

Syntax

Function.Data.DataTable.AddRow(datatable name [string], column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Arguments

datatable name

string

column 0 name

string

column 0 value

any

..
column n name

string

column n value

any

Examples

F.Data.DataTable.Create("Instructors", True)
F.Data.DataTable.AddColumn("Instructors", "Name", "String")
F.Data.DataTable.AddColumn("Instructors", "Age", "Long")
F.Data.DataTable.AddRow("Instructors", "Name", "Sally Mason", "Age", 37)

Remarks


Function.Data.DataTable.AddRowByMap

Function.Data.DataTable.AddRowByMap

Syntax

Arguments

Examples

Remarks


Function.Data.DataTable.AddRowChangedEventHandler

This function sets the specified subroutine to handle the RowChanged event on the specified DataTable. Event handlers must be added to each table and subtable desired. The event arguments are as follows: NAME, UID, ROWSTATE, INDEX, and ACTION.

Syntax

Function.Data.DataTable.AddRowChangedEventHandler(DataTable Name [String], Subroutine name [String])

Arguments

DataTable Name

String

Subroutine name

String

Examples

Remarks

  • The name is the table (or table$subtable name) the event is being raised from. The UID is the callstack ID of the datatable (-1 for globally-scoped datatables). The rowstate can have the following values:

4 - Added

8 - Deleted

1 - Detached

16 - Modified

2 - Unchanged


  • The index is the row ordinal of the datarow within the datatable. Action can have the following values:

0 - Nothing: The row has not changed.

1 - Delete: The row was deleted from the table.

2 - Change: The row has changed.

4 - Rollback: The most recent change to the row has been rolled back.

8 - Commit: The changes to the row have been committed.

16 - Add: The row has been added to the table.

32 - ChangeOriginal: The original version of the row has been changed.

64 - ChangeCurrentAndOriginal: The original and the current versions of the row have been changed.


Function.Data.DataTable.AddRunningTotalColumn

Function.Data.DataTable.AddRunningTotalColumn

Syntax

Arguments

Examples

Remarks


Function.Data.DataTable.AddTable

This function creates a sub-table to the specified datatable. The sub-table has the same scope as the base datatable.

Syntax

Function.Data.DataTable.AddTable(datatable name [string], sub-table name [string])

Arguments

datatable name

string

sub-table name

string

Examples

Remarks


Function.Data.DataTable.Clone

This function creates an empty DataTable or sub-table with the same structure as the source DataTable or subtable. If the scope-less overload is used, the scope will be set to local. Note: If a sub-table is created with this function, the scope will be ignored, as the scope is determined by the base DataTable. Note: The source and target DataTables do not need to be contained in the same base DataTable.

Syntax

Function.Data.DataTable.Clone(source datatable name [string], target datatable name [string])

Function.Data.DataTable.Clone(source datatable name [string], target datatable name [string], global scope [boolean])

Arguments

source datatable name

string

target datatable name

string

global scope

boolean

Examples

'Creating parent datatable
F.Data.DataTable.Create("Runner", true)
Function.Data.DataTable.AddColumn("Runner", "RunnerName", "String")
Function.Data.DataTable.AddRow("Runner", "RunnerName", "Lucy")
Function.Data.DataTable.AddRow("Runner", "RunnerName", "Rocky")
Function.Data.DataTable.AddRow("Runner", "RunnerName", "JoeDom")

'Clone dataTable
F.Data.DataTable.Clone("Runner", "Copy", true)

The "Copy" DataTable contains the same structure of Runner without any rows.

Remarks


Function.Data.DataTable.Close

Syntax

Function.Data.DataTable.Close(datatable name [string])

Arguments

Datatable Name

String

Examples

F.Data.DataTable.Create("Instructors", True)
F.Data.DataTable.Close("Instructors")

Remarks


Function.Data.DataTable.Compute

This returns a computed value when an expression and filter are passed. Expressions can consist of mathematical functions containing column names, and operations like SUM, AVG, MIN, MAX, COUNT, STDEV, and VAR. The filter is functionally similar to the criteria specified when creating a DataView.

Syntax

Function.Data.DataTable.Compute(datatable name [string], expression [string], filter [string], return value [any])

Arguments

datatable name

string

expression

string

filter

string

return value

any

Examples

V.Local.fTotal.Declare(Float)

'Create the DataTable Instructors
F.Data.DataTable.Create("Instructors", True)

'Add a Columns to the DataTable
F.Data.DataTable.AddColumn("Instructors", "Name", "String")
F.Data.DataTable.AddColumn("Instructors", "Title", "String")
F.Data.DataTable.AddColumn("Instructors", "Classes", "Long")

'Add Rows to the DataTable
F.Data.DataTable.AddRow("Instructors", "Name", "Ryan Young", "Title", "Manager", "Classes",2)
F.Data.DataTable.AddRow("Instructors", "Name", "Jan Ortiz", "Title", "Consultant","Classes", 1)
F.Data.DataTable.AddRow("Instructors", "Name", "Josh Withrow", "Title", "Consultant","Classes", 4)
F.Data.DataTable.AddRow("Instructors", "Name", "Sarah Crow", "Title", "Programmer/Analyst","Classes", 4)
F.Data.DataTable.AddRow("Instructors", "Name", "Chris Okamuro", "Title", "Chief Technology Officer","Classes",3)

'Get the Sum of Classes taught by Consultants
F.Data.DataTable.Compute("Instructors","SUM(Classes)","Title = 'Consultant'",v.Local.fTotal)

'fTotal = 5
F.Intrinsic.UI.Msgbox(v.Local.fTotal)

F.Data.DataTable.Close("Instructors")


Remarks


Function.Data.DataTable.Create

Syntax

Function.Data.DataTable.Create(datatable name [string])

Function.Data.DataTable.Create(datatable name [string], global scope [Boolean])

Arguments

datatable name

string

global scope

Boolean

Examples

'Create the DataTable Instructors with Global Scope
F.Data.DataTable.Create("Instructors", True)

F.Data.DataTable.Close("Instructors")


Remarks


Function.Data.DataTable.CreateFromCSV

This function creates a datatable with the name specified, filling it with the supplied data from the csv file. The field names and data types are passed in strings delimited by *!*, and must be listed in the order in which they appear within the data string.

Syntax

Function.Data.DataTable.CreateFromCSV(datatable name [string], CSV FQN [string], data types [string], global [boolean])

Function.Data.DataTable.CreateFromCSV(datatable name [string], CSV FQN [string], field names [string], data types [string], global [boolean])

Arguments

datatable name

string

CSV FQN

string

field names

string delimited by *!*

data types

string delimited by *!*

global scope

Boolean

Examples

V.Local.sRet.Set("X:\Test_CreateFromCSV.csv")
Function.Data.DataTable.CreateFromCSV("LucyTableFromCSV_1",v.Local.sRet,"string*!*string*!*string*!*long", true) 
Function.Data.DataTable.CreateFromCSV("LucyTableFromCSV_1$Table11",v.Local.sRet,"Last*!*First*!*Job*!*Salary", "string*!*string*!*string*!*string", true) 

Remarks


Function.Data.DataTable.CreateFromSQL

Syntax

Function.Data.DataTable.CreateFromSQL(datatable name [string], connection name [string], SQL query [string])

Function.Data.DataTable.CreateFromSQL(datatable name [string], connection name [string], SQL query [string], global scope [Boolean])

Arguments

datatable name

string

connection name

string

SQL query

string

global scope

Boolean

Examples

'Open Connection
Function.ODBC.Connection!Con.OpenConnection(V.Ambient.PDSN,V.Ambient.Puser,V.Ambient.PPass)

'Create Datatable with SQL Query
F.Data.DataTable.CreateFromSQL("Vendor", "Con", "Select * From INVENTORY_MSTR")

Remarks


Function.Data.DataTable.CreateFromString

This function creates a datatable with the name specified, filling it with the supplied data. The data can contain both column and row data, with parameter-supplied delimiters. The field names and data types are passed in strings delimited by *!*, and must be listed in the order in which they appear within the data string. By default, datatables are created with local scope, but the global scope parameter can be passed True.

Syntax

Function.Data.DataTable.CreateFromString(datatable name [string], data [string], field names [string], data types [string], inner delimiter [string], outer delimiter [string])

Function.Data.DataTable.CreateFromString(datatable name [string], data [string], field names [string], data types [string], inner delimiter [string], outer delimiter [string], global scope [Boolean])

Arguments

datatable name

string

data

string

field names

string

data types

string

inner delimiter

string

outer delimiter

string

global scope

Boolean

Examples

v.Local.sData.Declare(String)
v.Local.sColumns.Declare(String)
v.Local.sType.Declare(String)

'Set Information to a String.  Columns Delimited by *!* with Rows Delimited by !*!
v.Local.sData.Set("Ryan Young*!*Manager, Custom Programming*!*04/04/2016!*!Jan Ortiz*!*Consultant, Finance & Technology*!*04/04/2016!*!")
F.Intrinsic.String.Build("{0}Josh Withrow*!*Consultant, Operations & Technology*!*04/06/2016!*!Sarah Crow*!*Programmer/Analyst, Custom Programming*!*04/06/2016!*!", v.Local.sData, v.Local.sData)
F.Intrinsic.String.Build("{0}Chris Okamuro*!*Chief Technology Officer*!*04/06/2016", v.Local.sData, v.Local.sData)

'Column Titles
v.Local.sColumns.Set("Name*!*Title*!*Class_Date")
'Data TypesCre
v.Local.sType.Set("String*!*String*!*Date")

'Create the DataTable
F.Data.DataTable.CreateFromString("Instructors", v.Local.sData, v.Local.sColumns, v.Local.sType, "*!*", "!*!", True)

F.Data.DataTable.Close("Instructors")

Remarks


Function.Data.DataTable.CreateFromXML

This function loads the contents the specified XML file into the specified DataTable.

Syntax

Function.Data.DataTable.CreateFromXML(datatable name [string], fully-qualified filename [long], global scope [Boolean])

Arguments

datatable name

string

fully-qualified filename

long

global scope

Boolean

Examples

Remarks


Function.Data.DataTable.DeleteRow

This overload deletes all rows from the specified datatable. This can be used in conjunction with SaveToDB to synchronize deleted rows out of the source table.

Syntax

Function.Data.DataTable.DeleteRow(datatable name [string])

Function.Data.DataTable.DeleteRow(datatable name [string], row number [long])

Arguments

Datatable Name

String

Row Number

Long

Examples

Function.Data.DataTable.DeleteRow("Payment", 5)

Remarks


Function.Data.DataTable.ExportHTML

Function.Data.DataTable.ExportHTML. This function creates an HTML table from the contents of a datatable.

Syntax

Function.Data.DataTable.ExportHTML(datatable name [string], column names [string], column titles [string], Table ID [string], Date Null [Boolean], return [string])

Arguments

Datatable name

this is the name of the datatable or subtable

Column names

(string) - this is a delimited string (*!* separator) containing the ordered list of the columns to include in the table. If this value is blank, all of the columns of the datatable will be included in the HTML table, in no particular order.

Column titles

(string) - this is a delimited string (*!* separator) containing the ordered column titles to display in the table. If this value is blank, no header row will be inserted in the table. If this value is ***USETABLE***, the column titles will be populated with the datatable column names

Table ID

(string) - this is the ID of the table to be generated. This value can be blank, but CSS formatting requires an ID (if there are multiple tables in a document)

Date Null

(boolean) - this specifies how null dates should be handled. If this value is True, 1/1/1900 and null dates will return as a blank value.

Return

(string) - this return value will contain the HTML block for the table

Examples

f.data.datatable.exportHTML("mydatatable","","***usetable***","test",true,v.lo.s)

Remarks


Function.Data.DataTable.FillFromDictionary

This function sets field values within the specified datatable, based on the specified dictionary. If no target field is specified, it is assumed that the value should replace the source field value. If a key match is not found, the value specified with Function.Data.Dictionary.SetDefaultReturn is returned. If no default value is provided, the target field will contain the original source field value.

Syntax

Function.Data.DataTable.FillFromDictionary(datatable name [string], dictionary name [string], source field [string])

Function.Data.DataTable.FillFromDictionary(datatable name [string], dictionary name [string], source field [string], target field [string])

Arguments

datatable name

string

dictionary name

string

source field

string

target field

string

Examples

Remarks


Function.Data.DataTable.InnerJoin

Function.Data.DataTable.InnerJoin

Syntax

Arguments

Examples

Remarks


Function.Data.DataTable.Merge

This function adds the contents from the source DataTable or sub-table to the target DataTable or sub-table. When performing a merge, changes made to the existing data before the merge are preserved during the merge operation unless the developer specifies false for the preserveChanges parameter. If the preserveChanges parameter is set to true, incoming values do not overwrite existing values in the Current row version of the existing row. If the preserveChanges parameter is set to false, incoming values do overwrite the existing values in the Current row version of the existing row.

Syntax

Function.Data.DataTable.Merge(source datatable name [string], target datatable name [string], preserve changes [boolean], merge mode [long])

Arguments

source datatable name

string

target datatable name

string

preserve changes

boolean

merge mode

long Merge mode values are as follows:

  • Add = 1 :Adds the necessary columns to complete the schema.
  • AddWithKey = 4 :Adds the necessary columns and primary key information to complete the schema. The user may explicitly set the primary key constraints on each DataTable. This ensures that incoming records that match existing records are updated instead of appended.
  • Error = 3 :An run-time error is generated if the specified column mapping is missing.
  • Ignore = 2 :Ignores the extra columns.

Examples

F.Data.DataTable.CreateFromSQL("DT","CONX",V.Local.sSQLContactRead)
F.Data.DataTable.CreateFromSQL("DT$Vendor","CONX",V.Local.sSQLVendorRead)
F.Data.DataTable.Merge("DT$Vendor","DT",true,2)
Gui.ReturnAddrSel.AddrListDisp.DataSource("DT")
Gui.ReturnAddrSel.AddrListDisp.SetGridviewProperty("Customer","Editable","False")
Gui.ReturnAddrSel..Show
Gui.ReturnAddrSel..WaitForDismiss 

Remarks


Function.Data.DataTable.MoveRow

This function moves the source row to the target row ordinal position in the datarows collection in the specified DataTable. When a row is moved using MoveRow, the RowState changes to 4 (Added), regardless of the RowState prior to movement.

Syntax

Function.Data.DataTable.MoveRow(datatable name [string], source row [long], target row [long])

Arguments

datatable name

string

souce row

long

target row

long

Examples

Function.Data.DataTable.MoveRow("dt", v.lo.iCurrrow, v.lo.iCurrrow.++)

Remarks

  • Note: Calling MoveRow before SaveToDB will cause issues with SaveToDB modes using AND 2 (for example, mode 7 will not work properly)
  • Keep in mind for moving a row one position at a time, you can use transforms without a math step and an extra variable:

Function.Data.DataTable.MoveRow("dt", v.lo.iCurrrow, v.lo.iCurrrow.++)

or

Function.Data.DataTable.MoveRow("dt", v.lo.iCurrrow, v.lo.iCurrrow.--)


Function.Data.DataTable.RemoveColumn

This function removes the specified datatable column.

Syntax

Function.Data.DataTable.RemoveColumn(datatable name [string], column name [string])

Arguments

Datatable Name

String

Column Name

String

Examples

Function.Data.DataTable.RemoveColumn("Runner", "RunnerSpeed")

Remarks


Function.Data.DataTable.RunningTotal

This function sets the running total to the target column in a given datatable. The initial value will be used with the operator against the source column to set the return value in the target column, for each row.

Valid operators are “+”, ”-“, ”*”, or ”/”.

Evaluation: Initial Value (+,-,*,/) Source Column = Target Column

Syntax

F.Data.DataTable.RunningTotal(DataTable Name [String], Source Column [String], Initial Value [Numeric], Operator [String], Target Column [String])


Arguments

DataTable Name

Name of the DataTable to use.

Source Column

Source Column Name. (Column to evaluate against)

Initial Value

Starting Value.

Operator

Operator Sign. ("+","-","*", or "/")

Target Column

Target Column Name. (Column to save the evaluation to)


Examples

Starting value is 10000

DataTable has columns

amt1, amt2, exp, plus, minus, mult, div

DataTable has values of

100, 10, 90, 0, 0, 0, 0

90 , 10, 80, 0, 0, 0, 0

80 , 10, 70, 0, 0, 0, 0


After using RunningTotal with Source column as exp and target column is the value you are using, plus, minus, mult, div.

100, 10, 90, 10090, 9910, 900000 , 111.111111111111

90 , 10, 80, 10170, 9830, 72000000 , 1.38888888888889

80 , 10, 70, 10240, 9760, 5040000000, 0.0198412698412698


Program.Sub.ScreenSU.Start
Gui.Form..Create
Gui.Form..Size(15360,10695)
Gui.Form..MinX(0)
Gui.Form..MinY(0)
Gui.Form..Position(0,0)
Gui.Form..BackColor(-2147483633)
Gui.Form..MousePointer(0)
Gui.Form..Event(UnLoad,Form_UnLoad)
Gui.Form.GsGridControl1.Create(GsGridControl)
Gui.Form.GsGridControl1.Size(14385,6555)
Gui.Form.GsGridControl1.Position(240,1425)
Gui.Form.txt1.Create(TextBox,"10000",True,1215,300,0,8490,630,True,0,"Arial",8,-2147483643,1)
Gui.Form.cmd1.Create(Button)
Gui.Form.cmd1.Size(855,375)
Gui.Form.cmd1.Position(5715,8985)
Gui.Form.cmd1.Caption("start")
Program.Sub.ScreenSU.End

Program.Sub.Preflight.Start
Program.Sub.Preflight.End

Program.Sub.Main.Start
f.Data.DataTable.Create("DT",True)
f.Data.DataTable.AddColumn("DT","amt1","Float")
f.Data.DataTable.AddColumn("DT","amt2","Float")
f.Data.DataTable.AddExpressionColumn("DT","exp","Float","amt1 - amt2")
'f.Data.DataTable.AddColumn("DT","exp","String")
f.Data.DataTable.AddColumn("DT","plus","Float")
f.Data.DataTable.AddColumn("DT","minus","Float")
f.Data.DataTable.AddColumn("DT","mult","Float")
f.Data.DataTable.AddColumn("DT","div","Float")
f.Data.DataTable.AddRow("DT","amt1",100,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",90,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",80,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",0,"amt2",0)
f.Data.DataTable.AddRow("DT","amt1",60,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",50,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",40,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",v.Ambient.DBNull,"amt2",v.Ambient.DBNull)
f.Data.DataTable.AddRow("DT","amt1",30,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",20,"amt2",10)
f.Data.DataTable.AddRow("DT","amt1",10,"amt2",10)
f.Data.DataView.Create("DT","DV")
Gui.Form.GsGridControl1.AddGridviewFromDataview("GV","DT","DV")
Gui.Form.GsGridControl1.MainView("GV")
f.Data.DataTable.RunningTotal("DT","exp",v.Screen.Form!txt1.Text,"+","plus")
f.Data.DataTable.RunningTotal("DT","exp",v.Screen.Form!txt1.Text,"-","minus")
f.Data.DataTable.RunningTotal("DT","exp",v.Screen.Form!txt1.Text,"*","mult")
f.Data.DataTable.RunningTotal("DT","exp",v.Screen.Form!txt1.Text,"/","div")
Gui.Form..Show
Program.Sub.Main.End

Program.Sub.Form_UnLoad.Start
f.Intrinsic.Control.End
Program.Sub.Form_UnLoad.End 


Remarks


Function.Data.DataTable.SaveToCSV

This function creates a CSV file from the datatable supplied and saves it as the FQN. The include-header-titles boolean is set to include the column headers in the CSV file or not.

Syntax

Function.Data.Datatable.SaveToCSV(Datatable, Fully Qualified Name As string, include header titles As Boolean)

Arguments

Examples

Remarks


Function.Data.DataTable.SaveToDB

This function saves the contents of the specified datatable to the specified database table, using the passed connection.

Syntax

Function.Data.DataTable.SaveToDB(datatable name [string], connection name [string], DB table name [string], key fields [string])

Function.Data.DataTable.SaveToDB(datatable name [string], connection name [string], DB table name [string], key fields [string], mode [string])

Function.Data.DataTable.SaveToDB(datatable name [string], connection name [string], DB table name [string], key fields [string], mode [string], field map [string])

Arguments

datatable name

string

connection name

string

DB table name

string

key fields

string

mode

string

By default, the mode is 7, where all datatable changes will be reconciled against the database table. The mode is a value comprised of any combination of the following elements:

  • 1 - add records
  • 2 - modified records
  • 4 - delete records
  • 128 - treat all current records as new records to insert - key field parameter is used to exclude identity field(s)
  • 256 - modify existing records with key match and insert all unmatched records
field map

string

A field map may be specified, which defines the relationship between the Datatable field names and the database table field names. If the names are the same, no field map needs to be provided. The field map is made up of the Datatable field name, the @!@ delimiter, and the database field name. Each pair is delimited from the next using *!*.

Examples

Remarks


Function.Data.DataTable.SaveToXML

This function saves the contents of the specified DataTable to an XML file. The mode parameter accepts a value of 0 or 1. Mode 0 writes the XSD schema, while mode 1 does not write the XSD schema. Set the hierarchy parameter to True to write descendants of the specified DataTable to the XML file.

Syntax

Function.Data.DataTable.SaveToXML(datatable name [string], mode [long], hierarchy [Boolean], fully-qualified filename [string])

Arguments

datatable name

string

mode

long

hierarchy

Boolean

fully-qualified filename

string

Examples

Remarks


Function.Data.DataTable.Select

This function returns a delimited string containing the row ordinals in a DataTable where the expression condition is met. The expression rules are the same as the filter criteria expression for creating DataViews. If no matches are found, ***NORETURN*** is returned.

Syntax

Function.Data.DataTable.Select(datatable name [string], expression [string], return [string])

Arguments

datatable name

string

expression

string

return

string

Examples

Remarks


Function.Data.DataTable.SetPK

This function sets the primary key of the specified DataTable. If the primary key consists of multiple fields, they should be specified as individual parameters.

Syntax

Function.Data.DataTable.SetPK(datatable name [string], column 0 [string], ..., column n [string])

Arguments

Datatable Name

String

Column 0

String

...
Column N

String

Examples

'Create Datatable
F.Data.DataTable.Create("Runner", true)
Function.Data.DataTable.AddColumn("Runner", "RunnerName", "String")
Function.Data.DataTable.AddColumn("Runner", "RunnerAge", "Long")
Function.Data.DataTable.AddColumn("Runner", "RunnerSpeed", "Long")

Example #1: Setting 1 column as PK

Function.Data.DataTable.SetPK("Runner", "RunnerName")

Example #2: Setting 2 columns as PK

Function.Data.DataTable.SetPK("Runner", "RunnerName", "RunnerSpeed")

Remarks


Function.Data.DataTable.SetSeries

This function sets series values within a column of the specified datatable. Column data values should be consistent with the data types declared with Function.Data.DataTable.AddColumn. The intial value will be set in the column of the first row of the dataview, and subsequent rows will have the previous row's value, plus the interval value (the interval may be negative). An overload of this function accepts date type variables for the initial value; this overload requires the specification of the interval type. Valid interval types are as follows:


Syntax

Function.Data.DataTable.SetSeries(datatable name [string], column name [string], initial value [float/long], starting value [float/long], interval [float/long])

or

Function.Data.DataTable.SetSeries(datatable name [string], column name [string], initial value [date], starting value [date], interval [long], interval type [string])

Arguments

Examples

Remarks

d - Day; truncated to integral value

y - Day; truncated to integral value

h - Hour; rounded to nearest millisecond

n - Minute; rounded to nearest millisecond

m - Month; truncated to integral value

q - Quarter; truncated to integral value

s - Second; rounded to nearest millisecond

w - Weekday; truncated to integral value

ww - Week; truncated to integral value

yyyy - Year; truncated to integral value


Function.Data.DataTable.SetValue

Syntax

Function.Data.DataTable.SetValue(datatable name [string], row number [long], column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Function.Data.DataTable.SetValue(datatable name [string], row number [long], column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Function.Data.DataTable.SetValue(datatable name [string], row number [long], column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Arguments

datatable name

string

row number

long

column 0 name

string

column 0 value

any

..
column n name

string

column n value

any

Examples

Remarks


Function.Data.DataTable.SetValueFormat

This function formats the source column value, given the specified format string, and sets the value into the target column (usually a String column). The format mask values are the same as Function.Intrinsic.String.Format. If the row number is set to -1, all rows within the datatable will receive the specified formatting.

Syntax

Function.Data.DataTable.SetValueFormat(datatable name [string], row number [long], source column name [string], target column name [string], format mask [string])

Arguments

datatable name

string

row number

long

source column name

string

target column name

string

format mask

string

Examples

Remarks


Function.Data.DataView.Close

This function closes a named dataview of the specified datatable.

Syntax

Function.Data.DataView.Close(datatable name [string], dataview name [string])

Arguments

datatable name

string

dataview name

string

Examples

Remarks


Function.Data.DataView.Create

This function creates a named dataview of the specified datatable. Dataviews are mapped to the origin datatable, but can provide ordered sets or subsets of the datatable contents. Since datatables can be bound to GAB screen controls, rows that have been created, deleted, or modified can be exposed or omitted using a rowview mask. Sorting and filtering is similar, but not identical, to SQL. The sort string can contain any number of fields and their ordering direction, with the pairs separated with commas. The ordering directions are ASC and DESC. Sample syntax is as follows: v.local.sSort.Set("LastName ASC, FirstName ASC") The filter string allows parameter values to be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notation are permissible for numeric values.

Syntax

Function.Data.DataView.Create(datatable name [string], dataview name [string])

Function.Data.DataView.Create(datatable name [string], dataview name [string], rowview mask [integer])

Function.Data.DataView.Create(datatable name [string], dataview name [string], rowview mask [integer], filter [string], sort [string])

Function.Data.DataView.Create(datatable name [string], dataview name [string], rowview mask [long])

Function.Data.DataView.Create(datatable name [string], dataview name [string], rowview mask [long], filter [string], sort [string])

Arguments

datatable name

string

dataview name

string

rowview mask

long

filter

string

sort

string

Examples

Remarks


Function.Data.DataView.DeleteRow

Function.Data.DataView.DeleteRow

Syntax

Arguments

Examples

Remarks


Function.Data.DataView.SetFilter

This command sets the filter property of the named dataview of the specified datatable. Filtering is similar, but not identical, to SQL. The filter string allows parameter values to be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notation are permissible for numeric values.

Syntax

Function.Data.DataView.SetFilter(datatable name [string], dataview name [string], filter [string])

Arguments

datatable name

string

dataview name

string

filter

string

Examples

Remarks


Function.Data.DataView.SetRowView

This command sets the row view mask property of the named dataview of the specified datatable. Rowview mask values are as follows: New rows = 4 All current rows = 22 (default) Deleted = 8 Modified current = 16 Modified original = 32 None = 0 Original rows = 42 Unchanged rows = 2

Syntax

Function.Data.DataView.SetRowView(datatable name [string], dataview name [string], row view mask [integer])

Function.Data.DataView.SetRowView(datatable name [string], dataview name [string], row view mask [long])

Arguments

datatable name

string

dataview name

string

row view mask

long

Examples

Remarks


Function.Data.DataView.SetSeries

This function sets series values within a column of the specified dataview. Column data values should be consistent with the data types declared with Function.Data.DataTable.AddColumn. The intial value will be set in the column of the first row of the dataview, and subsequent rows will have the previous row's value, plus the interval value (the interval may be negative). An overload of this function accepts date type variables for the initial value; this overload requires the specification of the interval type. Valid interval types are as follows:

d - Day; truncated to integral value y - Day; truncated to integral value h - Hour; rounded to nearest millisecond n - Minute; rounded to nearest millisecond m - Month; truncated to integral value q - Quarter; truncated to integral value s - Second; rounded to nearest millisecond w - Weekday; truncated to integral value ww - Week; truncated to integral value yyyy - Year; truncated to integral value


Syntax

Function.Data.DataView.SetSeries(datatable name [string], view name [string], column name [string], initial value [float/long], starting value [float/long], interval [float/long])

or

Function.Data.DataView.SetSeries(datatable name [string], view name [string], column name [string], initial value [date], starting value [date], interval [long], interval type [string])

Arguments

Examples

Remarks

  • Note: this command writes values to all rows within the DataView, overwriting the existing column value. However, the underlying DataTable will not have an initialized value for the columns of rows not in the DataView unless a default value was set on the column (with AddColumn), or SetValue was used (presumably with a -1 row)


Function.Data.DataView.SetSort

This sets the sort property of the named dataview of the specified datatable. Sorting is similar, but not identical, to SQL. The sort string can contain any number of fields and their ordering direction, with the pairs separated with commas. The ordering directions are ASC and DESC. Sample syntax is as follows: v.local.sSort.Set("LastName ASC, FirstName ASC") Note: Unless there is a good reason to change the sort on a dataview post-creation, it should be specified during Function.Data.DataView.Create (for performance reasons)

Syntax

Function.Data.DataView.SetSort(datatable name [string], dataview name [string], sort [string])

Arguments

datatable name

string

dataview name

string

sort

string

Examples

Remarks


Function.Data.DataView.SetValue

Syntax

Function.Data.DataView.SetValue(datatable name [string], view name [string], row number, column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Function.Data.DataView.SetValue(datatable name [string], view name [string], row number, column 0 name [string], column 0 value [any], .. , column n name [string], column n value [any])

Arguments

datatable name

string

view name

string

row number
column 0 name

string

column 0 value

any

..
column n name

string

column n value

any

Examples

Remarks


Function.Data.DataView.ToDataTable

This function creates a new datatable (or subtable) from the specified dataview contents.

Syntax

Function.Data.DataView.ToDataTable(source datatable name [string], dataview name [string], target datatable name [string])

Function.Data.DataView.ToDataTable(source datatable name [string], dataview name [string], target datatable name [string], global scope [boolean])

Arguments

source datatable name

string

dataview name

string

target datatable name

string

global scope

boolean

Examples

Remarks


Function.Data.DataView.ToDataTableDistinct

Function.Data.DataView.ToDataTableDistinct

Syntax

Arguments

Examples

Remarks


Function.Data.DataView.ToString

This function creates a double delimited string containing the contents of the specified dataview. If the overload without a field list is used, all fields are exported in the order they are present in the dataview. Note: Do not specify the same delimiter for both the outer (rows) and inner (fields) delimiter parameters

Syntax

Function.Data.DataView.ToString(datatable name [string], dataview name [string], field list [string], inner delimiter [string], outer delimiter [string], return [string])

Function.Data.DataView.ToString(datatable name [string], dataview name [string], inner delimiter [string], outer delimiter [string], return [string])

Arguments

datatable name

string

dataview name

string

inner delimiter

string

outer delimiter

string

return

string

Examples

Remarks


Function.Data.Dictionary.AddItem

Syntax

Function.Data.Dictionary.AddItem(dictionary name [string], key [any], value [any])

Function.Data.Dictionary.AddItem(dictionary name [string], key [any], value [any], ignore duplicates [Boolean])

Arguments

dictionary name

string

key

any

value

any

ignore duplicates

Boolean

Examples

Remarks


Function.Data.Dictionary.Clear

Syntax

Function.Data.Dictionary.Clear(dictionary name [string])

Arguments

dictionary name

string

Examples

Remarks


Function.Data.Dictionary.Close

Syntax

Function.Data.Dictionary.Close(dictionary name [string])

Arguments

dictionary name

string

Examples

Remarks


Function.Data.Dictionary.Create

Syntax

Function.Data.Dictionary.Create(dictionary name [string])

Arguments

dictionary name

string

Examples

Remarks


Function.Data.Dictionary.CreateFromDataTable

Function.Data.Dictionary.CreateFromDataTable

Syntax

Arguments

Examples

Remarks


Function.Data.Dictionary.CreateFromSQL

Syntax

Function.Data.Dictionary.CreateFromSQL(dictionary name [string], connection name [string], SQL string [string])

Function.Data.Dictionary.CreateFromSQL(dictionary name [string], connection name [string], SQL string [string], format [long])

Arguments

dictionary name

string

connection name

string

SQL string

string

format

long

Examples

Remarks


Function.Data.Dictionary.Exists

Syntax

Function.Data.Dictionary.Exists(dictionary name [string], return [Boolean])

Arguments

dictionary name

string

return

Boolean

Examples

Remarks


Function.Data.Dictionary.List

Function.Data.Dictionary.List

Syntax

Arguments

Examples

Remarks


Function.Data.Dictionary.RemoveItem

Syntax

Function.Data.Dictionary.RemoveItem(dictionary name [string], key [any])

Arguments

dictionary name

string

key

any

Examples

Remarks


Function.Data.Dictionary.ReturnAllPairs

Function.Data.Dictionary.ReturnAllPairs

Syntax

Arguments

Examples

Remarks


Function.Data.Dictionary.ReturnKeyFromValue

Function.Data.Dictionary.ReturnKeyFromValue

Syntax

Arguments

Examples

Remarks


Function.Data.Dictionary.SetDefaultKey

Function.Data.Dictionary.SetDefaultKey

Syntax

Arguments

Examples

Remarks


Function.Data.Dictionary.SetDefaultReturn

Syntax

Function.Data.Dictionary.SetDefaultReturn(dictionary name [string], default [any])

Arguments

dictionary name

string

default

any

Examples

Remarks


Function.Data.Dictionary.UpdateItem

Syntax

Function.Data.Dictionary.UpdateItem(dictionary name [string], key [any], value [any])

Arguments

dictionary name

string

key

any

value

any

Examples

Remarks


Function.Data.Linq.Join

This command allows DataTables, DataViews, and object collections to be joined into a DataTable using LINQ.


Syntax

Function.Data.Linq.Join(join type [string], source type0 [string], source name0 and arguments [string], source type1 [string], source name1 and arguments [string], join expression -source0 and source 1 [string], column names [string delimited *!*], filter expression [string], group by expression [string], order by expression [string], return data table [string], global datatable scope [Boolean])

or

Function.Data.Linq.Join(join type [string], source type0 [string], source name0 and arguments [string], source type1 [string], source name1 and arguments [string], join expression -source0 and source 1 [string], [source type n [string], source n and arguments [string], join expression -source n and source n-2/source n-1 [string], ..., column names [string delimited *!*], filter expression [string], group by expression [string], order by expression [string], return data table [string], global datatable scope [Boolean])

Arguments

Examples

Data table join
F.Data.Linq.Join("Rightjoin","datatable","Employee*!*E","datatable","Department*!*D","E.DeptID=D.Dept_ID","datatable","Manager*!*M","E.MgID=M.MID","E.Emp_ID*!*E.FirstName*!*E.LastName*!* D.DeptName*!*M.MgName","E.Emp_ID <48", "D.DeptName","E.Emp_ID","DTJoinreturn",True)

Data table-Data view join
F.Data.Linq.Join("leftjoin","dataview","Employee","EmployeeDV*!*E,"datatable","Department*!*D","E.DeptID =D.Dept_ID","dataview","Manager,"ManagerDV*!*M","E.MgID=M.MID","E.Emp_ID*!*E.FirstName*!*E.LastName*!*D.DeptName*!*M.MgName","E.Emp_ID <48", "D.DeptName","E.Emp_ID", "DVJoinreturn",True)

Data table-Object join
F.Data.Linq.Join("Innerjoin","object","oInvoice*!*Inv",286,"GlobalDB","connection index [integer]","datatable","PartTable*!*P","Inv.Part.PartNumber =P.PartNumber,"Inv.InvoiceNumber*!*Inv.SalesOrderLineNumber*!*Inv.Part.PartNumber*!*P.LocationCode", "","Part.PartNumber","ObjectJoinreturn",True)

Object-Object join
F.Data.Linq.Join("Innerjoin","object","oInvoice",286,"GlobalDB","connection index [integer]","object",oPart*!*",1275,"Part.PartNumber =PartNumber,"InvoiceNumber*!*SalesOrderLineNumber*!*Part.PartNumber*!*LocationCode*!*PartNumberRevision","","","Part.PartNumber","ObjectJoinreturn",True)

Remarks

  • Valid join types are as follows:

InnerJoin

LeftJoin

RightJoin

FullJoin


  • Valid source types and source naming are as follows:

DataTable - DataTableName or DataTable - DataTableName*!*AliasName

Data View - DataTableName, DataViewName or Data View - DataTableName, DataViewName*!*AliasName

Object - ObjectName, Mode [integer], ConnectionObjectName [string], Connection Index [Integer] or Object - ObjectName*!*AliasName, Mode [integer], ConnectionObjectName [string], Connection Index [Integer]


Tutorial for Dataview

Example 1

V.Local.sSql.Declare(String)
V.Local.sCustomer.Declare(String)
v.Local.sFilter.Declare(String)
v.Local.sSort.Declare(String)
v.Local.sDomain.Declare(String)
V.Local.iCount.Declare(Long)

F.Intrinsic.String.Build("Global_{0}", v.Caller.CompanyCode, v.Local.sDomain)
F.ODBC.Connection!conx.OpenConnection(v.Local.sDomain,"Master","master")


'Create the DataTable Customer from an SQL Query
V.Local.sSql.Set("select customer,Name_Customer, City, State from Customer_Master Where Rec='1'")
F.Data.DataTable.CreateFromSQL("Customer","conx",v.Local.sSql)
'Add a Column to the DataTable
F.Data.DataTable.AddColumn("Customer","Amt_Last_Sale","Float")

'Loop through the DataTable to populate the Amt_Last_Sale Column
F.Intrinsic.Control.For(v.Local.iCount,0,v.DataTable.Customer.RowCount--,1)
	'Get the Customer ID
	v.Local.sCustomer.Set(v.DataTable.Customer(v.Local.iCount).Customer!FieldVal)
	F.Intrinsic.String.Build("Select Amt_Last_Sale From Customer_Sales Where Customer = '{0}' and Rec = '2'",v.Local.sCustomer,v.Local.sSql)
	F.ODBC.Connection!conx.OpenLocalRecordsetRO("rstCust",v.Local.sSql)
	F.Intrinsic.Control.If(v.ODBC.conx!rstCust.EOF,=,False)
		'Set Amount to the amt_last_Sale Column
		F.Data.DataTable.SetValue("Customer",v.Local.iCount,"Amt_Last_Sale",v.ODBC.conx!rstCust.FieldValFloat!Amt_Last_Sale)
	F.Intrinsic.Control.EndIf
	F.ODBC.conx!rstCust.Close
F.Intrinsic.Control.Next(v.Local.iCount)

F.Data.DataTable.AddRow("Customer","Customer","GSSRD","Name_Customer","Global Shop","City","The Woodlands","State","TX","AMT_Last_Sale",200.00)



F.Data.DataView.Create("Customer", "Customer_Orig", 22)

F.Data.DataView.Create("Customer", "Customer_New", 4)


v.Local.sFilter.Set("Amt_Last_Sale > 0.00")
v.Local.sSort.Set("Name_Customer ASC")

F.Data.DataView.Create("Customer", "Customer_Sales", 22, v.Local.sFilter, v.Local.sSort)


F.Data.DataView.Close("Customer", "Customer_Orig")
F.Data.DataView.Close("Customer", "Customer_New")
F.Data.DataView.Close("Customer", "Customer_Sales")
F.Data.DataTable.Close("Customer")
F.ODBC.Connection!conx.Close 


Example 2

V.Local.sSql.Declare(String)
V.Local.sCustomer.Declare(String)
v.Local.sFilter.Declare(String)
v.Local.sSort.Declare(String)
v.Local.sDomain.Declare(String)
V.Local.iCount.Declare(Long)
v.Local.fAmount.Declare(Float)
v.Local.fAdjust.Declare(Float)

F.Intrinsic.String.Build("Global_{0}", v.Caller.CompanyCode, v.Local.sDomain)
F.ODBC.Connection!conx.OpenConnection(v.Local.sDomain,"Master","master")


'Create the DataTable Customer from an SQL Query
V.Local.sSql.Set("select customer,Name_Customer, City, State from Customer_Master Where Rec='1'")
F.Data.DataTable.CreateFromSQL("Customer","conx",v.Local.sSql)
'Add a Column to the DataTable
F.Data.DataTable.AddColumn("Customer","Amt_Last_Sale","Float")

'Loop through the DataTable to populate the Amt_Last_Sale Column
F.Intrinsic.Control.For(v.Local.iCount,0,v.DataTable.Customer.RowCount--,1)
	'Get the Customer ID
	v.Local.sCustomer.Set(v.DataTable.Customer(v.Local.iCount).Customer!FieldVal)
	F.Intrinsic.String.Build("Select Amt_Last_Sale From Customer_Sales Where Customer = '{0}' and Rec = '2'",v.Local.sCustomer,v.Local.sSql)
	F.ODBC.Connection!conx.OpenLocalRecordsetRO("rstCust",v.Local.sSql)
	F.Intrinsic.Control.If(v.ODBC.conx!rstCust.EOF,=,False)
		'Set Amount to the amt_last_Sale Column
		F.Data.DataTable.SetValue("Customer",v.Local.iCount,"Amt_Last_Sale",v.ODBC.conx!rstCust.FieldValFloat!Amt_Last_Sale)
	F.Intrinsic.Control.EndIf
	F.ODBC.conx!rstCust.Close
F.Intrinsic.Control.Next(v.Local.iCount)

F.Data.DataTable.AddRow("Customer","Customer","GSSRD","Name_Customer","Global Shop","City","The Woodlands","State","TX","AMT_Last_Sale",200.00)

F.Data.DataView.Create("Customer", "Customer_Orig", 22)

F.Data.DataView.Create("Customer", "Customer_New", 4)


v.Local.sFilter.Set("Amt_Last_Sale > 0.00")
v.Local.sSort.Set("Name_Customer ASC")

F.Data.DataView.Create("Customer", "Customer_Sales", 22, v.Local.sFilter, v.Local.sSort)


F.Intrinsic.Control.For(v.Local.iCount, 0, v.DataView.Customer!Customer_Sales.RowCount--, 1)
	v.Local.fAmount.Set(v.DataView.Customer!Customer_Sales(v.Local.iCount).Amt_Last_Sale!FieldValFloat)
	F.Intrinsic.Math.Mult(v.Local.fAmount, .10, v.Local.fAdjust)
	F.Intrinsic.Math.Add(v.Local.fAdjust, v.Local.fAmount, v.Local.fAmount)
	F.Data.DataView.SetValue("Customer", "Customer_Sales", v.Local.iCount, "Amt_Last_Sale", v.Local.fAmount)
F.Intrinsic.Control.Next(v.Local.iCount)

F.Data.DataView.SetValue("Customer","Customer_Sales", 1, "Amt_Last_Sale", "5000.00")

F.Data.DataView.DeleteRow("Customer","Customer_Sales", "1")


F.Data.DataView.Close("Customer", "Customer_Orig")
F.Data.DataView.Close("Customer", "Customer_New")
F.Data.DataView.Close("Customer", "Customer_Sales")
F.Data.DataTable.Close("Customer")
F.ODBC.Connection!conx.Close