Use the SQL property to show geometry that is not stored in the current table, but in a related table. The current table may also be an attribute table without inherent geometry, but with a related feature.
By default, the SQL property of a geometry control is empty. In this case, the geometry of the current form is shown. You can use an SQL statement to show the geometry of a feature, which is saved in a related table (a "related child" feature).
Keep the following in mind:
- See Use the SQL Assistant in Forms
- SQL statements always return an FID.
- Attribute names must be written in braces, for example {FID}, {Y1}.
- You can use SQL statements that follow the relation of up to ten tables. If the SQL returns more than one FID, only the first FID is taken. Geometry controls using the SQL property always are read-only (locked).
- All attributes in {} are replaced by the actual value. The {} attributes are existing in the current form (in the following example: the Line table).
Following are some examples of using geometry controls with SQL.
Show the geometry of a related point
The feature class form (table) WA_LINE has an attribute FID_STARTPOINT that is related to a table WA_POINT from which the geometry shall be shown.
select FID from WA_POINT where fid={FID_STARTPOINT}
or:
{FID_StartPoint}
The FID is unique in the whole database, therefore the system can find the feature class (table) with this FID without knowing the table name.
Show the coordinates of the manholes at the beginning and end of a section in the WW_SECTION form
The manhole FIDs are saved in the WW_SECTION form in the attributes {FID_first_manhole} and {FID_last_manhole}.
In the WW_SECTION form: Create a geometry control and define the SQL:
{fid_first_manhole}
The detailed SQL is:
select FID from WW_MANHOLE where fid={FID_first_manhole}
The SQL statement has to yield an FID. The geometry of this FID is shown.
Show the pipe geometry in a sewage works form
SEWAGE_WORKS.FID_STRUCTURE; STRUCTURE.FID_PIPE; PIPE.GEOM
select FID_PIPE from STRUCTURE where FID = {FID_structure}
Select
select FID_<t1> from t2 where FID in (select FID from <t3> where FID_<t4>={t4} and structuretype ='iron')