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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (4,477)