cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Impactful Individual
Impactful Individual

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
Highlighted
Impactful Individual
Impactful Individual

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

Highlighted
Helper V
Helper V

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?
Highlighted
Helper V
Helper V

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

Highlighted
Impactful Individual
Impactful Individual

Re: SQL Filtering Question

You may miss Filter function before applying SortByColumns.
Hiro
Highlighted
Helper V
Helper V

Re: SQL Filtering Question

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

Highlighted
Impactful Individual
Impactful Individual

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.

 

 

 

Helper V
Helper V

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

Highlighted
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
Impactful Individual
Impactful Individual

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,055)