If 'Calendar.Category' text is the same as 'Table2.Category' text return 'Table2.Question'

[KyleJackDaniel1]

Hello,

I have two data sources, Galley1 which is a Calendar (SharePoint List) & Table2 (Excel Table).

I'm trying to return a value if "ThisItem.Category = the same as Table2.Category, the return Table2.Question"

For example:

Calendar Data Source	Table2 Data Source	Tablet2 Question
Category: HR	Category: HR	"Are they working?"
Category Accounting	Category Accounting	"Are they good at maths"

So if my gallery category is HR then it will return "Are they working?" in a text field.

Sorry for my bad explanation.

Many thanks,

Kyle

[KRider]

Is there anyway you could set a variable?

So if Table2 was loaded onVisible you could Set(var_checkcategory, Table2.Category) 

Hope this helps.

[KyleJackDaniel1]

I've tried playing round with setting variable however cant get it to work.

I believe the formula needs to be something like,

If(
ThisItem.Category.Value=Tablet2.'Category Name',Table2.Question
)

 But obviously need to filter the Table2 to only show the matching name and return the Question next to the matching name.

[KyleJackDaniel1]

I can get it partly to work with having Table2 in a Gallery and the referencing the selected item of that gallery.

If(ThisItem.Category.Value=Gallery5.Selected.'Category Name',Gallery5.Selected.Question)

But obviously that only displays the question if Gallery5 is selected and only returns if it matches the selection.

[KRider]

Right and you want your calendar gallery to know how to respond based on Table2. What if you used ClearCollect in your OnStart for the Table?

You could then reference the collection?

You could almost just use an IF statement for your calendar and then the label for question you could put

If(
ThisItem.Category.Value="Category1",
"question1",
ThisItem.Category.Value="Category2",
"question2",
)

[KyleJackDaniel1]

I don't think that'll work as Table2 gets updated a lot with different things, so id have to manually enter all category types and questions.

[KRider · Accepted solution]

Ok, so 

ClearCollect(collectionTable, Table2)

then on that label in the gallery

LookUp(
      collectionTable,
      ThisItem.Category = 'Category',
      Question
)

[KyleJackDaniel1]

Perfect, that has worked. That's very simple and something I completely missed. I was diving too deep down the 'If' statements and lost my way. 

Thank you @KRider .

[KRider]

Yeah man, no problem at all. I have done that many times myself, thinking complicated when the short easy answer was in front of my eyes.

Brainstorming can be a very powerful ally 😄