Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / DMF export filter for ...
Finance forum
Unanswered

DMF export filter for not exists join within data entity

(1) ShareShare
ReportReport
Posted on by Rich 203
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.
Categories:
Integrations
Development / Customization / SDK
Data Integration
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,995 Super User on at
    DMF export filter for not exists join within data entity
    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.
     
  • Anton Venter Profile Picture
    Anton Venter 9,599 Super User on at
    DMF export filter for not exists join within data entity
    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?
  • Rich Profile Picture
    Rich 203 on at
    DMF export filter for not exists join within data entity
    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.
  • Anton Venter Profile Picture
    Anton Venter 9,599 Super User on at
    DMF export filter for not exists join within data entity
    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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans