cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kathryn-Marie
Frequent Visitor

Collection LookUp Issue

Hi All,

 

I have a collection which technically works, but is not giving me the result I expect. I am trying to create a collection which has release name and project name. The release table currently has the prrject as an ID, but I want to use Name and Release in a combobox together. Here is my current code:

 

ClearCollect(
ProjectAndRelease,
AddColumns(
Releases,
"ProjectName",
LookUp(
Projects,
Project_ID = Releases[@Project],
Project
) ) )

 

What is happening, is I am getting a collection with the project ID in the Project Name Column, but I want the Name, pulled from the Projects table.

 

Thanks for your help 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

You add column to Releases, which causes confusion, since both tables are now in scope in the formula.

 

If both tables or data sources have field with the exact same name Project, 

then you must use disambiguation in this case of

Projects[@Project]

instead of just Project by itself.

 

 

 LookUp(Projects,Project_ID = Releases[@Project],Projects[@Project]) 

 

 


See if it helps   

 

For reference:

 

Operators - docs.microsoft.com

poweractivate_0-1662688880599.png

 

More info:

 

Disambiguation Operator - docs.microsoft.com

Some functions create record scopes for accessing the fields of table while processing each record, such as Filter, AddColumns, and Sum. Field names added with the record scope override the same names from elsewhere in the app. When this happens, you can still access values from outside the record scope with the @ disambiguation operator:

  • To access values from nested record scopes, use the @ operator with the name of the table being operated upon using this pattern:
    Table[@FieldName]
  • To access global values, such as data sources, collections, and context variables, use the pattern [@ObjectName] (without a table designation).

For more information and examples, see record scopes.


 

View solution in original post

6 REPLIES 6

@Kathryn-Marie 

 

Try to change this:

 

LookUp(Projects,Project_ID=Releases[@Project],Project)

 

 

to this:

 

LookUp(Projects,Project_ID=Releases[@Project],Your_Field_Name_for_Project_Name_Here)

 

In the above, change Your_Field_Name_for_Project_Name_Here to the name of your Project Name field.

 

 

If that doesn't work, try this:

 

LookUp(Projects,Project_ID=Releases[@Project],Project).Your_Field_Name_for_Project_Name_Here

 

In the above, change Your_Field_Name_for_Project_Name_Here to the name of your Project Name field.

 

 

 

See if it helps @Kathryn-Marie 

Kathryn-Marie
Frequent Visitor

Yeah that's what I've got. The field in the Projects table I'm trying to retrieve is called Project. But it looks like it's bringing back the field in the Releasestable called Project, which is the ID.

@Kathryn-Marie 

 

The issue might be that the data source Projects is pointing to the Releases table instead. Check where the Data sources are pointing to in Power Apps.

 

(your original formula is)

 

ClearCollect(
   ProjectAndRelease,
   AddColumns(
      Releases,
      "ProjectName",
      LookUp(
         Projects,
         Project_ID = Releases[@Project],
         Project
       )

   )
)

 

It also may be something else, since you are using AddColumns on the Releases table first, and the Lookup is being done inside.

I might know why that is then.

 

You add column to Releases, which causes confusion, since both tables are now in scope in the formula.

 

If both tables or data sources have field with the exact same name Project, 

then you must use disambiguation in this case of

Projects[@Project]

instead of just Project by itself.

 

 

 LookUp(Projects,Project_ID = Releases[@Project],Projects[@Project]) 

 

 


See if it helps   

 

For reference:

 

Operators - docs.microsoft.com

poweractivate_0-1662688880599.png

 

More info:

 

Disambiguation Operator - docs.microsoft.com

Some functions create record scopes for accessing the fields of table while processing each record, such as Filter, AddColumns, and Sum. Field names added with the record scope override the same names from elsewhere in the app. When this happens, you can still access values from outside the record scope with the @ disambiguation operator:

  • To access values from nested record scopes, use the @ operator with the name of the table being operated upon using this pattern:
    Table[@FieldName]
  • To access global values, such as data sources, collections, and context variables, use the pattern [@ObjectName] (without a table designation).

For more information and examples, see record scopes.


 

Kathryn-Marie
Frequent Visitor

That helped thanks! It wasn't the exact answer, as using Project[@Project] returned the whole table, but 'Project (project)' worked 😄

@Kathryn-Marie 

 

If it said that, it was a typo, the correct answer is Projects[@Project] . If it was wrong, I already edited it now in the post to be correct.

 

It's TableName[@FieldName] - so it'll be whatever the Table name is. It should be probably the same as the data source used for the Lookup, so if the LookUp on Projects data source otherwise works, and assuming you did indeed have Project as a field in the Table Projects it's probably Projects[@Project] for you.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,741)