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

Group by projects and show latest inspection for each

Hey All,

 

I'm struggling with a Power Apps query and hoping someone might be able to help. I have the following table in my SQL data source 

Jamesboltonuk_1-1606917015338.png

I'm looking to have a gallery that shows only 1 inspection number per job number and it should always be the next number along. I.e if inspection 1 is removed I'd expect to see inspection 2 

 

I can't quite work out what filter to use as I cant grab a dynamic siteinpsectionnumber. I've tried a group by with the following

Clear(colmydata);
Collect(colmydata,
  GroupBy(test, "Job_x0020_Number", "DATA")
  )


which grouped my inspection perfectly, but when they trying to display the first record of each grouped table with

 

    Clear(inspections2);
    ForAll(
        coldmydata.DATA,
        Collect(
            inspections2,
            First(First(coldmydata).DATA)
        )
    )

 

My Inspection2 collection returned 2 rows of data but from 1 inspection rather than showing me the top results from each

Jamesboltonuk_2-1606917552274.png

 

Any help is appreciated.

Thanks

James

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Hi @Jamesboltonuk ,

In  powerapps, is recommended to take advantage of the server processing power. So,  how about create a view in your SQL database to take care of your problem.

The SQL code is somehow simple,  like this (based on the info that I have):

CREATE VIEW [dbo].[GroupByView]
	AS 
 SELECT *
 FROM
 (
     SELECT ROW_NUMBER() OVER (PARTITION BY JobNumber ORDER BY SiteInspectionNumber) as RowNum, *
     FROM YourTable
 ) X
Where RowNum=1

And all you have to do is to create a connection to this SQL view. 

 

 Hope it helps !

View solution in original post

3 REPLIES 3
Super User II
Super User II

Hi @Jamesboltonuk ,

In  powerapps, is recommended to take advantage of the server processing power. So,  how about create a view in your SQL database to take care of your problem.

The SQL code is somehow simple,  like this (based on the info that I have):

CREATE VIEW [dbo].[GroupByView]
	AS 
 SELECT *
 FROM
 (
     SELECT ROW_NUMBER() OVER (PARTITION BY JobNumber ORDER BY SiteInspectionNumber) as RowNum, *
     FROM YourTable
 ) X
Where RowNum=1

And all you have to do is to create a connection to this SQL view. 

 

 Hope it helps !

View solution in original post

New Member

Thank you @gabibalaban that worked perfectly! Never thought out taking it back to the database

@Jamesboltonuk  Please don't forget to accept it as a solution  if meets your needs. 😊

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

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

Top Solution Authors
Top Kudoed Authors
Users online (33,040)