I am trying to create a single collection using data from two SharePoint lists.
My two lists look something like this:
List 1: Metric Values
| Title | MetricID | MTD Actual | MTD Target | Reporting Month | Reporting FY |
| Metric A | 1 | .74 | .75 | 7 | 23 |
| Metric A | 1 | .77 | .75 | 8 | 23 |
| Metric B | 2 | .81 | .8 | 7 | 23 |
| Metric B | 2 | .8 | .8 | 8 | 23 |
List 2: Metric Attributes
| Metric Name | MetricID | Definition | Owner (person field) | Reporter
(person field) | Reporting Cadence |
| Metric A | 1 | Definition A | John Smith | Jane Doe | Monthly |
| Metric B | 2 | Definition B | Angela Jones | Andy Woods | Monthly |
I want to pull all records from List A where Reporting Month = VarMonth and Reporting FY= VarFY (two variables I have set onstart of the app), and the matching records using the MetricID from List B into one collection using the MetricID column as a unique Identifier. I need all of the columns from both lists to use that collection to display the Values and the Attributes in the same gallery and be able to filter the gallery using columns from both.
So the end Result would be something like this if Varmonth=7 and VarFY=23
| Title | MetricID | MTD Actual | MTD Target | Reporting Month | Reporting FY | Metric Name | Definition | Owner (person field) | Reporter
(person field) | Reporting Cadence |
| Metric A | 1 | .74 | .75 | 7 | 23 | Metric A | Definition A | John Smith | Jane Doe | Monthly |
| Metric B | 2 | .81 | .8 | 7 | 23 | Metric B | Definition B | Angela Jones | Andy Woods | Monthly |
I've seen several questions like this all with different solutions but can't seem to get any of them to work for me.
Thank you!
