cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

counting related records

HI - I have two sharepoints lists.  Projects and Project Statuses.  Projects as a lookup column "Project Status" pointing to the list Project Statuses.  

 

I'm trying to put a data table on  a canvas, and have it show all the statuses, and a count of the projects in them.  Its easy in sharepoint using a lookup count, but I can't surface those in my canvas app.  So, I'm try it this way .. I'm close ... but not quite there yet.  

 

The problem below, I think is in the end, where the ID column its referencing is incorrect. PRobably the ID column of the Projects list, but what I want it the ID of the Project status ... like a "thisrecord" object outside the filter.

 

I've tried countif as well, but haven't gotten that to work.  If it makes a different, the ultimate goal is to make a pie chart with this information, but i thought a good starting place would be a data table,

 

I have the ITEMS property of a data table set as follows:

 

 

 

AddColumns('Project Statuses', "CountOfProject",CountRows(Filter(Projects,ThisRecord.'Project Status'.Id=ID)))

 

 

 

 Help!

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks for the idea .... adding a new column that contained just the value of the lookup was the key. However, it needed to be done on the projects list, not the project status list.  So this becomes the working solution .. 

 

set the Items property of a datatable to:

 

AddColumns(GroupBy(AddColumns('Projects', "NewStatus", 'Project Status'.Value), "NewStatus", "GroupedStatus"),"MyCount", CountRows(GroupedStatus))

View solution in original post

3 REPLIES 3
Super User III
Super User III

HI @cstiles ,

This should work in a gallery - it will produce two fields - ProjectStatus and MyCount (you can call these whatever you like). Your issue is exacerbated by using a Lookup column and you cannot filter or group on these.

AddColumns(
   GroupBy(
      AddColumns(
         'Project Statuses', 
         "ProjectStatus",
         'Project Status'.Value
      ),
      "ProjectStatus",
      "OtherData"
   ),
   "MyCount",
   CountRows(OtherData)
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Thanks for the idea .... adding a new column that contained just the value of the lookup was the key. However, it needed to be done on the projects list, not the project status list.  So this becomes the working solution .. 

 

set the Items property of a datatable to:

 

AddColumns(GroupBy(AddColumns('Projects', "NewStatus", 'Project Status'.Value), "NewStatus", "GroupedStatus"),"MyCount", CountRows(GroupedStatus))

View solution in original post

@cstiles ,

I can only interpret your post as best as I can - I try to provide syntax assistance for issues. If this guided you to the result desired, please accept as solution as it helps others find it more readily. It also closes the item.

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (69,584)