SQL Filtering Question

[jesenavaranjan]

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

[h-nagao]

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

[jesenavaranjan]

Thankyou, I can't get the form to work, I'm getting an error message saying the Sort by Columns has some invalid arguments

If(NewForm.Mode=FormMode.Edit,

First(
    SortByColumns(table2,ID="Identi",Type="Current"),
        stDate,Descending)
        ).Score 

,0)

Can you spot any errors please?

[jesenavaranjan]

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

[h-nagao]

You may miss Filter function before applying SortByColumns.
Hiro

[jesenavaranjan]

What do you mean miss filter? I'm confused by the reply...

[h-nagao]

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.

[jesenavaranjan]

Not quite

I get the error message saying 'Invalid Argument DateTime, expecting a text value instead'

which I think refers to StDate?

Thank you

[wyotim]

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.

[h-nagao · Accepted solution]

Sorry for my mistake.
Column name should be text, so we have to enclose in “ .
“stDate”, Descending