Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / How to join tables on ...
Finance forum
Suggested answer

How to join tables on DataEntity where date between DateFrom and DateTo

(2) ShareShare
ReportReport
Posted on by Sophs@4Sight 110

Hi

This is a very simplified version of what I am trying to do. 
 

I have 2 tables.  The first is standard F&O VendTrans and the second is a custom table xxxVendCert. The second table contains the VendAccount , CertId , DateFrom and DateTo.  The DateFrom and DateTo does not allow any overlapping of dates for the specific vendor.

How can I create a DataEntity where I can show all the VendTrans record with the CertId.  I want to create a outer join between VendTrans and xxxVendCert where VendTrans.TransDate between xxxVendCert.DateFrom and xxxVendCert.DateTo.  Or alternatively add a range where the range includes the Transdate of the VendTrans records.

Kind regards.

Categories:
Finance
Development / Customization / SDK
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,580 Moderator on at
    How to join tables on DataEntity where date between DateFrom and DateTo
    Seems issue is with the refresh. You can try refreshing Virtual Entity Metadata.
     
  • Sophs@4Sight Profile Picture
    Sophs@4Sight 110 on at
    How to join tables on DataEntity where date between DateFrom and DateTo
    Hi Andre
    Thanks for your response.  I am using this entity in dataverse as a virtual entity.  We are customizing the copilot, And so adding this field as a computed column or postload, then it is not visible.
    Unless I am missing something.
     
     
     
  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 290,665 Super User 2024 Season 2 on at
    How to join tables on DataEntity where date between DateFrom and DateTo
    Hi Sophs,
     
    You can consider creating a computed column or virtual field for the CertId field.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Another great gathering of our Community!

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

More Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,665 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,971 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,150

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans