Power Platform Community Forum Thread Details

Dears,

First time posting to this forum and eager to get involved in the community!

I'm building a Dataverse for teams canvas power apps with dataverse as datasource. I have a table, Timesheet Entries, which has for each record an Entry Date, defined like this (Date only) in Dataverse:

I have a many-to-many relationship between the people table and the timesheet entry table, because multiple people can be linked to a timesheet entry and multiple timesheet entries can be linked to the same person.

In my canvas app, I want to create a gallery with all Timesheet entries between 2 dates, starting from the person. The start and end date are defined as:

Set(
 StartDate;
 Date(cmbReportMonth.Selected.Year; cmbReportMonth.Selected.Month; 1)
);;
Set(
 EndDate;
 Date(cmbReportMonth.Selected.Year; cmbReportMonth.Selected.Month + 1; 1)
);;

(don"t mind the weird syntax, it's because I'm working in Dutch, not in English, but syntactically everything is ok).

If I create a filter for the Items of the gallery to show all timesheet entries between the start and end date for a specific person, it fails to properly fetch them (the Items are empty).

An overview of what works and what doesn't:

1) Filter('Timesheet Entries'; 'Entry Date' >= StartDate && 'Entry Date' < EndDate)
2) Filter('Timesheet Entries'; DateValue('Entry Date') >= StartDate && DateValue('Entry Date') < EndDate)
3) Filter(First(People).'Timesheet Entries'; 'Entry Date' >= StartDate && 'Entry Date' < EndDate)
4) Filter(First(People).'Timesheet Entries'; DateValue('Entry Date') >= StartDate && DateValue('Entry Date') < EndDate)

1) This works, using the start and enddate and comparing directly to the 'Entry date' gives me the records I need. Note that this is without using the many-to-many relationship, just as a test that the date fitering works.

2) This works as well

3) This is the one that I want to get working but am unable to! It returns an empty collection. It uses the 'Timesheet Entries' fetched from a relationship on the first selected person.

4) This one does work, but throws a delegation warning. I'm assuming this is because the DateValue is iterating over the entry dates and converting them to dates again.

So I want to work with number 3, and work around the delegation warning, but it doesn't return anything.

I have enabled the feature: "Record scope one-to-many and many-to-many relationships" in the hopes that this would solve it.

My guess is that the many-to-many relationship doesn't always fetches its dependent data, making the 'timesheet entries' of a person empty, until you execute something like the Datevalue function which forces it to fetch the related data.

I hope my explanation is clear.

Thank you for your help.

Kind regards,

Gerry

Categories:

Building Power Apps