@CatalinaBP - obviously I would not recommend creating a SharePoint List with 134 columns (which would be ridiculous).
Unfortunately, the way I would approach this requires a significant number of steps and solid foundational knowledge of different functions and controls.
This will be difficult to walk through in a forum post, but to give you a sense of direction:
Firstly, I would unpivot your static SharePoint List schema so that your "areas" are repeated are across rows, and your repeating columns are created once across columns (the green table in the image). This would eliminate the need for 134 columns.
Secondly, I would create a "manual" Table using the Table function to recreate the 67 row schema of your SharePoint List and populate existing values in that SharePoint list. For example:
Table(
{
'Reporting Period': "2023-2024",
Category: "A",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "A",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "A",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "B",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "B",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "B",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "C",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "C",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
},
{
'Reporting Period': "2023-2024",
Category: "C",
Status: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Status
),
Description: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Description
),
Notes: LookUp(
'Your List',
'Area Name' = Dropdown1.Selected.Value && 'Some Other Unique Identifier' = Dropdown12.Selected.Value,
Notes
)
}
)
Note it is very likely the above will yield performance issues, but these can be managed later.
Thirdly, I would create a single Form. The Default property should be your SharePoint List, and the Item property should be a LookUp back to your data source based on the selected Gallery Item. E.g.
LookUp(
'Your Data',
Area = Gallery1.Selected.Area && 'Reporting Period' = Gallery1.Selected.'Reporting Period'
)
------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.
If you like my response, please give it a Thumbs Up.
Imran-Ami Khan
