cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gaëlle
Level 8

ClearCollect with Filter Invalid argument type error

Hello,

I am trying to achieve some collecting by filtering my datas before displaying them.

I am adding several filters so the amount of data returned is not too big.

So, I have 3 tables : Reports, Solutions and Lines. Lines are Factory lines on which Reports are made when there is an incident. And the third table stores the Solutions brought to the Report.

 

I am trying to get the Reports and dependant solutions depending on the current user's manager (responsible of several lines, so the user only sees the lines he has to work on)

So, I created a ClearCollect formula, where to get these informations. I added a column to store the dependant solutions.
But suddenly, an error "Invalid argument type error" appeared right under my last equal (see the bolded line)
 :

ClearCollect(
    AllReports,
    SortByColumns(
        AddColumns(
        Filter(
            '[dbo].[Reports]',
            idLine in Filter(
                AllLines,
                Manager=Lower(myManagerMail)
            ).Id
        ),
    "DependantSolutions",
    Filter(
        '[dbo].[Solutions]',
        '[dbo].[Solutions]'.idReport='[dbo].[Reports]'.NumeroReport
    )
)
"DateIssue",
Descending
)
),


I don't know what to do, I have tried several things like '[dbo].[Solutions]'[@idReport]='[dbo].[Reports]'[@NumeroReport], I tried to add a "In" instead of a "=", but nothing seems to work.

Can someone help me please ?

Thanks a lot.

7 REPLIES 7
shailendra74
Level 8

Re: ClearCollect with Filter Invalid argument type error

Hi @Gaëlle 

 

Did you try to create a database view and use the view to filter futher.  Ensure you have all columns in your view which are required as part of Filter.

 

Thanks.

Gaëlle
Level 8

Re: ClearCollect with Filter Invalid argument type error

Hello @shailendra74 !
First of all, thank you to take the time for answering me !
Then, I don't really see what you are talking about ..
I'm not really used to SQL (I know how to perform basic operations), and moreover, I did not know that a View could contain several columns.
How do you use Views within PowerApps? (and how do you maintain the view updated ? how do you copy from your table?)

Could you show me some example please ??
Thanks a lot !!

Community Support Team
Community Support Team

Re: ClearCollect with Filter Invalid argument type error

Hi @Gaëlle ,

The right syntax of AddColumns is :

AddColumns( Table, ColumnName1, Formula1)

The 'formula1' needs to return a value.The result is added as the value of the corresponding new column.

While using filter function will return a table.

Waht's more, "'[dbo].[Solutions]'.idReport='[dbo].[Reports]'.NumeroReport" means that compare two table, it's not right.

Try this formula:

ClearCollect(
    AllReports,
    SortByColumns(
        AddColumns(
        Filter(
            '[dbo].[Reports]',
            idLine in Filter(
                AllLines,
                Manager=Lower(myManagerMail)
            ).Id
        ),
    "DependantSolutions",
  LookUp(
        '[dbo].[Solutions]',
        idReport='[dbo].[Reports]'.NumeroReport, fieldname
    )
)
"DateIssue",
Descending
)
),

Please replace fieldname with the fieldname that you want to display in DependantSolutions.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gaëlle
Level 8

Re: ClearCollect with Filter Invalid argument type error

Hello Phoebe !
First of all, thank you for your time and your explanations, I understand AddColumns formula a bit better now.

But still, even with a lookup I have the "Invalid argument type" error pointing on my equal :

ClearCollect(
        AllReports,
        SortByColumns(
            AddColumns(
                Filter(
                   '[dbo].[Reports]',
                    idLine in Filter(
                        AllLines,
                        Manager=Lower(myManagerMail)
                    ).Id
                ),
                "DependantSolutions",
                LookUp(
                    '[dbo].[Solutions]',
                    idReport = '[dbo].[Reports]'.NumeroReport,Report
                )
            ),
            "DateIssue",
            Descending
        )
    )

So, if you have an idea, I would be gratefull to hear it, because I am really stucked.

 

Thanks again !

Have a nice day

Community Support Team
Community Support Team

Re: ClearCollect with Filter Invalid argument type error

Hi @Gaëlle ,

Sorry, I forgot to modify the operator, try this:

ClearCollect(
        AllReports,
        SortByColumns(
            AddColumns(
                Filter(
                   '[dbo].[Reports]',
                    idLine in Filter(
                        AllLines,
                        Manager=Lower(myManagerMail)
                    ).Id
                ),
                "DependantSolutions",
                LookUp(
                    '[dbo].[Solutions]',
                    idReport in '[dbo].[Reports]'.NumeroReport,Report
                )
            ),
            "DateIssue",
            Descending
        )
    )

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
shailendra74
Level 8

Re: ClearCollect with Filter Invalid argument type error

Hi @Gaëlle 

 

You must have added SQL data connector and then selected tables from the selected data based. The same way you can select views too.

 

Moreover, having joins in DB will be more effective and performance wise would be good as the process will happen on database side and not in PowerApps (which run on either desktop browser or Tablet).

 

As you know basic of SQL, creating a view and maintaining the same same will not be an issue.

 

Thanks.

Super User
Super User

Re: ClearCollect with Filter Invalid argument type error

Hi @Gaëlle 

 

The issue here is '[dbo].[Reports]'.NumeroReport returns a one column table of all the values in NumeroReport column and same for  '[dbo].[Solutions]'.idReport . If you want to get all records where idReport is equal to NumeroReport, can you try to modify your formula as:
 
ClearCollect(
    AllReports,
    SortByColumns(
        AddColumns(
        Filter(
            '[dbo].[Reports]',
            idLine in Filter(
                AllLines,
                Manager=Lower(myManagerMail)
            ).Id
        ),
    "DependantSolutions",
    Filter(
        '[dbo].[Solutions]',
        idReport=NumeroReport
    )
)
"DateIssue",
Descending
)
)
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 60 members 4,348 guests
Please welcome our newest community members: