cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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.

 

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

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,217)