Lookup Not Working

[sscarcella]

I have a sharepoint list called onboarding with columns newhirename (Title), task1, task2, task3, task4.  The task columns are all yes/no columns for completed or not.

I have another list called assignedtasks with 3 single line text columns newhirename (Title), task, assignedto.  I pull this list into a collection called colAssignedTasks.

In my app I have an edit form that is connected to the onboarding list that shows the newhirename and all the tasks as cards with a yes/no toggle.  The datacardkey is the task name.  I populate the edit form by selecting a newhire in a gallery.  I want to only show the tasks that are assigend to the current logged in user.  

If(LookUp(colAssignedTasks, Title=newhirename.Text && Task=datacardkey.Text).AssignedTo=varCurrentUser,true,false)

I have tried many variations of the above formula and it doesnt work.  I have also tired to just return the AssignedTo in a label using LookUp(colAssignedTasks, Title=newhirename.Text && Task=datacardkey.Text, AssignedTo) but it doesnt return anything.

How can I hide the datacards that are not assigned to the current user?  the Lookup doesnt seem to like using the datacardkey.text as a parameter.

Any suggestions?

@Audrie-MSFT , @mrdang 

[PowerAddict]

Can you go to View->Collections and show me the data in the colAssignedTasks collection ?

[sscarcella]

@PowerAddict 
Sure, thanks for your help.  I actually stopped using the collection and tried connecting straight to the Sharepoint list but same results.

I have attached the collection and the sharepoint list screen shots  I have also tried to use a datatable and filter the items property with 
Filter('Task Assignments',Task="Name plate for workspace" && Title="Jane Doe") and also
Filter('Task Assignments',Task=TaskNamePlate.Text && Title=NewHireName.Text) using the datacard labels and the result is empty which doesnt make sense to me either...

[PowerAddict]

Is it possible for you to share a screenshot of your SP site properties that lists the diff column types?

[sscarcella]

@PowerAddict 
sure, no problem thanks for helping this makes no sense...  simple lookup not working.
Its all single line of text and even if you bring into a collection it would still be text...

[PowerAddict]

That looks good. When you were loading everything into a collection, was the Task column getting populated?

Also, can you do the lookup using the lower function? With something like this:

LookUp(colAssignedTasks, newhirename = Label81.Text && Lower(task) = Lower(Label89.Text)).assignedto

Let me know the results. We will get to the bottom of this, else I won't be able to sleep tonight 🙂 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
https://thepoweraddict.com

[v-xida-msft]

Hi @sscarcella ,

Have you take a try with the solution I provided above? Is it not working?

Firstly, please make sure the User().FullName formula returns a proper display name as that within the "AssignedTo" field in your "Task Assignments" List.

You could add a Label in your app, set the Text property to following:

User().FullName 

then check if it could display proper display name as that in your "AssignedTo" field

Note: I assume that the "AssignedTo" field is a Text type column in your SP List

Based on the formula you provided, I could not find any syntax issue with it. Please consider modify your formula as below:

If(
    Trim(DataCardKey.Text) in Trim(
                                   Filter('Task Assignments', NewHire = Dropdown1.SelectedText.EmployeeName && AssignedTo = varCurrentUser).Task
                              ),
    true,
    false
)

or

If(
    Trim(DataCardKey.Text) in Concat(
                                   Filter('Task Assignments', NewHire = Dropdown1.SelectedText.EmployeeName && AssignedTo = varCurrentUser),
                                   Task & ";"
                              ),
    true,
    false
)

Please also consider take a try with above solution, then check if the issue is solved.

Best regards,

[sscarcella]

@v-xida-msft 
Thank you for your reply but none of these solutions work...  No matter what I do I cannot use the Task column for anything.  I tried to leave the form out of it for now and am just trying to do the lookup on the sharepoint list and cannot get it to return the assignedto.

I can filter the sharepoint list in a datatable if I filter by assignedto and newhire .  I can filter by just assignedto or just newhire or I also have another column which is assignedtoemail and I can use that to filter the sharepoint list in a datatable but I cannot do anything with the Task column...  It makes no sense.
I tried your Trim formulas and still nothing.... I also tried using Lower.

[PowerAddict]

@sscarcella

I willbe more than happy to do a screenshare session with you to resolve this.

Let me know.

Thanks,
Hardit Bhatia
https://thepoweraddict.com

[v-xida-msft · Accepted solution]

Hi @sscarcella ,

Have you renamed the Title column into "NewHire" in your "Task Assignments" list?

Based on the needs that you mentioned, I think the LookUp function is enough for your scenario. Please consider set the Visible property of the Data Card in your Edit form to following:

If(
   IsBlank(LookUp('Task Assignments', Task = DataCardKey.Text && NewHire = Dropdown1.SelectedText.EmployeeName && AssignedTo = varCurrentUser)),
   false,
   true
)

or

If(
   IsEmpty(Filter('Task Assignments', Task = DataCardKey.Text && NewHire = Dropdown1.SelectedText.EmployeeName && AssignedTo = varCurrentUser)),
   false,
   true
)

Please replace the DataCardKey with actual Label name (DataCardKey1, DataCardValue2, ....) within your Edit form.

Best regards,

[sscarcella]

I was able to get it to work using the DataCard.DataField rather than DataCardKey.Text.
When creating the Sharepoint list columns if you use the Modern way by just hitting the + Add Column It will create the column without spaces.  If you use the classic way of adding columns by going into list settings and then adding a new column it will put the _x0020_ in as spaces.  I had a combination of both so using the datacardkey would work sometimes but not others.

I thought it would work since the text field and collection had spaces but... As we know, Sharepoint doesnt like spaces so best to take out all spaces and change the display name and use the DataCard.DataField.