If you turn on the Enable Table/Field Favorites configuration setting and the user uses the Analyze Database function in Stonefield Query, a new table called Favorites.DBF is created in the Data subdirectory of the Stonefield Query program folder. This table contains the names of tables and fields that contain meaningful data in the database. The user can turn on the Show only favorites button in Step 2 of the report wizards to display only those tables and fields in Favorites.

One problem is that tables and fields you add dynamically to the data dictionary, such as in the DataEngine.GetCustomMetaData script, may not be marked as favorites, so do not appear when the user turns on Show only favorites. To prevent this, call DataEngine.AddToFavorites to dynamically add tables and fields to the Favorites table.

Syntax

AddToFavorites(ObjectName as String)

Parameters
ObjectName
The name of the table or field to add to the Favorites table.

Return Value
None.

Example
This example, taken from DataEngine.GetCustomMetaData, adds custom fields the user has added to their tables to the Stonefield Query data dictionary. It assumes there's a table called CUSTFLDS that contains the necessary information. In this table, FIELDNAME contains the field name, FIELDTYPE contains the field type, FIELDLEN contains the field width, FIELDDEC contain the number of decimals, and FIELDHEAD contains the caption for the field. After adding the field, the call to AddToFavorites ensures the fields show up when the user turns on the Show only favorites button.

Visual FoxPro

lparameters toApplication as SQApplication
local lnSelect, loField as Field, lcName
lnSelect = select()
select 0
use (toApplication.TargetApplicationDirectory + 'CUSTFLDS')
scan
  lcName  = trim(FIELDNAME)
  loField = toApplication.DataEngine.Fields.AddItem(lcName)
  loField.Type     = FIELDTYPE
  loField.Length   = FIELDLEN
  loField.Decimals = FIELDDEC
  loField.Caption  = trim(FIELDHEAD)
  toApplication.DataEngine.AddToFavorites(lcName)
endscan
use
select (lnSelect)
return .T.

VBScript

function Main(Application)
dim Conn, RS
set Conn = createobject("ADODB.Connection")
set RS = createobject("ADODB.RecordSet")
Conn.ConnectionString = "provider=SQLOLEDB.1;" & _
  "data source=(local);initial catalog=MyDatabase;"
Conn.Open
RS.ActiveConnection = Conn
RS.Open("select * from CUSTFLDS")
do while not RS.EOF
  Name  = RS.Fields("FIELDNAME").Value
  Field = Application.DataEngine.Fields.AddItem(Name)
  Field.Type     = RS.Fields("FIELDTYPE").Value
  Field.Length   = RS.Fields("FIELDLEN").Value
  Field.Decimals = RS.Fields("FIELDDEC").Value
  Field.Caption  = RS.Fields("FIELDHEAD").Value
  Application.DataEngine.AddToFavorites(Name)
  RS.MoveNext
loop
RS.Close
Conn.Close
Main = True
end function

JavaScript

function Main(Application) {
var Conn, RS, UserName, User, e ;
Conn = new ActiveXObject('ADODB.Connection') ;
RS = new ActiveXObject('ADODB.RecordSet') ;
Conn.ConnectionString = 'provider=SQLOLEDB.1;' +
  'data source=(local);initial catalog=MyDatabase;' ;
Conn.Open ;
RS.ActiveConnection = Conn ;
RS.Open('select * from CUSTFLDS') ;
do {
  Name  = RS.Fields("FIELDNAME").Value ;
  Field = Application.DataEngine.Fields.AddItem(Name) ;
  Field.Type     = RS.Fields("FIELDTYPE").Value ;
  Field.Length   = RS.Fields("FIELDLEN").Value ;
  Field.Decimals = RS.Fields("FIELDDEC").Value ;
  Field.Caption  = RS.Fields("FIELDHEAD").Value ;
  Application.DataEngine.AddToFavorites(Name) ;
  RS.MoveNext ;
  }
}
while (! RS.EOF) ;
RS.Close ;
Conn.Close ;
return true ;
}

C#

public static bool DataEngine_GetCustomMetaData(SFQApplication sfqApplication)
{	
  Database database = SQApplication.DataEngine.Databases.GetMainDatabase();
  string resultSet = database.ExecuteSQLStatement("select fieldname, fieldtype, 
    fieldlen, fielddec, fieldhead from custflds");

  XmlDocument doc = new XmlDocument();
  doc.LoadXml(resultSet);

  string nodePath = "/DataSet/_resultset";
  XmlNode node = doc.SelectSingleNode(nodePath);
  if (node != null)
  {
    do
    {
      string fieldName = node.Attributes[0].Value;
      string fieldType = node.Attributes[1].Value;

      int fieldLen = 0;
      Int32.TryParse(node.Attributes[2].Value, out fieldLen);

      int fieldDec = 0;
      Int32.TryParse(node.Attributes[3].Value, out fieldDec);

      string fieldHead = node.Attributes[4].Value;

      // Please note that any changes you make to this item will only be saved in
      // Stonefield Query
      // after you call the Dispose() method or by using the 'using' syntax below
      // which calls Dispose() for you automatically
      using(Field field = sfqApplication.DataEngine.Fields.AddItem(fieldName.Trim()))
      {
        field.Type     = fieldType;
        field.Length   = fieldLen;
        field.Decimals = fieldDec;
        field.Caption  = fieldHead.Trim();
        sfqApplication.DataEngine.AddToFavorites(fieldName.Trim());
      }

      node = node.NextSibling;
    } while (node != null);
  }
  return true;
}

VB.NET

public shared function DataEngine_GetCustomMetaData(sfqApplication as SFQApplication) _
    as Boolean
  Dim database as Database = SQApplication.DataEngine.Databases.GetMainDatabase()
  Dim resultSet as string = database.ExecuteSQLStatement("select fieldname, _
    fieldtype, fieldlen, fielddec, fieldhead from custflds")

  Dim doc as XmlDocument = new XmlDocument()
  doc.LoadXml(resultSet)

  Dim nodePath as string = "/DataSet/_resultset"
  Dim node as XmlNode = doc.SelectSingleNode(nodePath)
  if Not node is Nothing
    do
      Dim fieldName as string = node.Attributes(0).Value
      Dim fieldType as string = node.Attributes(1).Value

      Dim fieldLen as Integer = 0
      Int32.TryParse(node.Attributes(2).Value, fieldLen)

      Dim fieldDec as Integer = 0
      Int32.TryParse(node.Attributes(3).Value, fieldDec)

      Dim fieldHead as string = node.Attributes(4).Value
            
      ' Please note that any changes you make to this item will only be saved in
      ' Stonefield Query
      ' after you call the Dispose() method or by using the 'using' syntax below 
      ' which calls Dispose() for you automatically
      using field as Field = sfqApplication.DataEngine.Fields.AddItem(fieldName.Trim())
        field.Type     = fieldType
        field.Length   = fieldLen
        field.Decimals = fieldDec
        field.Caption  = fieldHead.Trim()
        sfqApplication.DataEngine.AddToFavorites(fieldName.Trim())
      End Using

      node = node.NextSibling
    Loop While Not node is Nothing
  End If
  return true
End Function

See also

DataEngine Object | DataEngine.GetCustomMetaData

© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic