DataMgr Information

Subscribe to DataMgr

Email:

Lists

List Relation Fields allow you to automate the handling of many-to-many relationships using lists. This essentially automates the saveRelationList() method with the added benefit that it brings the list into the recordset returned by getRecords().

You can filter by this value as well, limiting the recordset to records that have at least one of the list items given (so the longer the list you pass in to this key, the more results you are likely to get).

Attributes/Keys:

type (required): "list".
table (required): Name of table from which you will pull a list of values.
field (required): The name of the field which will hold the values for the list.
delimiter (optional): The list delimiter character (defaults to ",").
sort-field (optional): The name of the field in the related table by which to sort the values in the list.
bidirectional (optional): A Boolean value used only for self-referential many-to-many relationships (see note at end of this section).
join-table (optional): If you need to pull values for a many-to-many relationship, the table that indicates the relationships between the local (main) table and the remote (related) table.
join-field (optional): If the join-table has a field matching the primary key field of your local table and a field matching the field on which you want to join in the remote table, use join-field to indicate the field joining the remote table to the join-table. Otherwise use the keys below.
local-table-join-field (optional): The field in the local table used in the relationship (its value will match the value of the join-table-field-local field in the join-table).
remote-table-join-field (optional): The field in the remote table used in the relationship (its value will match the value of the join-table-field-remote field in the join-table).
join-table-field-local (optional): The field in the join-table used in the relationship (its value will match the value of the local-table-join-field in the local table).
join-table-field-remote (optional): The field in the join-table used in the relationship (its value will match the value of the remote-table-join-field in the remote table).

Example 1:

<field ColumnName="Items">
  <relation
    type="list"
    table="items"
    field="item_id"
    join-table="rec2items"
    join-field="rec_id"
  />
</field>

The above example assumes that the join-table has a field of the same name as the primary key field of your main table and that the "join-field" field is the other key field in the join-table and exists in the table indicated in the "table" attribute. If these things are not true, you will need to use a more verbose syntax (introduced in DataMgr 2.1):

Example 2:

<field ColumnName="Items">
  <relation
    type="list"
    table="items"
    field="item_id"
    join-table="rec2items"
    join-table-field-local="rec_id"
    join-table-field-remote="item_id"
    local-table-join-field="rec_id"
    remote-table-join-field="item_id"
  />
</field>

This example indicates the exact same structure as the first example, just using the more verbose format.

Note on bidirectional:

If you have a self-referential many-to-many relationship, you can use the "bidirectional" key to indicate if each relationship should be represented in both directions. A self-referential many-to-many relationship is one in which the local (main) table is the same as the remote (related) table and a join-table is in use.