Whilst you can join almost any field to any other, choosing the correct fields to join is important if the query is to give the desired results.

Joins and Record Links

Most joins are made to connect a Record Link field (on say 'Table A') to the Table it links to (say 'Table B').

To do this you need to ensure that you have added a Record ID field to 'Form B', as you need to join the Record Link field on 'Form A' to the Record ID field on 'Form B'

Record IDs

Every Record in a Table has a Record ID. This is a system-generated number which uniquely identifies the record, and which never changes. In many situations the Record ID is used internally, and you do not need to expose it. However, if you do, for example to participate in a Join, you need to add a field of type 'Record ID' to the form. (The name you give the field does not matter – often simply 'Record ID' is a good choice, but if you have many Tables involved in a  join it may be less confusing if you include the Table name, thus say 'Project ID'

Joins and Multi Record Links

A Multi Record link field allows multiple items to be selected from another table, (often visualised as a checkbox array).

Since a Join implies a one-to-one relationship, we need to involve an intermediate table to allow for this expansion.

The system automatically creates this table whenever you add a Multi Record Link field to a Table, giving it the name of the format:

{Table A}.{Multi Record Link field name}_{Table B}

1. Add 'Table A', the intermediate table, and 'Table B' to the Query.
2. Draw a Join between a Record ID field on 'Table A' and the 'Source RecordId' field on the intermediate table
3. Draw a Join between a Record ID field on 'Table B' and the 'Object RecordId' field on the intermediate table

Note: a common error is to try to draw the join line from the Multi Record Link field on the source Table. You need to draw it from the Record ID instead.

Sequencing

Note that the intermediate table contains a 'Sequence' field, which you can sort by. This contains the sequence information which can be specified in some Multi Record Links.