Applicable Versions: All



Required values occur when conditions are created against the subject or query, that have been designated as Required Values in either the subject or query wizard. In normal operation, DataPA prompts the user for values to satisfy these required values in the query wizard at runtime. However, when embedding DataPA, you may often wish the application to set these required values, for instance if you are designing an invoice viewer, you may want the Application to select the Invoice to be displayed.


The RequiredFields collection has one property and one method that allow us to retrieve information about the required fields. First is the Count property that retrieves the number of required values for the query. The following VB code example shows how to display a message showing the number of required fields:


MsgBox mApplication.Query.RequiredFields.Count


To retrieve individual required fields, the required fields property has an Item method. The Item method returns a DataPA.Fields object. The table below summarised the properties of the fields object: 


Property
Type
Description
Name
Character
The name of the required field
Label
Character
The label used when prompting for the required field
Description
Character
The description used when prompting for the required field
DataType
Character
The Progress data type of the required field
Mandatory
Logical
Indicates whether the required field is mandatory or not
Format
Character
The Progress format of the required field



The following code shows how to display a message showing the name of each required field for a query:


Dim ix As Integer
Dim RequiredFields As DataPA.Fields
 
Set RequiredFields = mApplication.Query.RequiredFields
For ix = 1 To RequiredFields.Count
   MsgBox RequiredFields.item(ix).Name
Next ix
 




The SetRequiredField method on the query object allows you to programmatically set a value of a required field. If a required field value is set programmatically BEFORE you call the RunQuery method, the query wizard will not prompt the user for that value. The SetRequiredField method requires two parameters. The first is the field name, a character expression that resolves to a field name. The field name should match exactly the name value returned from the Field object. The second is the value, an expression that represents the value you wish to assign to the field. The value should be the data type specified by the DataType property of the Field object. The following code extract sets the value of the custnum field to 1 before running the query:


Call mApplication.Query.SetRequiredValue(""sports2000.Customer.CustNum"", 1) 
mApplication.Query.RunQuery
 
 




Required values can sometimes be optional. If the person who created the query did not check the mandatory option when creating the required field, the user is given the option to skip the required field. You can programmatically skip a required field using the SetSkipField method. If the SetSkipField method is called on a non-mandatory required field BEFORE the RunQuery method is called, the required field will not be prompted for by the run query wizard. The SetSkipField method requires a single parameter, FieldName, a character expression that resolves to a field name. The field name should match exactly the name value returned from the Field object. The following code extract sets the custnum field to skip before running the query: 





Call mApplication.Query.SetSkipField(""sports2000.Customer.CustNum"")
mApplication.Query.RunQuery
 




The following code extract shows how we can put all this together to prompt for any parameters from any query from our VB application before we run the query:





  Dim ix As Integer
  Dim vValue As Variant
  Dim vDate As Date
  Dim mQuery As DataPA.Query
 
  Set mQuery = mApplication.Query
  If TypeName(""mQuery"") = ""Nothing"" Then Exit Sub
 
  If MsgBox(""Prompt for parameters first?"", vbQuestion + vbYesNo) = vbYes Then
    For ix = 1 To mQuery.RequiredFields.Count
      If mQuery.RequiredFields.item(ix).DataType <> ""date"" Then
        vValue = """"
      Else
        vValue = Format$(Date, ""mm - dd - yy"")
      End If
     
      vValue = InputBox(mQuery.RequiredFields.item(ix).Description, mQuery.RequiredFields.item(ix).Label, vValue)
      If vValue <> """" Then
        If mQuery.RequiredFields.item(ix).DataType <> ""date"" Then
          mQuery.SetRequiredValue mQuery.RequiredFields.item(ix).Name, vValue
        Else
          mQuery.SetRequiredValue mQuery.RequiredFields.item(ix).Name, DateValue(vValue)
        End If
      Else
        mQuery.SetSkipField mQuery.RequiredFields.item(ix).Name
      End If
    Next ix
    mQuery.SkipIntro = True
    mQuery.SkipExport = True
  End If
  mQuery.RunQuery

  mApplication.Query.RunQuery