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

[LeeB_CDN]

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!

[Drrickryp]

@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]

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]

@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]

@Drrickryp,

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

[Drrickryp · Accepted solution]

@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]

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!