Hello,
Hoping someone can help me wrap my head around this. I need to filter the below table by Distinct equipmentID and latest timestamp.
The records highlighted below should be the results.
Sample data:
ID | equipmentID | timestamp | xvalue | yvalue |
1 | 123 | 2021-03-03T060000Z | Test | Test2 |
2 | 123 | 2021-03-03T050000Z | Test0 | Test4 |
3 | 345 | 2021-03-03T080000Z | TestTest | TEst |
4 | 345 | 2021-03-03T070000Z | Yes | Yep |
5 | 456 | 2021-03-03T080000Z | Sure | Uhuh |
Something like:
ClearCollect(colLatestRecords,
Filter(
Distinct(TABLE_NAME, equipmentID),
Max(timestamp)
)
);
But this is not correct.
Any ideas?
Thank yoU!
Use AddColumns() for a formula that concatenate the equipment ID and the time stamp and use distinct on the new column.
@Drrickryp , thanks for the reply. Where does the Distinct() come in?
ClearCollect(colLatestRecords,
Filter(
AddColumns(TABLE_NAME,
"uniqueLastRecord",
Concatenate(ThisRecord.equipmentID, Text(ThisRecord.timestamp))
),
??
)
)
I believe what you are looking for is this:
ForAll(
GroupBy(
AddColumns(
TABLE_NAME,
"cDate", DateTimeValue(timestamp)
),
"equipmentID",
"_recs"
),
Patch(First(SortByColumns(_recs, "cDate", Descending)), {equipmentID: equipmentID})
)
I hope this is helpful for you.
I will test this now. I was not expecting to see a Patch() here!
Patch patches records and returns a record! In this case, we are patching the equipmentid column back into the grouped record - because when you group by a column, that column is the column in the resulting table, not the grouped records. So, we're just putting it back in to the record we want.
@RandyHayes , I understand the formula you proposed, but it looks like I'm hitting delegation.
Example:
SQL Table Rows: ~4500
Function: (as you proposed)
ClearCollect(colLatestRecords,
ForAll(
GroupBy(
AddColumns(
'[dbo].[TABLE_NAME]',
"cDate",
timestamp
),
"equipmentID",
"_recs"
),
Patch(
First(SortByColumns(_recs, "cDate", Descending)),
{
equipmentID: equipmentID
}
)
)
)
Results: in a single record though there are many equipmentID's in the table (equipmentID is null though until ~ID 2500).
- "PowerApps encountered an error" message is because the field is blank (verified).
- I have AdvancedSettings/DataRowLimit at default 500
ID | equipmentID | cdate |
500 | "Powerapps encountered an error" | 2/21/2021 0500 |
Delegation issue:
So I tried a Filter function on the SQL table like so (timestamp is a DATETIME(2) data type):
Set(varLookback, Now() - 1);
ClearCollect(colLatestRecords,
ForAll(
GroupBy(
AddColumns(
Filter(
'[dbo].[TABLE_NAME]',
timestamp > varLookback
),
"cDate",
timestamp
),
"equipmentID",
"_recs"
),
Patch(
First(SortByColumns(_recs, "cDate", Descending)),
{
equipmentID: equipmentID
}
)
)
)
But this always returns an empty collection. Shouldn't the dates in the timestamp column and varLookback be comparable in this case?
Yes...the formula I provided is not delegable. The AddColumns is the issue there as it is not delegable.
However, delegation did not seem like and issue as you originally had Distinct - which is also not delegable.
You are on the right path though...to pre-filter the information going into the AddColumns.
But, your timestamps are going to be a problem to filter on as they are not going to be compatible with the date type you are putting in a variable.
My suggestion is that, since you are on SQL...can you just put together a View in SQL to return the filtered information i.e. last month worth of data?
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
User | Count |
---|---|
196 | |
67 | |
46 | |
41 | |
28 |
User | Count |
---|---|
255 | |
121 | |
84 | |
80 | |
80 |