Joining data source by custom field breaks the underlying SQL
This may be hard to explain, but I'll try: I have one Data Source with a system name of "foundations". It is a data source of type Organization, with a filter for a specific contact sub-type. That Data Source pulls from civicrm_contact and brings in the custom fields I have set up for the contact sub-type, which is great.
One of those custom fields is a contact reference field, so it's values are the Contact ID of another contact in civicrm_contact. I want to then add another Data Source (of type Contacts or type Individual, either one), join to it via this custom field, and show the Display Name of that other Contact.
So I created a new Data Source with a type of Individual (with system name "individual"). I then used the join section of the data source window to join my custom field to the Contact ID of the Contacts table. It seems like this should work fine - I'm joining a field that is a Contact ID to the Contacts table, ID to ID.
However, once I do that, the data processor gives a fatal DB error. When I go to check the CiviCRM log, I see that the underlying SQL from the data processor is wrong. It is referencing the original civicrm_contact table (in this case, with an alias of "foundations") to try and join to the civicrm_contact table again. But the custom field is not in the "foundations" table, it's in a separate custom field table. So the SQL should instead be bringing in the custom field table and joining on the associated columns.
It seems like the data processor does not actually bring in custom tables from the database. I don't see those tables in any of the underlying SQL statements. Therefore, I'm guessing it cannot do joins from a custom field to another table.
Attached is a file with the specific error so you can see the missing table and therefore missing field to join on. The table column "tsw_connection_48" is not in the civicrm_contact (alias of "foundations") table, it's in a custom field table, in this case with the name "civicrm_value_foundation_in_8".