You’re offline. This is a read only version of the page.
359
@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 🙂
Hi @WarrenBelz
Been checking data all morning and I have it working thanks to your assistance. I had to change the sort by in the first part of the code from ID to meterref.Ascending - then it is giving me correct results.
I wasn't thinking straight when I commented earlier about the number of records expected - the wider the dates the LESS number of records because we have a larger time span and so likely to have MORE THAN 1 reading per meterref!
thanks again Warren 🙂
Hi @bobgodin ,
So you are looking for instances where there is only one meterref for each group between the dates ?
Share
Report
All responses (
Answers (
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"
)
)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:
hope this is a bit clearer lol
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.
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
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
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.
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)
Hi @bobgodin ,
Sorry a bit lost here - why are not meterref 1456789 and 1765432 being included ?
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!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
