Delegation not working, don't understand why...

[_n_MarianLein]

Hi all,
I'm trying to get data from and push data to a dataverse table for time entries
Data source "Entries":

EntryDate	WorkTime	ProjectName
2024-01-01	2	ThisProject
2024-01-01	1.75	ThatProject
2024-01-01	5	OtherProject

 
The issue I'm running into is related to delegation but I don't know what I'm doing wrong.
"info": "Formula not delegated. Partial dataset of 500 rows scanned.", "helpUrl": "https://aka.ms/pamonitordelegation", "issue": "This function can be delegated but the selection formula used prevented the datasource from processing the query.It might not work correctly on large data sets and an incorrect result might be returned.The data row limit for non-delegable queries defined in the app settings is 500."

My code for the "get data" part looks like this, which if I understood the documentation correctly, should be delegated: 

"Letzte Änderung: " & If(
    IsBlank(
        Filter(
            Entries,
            EntryDate = Today()
        )
    ),
    "n/a",
    Text(
        Max(
            Filter(
                Entries,
                EntryDate = Today()
            ),
            'Modified On'
        ),
        "yyyy-mm-dd HH:mm"
    )
)

To push data, I use this code, which I am fairly certain should not be delegated, but I need to modify it to work correctly...

Parent.Selected;
If(
    !IsBlank(
        LookUp(
            Entries,
            EntryDate = Today() && ProjectName.Name = ProjektName.Text
        )
    ),
	Patch(
		Entries,
		LookUp(
			Entries,
			EntryDate = Today() && ProjectName.Name = ProjektName.Text
		),
		{
			WorkTime: LookUp(
				Entries,
				EntryDate = Today() && ProjectName.Name = ProjektName.Text
			).WorkTime + 1
		}
	),
    Patch(
        Entries,
        Defaults(Entries),
        {
            WorkTime: 1,
            EntryDate: Today(),
            ProjectName: LookUp(
                Projects,
                Name = ProjektName.Text
            )
        }
    )
)

Also, by all means if there's a more elegant way to achieve the same functionality,

[Amik]

@_n_MarianLein - I do not understand the terminology you're using ( "get data" ? ) and it seems like you're asking two questions.

For the first question (the formula which you have referred to get your "get data") - this is simply returning a text value. Where are you applying this formula? on the Text property of a Label?

MAX is not delegable with DateTime and this is noted in the documentation:

Image ref: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-common-data-se... 

However, MAX is delegable with Number. The common workaround here is to create number column for your date column as a number equivalent ( yyyymmdd ).  You can then run the MAX function and delegate the operation back to the server.

Regarding the second problem, if you are seeing a delegation issue, please could you share a screenshot of the code so I can see where the delegation warning is appearing

[_n_MarianLein]

Hi @Amik,

Thanks for your support. Yes, there are basically two questions...

To the first one, yes, this is the text property of a label.

I was under the impression, that in this case:

    Text(
        Max(
            Filter(
                Entries;
                EntryDate = Today()
            );
            'Modified On'
        );
        "yyyy-mm-dd HH:mm"
    )

...the commands would be run sequentially, i.e.

1. Filter the table "Entries" → Delegable
2. Take the maximum value from the filtered table (up to ~20 elements remaining) → Not delegable, but also not a problem
3. Format this as text

...is this not the case?

For the second question, unfortunately, there is no warning/error message.

The code is shown as above:

...what's not working is lines 22..33.

[Amik]

@_n_MarianLein - Regarding the first question, yes your understanding is broadly correct. Remember, the delegation warning is just a warning, not an error.

So long as you can guarantee that the delegable Filter function will return less than 500 rows (up to a maximum of 2000 via settings), this formula is fine.

If you want to hide the warning message, you can use the With function which will produce the same result.

With(
    {
        _pre_filtered_data: Filter(
            Entries;
            EntryDate = Today()
        )
    };
    "Letzte Änderung: " & If(
        IsBlank(_pre_filtered_data);
        "n/a";
        Text(
            Max(
                _pre_filtered_data;
                'Modified On'
            );
            "yyyy-mm-dd HH:mm"
        )
    )
)

Regarding the second question, when you say it's "not working", do you mean the Patch formula is not updating your data source?

Please could you share an actual screenshot of the formula inside the formula bar in App studio.

If you isolate the second Patch and run it on it's own (e.g. copy that section onto a separate Button), does the Patch successfully update your data source?

[_n_MarianLein]

Hi @Amik,

Thanks for your explanation of the "with()" - I have my display formula for the first part to this, but this didn't solve the issue. Even trying only this limited part, I don't get any data back from the data table, but there is data matching the filter in there.

Any ideas as to why?

[Amik]

@_n_MarianLein - the With function will return the same result. Does this formula return data?

Filter(
            Entries;
            EntryDate = Today()
        )

[_n_MarianLein]

Hi @Amik ,

Fascinatingly, this is also not working:

The today() part seems to be working:

but the filter doesn't return any rows - I have no idea why...

[Amik]

@_n_MarianLein - in your screenshot, I can see that the EntryDate field is formatted to Date Only, but is the data type also Date Only? Or Date and Time?

[_n_MarianLein]

@Amik, to be honest, I'm not sure:

This only shows date, but the column definition shows:

[...],"@odata.type":"#Microsoft.Dynamics.CRM.DateTimeAttributeMetadata","MinSupportedValue":"1753-01-01T00:00:00Z","MaxSupportedValue":"9999-12-30T23:59:59Z","Format":"DateOnly",[...]

tbh, I'm at a bit of a loss what to do here...

[Amik]

@_n_MarianLein - well there's your answer. A Date and a Date Time data type should not be compared because they will never equal.

A workaround is to use the DateValue function to change the Date Time field to return a Date only with e.g.:

Filter(
    Entries;
    DateValue(EntryDate) = Today()
)

However, "cast" functions like DateValue are not delegable. If possible, an alternate option is to simply replace your Date Time column with a Date Only column.