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

(0) Share

Report

Posted on by LeeB_CDN

179

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@LeeB_CDN

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

Was this reply helpful? Yes No
LeeB_CDN 179 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
LeeB_CDN 179 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp,

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

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
LeeB_CDN 179 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No