Group by projects and show latest inspection for each

[Jamesboltonuk]

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 

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

Any help is appreciated.

Thanks

James

[gabibalaban · Accepted solution]

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 !

[Jamesboltonuk]

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

[gabibalaban]

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