App to Dynamically Create a Data Entry Form from a list of values

[bplantes]

I am new to Power Apps.  I'm hoping to get the community's help with how to approach an app.  I have a data set that contains a setof KPIs for each Department and Goal Category.  There are other attributes tied to the KPIs like thresholds that can be used for measuring in reporting later.

Owner	Goal Category	Measure	Type	Goal	Freq	Threshold 1 - Lower	Threshold 1 - Upper	Threshold 2 - Lower	Threshold 2 - Upper	Threshold 3 - Lower	Threshold 3 - Upper
Department A	Category A	KPI 2	integer	95	weekly	0	100	50	150	75	125
Department A	Category A	KPI 3	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department A	Category A	KPI 4	percentage	95%	weekly	0	1	0	1	0	1
Department A	Category A	KPI 5	integer	95	monthly	0	100	50	150	75	125
Department A	Category A	KPI 6	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department A	Category B	KPI 7	percentage	95%	monthly	0	1	0	1	0	1
Department A	Category C	KPI 8	integer	95	weekly	0	100	50	150	75	125
Department A	Category C	KPI 9	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department A	Category D	KPI 10	percentage	95%	weekly	0	1	0	1	0	1
Department A	Category D	KPI 11	integer	95	weekly	0	100	50	150	75	125
Department A	Category D	KPI 12	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department B	Category A	KPI 13	percentage	95%	monthly	0	1	0	1	0	1
Department B	Category A	KPI 14	integer	95	monthly	0	100	50	150	75	125
Department B	Category A	KPI 15	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department B	Category B	KPI 16	percentage	95%	weekly	0	1	0	1	0	1
Department B	Category B	KPI 17	integer	95	weekly	0	100	50	150	75	125
Department B	Category B	KPI 18	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department B	Category C	KPI 19	percentage	95%	weekly	0	1	0	1	0	1
Department B	Category E	KPI 20	integer	95	weekly	0	100	50	150	75	125
Department B	Category E	KPI 21	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department B	Category E	KPI 22	percentage	95%	monthly	0	1	0	1	0	1
Department C	Category A	KPI 23	integer	95	monthly	0	100	50	150	75	125
Department C	Category A	KPI 24	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department C	Category A	KPI 25	percentage	95%	weekly	0	1	0	1	0	1
Department C	Category B	KPI 26	integer	95	weekly	0	100	50	150	75	125
Department C	Category B	KPI 27	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department C	Category B	KPI 28	percentage	95%	weekly	0	1	0	1	0	1
Department C	Category C	KPI 29	integer	95	monthly	0	100	50	150	75	125
Department C	Category C	KPI 30	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department C	Category C	KPI 31	percentage	95%	monthly	0	1	0	1	0	1
Department C	Category C	KPI 32	integer	95	weekly	0	100	50	150	75	125
Department C	Category C	KPI 33	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department D	Category A	KPI 34	percentage	95%	weekly	0	1	0	1	0	1
Department D	Category A	KPI 35	integer	95	weekly	0	100	50	150	75	125
Department D	Category B	KPI 36	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department D	Category C	KPI 37	percentage	95%	monthly	0	1	0	1	0	1
Department E	Category A	KPI 38	integer	95	monthly	0	100	50	150	75	125
Department E	Category B	KPI 39	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department E	Category C	KPI 40	percentage	95%	weekly	0	1	0	1	0	1
Department E	Category C	KPI 41	integer	95	weekly	0	100	50	150	75	125
Department F	Category A	KPI 42	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department F	Category B	KPI 43	percentage	95%	weekly	0	1	0	1	0	1
Department F	Category C	KPI 44	integer	95	weekly	0	100	50	150	75	125
Department G	Category A	KPI 45	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department G	Category A	KPI 46	percentage	95%	monthly	0	1	0	1	0	1
Department G	Category A	KPI 47	integer	95	monthly	0	100	50	150	75	125
Department G	Category C	KPI 48	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department H	Category A	KPI 49	percentage	95%	weekly	0	1	0	1	0	1
Department H	Category B	KPI 50	integer	95	weekly	0	100	50	150	75	125
Department H	Category C	KPI 51	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department I	Category A	KPI 52	percentage	95%	weekly	0	1	0	1	0	1
Department I	Category B	KPI 53	integer	95	monthly	0	100	50	150	75	125
Department I	Category D	KPI 54	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department I	Category E	KPI 55	percentage	95%	monthly	0	1	0	1	0	1
Department J	Category A	KPI 56	integer	95	weekly	0	100	50	150	75	125
Department J	Category C	KPI 57	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department J	Category D	KPI 58	percentage	95%	weekly	0	1	0	1	0	1
Department J	Category E	KPI 59	integer	95	weekly	0	100	50	150	75	125
Department J	Category F	KPI 60	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department J	Category G	KPI 61	percentage	95%	monthly	0	1	0	1	0	1
Department K	Category F	KPI 62	integer	95	monthly	0	100	50	150	75	125
Department K	Category F	KPI 63	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department K	Category F	KPI 64	percentage	95%	weekly	0	1	0	1	0	1
Department K	Category G	KPI 65	integer	95	weekly	0	100	50	150	75	125
Department K	Category A	KPI 66	text	HIGH	weekly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department K	Category G	KPI 67	percentage	95%	weekly	0	1	0	1	0	1
Department K	Category A	KPI 68	integer	95	weekly	0	100	50	150	75	125
Department K	Category A	KPI 69	text	HIGH	monthly	LOW	HIGH	LOW	HIGH	LOW	HIGH
Department K	Category A	KPI 70	percentage	95%	monthly	0	1	0	1	0	1

As a user, I'd like to develop an app that has a form that allows me to fill out a "Reporting Date" and select the distinct "Department" values from a dropdown list.  This action would generate a grid with the all the KPIs for that Department.  I could then fill out the KPI values and click a "Submit" button which would put those values into another table:

Reporting Week End	Owner	Goal Category	Measure	Goal	Type	Value	Link	Roadblocks	Next Action	Assigned To
1/11/2019	Department A	Customer Satisfaction	KPI 2	95	integer	100		None
1/11/2019	Department A	Customer Satisfaction	KPI 3	HIGH	text	HIGH		None
1/11/2019	Department A	Customer Satisfaction	KPI 4	95%	percentage	75%		A has poor performance	Move A to B	A's Boss
1/11/2019	Department A	Customer Satisfaction	KPI 5	95	integer	40		None
1/11/2019	Department A	Customer Satisfaction	KPI 6	HIGH	text	MEDIUM		None

Is this at all possible with Power Apps?

[Mr-Dang-MSFT · Accepted solution]

This is very much possible. 

Think of your data in two tables

• list of KPIs
• raw data for reporting against KPIs

I shared an app very similar to this kind of structure, but it's about quizzes/forms.

• Table 1: list of all quizzes
• Table 2: list of questions related to each quiz
• Table 3: list of responses to each question in each quiz

https://powerusers.microsoft.com/t5/Community-Apps-Gallery/Design-Patterns-from-Mr-Dang-Part-2/m-p/1...

In a traditional solution, the questions of a quiz would be columns of a table, and answers to the questions would be values in each column (question). But that's not scalable since you would have to create new columns with each new question.

In a relational solution, multiple responses are saved to a separate table with a column that contains the id to the related question. With this structure in place, I would use a gallery to show all the questions for a selected quiz. In the gallery, I show and hide different controls depending on the type of question it is.

In your case, if you were to apply this solution, you would use a gallery for each of the KPIs for a given seleced department. The gallery would show and hide different controls for each type: percent, integer, text, etc. A form would not be used here since forms are meant for filling in data to specific columns of specific types. Since each question will change here, a gallery is more appropriate.

You can view the app with the instructions at the bottom of the post. Let me know if you have further questions.