Skip to main content

Initialize a Control Using SQL

  1. Last updated
  2. Save as PDF

Overview

Use SQL to initialize a control.

Details

Initialized data does not have to come from another form or table. Starting with TIER 5.02 and higher, the Filter section under the initialize property of a control will allow writing custom filters using full SQL statements. In the Filter section of the initialization, be sure to put /*SQL*/ first and then type your query.

For example, to display the first day of the year on a form, you could use SQL initialization, as outlined in the next section. This is just one of the many examples of when you may want to use SQL to initialize a control. 

Steps

  1. First, write your SQL query to get this information and confirm that it works in SQL. For the example above, our query is as follows:

SELECT DATEADD(YYYY,DATEDIFF(YYYY,0,GETDATE()),0) AS FDOY

  1. Once you’ve confirmed that the query works in SQL, you are ready to open TIER Design and add this information to your control. 
    • Select the control to initialize and expand the initialize property in the Object Inspector. 
    • Enter the field you want to initialize.
      note-3L.png This is the field from the results of the SQL query. If you are using a calculation, convert, and so on to get your field, you must include an alias name so you have something to reference. In this example FDOY is the alias field name. If this is an alias entry or if the table name will be an alias, blank out the table name until after you have typed in the field name. 
    • In the filter section, enter /*SQL*/ followed by the SQL query.
    • In the table section, enter a table name.
      note-3L.png The SQL will overwrite this, so the table does not have to exist in your database, but it must be there in order for the initialize property to say Yes. You should leave this blank until after you enter the field if the field you are entering does not exist in the table you are entering.
  2. Set the frequency of your initialization – Everytime or CreationOnly.

tier_sqlinitialize.png
Sample Object Inspector Properties of Initialization

  1. Test your form to see if it initializes properly.