Unanswered

Trying to display a list of meter readings where the datasource has only 1 reading per client

(0) Share

Report

Posted on by bobgodin

380

@WarrenBelz I have a Sharepoint list called MeterReadings. Each record is assigned a clientname and a reading (usually there will be a reading every 4-6 months so in a year there should be 3 readings.

I want to display a Gallery list of records where there is only 1 reading for a clientname. My sharepoint list is over 5000 records so delegation is going to be a problem.

I know I need to use Count or CountRows and GroupBy but I just can't seem get my head around how to do it

Any advice greatly appreciated thank you 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (20)

Answers (2)

WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

@bobgodin ,

You have indeed got an issue unless you can "pre-filter" your working dataset under 2,000 items as both CountRows and GroupBy are restricted by your Delegation limit. Are you able to do this ?

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @WarrenBelz I can pre-filter my dataset yes...

Was this reply helpful? Yes No
WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @bobgodin ,

If you give me the example code where the filter output is less than this and a description of what you want to achieve including field, control and list names, I will see what I can do.

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report
Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @WarrenBelz my code is a little more complex than I first described:

If( varAuth <> "", SortByColumns( Filter( MeterReadings, readdate >= (DatePickerFrom.SelectedDate) && readdate <= DatePickerTo.SelectedDate And auth = varAuth ), "meterref" ), SortByColumns( Filter( MeterReadings, readdate >= (DatePickerFrom.SelectedDate) && readdate <= DatePickerTo.SelectedDate And (StartsWith( meterref, SearchBox.Text ) Or StartsWith( auth, SearchBox.Text )) ), "meterref" ) )
I am looking at displaying the data in a Gallery with the fields:
clientname
meterref
reading
read-date
comments

Was this reply helpful? Yes No
Verified answer

WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report
Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @bobgodin ,

I assume you want the last reading for the client ?

With( { _Data: Sort( Filter( MeterReadings, readdate >= (DatePickerFrom.SelectedDate) && readdate <= DatePickerTo.SelectedDate && ( !IsBlank(varAuth) || ( IsBlank(varAuth) && ( StartsWith( meterref, SearchBox.Text ) || StartsWith( auth, SearchBox.Text ) ) ) ) ), ID, SortOrder.Descending ) }, SortByColumns( AddColumns( GroupBy( _Data, "clientname", "Grouped" ), "MeterRef", First(Grouped).meterref, "Reading", First(Grouped).reading, "ReadDate", First(Grouped).'read-date', "Comments", First(Grouped).comments ), MeterRef" ) )

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

@WarrenBelz thank you again Warren - that worked a treat!

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @WarrenBelz at first I thought the above code worked, however on 2nd look I realise that it is grouping by clientname and not by meterref. This was my mistake in telling you in the original post I wanted it "1 reading for a clientname"

I should have said "where there is only 1 reading for a meterref"

My apologies for the mistake but how can I change the code to display only meterrefs that have 1 reading only?
thanks Warren

Was this reply helpful? Yes No
WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report

Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @bobgodin ,

Have you tried grouping by meterref and then changing the first added column to clientname ?

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report
Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @WarrenBelz I have changed the code as attached but it's now showing 1081 records instead of 693 the first time - I don't think there should be more than 200 meterefs with only 1 reading....
With( { _Data: Sort( Filter( MeterReadings, readdate >= (DatePickerFrom.SelectedDate) && readdate <= DatePickerTo.SelectedDate && (StartsWith( meterref, SearchBox.Text ) || StartsWith( auth, SearchBox.Text )) ), ID, SortOrder.Descending ) }, SortByColumns( AddColumns( GroupBy( _Data, "meterref", "Grouped" ), "clientname", First(Grouped).clientname, "clientref", First(Grouped).clientref, "reading", First(Grouped).reading, "readdate", First(Grouped).readdate, "dialscode", First(Grouped).dialscode, "wma", First(Grouped).wma, "auth", First(Grouped).auth, "comments", First(Grouped).comments ), "meterref" ) )
thanks

Was this reply helpful? Yes No
WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report
Re: Trying to display a list of meter readings where the datasource has only 1 reading per client

Hi @bobgodin ,

The code will do as you have asked it to do and group the output of the top With() filter by each incidence of meterref in it. This piece

Sort( Filter( MeterReadings, readdate >= (DatePickerFrom.SelectedDate) && readdate <= DatePickerTo.SelectedDate && ( StartsWith( meterref, SearchBox.Text ) || StartsWith( auth, SearchBox.Text ) ) ), ID, SortOrder.Descending )

will output record numbers subject to your Delegation limit (the input can be any number) and then this

GroupBy( _Data, "meterref", "Grouped" )

will take that output and create a record for each incidence of meterref, with the remaining fields in the Grouped table field. I cannot see your data, but is this not the structure you require ?

Was this reply helpful? Yes No