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 

You will need to specify the column name in your formula. You cannot refer to a column name by some other means.

So, there will be some logic in your formula.

Your Items property on your Gallery would be:

AddColumns(List1,
    "_response", 
        With(LookUp(List2, Name=User().DisplayName),
            Switch(Question,
                "Q1", Q1,
                "Q2", Q2,
                "Q3", Q3
            )
        )
)

This will provide what you need for the Gallery.

The reason you have to do it like above is because you have chosen to make your column name based on Row values.  So, you have to "Switch" for each condition.

I hope this is helpful for you.

[gdbgdb]

@RandyHayes 

Thanks for the advice - I've tried to do this but I have a couple of complications. Firstly, when importing the Sharepoint lists, the columns come through as Title, field_1, field_2 for both lists. This creates a slight headache in that I've had to reverse engineer what the column names actually are as it's not as straightforward as Q1, Q2.

So, for List1, the first column ('Title' when imported) is actually "A.1", A.2", etc.. through to "E.7" - about 30 rows in total.

For List2, when read in is 'Title', 'field_1', 'field_2', .... 'field_31' 

I can almost get the gallery to work, but there an issue in that the Switch statement isn't working. My code is as below where I'm using this to try and get the first line to populate.

AddColumns('List1',
    "Response", 
        With(
            LookUp('List2', Title=User().FullName),
            Switch(Title,"A.1",field_1.Value,"aa")
        ))

 This should add a new column to the List1 list with a field name "Response" where the values are determined by the lookup statement, populated with the appropriate value corresponding to the column in List2, for the User specifed in the List2 Title column. ! 

I'm not convinced the Switch formula is right and I can't confirm which 'Title' this is using - the 'Title' in List1 or that in List2. It should be Title in List1 I think, I added the default response "aa" so, all I get just now is "aa" in the Response column for all gallery items, so at least it's returning a value, just not the value I want.

Help?

[RandyHayes · Accepted solution]

@gdbgdb 

First, when you import lists into SharePoint it will name your columns like you are seeing.  You cannot rename a column once it is created (you can rename in SharePoint, but the column will always retain the original name.)  The only solution to that is to manually create your list and then import your data into the list once the columns are established and named.

For the Switch formula, which Title do you want to look at?  If you want the one from List1, then you need to modify the formula to the following:

AddColumns('List1' As _lst1,
    "Response", 
        With(
            LookUp('List2', Title=User().FullName),
            Switch(_lst1.Title,"A.1",field_1.Value,"aa")
        ))

Otherwise, in the formula you have, it will be looking at the Title in List2.

[gdbgdb]

@RandyHayes  - you are indeed a genius. Fantastic. Just what I was looking for 👍

[gdbgdb]

I now need to be able to toggle an icon (yes/no) in that gallery view, so I'm looking for the reverse of this now, ie I don't want to update the 'Response' column. I want to update the underlying Sharepoint list.

Another way, if user 'Bob' logs in and clicks an icon to toggle from Yes to No (or vice versa) I want to be able to update the entry in the List2 table, that corresponds with the question in List1.

Suspect this is a Patch command but I'm having no luck with that either. (yes, I'm a newbie!)

Can you advise @RandyHayes ?

[RandyHayes]

@gdbgdb 

You didn't mention what in List2 you want to update or what logic it should have.

I am not sure what you mean about "toggle an icon"...what are you looking to change about the icon?

As for the Update to the List2...it would essentially use a formula such as this:

Patch(List2, LookUp(List2, Title=User().FullName), {column: value})

[gdbgdb]

More detail - so the gallery I now have is something like:

Num	Question	Detail	Complete	Icon
Q1	blah blah	blah blah	Yes	👍
Q2	blah	blah	No	👎👍
etc....				👍

and this is the output of the revised Gallery including the Response column (which brings data from List2 into List1).

What I'd like is for the User to simply click on the icon so that it toggles between "CheckBadge" and "CancelBadge" for Yes and No and in doing so, change the value in List2 for the corresponding question, ie sets the value in the appropriate column of List2 (filtered for the User().FullName)

In do so, the Response column of the Gallery updates.

Seems convoluted I know, but I'm almost there... it's the {column:value} references I'm struggling with once again. 

[RandyHayes]

@gdbgdb 

So let's get to some more detail even beyond that...

What is the structure (columns, etc.) of your List1 and List2 (at least those that are relevant to this solution).

What is the Items property of your Gallery. The Icon formulas, etc.

[gdbgdb]

@RandyHayes 

More information below

List 1 column goes A1.1-A1.7, B1.1-B1.5, etc... Small table, about 31 rows. Held in Sharepoint as central dataset.

List1

Question	Description	Detail
A1.1	blah	blah blah
A1.2	blah	blah blah blah
A1.3	blah	blah

List2 is the answers to these questions (Yes/No) answers for each user

Name	A1.1	A1.2	etc..
Bob	Yes	Yes	Yes
Jane	Yes	No	Yes

The Gallery now shows, thank to your help

Question	Description	Detail	Icon
Q1	blah blah	Blah	(Checkbadge)
Q2	blah blah blah	Blah	(Cancelbadge)
Q3	blah		etc..

Only property on Icons that's of relevance is:

If(
    ThisItem.Response="Yes",   
    Icon.CheckBadge,
    Icon.CancelBadge
)

I'm looking to create the formula on Icon.OnSelect so that when selected the value will toggle between Yes and No. What I want is for the dataset stored in Sharepoint (List2) to be updated when toggled.

Hope thats clear(er)