Creating check sheet

[paranap]

Hi.

I am new to powerapps and have made some progress but am now stuck.  I have a check sheet with a number of training requirements that have to be monitored.  So have got a spreadsheet which is my data source with the headers as the different requirements then each record has a name and then yes/no to whether they have done them.  So what I'd like from the app is a list of the requirements then a check box against each one for people to go down and tick to indicate whether the training has been done.  

Hope that makes sense and can be achieved

Neil

[v-sheyu-msft]

Hi @paranap,

What is the spreadsheet ?

Can you show me the structure of your spreadsheet ?

You can add a gallery to display requirements of spreadsheet and add a check box to the first item of the gallery.

I made a test.

Set Oncheck property of check box to:

Patch(test3,LookUp(test3,Gallery1.Selected.Title=Title),{column2:"Yes"})

Set Onuncheck property of check box to :

Patch(test3,LookUp(test3,Gallery1.Selected.Title=Title),{column2:"No"})

Regards,

Eason

[paranap · Accepted solution]

Hi.

Thanks for your reply.  That looks exactly what Im looking for.  This is an idea of what I have so in your example Training 1 . 2... would be where the requirment bit goes and the Option check box would then deposit a Yes or No in the relevent cell.  How do you get the Gallery to display the Row titles.  I've only been able to get it to display the records.

Thank you again

Neil

[Drrickryp]

Hi @paranap

I accidently accepted this as a solution when I meant to hit the post button. IMHO, While you may be able to create an entry form for your spreadsheet, your underlying design is flawed. What you have is a classical example of a Many to many relationship between Trainees and Courses. Unless you set up the underlying tables properly, you will have problems with it in the future when you try to retrieve useful information, add or remove courses and trainees. You are already encountering some issues with respect to showing your column listings. The figure illustrates the way to design the tables for a many to many relationship.  

The central table is referred to a junction table and is used for showing the Many side when a selection is made from either of the One sides of the relationship (either Student or Courses). For example in your app, you can have a dropdown control for selecting student, and a gallery or datatable that shows the courses they have taken. Likewise, you could have dropdown that shows the Course and a datatable that shows the students who have completed it.  For more information regarding database design in Powerapps, see my posts in the community blog starting with Database-Design-Fundamentals-and-PowerApps-An-Overview

[paranap]

Hi

Thank you so much for your comments and advice.  I thought it was going to be a simple thing but started to grow.  I agree I do need to have proper table structures and normalise the data.

I will do this and try to implament the front App section once that is done.  I will probably need some more advice with that when I come to it.

Neil

[paranap]

This is the table I've done so far.  Should be a little bit better and more robust than what I had.

[Drrickryp]

It looks like you are on the right track now. Let PowerApps create the app from your junction table, then add the other two lookup tables. Adjust the edit form by adding dropdowns to the cards for the foreign keys and have the Update property of the card reference dropdown.selected.id.
In.the gallery, use Lookup() to get the names and other relevant data from the lookup tables.
Then create the galleries and edit forms for the lookup tables. Et voila.