Unanswered

Count Rows of Related Children

(0) Share

Report

Posted on by procodile85

I have a table of Programs, related to a table of Milestones, related to a table of Risks. I would like to get a list of Risks when a Program is selected. Here is one of the many formulas I tried that did not work, but seems like it should...

Filter(Risks,Milestone in Filter(Milestones,Program.Programs = ProgramGallery.Selected.Programs).Milestones)

The only error I get is that "in" is not delegable, but that should not be an issue since the number should be small.

I can get a list of Risks if I have a Gallery of Programs, and then select from a Gallery of Milestones, but I can't find a way to get directly to the list of Risks from the Program selection. Essentially, I am trying to make a view for the Program that has a summary with the Number of Risks for the Program regardless of which Milestone they are related to.

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @procodile85 ,

You can get rid of the Delegation warning like this

With( { _Program: Filter( Milestones, Program.Programs = ProgramGallery.Selected.Programs ) }, Filter( Risks, Milestone in _Program.Milestones ) )

however before we go further, you seem to have a data source and one of its fields named the same (Milestones) - is this the case ?

Was this reply helpful? Yes No
procodile85 28 on at

Like (0)

Report

@WarrenBelz My "Milestones" table does have a "Milestones" column which is the Unique Identifier. All my tables are set up that way (Dataverse created the Unique Identifier columns with those names automatically). Could this be part of the issue?

I did try your code suggestion and "in" still returns a delegation error.

Was this reply helpful? Yes No
procodile85 28 on at

Like (0)

Report

I should add that the reason my formula does not work is that it returns nothing. I would expect it to return items like it does when I use the two steps of Galleries.

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@procodile85 ,

Identically named elements are always a candidate for Ambiguity, which is never a good thing. Yes, looking at it again, it does not solve the Delegation issue. It is always advisable to post your data source in your initial question as this will affect both the response and at times who responds (I am not a Dataverse user, so may be of limited help here)

Was this reply helpful? Yes No
procodile85 28 on at

Like (0)

Report
@WarrenBelz Thanks for the data source suggestion. I'll try to remember that the next time!

I went in and updated my tables so the Unique Identifiers are now xxxxID and therefore the formula would be as follows:
Filter(Risks,Milestone in Filter(Milestones,Program.ProgramID = ProgramGallery.Selected.ProgramID).MilestoneID)
This did not solve the issue. I also tried using a clearcollect(myCollection,...) but that always says the collection is not recognized (every reference I have found says that myCollection is the name for the collection you want to create).

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@procodile85 ,

What type of field is Program in the Milestones Table ?

Was this reply helpful? Yes No
procodile85 28 on at

Like (0)

Report

@WarrenBelz
The Program field in the Milestones table is a Lookup that references the Programs table.

Additionally, the Milestone field in the Risks table is a Lookup that references the Milestones table.
The Primary Name Column in the Programs table is Program ID, Milestones table is Milestone ID, and Risks table is Risk ID. These are all auto number columns with a prefix.

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@procodile85 ,

If you are referring to an Option Set, this article may be of assistance

Was this reply helpful? Yes No
procodile85 28 on at

Like (0)

Report

I am talking about a Lookup Column that references a related table. See the screenshot below from the settings of that field. Am I going about this in the wrong way? Is there a simpler solution (maybe a calculation field directly in the Dataverse?) to count the number of risks associated with a program?

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report

@procodile85 ,

As I noted earlier, I am of limited assistance here, but your second option possibly makes sense.

Was this reply helpful? Yes No