Best Practices for Saving and Loading Multiple Text, Dropdown, & Date Inputs

[CEChandler]

Hey folks,

I recognize that I'm at the deep-end of the pool and need some assistance. Until Mid-July I had no experience with Power Apps, being a tech writer, but I have been tasked with creating an Onboarding application for new trainers. Aside from including easy stuff like helpful links and static information, the app needs to capture the new hire's demographic data, professional goals, and then a series of checklists for 19 weeks of certification, along with a few other additional checklists. The total app will end up with a few hundred points of data entry, all of which need to be dealt with in some capacity.

The meat of the Onboarding app is the weekly Task Checklist.  Each of these checklists has anywhere from 10 to over 20 "tasks", where the new hire records when they completed a task, and with whom they completed it. As such it's definitely not meant to be completed all at once, and it will almost always be interfaced on a desktop or laptop computer, rather than a phone or tablet.

I have a series of SharePoint Lists that either a) contain associated training team member information or b) are meant to hold completed data entry.

I've burned many hours trying to figure out how best to Patch entries into a list, and then to bring those entries back when the app is reloaded.

I know that I have not made the best decisions on layout and general structure, but followed my best instincts and understanding at the time.  I've been successful with getting the entries patched (but it's awful spaghetti, I know), but I'm not at all sure how to bring those entries back on-load.  There will be a few hundred entries that will eventually need to be submitted and retrieved for an individual, and the particular department has higher-than-normal turnover (1-2 per month - it's the "entry to the company" department) so the Lists will become progressively larger if we keep the entries from every new hire. 

I've attached a screenshot of an example task "form".  It isn't a form.  It's a grid of labels, dropdowns, and date-pickers. Currently, the "Completed With" entry pulls itself from a SharePoint list that is updated daily.  The tasks descriptions are labels, but I need to pull the text of those into an entry for the list.  My successful script for how I patch this info into the list can be seen below:

Patch('Onboarding Tasks',
Defaults('Onboarding Tasks'),
{'Team Member':{Value:TextInput8},
Objective:TextInput6.Text,
'Completed With':{Value:TextInput8_1},
'Date Completed': DatePicker2_22.SelectedDate
}
);

It repeats for every single task, meaning 25 iterations of this script just for this one checklist.  I would also like to point out that I tried to duplicate this method in another section, and it didn't work, which tells me that this isn't actually viable.

"TextInput6" in this context is actually an invisible text box adjacent to the label.  "TextInput8_1" is similar, but pulling the text of the dropdown value.  This is the spaghetti.  I know there's a better way, I just don't know what that is.

So, Question 1:  How do I make what I'm doing above more efficient?  It's going to be enormous if I finish it this way, and I also need to automatically send an email "report" to a manager/mentor when a week's tasks are complete. If there are efficiencies within the layout that will improve things, I'm all ears.  If there are scripts that will ease the pain, I'll consider whatever.

Question 2 is the flip of this, but has proven much more complicated to me. I don't have a good answer.  Within the context of the Tasks Checklists, I have an Onboarding Tasks SharePoint List (also attached) that contains the new hire Name, the Objective (task), the Date Completed, and who it was Completed With.  I've left the Title column unused and not required. I need the user to be able to open their computer, load up the app, and then the completed entries are reloaded in as easy a way as possible.  I've tried something dealing with the the Default property of the Dropdown being a lookup to the list, but I think two criteria (user and task) is too much.  This is my current script:

LookUp('Onboarding Tasks',Text(Objective)=Text(TextInput6),'Completed With')

But this ignores the user altogether. Also, it doesn't work.  It throws up a warning, which I've attached, as well.

So, if I have to start over, okay, I'll deal, but I mostly just need to know how best to build out these task pages so that they individual entries that can be stored in lists, then retrieved on open, for user reference.  It's the new hire's task journal, so it needs to be usable for them.  Sorry for being long-winded. 

Thank you so much for your time and energy with this,

Chris

[felipetesting]

test test test

[RandyHayes]

@CEChandler 

Yes, there is a lot of work that you are putting into this that is not needed.  Keep it Super Simple and PowerApps will be your friend.

Let's start with this...what is the Items property of your Gallery?  Or are you stating that you did NOT use a gallery, that everything I am seeing in that photo is all hand made?

[CEChandler]

@RandyHayes 

Yes, that's right, it's a custom grid of labels and inputs, not a gallery. As such, there isn't an Items property to determine, in this instance.

I would be happy to do it differently.

[RandyHayes · Accepted solution]

@CEChandler 

Yes, in general, you will want to do that much differently as you will be duplicating so much work over and over again, not to mention the maintainability of your app over time.

Let's start with this question:

The list of tasks - is that Always the same and hard-written currently into your app, or do you have a list of tasks that you can start with?  I would recommend that you have a list that contains all the tasks.

If your app starts with the concept of someone putting in all the tasks for a person into the actual recorded task list, that is fine.  If not, then I would recommend a separate list that contains all the standard tasks.  It would be a simple list that would really only need the Title column to be filled in with the name of the task.  Also of assistance might be an "order" column that would define the order in which the tasks need to be done. You might even expand on that list with things like "employee type" (to provide different sets of tasks for different employees).  There are a lot of options.

Then, with that list in place, you would get rid of all of the hand-made grid and replace it with a single gallery.  That gallery will get its records from the list mentioned above in combination with the destination list of recorded tasks.

This will make your experience MUCH simpler and easier to do.

[CEChandler]

@RandyHayes 

So, let me see if I understand correctly.  Following your suggestion, I should create a SharePoint List of Tasks, and I think I could also include a Week column to ID where they live (per your "order" suggestion).  If I do that, it sounds like I could create a gallery pulling in these tasks, filtered by the Week in question.  The Gallery would display the Task, and then the team member would record the date and the team member the person worked with to complete the task.

I will get started on that List and gallery today.  I will be back when I finish to ask about the Save/Load questions, which will then have different answers.

[RandyHayes]

@CEChandler 

Yes!  That would be a good start.  Ideally, you don't want to have a lot of hard-written information in your app to start with.  You want to be able to have flexibility by simply changing the data and not having to go back into the designer of the app and re-do the app.  

A list with the tasks and weeks is a good start and then the app can draw in that data and display it for interaction.

[CEChandler]

Hey @RandyHayes :

Okay, I've built out the Gallery, and an associated form, which grabs the Objective selected in the gallery, then the user fills out a few fields, and then submits the Objective on a button click, which resets the form to be ready to add a new row to the SharePoint list.  It works well.  This has saved hours of duct-taping.  In order for a user to be able to see the "archive" of their submitted tasks, I think all I'll have to do is just create another screen with a data table of the SharePoint list in question.  So, that answers most of my original questions.  Of course, others have cropped up. 

While building the gallery, I followed your advice regarding how to create alternating-color Gallery rows (yay YouTube!) and it worked a treat.  The next thing I need to do is to be able to filter that list by the specific week of tasks objectives, which can be anywhere from 9-26 tasks.  This would be cake if for one roadblock.

The user will need to filter the Gallery by Week.  I have a Dropdown to select a single week.  The dropdown is connected to a SharePoint List column, in which an individual item can have multiple entries (some task objectives occur during multiple weeks).  I'm really not sure how best to approach this, though I thought I'd come across a solution.  It's not perfect.  What I found was a method of grouping gallery items, then hiding the group if it doesn't meet the criteria.

In order to hide a Gallery row in a Flexible Height gallery I grouped my Gallery Items, and used this code to hide the text.  In the Visible property of the group I have:

Dropdown5.Selected.Name in ThisItem.'Task Week'. Value

Where Dropdown5 is the input I'm using for filtering, and 'Task Week' is the column that can have multiple entries.  

What is happening is that, even though this is a flexible height gallery, and I think it technically does "hide" the irrelevant gallery rows, there are still gaps and inconsistencies, blanks, and the order is weird. I know I'm going to get push back if it looks weird. 

I'm not married to this "hide" method of filtering, but I do have a need to filter based on week.  I suppose I could break out those multi-entry items and lay out the duplicate rows in the SharePoint List, but for each week, so that I don't have multi-entry items, but that would really increase the size of the List, and I'm trying to lean toward best practices and keeping my data footprint small.  Do you have a method for filtering a Gallery based on a dropdown selection, where the SharePoint list column is a multi-entry type?  I'd really appreciate it.

Thanks so much!

-Chris

[CEChandler]

Okay @RandyHayes , so I went ahead and filled out the SharePoint list with the extra rows, to simplify the app.  The filter works, and I think everything is good to go.  Thank you for the excellent advice!