Collection LookUp Issue

[Kathryn-Marie]

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 🙂

[poweractivate]

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

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.

[poweractivate]

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

[poweractivate · Accepted solution]

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

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]

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

[poweractivate]

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