Hi @MarvinBangert ,
Thanks for your suggestion. However, I realized that this approach will not be very helpful if the values within the matrix are changing (new coordinators might get assigned or roles might change). To deal with this, I came up with an indigenous approach that is working well for me now.
1. I populated the matrix and the coordinators' list in the form of table1 and table2 in an excel spreadsheet (saved on onedrive).
2. I used power query to unpivot the data in table1
3. I got table1 in a new layout which we can call as table_new
4. I loaded data from table_new to Powerapps. I also loaded data in table2 (Coordinators' list) to Powerapps
5. I created a collection of table_new where I added an additional column which is a combination of columns 'Site', 'IWorkFor' and 'INeedHelpWith' (let's call it LookupField).
6. In the same collection, I also added another column where I looked up coordinator's name from the Coordinators' list.
//Create a collection of coordinator matrix
ClearCollect(CoordinatorMatrixTemp,
AddColumns(CoordinatorMatrix,
"LookupField",Site &"-"& IWorkFor &"-"& Attribute,
"Coordinator1",First(Filter(CoordinatorList,CoordinatorList[@Value] = CoordinatorMatrix[@Value])).Coordinator1,
"Coordinator1Email",First(Filter(CoordinatorList,CoordinatorList[@Value] = CoordinatorMatrix[@Value])).Coordinator1Email,
));
7. Now in my form, after a user has selected values in the 3 fields 'Site', 'IWorkFor' and 'INeedHelpWith', I concatenate these fields (LookupField) and lookup the respective Coordinator's name from the collection I created.
If(RequestorSite_Input.Text <> "Kiel",
LookUp(CoordinatorMatrixTemp,"Others" &"-"& IWorkFor_Input.Selected.IWorkFor &"-"& INeedHelpWith_Input.Selected.INeedHelpWith
in LookupField).Coordinator1,
LookUp(CoordinatorMatrixTemp,"Kiel" &"--"& INeedHelpWith_Input.Selected.INeedHelpWith in LookupField).Coordinator1
)
Done. If there are any future changes in the matrix, the admin open the excel file, make the changes to the table and refresh the query and the changes will reflect in the app.
Regards,
Aakash
