Changing an icon to true/false depending on the outcome of a lookup across two lists

[gdbgdb]

I have one sharepoint list (List1)with

Question	Description
Q1	blah blah
Q2	blah blah blah
Q3	blah

and another sharepoint list (List2) with

Name	Q1	Q2	Q3
Bob	Yes	Yes	Yes
Jane	Yes	No	Yes

What I want to do, in a Gallery view which lists the questions and has the Name set to the User().FullName is to be able to toggle an icon from true to false so that it changes, and the underlying table values also change.

So, what I'm looking for is the appropriate Lookup/Filter Combination that does something like

1) Filter List 2 to just provide the rows that are relevant to the user, ie Filter(List2, User().FullName=Name) or equivalent

2) Do a Lookup on List 1 to then provide the logic against the appropriate Question

Ultimately I want the gallery to show, for a given user, which for Bob above would be

Question	Description	Result (yes/No)
Q1	blah blah	Yes
Q2	blah blah blah	Yes
Q3	blah	Yes

Question : what is the formula and combination of lookup/filter/etc. that I need across both lists. I'm going round the bend trying to work this out

[RandyHayes]

@gdbgdb 

So your biggest challenge is going to be with List2.  In there you have columns for each question.  This is going to cause you much more work in your formulas.  As well, if you create, let's say, 10 columns for questions and then later have 11, what then?  In your current setup, you will need to not only add the column to the list, but then have to modify the app to accommodate the question.

Normally, you would want to have your List to as - User, Question, Answer

This way you can update much more easily.

SO...that said, currently your formula on the OnSelect of the Icon will need to be this:

With({_value: If(ThisItem.Response="Yes", "No", "Yes")},
    With({_record:
        Switch(ThisItem.Question,
            "A1.1", {A1.1: _value},
            "A1.2", {A1.2: _value},
            "A1.3", {A1.3: _value},
            ..etc...
        )},
        Patch(List2, LookUp(List2, Title=User().FullName), _record)
    )
)

[gdbgdb]

@RandyHayes 

A few minor tweaks needed to adjust for imported column titles and that also works fine .... for the first time I select an icon. However for all subsequent icon clicks, nothing happens.

I need to refresh something somewhere, but not clear where that is.

[RandyHayes]

@gdbgdb 

What is the Items property of the Gallery?

[gdbgdb]

As you provided earlier.

AddColumns(List1 As _Lst1,
    "Response", 
        With(
            LookUp(List2, Title=User().FullName),
            Switch(_Lst1.Title,
            "A.1",field_1.Value,"A.2",field_2.Value,"A.3",field_3.Value,
            :
            :
            "E.7",field_31.Value,
            "--"
            )
        )
)

[RandyHayes]

@gdbgdb 

Ah yes...forgot about that one 😜

Tell me more about the Icon.  What is your OnSelect action formula at this point, did you change anything?

Also, to be clear - the Response column is a text column - right?

[gdbgdb · Accepted solution]

The only difference is I change ThisItem.Question to ThisItem.Title as the column header 'Question' didn't come through on the Sharepoint upload.

With({_value: If(ThisItem.Response="Yes", "No", "Yes")},
    With({_record:
        Switch(ThisItem.Title,
            "A.1", {field_1: _value},
            "A.2", {field_2: _value},
            "A.3", {field_3: _value},

etc...

        )},
        Patch(List2
          , LookUp(List2,Title=User().FullName), _record)
    )
)

Having saved, published, come out and then back in again the icon toggle doesn't work at all now and text in Sharepoint list is not being updated. 😞

[gdbgdb]

@RandyHayes 

Sorted - I was being an idiot and had the Sharepoint list using Choice columns rather than text for some columns, so the above formulae needs to change to remove .Value references.

I'll do that tomorrow/tonight and report back.

Thanks for all your help @RandyHayes - fantastic help indeed. Learnt so much in such a short time!

[RandyHayes]

@gdbgdb 

Sounds good.  Glad to help and let me know how it goes!

[gdbgdb]

@RandyHayes 

All sorted - I decided to crack on and fix it tonight after all. Much appreciate again 👍