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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

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

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,170)