Answered

Lookups in GroupBy

(0) Share

Report

Posted on by RMDNA

100

Hi all. I'm having issues using data typed as Lookup in a GroupBy for aggregation. I've checked the other related posts on the board, but they seem to be pretty specific to their scenario, and I'm not good enough yet to get them to apply to my case. Datasource is Dataverse.

The GroupBy formula I have, which will be used as the Items property in a gallery, is as follows:

AddColumns(
 GroupBy(
 Filter('Time Entries','Time Entries (Views)'.'Time Entries for Approval',Date > VarSelectedStartDate && Date < VarSelectedEndDate),
 "'Bookable Resource'.Name","Project.'Project Name'", "Date", "Prj Data"),"Hours",Sum('Prj Data',Duration))

However, 'Bookable Resource'.Name and Project.'Project Name' are data type Lookup. I'd imagine the solution would involve nested AddColumns to create a "new" value for Resource Name and Project Name that can be referenced in the GroupBy, but that's beyond my current skill level. Can anyone assist?

A side question: can I encode 'Time Entries','Time Entries (Views)'.'Time Entries for Approval' into a variable for easier and more efficient use in formulas? Something similar to using VAR in Power BI measures.

I've had a lot of questions on here lately, and I anticipate having more, but people have been incredibly helpful, so thanks to everyone.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

KroonOfficeSol 587 on at

Like
a
(0)

Report
Copy link

Link copied!
You could try this:
With( { table: Filter('Time Entries','Time Entries (Views)'.'Time Entries for Approval' ,Or(IsBlank(VarSelectedStartDate), Date > VarSelectedStartDate) ,Or(IsBlank(VarSelectedStartDate), Date < VarSelectedEndDate) ) } ,AddColumns( GroupBy( AddColumns(table, "BookableResourceName", "'Bookable Resource'.Name","ProjectName", "Project.'Project Name'") ,"BookableResourceName","ProjectName", "Date", "Prj Data" ),"Hours",Sum('Prj Data',Duration) ) )
Know that you lose pagination and will load in all matching records.

For the second question on connection name the answer is you can't. But what you can do is take away the second and third part of and add the conditions which you apply in the dynamics view manually in your Filter formula.
RMDNA 100 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @KroonOfficeSol,

In your formula, when I'm creating the columns BookableResourceName and ProjectName, putting double quotes around 'Bookable Resource'.Name and Project.'Project Name' turns them into strings and makes every entry in the gallery that text:

However, if I take the double quotes out, it just doesn't work: my only choices are all the non-lookup fields in that view. Even then, choosing any of those fields is what breaks the formula.

In any case, I tried both 'Bookable Resource'.Name, which is how I refer to it in my un-aggregated gallery (when using ThisItem), and also its backend name 'msdyn_bookableresource'. Am I misunderstanding something about my data?

On the side: this seems like an incredibly complicated solution to what I figured would be a simple problem. Are Lookup fields just bad practice? Is aggregation via GroupBy a really bad approach?
Verified answer

RMDNA 100 on at

Like
a
(0)

Report
Copy link

Link copied!
I was able to get it working with the following formula:

SortByColumns( AddColumns( GroupBy( AddColumns( Filter( 'Time Entries', 'Time Entries (Views)'.'Time Entries for Approval', Date > VarSelectedStartDate && Date < VarSelectedEndDate ), "DateString", Text(Date, ShortDate), "ResourceName", 'Bookable Resource'.Name, "ProjectName", Project.'Project Name' ), "DateString", "ResourceName", "ProjectName", "Prj Data" ), "Minutes", Sum( 'Prj Data', Duration ) ), "DateString", Ascending )