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.

 

 

 

Highlighted
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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response 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 (8,368)