cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasFoster
Level: Powered On

Filtering a data source to return rows that contain any of the values in a collection

I have an issue,

 

I am looking to get the list of accounts that are attached to the current user of the app. I am using Salesforce as my data sources, I have gotten as far as being able to get the users email, get the Id associated with that user and finding the accounts associated with that user. These account ids are being stored in a collection.

 

However next I want to be able to get the rows of the account table that match the values held in that collection so I can get other information about the accounts. My current code is as follows 

ClearCollect(
    v_Accounts,
    RenameColumns(
        ShowColumns(
            Filter(
                'Account Team Member',
                'Account Team Member' = v_UserId
            ),
            "Account__c"
        ),
        "Account__c",
        "Id"
    )
);
ClearCollect(
    v_AccountDetails,
    ShowColumns(
        Filter(
            Accounts,
            v_Accounts.Id in Id
        ),
        "Id",
        "Name"
    )
)

v_UserId is a variable with the salesforce user id of the current user, the v_Accounts collection works as expected. However v_AccountDetails does not, it returns the error "Invalid Argument Type: Cannot use table values in this context". How do I fix this error or is there another way I can solve my problem.

 

 

I have seen some other posts that were close but none with my exact scenario plus most used SP lists not salesforce. Salesforce has different delegatable functions to sharepoint which helps as 'in' is delegatable.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list 

 

Thanks for your assistance

Tom

7 REPLIES 7
Super User
Super User

Re: Filtering a data source to return rows that contain any of the values in a collection

Hi @ThomasFoster,

I may be mistaken but I think that even though "in" is delegatable in certain connectors, like Sql, It usually has to be a text type column.  Is there a reason why you aren't using = for the Id column?  If the columns are not identical, can you extract the part of the  Id in the Accounts datasource say with Left(). 

ThomasFoster
Level: Powered On

Re: Filtering a data source to return rows that contain any of the values in a collection

The reason I am using in is that I have multiple accounts that I want to return.

Say in a collection I have the following column

AccountId

A

C

 

and in one of my data sources I have a table with every account and info about them

 

AccountId, Name, Region

A, First Account, Americas

B, Second Account, Europe

C, Third Account, Asia

D, Fourth Account, Africa

 

What I want to be able to do is filter the data source so that it only returns rows where the accountId matches any of the values in the collection. So I can then select the columns I want and display them in a gallery or other visuals.

 

So the filter would return

AccountId, Name, Region

A, First Account, Americas

C, Third Account, Asia

 

So I can then use showcolumns to say select only Name and region.

 

This may not be the best way to solve this kind of problem as I haven't tried to implement this kind of many value filtering before. If you know anyway I can solve this I am very interested.

 

As for the account Ids, they are strings as far as I am aware. Also as far as I am aware 'in' is delegatable with salesforce 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list.

Super User
Super User

Re: Filtering a data source to return rows that contain any of the values in a collection

I have an idea. Use the Search() function instead of Filter. Then you can search for instances of your Id inside the columns you specify. According to the documentation, Search is delegatable in Salesforce. I'm on my cell otherwise I would put in an example of the formula. Let me know if that works for you.
ThomasFoster
Level: Powered On

Re: Filtering a data source to return rows that contain any of the values in a collection

Ill try it when I am in the office tomorrow, if you could post an example it would be helpful.

 

Thanks

ThomasFoster
Level: Powered On

Re: Filtering a data source to return rows that contain any of the values in a collection

@Drrickryp I have tried to use search and I dont think it is possible in my use case.

As I have multiple strings that I am searching with. Each row in the collection contains a string that is the primary key of the data source. I want to return the rows of the data source which corospond to those values. 

 

 

I know how I would implement something like this with DAX or SQL but when I try to follow the documentation for Power apps it keeps failing. If you have any advice it would be greatly appreciated. 

 

Edit: I have simplified the problem even further and it still doesn't work. What I tried was defining a collection of two account ids

 

Collect(testIds,Table({Id:"0012000000N0K1oAAF"},{Id:"0012000001alD2RAAU"}));

Then I tried two different methods, first I tried to filter the accounts table by these Ids, the collection returned emptyu. Second I tried to add a column to the testIds collection that contained information from the accounts table with both of the below formulas. Neither worked.

 

AddColumns(testIds,"Name",LookUp(Accounts,testIds[@Id] = 'Account ID',Name));
AddColumns(testIds,"Name1",LookUp(Accounts,Id = 'Account ID',Name));

Where am I going wrong with either of these approaches?

Super User
Super User

Re: Filtering a data source to return rows that contain any of the values in a collection

Hi @ThomasFoster

We can go round and round on this if I don't understand your database structure.  It seems to me that you require three tables to describe your database. 

Screenshot_1.png

 

 

TeamMembers, Regions and Accounts.  It is particularly important to understand the relationship between Accounts and the other two tables. 

  • Can an account have more than one Team member?
  • Can an account be in more than one region?

 

ThomasFoster
Level: Powered On

Re: Filtering a data source to return rows that contain any of the values in a collection

Hello @Drrickryp

diagram.jpg

This is the model I am working with. I get the users email with User.Email. I can then lookup the users Id and store it in a variable, this works fine. I can then filter on the account team table which gets me any accounts assocaiated with them. This is then stored in a collection as a one column table called v_Accounts. This also works correctly

 

The part that is not working is that I want to be able to filter the accounts table so that only the rows that corrospond to the account ids present in v_Accounts.

 

I have tried it with Filter and In and have got no luck despite documentation saying that in is supported with salesforce. I have also tried creating a new collection that is based of v_Accounts but adding lookup columns to it using lookup on the Accounts table but also with no luck.

 

If I was writing the query to salesforce myself it would look something like 

SELECT * FROM Account WHERE Id IN ('0012000001alD2RAAU','0012000000N0K1oAAF')

 I appreciate you helping on this.