Why isn't this lookup working?

[EpicTriffid]

Hi all,

I have a gallery with a set of items that I am wanting to insert a column into. This is populated by a lookup, but no matter what i do, all i'm getting is zero. Here is the code going into the items gallery:

AddColumns(
    colCalendar,
    "SortOrder",
    LookUp(
        colTaskTypes,
        TaskType = ThisRecord.TaskType
    ).SortOrder
)

This is the lookup list:

    ClearCollect(
        colTaskTypes,
            {TaskType: "Task Type", Colour: "", SortOrder: "0"},
            {TaskType: "Module Design", Colour: "#2F75B5", SortOrder: "1"},
            {TaskType: "Module Planning", Colour: "#9BC2E6", SortOrder: "2"},
            {TaskType: "Module Delivery", Colour: "#BDD7EE", SortOrder: "3"},
            {TaskType: "Module Management", Colour: "#DDEBF7", SortOrder: "4"},
            {TaskType: "Team Leadership", Colour: "#70AD47", SortOrder: "5"},
            {TaskType: "Timetabling & Room Booking", Colour: "#548235", SortOrder: "6"},
            {TaskType: "Programme Development", Colour: "#F4B084", SortOrder: "7"},
            {TaskType: "Programme Management", Colour: "#C65911", SortOrder: "8"}
   )

So I'm wanting to bring over that sort order by colTaskTypes. but all i get is this:

Am I missing something here?

[KRider]

I may be able to help 😄 

Sorry, forgot to add the new Collection:

ClearCollect(
    colNewCalender,
    AddColumns(
        colCalendar,
        "SortOrder",
        LookUp(
            colTaskTypes,
            TaskType = colCalendar[@TaskType],
            SortOrder
        )
    )
)

[EpicTriffid]

Very helpful! Thank you @KRider

However, to complicate things, this is the original code that snipped came out of:

AddColumns(
    Filter(
        colCalendar,
        Semester = varSemesterView, 
        WeekName = btn_EventWeekName.Text, 
        WeekCommencing = DateValue(lbl_BGEventWeekCommencing.Text), 
        StartDateTime <> Blank(), 
        Or(
            Department = ddl_DepartmentFilter.Selected.ShortName, 
            Department = "All"),
        Or(
            ddl_ResponsibilityFilter.Selected.Value = "Responsibility",
            Responsibility = ddl_ResponsibilityFilter.Selected.Value),
        Or(
            ddl_TaskTypeFilter.Selected.TaskType = "Task Type",
            TaskType = ddl_TaskTypeFilter.Selected.TaskType)
    ),
    "LeadCount",
    CountRows(TaskLeads),
    "SortOrder",
    LookUp(
        colTaskTypes, TaskType = colCalendar[@TaskType], SortOrder)
)

On it's own, your code works fine, but when added into the above, I get an error in the look that it can't compaire incompatible types (text, table). Why is that?

[KRider]

Forgive the code wrecking. I was making it work in my own example. You can use ThisRecord.TaskType

ClearCollect(
    colNewCalendar,
    AddColumns(
        Filter(
            colCalendar,
            Semester = "1st", 
            WeekName = "", 
            WeekCommencing = Today(), 
            StartDateTime <> Blank(), 
            Or(
                Department = "Mine", 
                Department = "All"
            ),
            Or(
                Responsibility = ""
            )
        ),
        "LeadCount",
        CountRows(colCalendar[@TaskLeads]),
        "SortOrder",
        LookUp(
            colTaskTypes, TaskType = ThisRecord.TaskType, SortOrder
        )
    )
)

[EpicTriffid]

Hi @KRider 

Hmm, that's not working either:

AddColumns(
    Filter(
        colCalendar,
        Semester = varSemesterView, 
        WeekName = btn_EventWeekName.Text, 
        WeekCommencing = DateValue(lbl_BGEventWeekCommencing.Text), 
        StartDateTime <> Blank(), 
        Or(
            Department = ddl_DepartmentFilter.Selected.ShortName, 
            Department = "All"),
        Or(
            ddl_ResponsibilityFilter.Selected.Value = "Responsibility",
            Responsibility = ddl_ResponsibilityFilter.Selected.Value),
        Or(
            ddl_TaskTypeFilter.Selected.TaskType = "Task Type",
            TaskType = ddl_TaskTypeFilter.Selected.TaskType)
    ),
    "LeadCount",
    CountRows(TaskLeads),
    "SortOrder",
    LookUp(
        colTaskTypes, TaskType = ThisRecord.TaskType, SortOrder)
)

I've got absolutely no idea why this isn't working!

[KRider]

You need to recollect it:

ClearCollect(
    colNEWcalendar,
AddColumns(
    Filter(
        colCalendar,
        Semester = varSemesterView, 
        WeekName = btn_EventWeekName.Text, 
        WeekCommencing = DateValue(lbl_BGEventWeekCommencing.Text), 
        StartDateTime <> Blank(), 
        Or(
            Department = ddl_DepartmentFilter.Selected.ShortName, 
            Department = "All"),
        Or(
            ddl_ResponsibilityFilter.Selected.Value = "Responsibility",
            Responsibility = ddl_ResponsibilityFilter.Selected.Value),
        Or(
            ddl_TaskTypeFilter.Selected.TaskType = "Task Type",
            TaskType = ddl_TaskTypeFilter.Selected.TaskType)
    ),
    "LeadCount",
    CountRows(TaskLeads),
    "SortOrder",
    LookUp(
        colTaskTypes, TaskType = ThisRecord.TaskType, SortOrder)
)
)

[EpicTriffid]

I'm really and thank you so much for your time and patience but it's still not working, even Collecting it again:

ClearCollect(colTestCal,
AddColumns(
    Filter(
        colCalendar,
        Semester = varSemesterView, 
        WeekName = btn_EventWeekName.Text, 
        WeekCommencing = DateValue(lbl_BGEventWeekCommencing.Text), 
        StartDateTime <> Blank(), 
        Or(
            Department = ddl_DepartmentFilter.Selected.ShortName, 
            Department = "All"),
        Or(
            ddl_ResponsibilityFilter.Selected.Value = "Responsibility",
            Responsibility = ddl_ResponsibilityFilter.Selected.Value),
        Or(
            ddl_TaskTypeFilter.Selected.TaskType = "Task Type",
            TaskType = ddl_TaskTypeFilter.Selected.TaskType)
    ),
    "LeadCount",
    CountRows(TaskLeads),
    "SortOrder",
    LookUp(
        colTaskTypes, TaskType = ThisRecord[@TaskType], SortOrder)
))

The only thing i can think of is that the lookup isn't iterating over each row of colCalendar and doing the lookup? But I was under the impression ThisRecord would achieve that?

[KRider]

I see it!

ThisRecord.TaskType

[EpicTriffid]

Argh! Still no! 

I have noticed though, that it is performing some kind of lookup, because it's returning 0, which is the first option in colTaskTypes. Changing that 0 to 9 means I now return only 9s. So it's not really checking the whole lookup list?

[KRider · Accepted solution]

Check this out, Filter() As NewRecord

Then the lookup uses NewRecord.TaskType

ClearCollect(
    colNewCalendar,
    AddColumns(
        Filter(
            colCalendar, 
            WeekName = "", 
            WeekCommencing = Today(), 
            StartDateTime <> Blank(), 
            Or(
                Department = "Mine", 
                Department = "All"
            ),
            Or(
                Responsibility = ""
            )
        ) As NewRecord,
        "LeadCount",
        CountRows(colCalendar[@TaskLeads]),
        "SortOrder",
        LookUp(
            colTaskTypes, 
            TaskType = NewRecord.TaskType,
            SortOrder
        )
    )
)