Skip to main content
Community site session details

Community site session details

Session Id : kowZ1FZrMAReH5yqWjTK3v
Power Apps - Building Power Apps
Answered

Need help with ClearCollect/AddColumns/Lookup combination

Like (0) ShareShare
ReportReport
Posted on 25 Jan 2020 20:55:39 by 68

I am looking for syntax help with a Polymorphic 'Lookup'.  I am working in a Canvas App connected to CDS entities (Accounts, Tasks (renamed 'Tasks-Memos' here)).  This is a very simplified example, but captures my issue.

 

In OnVisible for the Home Screen of the app,

gblColAccounts is loaded with data from the Accounts entity.

gblColTasksMemos is loaded with data from the Tasks entity, which includes a 'Regarding' field. 

 

In the App, the user is able to search for text in both the Subject and Regarding string (ie if regarding is an Account, the search includes the Account Name, if regarding is an Opportunity, the search includes the Opportunity Topic, etc).  To make this work, I use AddColumns to add the column "Regarding Search String" and use IsType/AsType to grab the name/topic/etc string from the appropriate entity.

 

Because I am loading Accounts into gblColAccounts, for use elsewhere in the app, I would like to use a Lookup into gblColAccounts to get the 'Account Name' rather than going out to the Accounts entity with a query (AsType).  Is this possible?  I have tried many combinations but cannot nail down the syntax to do a lookup into gblColAccounts using Regarding from gblColTasksMemos.

 

The example below works properly, but if I uncomment the Lookup and try using that instead of the AsType, there is a red line under the '=' and a Formula error: 'Invalid Argument Type'.  I have not been able to create a search string for the Lookup.  Can anyone help?

 

(I do have Advanced Settings->Explicit Column Selection set to 'Off', if that helps)

 

lookup collection.jpg

Categories:
  • Verified answer
    LynF Profile Picture
    68 on 27 Jan 2020 at 21:30:26
    Re: Need help with ClearCollect/AddColumns/Lookup combination

    Thanks to you both @Anonymous and @mdevaney for your feedback.  I ultimately decided that the IsType/AsType was the best route to go, and this worked: 

     

    ClearCollect(gblColTasksMemos,
    AddColumns(
    'Tasks-Memos',
    "Regarding Search String",
    If (IsType( Regarding, [@Accounts]AsType (Regarding,[@Accounts]).'Account Name',"not an account")))
  • mdevaney Profile Picture
    29,987 Moderator on 25 Jan 2020 at 23:37:16
    Re: Need help with ClearCollect/AddColumns/Lookup combination

    @lfinman 
    I’ll throw my hat into the ring too... I suspect this line of code is causing the issue.

    gblColAccounts.'Account' = Regarding.'Account Name'


    The invalid argument type error appears when comparing two different data types to one another. In this case you are likely comparing a Table or Record to a Text value.  The full error message will tell you exactly what is going on.

     

    It’s hard to say exactly what you should do without knowing the structure of your collection but my examples below should offer some ideas

    First(gblColAccounts.'Account').your_fieldname_here = Regarding.'Account Name'
    First(gblColAccounts).'Account' = Regarding.'Account Name'

     

    ---
    Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

  • Community Power Platform Member Profile Picture
    on 25 Jan 2020 at 23:01:42
    Re: Need help with ClearCollect/AddColumns/Lookup combination

    @lfinman 

    My quick (first ever) read of the IsType/AsType docs seems to tell me that your first argument of these functions are not RECORDS but are fields in a record ie Regarding is a field not a record. Maybe I have that wrong ...?

     

    The first arguments of IsType/AsType need to be RecordReferences maybe this would work?

    First(glbColTaskMemos)

     

    Happily ignore the above if I'm way off, lol

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Loading complete