Answered

AddColumn with Lookup for each Record in Collection

Like (0) Share

Report

Posted on 28 Apr 2020 12:47:45 by Community Power Pla...

Hi,

I have a collection that I want to add a column to. For each record I need to return the Question by looking up the idQuestion from the collection to a different table. The problem is that the collection results in repeating the first result (Question) through each record.

For example:

Here is what I'm getting in my collection:

idQuestion	Question
1	Question 1
2	Question 1
3	Question 1
4	Question 1

This is what I want:

idQuestion	Question
1	Question 1
2	Question 2
3	Question 3
4	Question 4

Here is my code:

ClearCollect(
 colSurvey,
 AddColumns(
 '[dbo].[Surveys]',
 "Question",
 LookUp('[dbo].[SurveyQuestions]',idQuestion = idQuestion,Question)
 )
)

Thank you

Categories:

General Questions

All responses (2)

Answers (1)

Community Power Pla... on 28 Apr 2020 at 12:56:05

Like (0)

Report

Re: AddColumn with Lookup for each Record in Collection

@mdevaney Perfect! Silly oversight on my part.

Thank you!
Verified answer

mdevaney 29,987 Super User 2025 Season 1 on 28 Apr 2020 at 12:52:58

Like (1)

Report
Re: AddColumn with Lookup for each Record in Collection

@justair007
The issue is with this section of code

idQuestion = idQuestion

You'll need to specify which table the idQuestion comes from using the disambiguation operator [@] as I have done below.

ClearCollect( colSurvey, AddColumns( '[dbo].[Surveys]', "Question", LookUp('[dbo].[SurveyQuestions]',idQuestion = '[dbo].[Surveys]'[@idQuestion],Question) )

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