LookUp(Table2,ID="1" && Type="Type1",Max(EnteredDate),Score)
Hi - I'm having an issue with a lookup against a SQL table. So I have various scores that may be recorded against Type1 and ID=1, with various dates
But I want my lookup to find the latest score and show it, hence the 'Max(date)' but Power apps gives me a blue squiggly line under this, giving me delegation warnings.
What can I do about this? Can anyone help?
Please and thank you,
Jese
Solved! Go to Solution.
Hi Jese,
Did you try to use SortByColumns and First() ?
For your case, following code would be work;
First( SortByColumns( Filter(Table2, ID="1",Type="Type1"), EnteredDate,Descending) ).Score
Using Filter function (colored in Green), you can get records from Table2 satisfying 2 conditions.
SortByColumns (colored in Red), make filtered records descending order.
First(), get latest record.
Does it work fine for your case?
Thank you,
Hiro
Thankyou, I can't get the form to work, I'm getting an error message saying the Sort by Columns has some invalid arguments
Some more info, the erro messge days
StDate is DateTime, and the argument is expecting text instead
But when I use Text(stDate, "dd/mm/yyyy"), I get the blue line and delegation warnings again
J
What do you mean miss filter? I'm confused by the reply...
Apologies for my (too) short reply...
I mean that a part of your code,
First( SortByColumns(table2,ID="Identi",Type="Current"), stDate,Descending) ).Score
will not work.
First( SortByColumns( Filter(table2,ID="Identi",Type="Current"), stDate,Descending) ).Score
Filter function is needed to apply conditions (ID="Identi", Type="Current").
On my side, SortByColumns(Datasource, DateTimeField, Descending) works fine.
Not quite
I get the error message saying 'Invalid Argument DateTime, expecting a text value instead'
which I think refers to StDate?
Thank you
When using SortByColumns I think you need to wrap the column name in double quotes. Maybe try:
If(NewForm.Mode=FormMode.Edit, First( SortByColumns(table2,ID="Identi",Type="Current"), "stDate",Descending) ).Score ,0)
Or replace SortByColumns with Sort like:
If(NewForm.Mode=FormMode.Edit, First( Sort(table2,ID="Identi",Type="Current"), stDate,Descending) ).Score ,0)
Either of those should work but let me know if it still gives an error.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
198 | |
177 | |
64 | |
34 | |
32 |
User | Count |
---|---|
339 | |
273 | |
115 | |
74 | |
57 |