In , we added a new field property to define what fields can be used to join with a field. This ticket is to implement that behavior and use it when deciding what fields can be used in a join (MODFQMMGR-608).

Prior to this change, when FQM decides which fields it should allow for a join in an ad-hoc ET, it would look at existing entity types to see what fields are used. With this change, it will also look at the joinable fields list for every field.

(note: this is the same example as , but augmented with the updated behavior) So, for example, if a user selects items as the base entity type for their ad-hoc ET, the UI will send a request to FQM with that entity type (wrapped in a fairly sparse ad-hoc ET object) and no other parameters specified, to determine what can be joined with it. FQM will then:

1. look for all fields from items that are either used in one of those predefined joins

2. look for all fields from items that are in the list of joinable fields from any other entity types

3. look for entity types that contain items

4. return that list of fields from 1 and 2 as possible ad-hoc fields

5. return the list of ETs from 3 plus the ETs that contain any of the fields from 2 as the possible target ETs

The UI will then display that data for the user to narrow down the details for the join.

If the user selects then selects locations as the target ET for a join, the UI will send that FQM. FQM will then:

1. look for fields from items that are used in the joins for predefined ETs that join items and locations (e.g., effective location ID, temp location ID, permanent location ID)

2. look for fields from items that are in the list of joinable fields for any fields in locations

3. look for fields from locations that are used in the joins for predefined ETs that join items and locations

4. look for fields from locations that are in the list of joinable fields for any fields in items

5. return the fields from 1 and 2 as the list of possible ad-hoc ET fields

6. return the fields from 3 and 4 as the list of possible target fields

After the user selects either an ad-hoc field or a target field, the UI will send that, and FQM will run through the same process for the other fields, filtering down to only ones that match the field they already selected (so selecting an ad-hoc field will make it look for fields from locations that are used in joins between items and locations using the selected ad-hoc field from items).

After the user has all selected all 3 components, they will be added to the ad-hoc entity type definition. If the user wants to join another source with that, then the above process is all the same, except everywhere that we searched for items before will change to searching both items and locations.