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

[bobgodin]

@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 🙂

[WarrenBelz]

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 ?

[bobgodin]

Hi @WarrenBelz I probably haven't explained the situation well enough. I will give you a sample of the data in my Sharepoint list and then another list displaying what I would like the outcome to be:

meterref	clientref	clientname	wma	dialscode	auth	readdate	reading
1234567	99587	Fred Bloggs	Dawson Valley	m53	05982B	4/12/2023	0876543
1456789	99871	Barney Rubble & Co	Dawson Valley	m54	045678A
1765432	88755	Fred Flintstone Pty Ltd	Atherton Groundwater Area	m42	0323113A	1/11/2021	198765
1765432	88755	Fred Flintstone Pty Ltd	Atherton Groundwater Area	m42	0323113A	1/11/2022	201123
meterref	clientref	clientname	wma	dialscode	auth	readdate	reading
1234567	99587	Fred Bloggs	Dawson Valley	m53	05982B	4/12/2023	0876543

hope this helps Warren!

[WarrenBelz]

Hi @bobgodin ,

Sorry a bit lost here - why are not meterref 1456789 and 1765432 being included ?

[bobgodin]

Hi @WarrenBelz 

1456789 doesn't have a reading (or its blank)

1765432 has 2 readings (one in 2021 and one in 2022)

The only record that satisfies the criteria is 1234567 (which has only 1 reading)

[WarrenBelz]

Hi @bobgodin ,

Assuming your read date pickers were set to 4/12/22 until 4/12/23 then that should be the outcome. I am also assuming you have "" (empty string) as the Default of your Search Box to avoid any filtering from there.

[bobgodin]

Hi @WarrenBelz 

I am checking the Sharepoint list for single reads at the moment to see if that matches up with the results from the app. Thanks again for your help

[bobgodin]

Hi @WarrenBelz 

I have done a bit of digging and I have attached 2 screenshots - one is the sharepoint list and the 2nd is the Powerapp Gallery display:

Focussing on meterref 10004 and 10060 - notice that the Sharepoint list has 2 readings for each of these meterrefs 

Notice also that the clientref for each meteref is the same (as is usually the case) and sometimes the reading is the same (in the case of 10004) - even though the reading has the same value there are still 2 readings for that meterref.  It is the readdate that is different per meterref.

I am thinking that the code may need changing because of the above factors - could you assist please Warren

thank you

[WarrenBelz]

Hi @bobgodin ,

Sorry but a bit unclear as to what you require. What is the "singularity" here - the meterref or the client (or the reading). It can be grouped by whatever you want (including a combintion of more than one field), but I need to know what the "group" consists of.

[bobgodin]

Hi @WarrenBelz 

I don't really know how to explain it any better, however I will give you the real life scenario and see if that helps:

• Client submits a meter reading for Water they have used - they supply a readdate, a reading, the meterref (meter identifier) and their clientref
• The Client is required to submit a reading once a year (usually around July)
• To be able to calculate how much water they have used in 1 year we obviously need 2 readings - we subtract the Min(reading) from the Max(reading) to get the usage
• Other constraints come into play then and we have to establish whether they have "OverUsed" or not
• Those clients with a meterref that has only 1 reading (between specified dates) - we can't calculate whether they have "OverUsed" or their meter is broken etc but these are the ones we have to investigate
• So we are looking for data where there is only 1 record per meterref with a readdate between specified dates and the reading is not blank. Meaning that the client has not submitted more than 1 reading.

hope this is a bit clearer lol

[WarrenBelz · Accepted solution]

Hi @bobgodin ,

So you are looking for instances where there is only one meterref for each group between  the dates ?

With(
   {
      _Data: 
      Sort(
         Filter(
            MeterReadings,
            readdate >= (DatePickerFrom.SelectedDate) && 
            readdate <= DatePickerTo.SelectedDate && 
            (
               StartsWith(
                  meterref,
                  SearchBox.Text
               ) || 
               StartsWith(
                  auth,
                  SearchBox.Text
               )
            )
         ),
         ID,
         SortOrder.Descending
      )
   },
   SortByColumns(
      AddColumns(
         Filter(
            GroupBy(
               _Data,
               "meterref",
               "Grouped"
            ),
            CountRows(Grouped) = 1
         ),
         "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"
   )
)