GetValues method

Returns a variant array containing values from the current Table object. If the query does not match any values, the method returns an empty variant.

Syntax

GetValues([MatchColumns], [MatchValues], [OutColumns], [Distinct], [OrderBy], [FieldsInfo]) As Variant

Remarks

The GetValues method executes an SQL query with the specified parameters. For example, the following VBScript statement:

Vault.Table("Employees").GetValues(Array("Role","Department"), _
    Array("Manager","Engineering"), _
    Array("FirstName","LastName"), _
    False, "LastName")

Translates into the following SQL query:

SELECT FirstName, LastName FROM Employees WHERE Role='Manager' AND Department='Engineering' ORDER BY LastName

Note    The returned values array will hold the property values in the second dimension of the array, not the first dimension as might be expected.

If the FieldsInfo parameter is specified, that array contains information about the returned value array. This can be useful if you want a generic function that can operate with the results of different queries regardless the output columns and the order in which order they were requested. Moreover, using FieldsInfo, you can process results from different tables (or queries to external data sources) without specific knowledge of the column names, which may be named differently in different tables but have the same purpose in the context of your processing.

The following example functions demonstrate the use of FieldsInfo.

' Helper function that finds the index of a ShareName column in the results table.
Function FindShareNameIndex(fi)
  FindShareNameIndex = -1
  If IsArray(fi) Then
    For i = 0 To UBound(fi,1)
      If fi(0,i) = "ShareName" And fi(1,i) = 202 Then
        FindShareNameIndex = i
        Exit Function
      End If
    Next
  End If
End Function

Function TestFieldsInfo()
  Dim fi
  Dim res
  res = Vault.Table("GCFShares").GetValues(,,,,,fi)
  If IsArray(fi) Then
    Dim sni
    sni = FindShareNameIndex(fi)
    TestFieldsInfo = ""
    For j = 0 To UBound(res,2)
      TestFieldsInfo = TestFieldsInfo & res(sni,j) & ";"
    Next
  Else
    TestFieldsInfo = fi
  End If
End Function