cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeeB_CDN
Helper III
Helper III

Syntax for both a Lookup and ShowColumns function in ClearCollect statement?

Hello, PowerApps Super Humans,

 

I haven't been able to solve this one despite searching extensively so any help would be greatly appreciated!

I am trying to create a collection from a unique record and specific columns from a Sharepoint list, using LookUp and ShowColumns  functions, but just can't get the right syntax (I'm sure this must be possible?!?)

Here is a snip of the code, this doesn't throw errors but returns more than just the single record I am looking for and doesn't seem to be filtering the columns either!:

 

ClearCollect(colCurrentItem, LookUp('SANDBOX-All_PPE_List', 'Row Number' = varMaxRowCount), ShowColumns('SANDBOX-All_PPE_List', "RowNum", "AdminEmail", "AdminName", "AdminMobPhone", "AdminBranchLoc", "AdminDept", "AdminJobTitle", "AdminManagerName", "AdminManagerEmail", "AdminManagerMobPhone", "AdminManagerBranchLoc"));

 

Any suggestions would be welcome...

 

Thanks in advance!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

@LeeB_CDN 

That figures, 

With({sblist:ShowColumns(
                          Filter(
                                 'SANDBOX-All_PPE_List', 'Row Number' = varMaxRowCount
                            ), "RowNum", "AdminEmail", "AdminName", "AdminMobPhone", 
                                "AdminBranchLoc", "AdminDept","AdminJobTitle", "AdminManagerName",
                                "AdminManagerEmail", "AdminManagerMobPhone", "AdminManagerBranchLoc"
             )
       },
   ClearCollect(
                 colCurrentItem,  
                                 First(
                                       sblist
                                 ),
   )
);

This way, the table shaping function ShowColumns() will have a table to work with (even though it is only one record) as Filter() produces a table.  Then you can use First() to show the record that you want to collect.

View solution in original post

6 REPLIES 6
Drrickryp
Super User
Super User

@LeeB_CDN 

 

ClearCollect(
             colCurrentItem, ShowColumns(
                                         LookUp( 
                                                'SANDBOX-All_PPE_List', 'Row Number' = varMaxRowCount
                                             ), "RowNum", "AdminEmail", "AdminName", "AdminMobPhone", 
                                                 "AdminBranchLoc", "AdminDept","AdminJobTitle", "AdminManagerName",
                                                 "AdminManagerEmail", "AdminManagerMobPhone", "AdminManagerBranchLoc"
                                 )
);

 

 

LeeB_CDN
Helper III
Helper III

Hi @Drrickryp!

 

Thanks for the reply! I have tried your suggested syntax but that throws an error...  I do want only one row of data that matches varMaxRowCount, so I think LookUp rather than filter is what I am after, but maybe there's another way to accomplish this? I have to be able to show only the columns specified as well...

 

Any more ideas?

Drrickryp
Super User
Super User

@LeeB_CDN 

Please post the error.  I read your request more carefully and modified my formula eliminating the filter part.  You are showing a lot of columns, it may be easier to use DropColumns() if there are fewer of them than the ones you want to show. I haven't tried to use the table shaping functions on a record so that could potentially be a problem.  If that's the issue, I believe there would be an easy way around it. 

LeeB_CDN
Helper III
Helper III

@Drrickryp,

 

Yes, the error states:  Invalid Argument type (Record) Expecting a Table value instead.

 

Drrickryp
Super User
Super User

@LeeB_CDN 

That figures, 

With({sblist:ShowColumns(
                          Filter(
                                 'SANDBOX-All_PPE_List', 'Row Number' = varMaxRowCount
                            ), "RowNum", "AdminEmail", "AdminName", "AdminMobPhone", 
                                "AdminBranchLoc", "AdminDept","AdminJobTitle", "AdminManagerName",
                                "AdminManagerEmail", "AdminManagerMobPhone", "AdminManagerBranchLoc"
             )
       },
   ClearCollect(
                 colCurrentItem,  
                                 First(
                                       sblist
                                 ),
   )
);

This way, the table shaping function ShowColumns() will have a table to work with (even though it is only one record) as Filter() produces a table.  Then you can use First() to show the record that you want to collect.

LeeB_CDN
Helper III
Helper III

Intriguing,  @Drrickryp 

 

Thanks for your effort!   That did the trick... I was figuring it would require a more robust solution... I have yet to employ the 'With' function, so now I will look into it some more!

 

Appreciate your help!

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,712)