cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesenavaranjan
Level: Powered On

SQL Filtering Question

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
h-nagao
Level 10

Re: SQL Filtering Question

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

View solution in original post

9 REPLIES 9
h-nagao
Level 10

Re: SQL Filtering Question

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
Level: Powered On

Re: SQL Filtering Question

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
Level: Powered On

Re: SQL Filtering Question

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
Level 10

Re: SQL Filtering Question

You may miss Filter function before applying SortByColumns.
Hiro
jesenavaranjan
Level: Powered On

Re: SQL Filtering Question

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

h-nagao
Level 10

Re: SQL Filtering Question

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
Level: Powered On

Re: SQL Filtering Question

Not quite

 

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

which I think refers to StDate?

 

Thank you

Super User
Super User

Re: SQL Filtering Question

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.

 

Highlighted
h-nagao
Level 10

Re: SQL Filtering Question

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

View solution in original post

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,275)