DataMgr Build 1 Documentation: "ftable"Attribute

"ftable"Attribute

In any relation field in which DataMgr gets data from another table (other than the primary table for the query), it has to know how the two tables connect.

Normally this is done by the use of "join-field" attributes. You can, however, add an "ftable" attribute to a field to indicate that it points to the primary key of the indicated table.

This is very much like establishing a foreign-key relationship in a database except that it is done at the DataMgr level instead of the database level and does not actually create a constraint in the database.

If you have a "ftable" field then you can omit the "join-field" attributes in your relation fields between the two tables.

For example, this code:

<tables>
	<table name="categories">
		<field ColumnName="CategoryID" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryName" CF_Datatype="CF_SQL_VARCHAR" Length="120" />
	</table>
	<table name="products">
		<field ColumnName="ProductID" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryID" CF_Datatype="CF_SQL_INTEGER" />
		<field ColumnName="Category">
			<relation
				type="label"
				field="CategoryName"
				join-field="CategoryID"
			/>
		</field
	</table>
</tables>

could be replaced with this code:

<tables>
	<table name="categories">
		<field ColumnName="CategoryID" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryName" CF_Datatype="CF_SQL_VARCHAR" Length="120" />
	</table>
	<table name="products">
		<field ColumnName="ProductID" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryID" CF_Datatype="CF_SQL_INTEGER" ftable="categories" />
		<field ColumnName="Category">
			<relation
				type="label"
				field="CategoryName"
			/>
		</field
	</table>
</tables>

This would work even if the primary key field and the ftable field were not the same. So, this code:

<tables>
	<table name="categories">
		<field ColumnName="id" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryName" CF_Datatype="CF_SQL_VARCHAR" Length="120" />
	</table>
	<table name="products">
		<field ColumnName="id" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="category_id" CF_Datatype="CF_SQL_INTEGER" />
		<field ColumnName="Category">
			<relation
				type="label"
				field="CategoryName"
				join-field-local="category_id"
				join-field-remote="id"
			/>
		</field
	</table>
</tables>

could be replaced with this code:

<tables>
	<table name="categories">
		<field ColumnName="id" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="CategoryName" CF_Datatype="CF_SQL_VARCHAR" Length="120" />
	</table>
	<table name="products">
		<field ColumnName="id" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
		<field ColumnName="category_id" CF_Datatype="CF_SQL_INTEGER" ftable="categories" />
		<field ColumnName="Category">
			<relation
				type="label"
				field="CategoryName"
			/>
		</field
	</table>
</tables>

This is a small savings if you have only one relation field between the two tables, but becomes a much bigger savings if you have several. It also helps with clear documentation as you more fully see the purpose of a field by looking at it in isolation.