These functions allow you to read and write to text spreadsheets called Lookup Tables. They also provide the ability to perform database-style Transactions, which allows a set of updates to be done atomically.

• With most lookup table functions, if an exact match for a row or column value cannot be found, the next highest value will be used. This is useful for door price tables, where the dimensions of a door will need to be rounded up to the next available size if the exact specified size does not exist. For more information on this and other features, refer to the Lookup Tables tutorial.
• When specifying the file names of lookup tables, you can either use an absolute path (e.g. "c:\sample.qlt") or a relative path (e.g. "examples\sample.qlt"). When a relative path is used, the base folder is TABLE_FOLDER.
• Some of these functions are marked Action Only.
• We will define the following example table as "sample.qlt", so it can be used to illustrate the usage of lookup table functions:

AddCol
Add a new column to the table with the given label and type
Syntax	AddCol (table, label [,type])
Inputs	Table is the name of the lookup table to add a column to. Optionally allows column Type specification when adding table column from middleware.
Notes	When specifying a type for third param in the ADDCOL() function, you can use a string like "Money" or the named constant TYPE_MONEY. Either version is case independent, but the quoted string version cannot have the "type_" prefix, whereas the named constant version must include it.
Examples	AddCol("tablename.qlt", "ColumnName", 4) where 4 is a TypeIndex i.e. an index into the QAnyValue::TYPE enum, or AddCol("tablename.qlt", "ColumnName", "Money") where "Money" is any friendly TypeName (case-independent)
See Also	RemoveCol, RemoveColByIndex

AddRow
Add a new row to the table with the given label
Syntax	AddRow (table, label)
Inputs	Table is the name of the lookup table to add a row to
See Also	RemoveRow, RemoveRowByIndex

AddToCatalog [Action Only]
Saves current item to catalog
Syntax	AddToCatalog ()
Inputs	None (you still need to use the empty brackets)
See Also	FormEditor, HasCatalog

BeginTrans [Action Only]
Indicates the start of lookup table Transactions
Syntax	BeginTrans QLT table or Begin Transaction QLT table
Inputs	Table is the name of the lookup table to start a transaction on
Notes	Read the tutorial on Transactions for details
Examples	Begin Transaction QLT "sample"
See Also	CommitTrans, Rollback, Update

ColCount
Returns the number of columns in a lookup table
Syntax	ColCount (table)
Inputs	Table is the name of the lookup table involved
Examples	ColCount ("sample") = 4
See Also	RowCount

ColLabel
Reads a specific column heading from a lookup table and returns it
Syntax	ColLabel (table, index)
Inputs	Table is the name of the lookup table involved. Index is the number of the column to get the heading of
Notes	If the column index is out of range, #N/A is returned
Examples	ColLabel ("sample", 3) = "95mm"
See Also	RowLabel

ColLabels
Returns a list of all the column headings in a specified lookup table
Syntax	ColLabels (table)
Inputs	Table is the name of the lookup table involved
Outputs	A string of all the headings joined together, separated by | (pipe)
Examples	ColLabels ("sample") = "40mm|65mm|95mm|115mm"
See Also	LookupColLabels, RowLabels

ColLabelsForRow
Returns a list of column names, where those columns contain a specified value in a certain row
Syntax	ColLabelsForRow (table, row, value, match)
Inputs	Table is required, and is the lookup table to be used. If value is found anywhere along row, the names of the corresponding columns will be returned. Match is an optional Yes/No value and if set to No, will return all columns which contain a cell not equal to value in row
Outputs	A string of all the matched column names joined together, separated by | (pipe)
Notes	Value is optional - if it is omitted, all columns which do not have an empty value in row will be returned. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples
See Also	RowLabelsForCol, ColLabelsForVal, RowLabelsForVal

ColLabelsForRow2
Returns a list of column names, where those columns contain specified value in two rows
Syntax	ColLabelsForRow2 (table, row1, value1, match1, row2, value2, match2)
Inputs	Gets the names of the columns where row1 contains value1 and row2 contains value2. Match1 and Match2 are optional Yes/No variables which default to yes. If set to no, it instead searches for where the row contains any other value (Match1 affects row1 and value1, Match2 affects row2 and value2)
Outputs	A string of all the matched column names joined together, separated by | (pipe)
Notes	Value1 and Value2 are optional - if they are omitted, the column names returned will be any columns with non-empty fields for row1 and row2. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples

ColLabelsForVal
Returns a list of column names, where those columns contain a specified value
Syntax	ColLabelsForVal (table, value, match)
Inputs	Table is required, and is the lookup table to be used. If value is found in a particular column, the name of that column will be added to the result list. Match is an optional Yes/No value and if set to No, will return all columns which contain a cell not equal to value
Outputs	A string of all the matched column names joined together, separated by | (pipe)
Notes	Value is optional - if it is omitted, all columns which are not completely empty will be returned. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples
See Also	RowLabelsForVal, ColLabelsForRow, RowLabelsForCol

ColValues
Returns a list of all the values in a specific column of a lookup table
Syntax	ColValues (table, name)
Inputs	Table is the name of the lookup table involved. The column to return the values from is specified by its column heading, name
Outputs	A string of all the values joined together, separated by | (pipe)
Notes	If a column with a heading equal to (or more than) name can't be found, #N/A is returned
Examples	ColValues ("sample", "40mm") = "$23.10|$51.00|$72.90"
See Also	ColValuesByIndex, LookupCol, LookupColMap, RowValues

ColValuesByIndex
Returns a list of all the values in a specific column of a lookup table
Syntax	ColValues (table, index)
Inputs	Table is the name of the lookup table involved. The column to return the values from is specified by its column number, index
Outputs	A string of all the values joined together, separated by | (pipe)
Notes	If index is out of range, #N/A is returned
Examples	ColValuesByIndex ("sample", 3) = "$56.10|$118.80|$149.40"
See Also	ColValues, RowValuesByIndex

ColValuesFilterByCol2
Returns a list of values from a column, where another column in the same row contains specified values.
Syntax	ColValuesFilterByCol2 (table, col1, col2, val2)
Inputs	Finds the group of rows where col2 contains val2 and gets the values in col1.
Outputs	A string of all the col1 values from the rows that matched the criteria separated by | (pipe)
Notes	For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples

CommitTrans [Action Only]
Save any changes made during the current Transactions on a specified lookup table
Syntax	CommitTrans QLT table
Inputs	Table is the name of the lookup table to close a transaction on
Notes	CommitTrans must be called at the end of a transaction, otherwise all changes made since the start of the transaction will be lost. Read the tutorial on Transactions for details
Examples	Commit Transaction QLT "sample"
See Also	BeginTrans, Rollback, Update

FormEditor [Action Only]
Launch form editor
Syntax	FormEditor (filestub, [qltfile1,qltfile2...])
Inputs	filestub launches form editor using filestub.qiq with default qlt, default tablefolder
Examples	FormEditor(filestub,qltfilename) and FormEditor(filestub,[qltfile1,qltfile2...]) edits specified qlts for each friendly group. FormEditor(qiqfilename,qltfilename) launches a form editor using qiqfilename.qiq and qltfilename.qlt, default folder Tablefolder
See Also	TableEditor

HasCatalog [Action Only]
Tests if there is an active catalog for the current library.
Syntax	HasCatalog ()
Inputs	None (you still need to use the empty brackets)
Outputs	If you are running with an active catalog selected, then the answer is yes. If the selected and active library has no catalog then the answer is no.
Notes	Useful to check whether it is possible, before proceeding, to try to add the currently selected item to the currently active catalog. If there is no catalog, that will not work. If there is a button for adding to catalog, it might be useful to grey it out or, if that does not work, make it disappear as HASCATALOG returns No.
See Also	FormEditor, AddToCatalog

LookUp
Returns the value of a cell from a lookup table, specified by the names of its row and column
Syntax	LookUp (table, rowLabel, colLabel, default)
Inputs	Table is the name of the lookup table involved. RowLabel and colLabel are equal to (or less than) the cell's row and column headings. Default is optional, and is what will be returned if the specified cell cannot be found
Outputs	If default is not provided and the cell cannot be found, #N/A is returned
Notes	This function is best suited to looking up data like door prices, where the Next Value rule will likely be used
Examples	LookUp ("sample", 900mm, 95mm) = $118.80 LookUp ("sample", 400mm, 120mm) = #N/A LookUp ("sample", 400mm, 120mm, $200) = $200.00
See Also	LookupByIndex, LookupExact

LookupByIndex
Returns the value of a cell from a lookup table, specified by the indexes of its row and column
Syntax	LookupByIndex (table, rowNum, colNum, default)
Inputs	Table is the name of the lookup table involved. RowNum and colNum are the cell's row and column indexes. Default is optional, and is what will be returned if the specified cell cannot be found
Outputs	If default is not provided and the cell cannot be found, #N/A is returned
Examples	LookupByIndex("sample", 3, 1) = $72.90 LookUpByIndex("sample", 6, 2, $0) = $0.00
See Also	LookUp, LookupExact

LookupCol
Returns a list of all the values in a specific column of a lookup table
Syntax	LookupCol(table, name)
Inputs	Table is the name of the lookup table involved. The column to return the values from is specified by its column Name heading.
Outputs	Returns an array containing the contents of the named column
Notes	This returns the same information as ColValues(table,name) but returns it as an array rather than a pipe separated string. This has the primary advantage of not discarding type information by converting everything to string values.
Examples	LookupCol ("sample", "40mm") = [$23.10,$51.00,$72.90]
See Also	ColValues, LookupColMap, LookupRow

LookupColLabels
Returns a list of all the column headings in a specified lookup table
Syntax	LookupColLabels(table)
Inputs	Table is the name of the lookup table involved
Outputs	Returns an array containing the labels for each column
Notes	Equivalent to ColLabels except that it returns an array instead of a pipe-delimited string, and therefore preserve type information.
See Also	ColLabels, LookUp, LookupExact, LookupByIndex

LookupColMap
Looks up a QLT column returning it as a map.
Syntax	LookupColMap(table [, colName])
Inputs	Table is the name of the lookup table involved. Optional colName is the label for the column to lookup
Outputs	Returns a map with keys that are the rowlabels and values that are the elements from the specified column of the table. If colName not specified, then fetches the whole table.
Notes	This returns the same values as ColValues(table,colName) and LookupCol(table,colName) but returns the values as a map with keys that are the rowlabels. ColValues returns a pipe separated string and LookupCol returns an array of these same values. Search items are case independent.
Examples	LookupColMap("My.qlt", "colName") = ["RowName1":"Value1", "RowName2":"Value2"]
See Also	ColValues, LookupCol, LookupRowMap

LookupColUnique
Returns a copy of the specified array with elements sorted and all the adjacent duplicate elements removed
Syntax	LookupColUnique(array)
Inputs	Array is what you want to sort the elements of and all the adjacent duplicates removed from
Notes	This is equivalent to Uniq(Sort(LookUpCol(...))) but has better performance characteristics when working with large tables.
See Also	LookUp, LookupCol, LookupExact, LookupByIndex

LookupColWithFilter
Returns a list of filtered values in a specific column of a lookup table
Syntax	LookupColWithFilter(table, name, [col:val])
Inputs	Table is the name of the lookup table involved. The column to return the values from is specified by its column Name heading. [col:val] pairs to match each row against.
Notes	This is equivalent to LookupCol but with an extra parameter which takes a map value with col:value pairs to match each row against.
Examples	LookupColWithFilter("test.qlt","Thickness",["Brand":"MyBrand","Material":"Melamine"]) Returns the "Thickness" value for every row in the qlt where the column "Brand" had the value "MyBrand" AND the row "Material" had the value "Melamine".
See Also	LookupCol, LookupColMap, LookupRowMapWithFilter

LookupExact
Returns the value of a cell from a lookup table, specified exactly by the names of its row and column
Syntax	LookupExact (table, rowLabel, colLabel, default)
Inputs	Table is the name of the lookup table involved. RowLabel and colLabel are the cell's row and column headings. Default is optional, and is what will be returned if the specified cell cannot be found
Outputs	If default is not provided and the cell cannot be found, #N/A is returned
Notes	Because this function only matches row and column headings exactly, the Next Value rule does not get applied
Examples	LookUpExact ("sample", 1.6m, 50mm) = #N/A LookUpExact ("sample", 1.6m, 50mm, $0) = $0.00
See Also	LookUp, LookupByIndex

LookupRow
Returns a list of all values in a specific row of a lookup table, as an array of values
Syntax	LookupRow(table, name)
Inputs	Table is the name of the lookup table involved. The row to return the values from is specified by its row Name heading
Outputs	Returns an array containing the contents of the named row
Notes	This returns the same information as RowValues(table,name) but returns it as an array rather than a pipe separated string. This has the primary advantage of not discarding type information by converting everything to string values.
Examples	LookupRow ("sample", "140mm") = [$23.10,$51.00,$72.90]
See Also	RowValues, LookupRowMap, LookupCol

LookupRowLabels
Returns a list of all the row headings in a specified lookup table
Syntax	LookupRowLabels(table)
Inputs	Table is the name of the lookup table involved
Outputs	Returns an array containing the labels for each row
Notes	Equivalent to RowLabels except that it returns an array instead of a pipe-delimited string, and therefore preserve type information.
See Also	LookUp, LookupExact, LookupByIndex, RowLabels

LookupRowLabelsWithFilter
Returns a list of filtered values in a specific row of a lookup table
Syntax	LookupRowWithFilter(table, name, [row:val])
Inputs	Table is the name of the lookup table involved. The column to return the values from is specified by its row name heading. row:value pairs to match each row against
Notes	This is equivalent to LookupCol but with an extra parameter which takes a map value with col:value pairs to match each row against.
Examples	LookupRowWithFilter("test.qlt","Thickness",["Brand":"MyBrand","Material":"Melamine"]) Returns the "Thickness" value for every row in the qlt where the column "Brand" had the value "MyBrand" AND the row "Material" had the value "Melamine".
See Also	LookupCol, LookupRow, LookupExact, LookupByIndex

LookupRowMap
Looks up a QLT row returning it as a map.
Syntax	LookupRowMap(table [, rowName])
Inputs	Table is the name of the lookup table involved. Optional rowName is the label for the row to lookup
Outputs	Returns a map with keys that are the colLabels and values that are the elements from the specified row of the table. If rowName not specified, then fetches the whole table.
Notes	This returns the same values as RowValues(table,rowName) and LookupRow(table,rowName) but returns the values as a map with keys that are the colLabels. RowValues returns a a pipe separated string and LookupRow returns an array of these same values. Search items are case independent.
Examples	LookupRowMap("My.qlt", "rowName") = ["ColName1":"Value1", "ColName2":"Value2"]
See Also	RowValues, LookupRow, LookupColMap

LookupRowMapWithFilter
Returns a list of filtered values in a specific row of a lookup table
Syntax	LookupRowMapWithFilter(table, rowName, [row:val])
Inputs	Table is the name of the lookup table involved. The row to return the values from is specified by its row Name heading. [row:val] pairs to match each row against.
Notes	This is equivalent to the unary version of LookupRowMap except that it takes an extra parameter which is a map-style filter, similar to LookupColWithFilter. Search items are case independent.
See Also	LookupColWithFilter, LookupRowMap, LookupRowLabelsWithFilter

Reload [Action Only]
Refreshes the cached version of the specified table
Syntax	Reload QLT table or Reload *
Inputs	Table is the name of the lookup table to reload
Notes	Copies of lookup tables are kept in memory (cached) when they are first read to speed up access. If a table is changed on disk, the old copy in memory will keep being used unless Reload is called. See the section on Caching for details. If Reload * (asterisk) is used, all cached tables will be reloaded
Examples	Reload QLT "sample" Reload *

RemoveCol
Remove the column from the table
Syntax	RemoveCol (table, row)
Inputs	Table is the name of the lookup table to remove a column from
See Also	AddCol, RemoveColByIndex

RemoveColByIndex
Remove the nth column from the table
Syntax	RemoveColByIndex (table, n)
Inputs	Table is the name of the lookup table to remove a column from
See Also	AddCol, RemoveCol

RemoveRow
Remove the row from the table
Syntax	RemoveRow (table, row)
Inputs	Table is the name of the lookup table to remove a row from
See Also	AddRow, RemoveRowByIndex

RemoveRowByIndex
Remove the nth row from the table
Syntax	RemoveRowByIndex (table, n)
Inputs	Table is the name of the lookup table to remove a row from
See Also	AddRow, RemoveRow

Rollback [Action Only]
Abandons any changes made during the current Transactions on a specified lookup table
Syntax	Rollback QLT table
Inputs	Table is the name of the lookup table to abort the transaction on
Notes	Read the tutorial on Transactions for details
Examples	Rollback QLT "sample"
See Also	BeginTrans, CommitTrans, Update

RowCount
Returns the number of rows in a lookup table
Syntax	RowCount (table)
Inputs	Table is the name of the lookup table involved
Examples	RowCount ("sample") = 3
See Also	ColCount

RowLabel
Reads a specific row heading from a lookup table and returns it
Syntax	RowLabel (table, index)
Inputs	Table is the name of the lookup table involved. Index is the number of the row to get the heading of
Notes	If the row index is out of range, #N/A is returned
Examples	RowLabel ("sample", 1) = "400mm"
See Also	ColLabel

RowLabels
Returns a list of all the row headings in a specified lookup table
Syntax	RowLabels (table)
Inputs	Table is the name of the lookup table involved
Outputs	A string of all the headings joined together, separated by | (pipe)
Examples	RowLabels ("sample") = "400mm|900mm|1600mm"
See Also	ColLabels, LookUpRowLabels

RowLabelsForCol
Returns a list of row names, where those rows contain a specified value in a certain column
Syntax	RowLabelsForCol (table, col, value, match)
Inputs	Table is required, and is the lookup table to be used. If value is found anywhere down col, the names of the corresponding rows will be returned. Match is an optional Yes/No value and if set to No, will return all rows which contain a cell not equal to value in col
Outputs	A string of all the matched row names joined together, separated by | (pipe)
Notes	Value is optional - if it is omitted, all rows which do not have an empty value in col will be returned. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples
See Also	ColLabelsForRow, RowLabelsForVal, ColLabelsForVal

RowLabelsForCol2
Returns a list of row names, where those rows contain specified value in two columns
Syntax	RowLabelsForCol2 (table, col1, value1, match1, col2, value2, match2)
Inputs	Gets the names of the rows where col1 contains value1 and col2 contains value2. Match1 and Match2 are optional Yes/No variables which default to yes. If set to no, it instead searches for where the columns contains any other value (Match1 affects col1 and value1, Match2 affects col2 and value2)
Outputs	A string of all the matched row names joined together, separated by | (pipe)
Notes	Value1 and Value2 are optional - if they are omitted, the column names returned will be any columns with non-empty fields for col1 and col2. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples

RowLabelsForVal
Returns a list of row names, where those rows contain a specified value
Syntax	RowLabelsForVal (table, value, match)
Inputs	Table is required, and is the lookup table to be used. If value is found in a particular row, the name of that row will be added to the result list. Match is an optional Yes/No value and if set to No, will return all rows which contain a cell not equal to value
Outputs	A string of all the matched row names joined together, separated by | (pipe)
Notes	Value is optional - if it is omitted, all rows which are not completely empty will be returned. For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples
See Also	ColLabelsForVal, RowLabelsForCol, ColLabelsForRow

RowValues
Returns a list of all the values in a specific row of a lookup table
Syntax	RowValues (table, name)
Inputs	Table is the name of the lookup table involved. The row to return the values from is specified by its row heading, name
Outputs	A string of all the values joined together, separated by | (pipe)
Notes	If a row with a heading equal to (or more than) name can't be found, #N/A is returned
Examples	RowValues ("sample", "1.6m") = "$72.90|$102.20|$149.40|$198.60"
See Also	RowValuesByIndex, LookupRow, LookupRowMap, ColValues

RowValuesByIndex
Returns a list of all the values in a specific row of a lookup table
Syntax	RowValues (table, index)
Inputs	Table is the name of the lookup table involved. The row to return the values from is specified by its row number, index
Outputs	A string of all the values joined together, separated by | (pipe)
Notes	If index is out of range, #N/A is returned
Examples	RowValuesByIndex ("sample", 2) = "$51.00|$72.10|$118.80|$163.20"
See Also	ColValuesByIndex, RowValues

RowValuesFilterByRow2
Returns a list of values from a row, where another row in the same column contains specified values.
Syntax	RowValuesFilterByRow2 (table, row1, row2, val2)
Inputs	Finds the group of columns where row2 contains val2 and gets the values in row1.
Outputs	A string of all the row1 values from the columns that matched the criteria separated by | (pipe)
Notes	For detailed explanations and examples on this set of functions, see the Lookup Table tutorial section called Listing Functions
Examples	See the tutorial referred to above for examples

TableEditor
Launch table editor
Syntax	TableEditor (filestub)
Inputs	filestub launches table editor for filestub.qlt and default folder Tablefolder
See Also	FormEditor

Update [Action Only]
Changes the value of a cell in a lookup table, specified by either the names or indexes of its row and column
Syntax	Update QLT table Set Cell = newValue Where Row = rowLabel And Col = colLabel or Update QLT table Set Cell = newValue Where RowIndex = rowNum And ColIndex = colNum
Inputs	Table is the lookup table to update. RowLabel, colLabel are the names of the cell's row and column; rowNum, colNum are their indexes. If the cell exists, its value is changed to newValue
Outputs	Returns #N/A if the exact row and column cannot be found
Notes	This function only changes the values of existing cells - no new rows or columns are created. The Update statement must also be inside a transaction. Read the tutorial on Transactions for details
Examples	Update QLT "sample" Set Cell = $154.90 Where Row = 1.6m And Col = 95mm
See Also	BeginTrans, CommitTrans, Rollback