D365FO pu20 union query problem: “There is a field mismatch in the union query. Field [Field Name] is not compatible with field .”

I found an exciting and obscure bug (? – “feature?”) in Dynamics 365 for Finance and Operations. It doesn’t seem to rear its head in platform update 15, but does in platform update 20. If you get the run time error message (notice the intentional space that looks like a missing word before the period at the end):

There is a field mismatch in the union query. Field [Field Name] is not compatible with field .

…then you might have made the same mistake we made.

If you are creating a union Query, and you’ve set “Derived Table,” I think it was ignored in the past. However, as of platform update 20, if you set it– and if there is a mismatch within the fields of a data source– you will get no error when you compile/build, but you WILL get the above error at run time.

Here’s an example showing the mistake we had:

Derived Table matches

Derived Table typo

Look closely. MORE closely. For most of the columns under ProjEmplTransactionsView in XskProjVendTransQuery, the “Derived Table” is set to ProjEmplTransactionView. (No pluralizing “s.”) It is ProjEmplTransactionsView (with the “s”) for XskVendInvoiceTransRecId.

I’m not sure that “Derived Table” is even needed (what does it do?) but when it was consistently ProjEmplTransactionsView for all the columns where it is set to something, the run time errors went away.