DMF export filter for not exists join within data entity

(1) Share

Report

Posted on by Rich

326

I have a custom data entity joining VendTrans to VendInvoiceJour and then to a custom table (call it ExternalID) which only contains records for certain invoices. This custom entity was designed to cover two different export scenarios: those where the ExternalID exists & those where it doesn't: as such, the data entity contains both the human-relevant ExternalID string value and the the F&O ExternalID.RecordID field. For the first scenario, a simple ">0" criteria on the ExternalID.RecordID provides the filtered records as I'd expect, but I haven't found a way to filter solely for the VendTrans/VendInvoiceJour records with no ExternalID record. The RecordId will appear to be 0 as a sentinel/null-replacement on export, but using 0 as a criteria presumably compares 0 to null during export, and excludes the record. Because the relationship is within the data entity, I don't believe there's a way to use the table joins feature, but I'd happily be educated if I'm missing something: any suggestions please?

If there's no way to specify a "null value" criteria, I can only think that a calculated column that makes the presence/absence of ExternalID clear could be added to the entity, but that feels like it should be unnecessary.

I have the same question (0)

All responses (6)

Answers (1)

Anton Venter 20,346 Super User 2025 Season 2 on at

Like (0)

Report

Hello,

From what I understand in your situation, you should be able to filter on where RecordId is 0 to filter all records without an ExternalID and where RecordId !0 for the records with an ExternalID. Filtering >0 is not 100% correct because RecordIDs can be negative.

In Dynamics 365 F&O all datatypes are defaulted to the "null" value of the datatype e.g. int / int64 defaults to 0 and strings to "". Null is not used in Dynamics 365 F&O.

Was this reply helpful? Yes No
Rich 326 on at

Like (0)

Report

Anton,

Thanks for input (particularly the RecordID negative point: never seen that, so appreciate the education). I could have been clearer that my "using 0 as a criteria presumably compares 0 to null during export" is a theory based on observation: specifying any of the Record IDs available as 0 (as below) gives me no records on output (where an export with no filter gives me several hundred), so I believe the query must be effectively enforcing the presence of the ExternalID table: if there is a row in that table, the RecordID will be something other than 0, and if there isn't a row, then the comparison can't be completed, so the data entity filter considers the primary datasource rows to be filtered out as well.

Was this reply helpful? Yes No
Anton Venter 20,346 Super User 2025 Season 2 on at

Like (0)

Report

I recommend to add labels to the fields in the table so that you can see which field is selected in the filter / range grid section. Now you cannot be certain you have selected the correct field to filter on. There are multiple fields with the same Record-ID label. I assume you have added more than one field referring to other tables.

How is the data entity built up? How did you define the relation?

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,146 Super User 2025 Season 2 on at

Like (1)

Report

Hi Rich,

When adding filters, the filter on record ID with value zero will not work for your scenario. Instead, you can try to add a NotExist join in your export filter.

You can then add your custom table like the VendTrans in the example below.

Was this reply helpful? Yes No
Rich 326 on at

Like (0)

Report

Thanks André. I was pretty confident (from observation & expectation) that record ID 0 wasn't going to work, but always appreciate hearing it from the voice of authority :)

My custom table doesn't appear in that list: I'd looked at it, and was trying to communicate as much when saying "I don't believe there's a way to use the table joins feature". Suspect I need to understand how the DMF filter dialog lists the tables available there: I've tinkered with adding relationships to the data entity, but ended up finding it quicker to use SysComputedColumn::isNullExpression()

Very happy to be told this is less efficient/overcomplicated if you can easily point me to how I can get my custom table available for a NotExists join. Given your screenshot, I might be able to work it out by looking at the Vendors table and its relation to Vendor transactions, and will reply if I do...

Was this reply helpful? Yes No
Rich 326 on at

Like (0)

Report

Examining VendTable and VendTrans showed me that the NotExists availability is based on the "far" table having a foreign key relationship to the data entity/table where I'm "starting" the filter. Be curious whether anyone knows for certain which is more computationally efficient: can probably work it out with DB tuning advisor or similar.

Both the isNullExpression calculated column and André's suggestion work, but the NotExists join feels slightly less artificial, so I like that as the answer.

Was this reply helpful? Yes No