Finance | Project Operations, Human Resources, ...

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

(3) Share

Report

Posted on by Sophs@4Sight

269

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.

All responses (4)

Answers (0)

Sort by

Suggested answer

André Arnaud de Cal... 307,259 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Sophs,

You can consider creating a computed column or virtual field for the CertId field.

Computed columns and virtual fields in data entities - Finance & Operations | Dynamics 365 | Microsoft Learn
Sophs@4Sight 269 on at

Like (0)

Report
Copy link

Link copied!

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

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

Seems issue is with the refresh. You can try refreshing Virtual Entity Metadata.

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/enable-virtual-entities
André Arnaud de Cal... 307,259 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Sophs,

Computed columns are visible when using OData. So, I do assume it should be visible for Copilot as well. I do assume you are only reading data from this entity?