Collections, Forms, and Workflows

[dungar]

I've got a workflow that goes something like this:

1. Choose Location and Date, this is then validated.

2. Enter employees

3 . Enter information for each employee

4.  Enter info for that location, about that day.

5. Data is validated again and submitted to the DB.

All of this then gets submitted to a database table, with a record for each employee that was entered (the data for the day is duplicated and in an ideal world it wouldn't, but that i know how to fix.)  Of note users should NEVER see underlying data from the table itself.

This issue I keep running into is how best to handle this workflow.  Right now 1/2 are easy.  I just have them enter this data and store it in a collection that mirrors the database table exactly (even though at this point most of it is just empty/0/whatever).

From there I keep running into issues. 

What i'd like to do is make a gallery of the employees from the collection (that I know how to do) and then have a form pointing at the table I want to submit to (easy), that then uses that galleries selection to populate for edit mode (i assume impossible).

So something like:

colEmployees Names displayed in Gallery

Gallery.Selected Displayed in Form

Form tied to underlying table.

Unfortunately no matter how perfectly I make the collection match the table, this just does not work, and I get a vague "Expected value compatible with data source" error that I've not been able to get around.

So instead what I've done is make the form point to the collection, with the goal of submitting to update the collection, and then patching that to the db but this causes all sorts of obnoxious issues.

1. You cannot quickly populate fields from a form.  This is a 40+ column table.  I've made all the boxes I need but that seems needlessly awful given how you can normally just pull a field from a datasource.

2. I cannot do basic things like display the total entered for the employee, next to their name, as it'll cause some sort of circular dependency error

3. I have often liked that Forms have the OnSuccess trigger for various use cases.  This will need to be replicated with the db patch(or i suppose i can just do an identical invisible form pointed at the db and populated with the collection, but that seems extreme).

All of this feels like I'm missing some key tool in my kit, or some small piece of knowledge that will make the whole process much easier.  Unfortunately with powerapps i'm pretty used to the answer being "yeah that would be nice....", so i'm hoping I'm missing something?   I've dug through documentation and various other resources for about a week now and keep coming up on dead ends.

[WarrenBelz]

Hi @dungar ,

You did not post your data source, so I will assume SharePoint for the moment. What is the Collect code for colEmployees ?

[dungar]

The datasource, as mentioned, is a database table (call it EntryTable to make this easy). 

The collect code is just mocking up EntryTable as a collection.

So essentially:

Collect{colEmployees, 
{  EmployeeName: <name they entered>,
   EmployeeID: <id they entered>,
   Date: <Date they entered>,
   Location: <Location they entered>,
   EmployeeValue1: 0,
   EmployeeValue2: 0,
   ...
   DayValue1:0,
   DayValue2:0
}

 I've done my best to match the EntryTable from the database 1 to 1, case specific.  I don't think powerapps lets you type the "columns" of a record (int/string/etc) so I can't match that up to my knowledge.

I believe I've also tried adjusting that collect to, once it's done, wrap the whole thing in []'s to make it a table, but I don't think that worked either.

Edit- I know my syntax in the code block there isn't perfect as I swapped my ( with a {, but I do have it correct in the actual app.

edit again - just hit me you might have meant what type of database so azure ms sql in that case. Premium connector

[WarrenBelz]

@dungar ,

The issue is that you cannot base a Form on a Collection record - it needs to refer to an actual data source.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[dungar]

Well:

1. It seems you absolutely can?  It just doesn't auto populate fields.

2. I've tried pointing it at a data source, and the issue becomes that I can no longer use the gallery linked to it to navigate, because the gallery is populated by the employee collection created earlier.

[WarrenBelz]

@dungar ,

It is an illusion (or sorts) - you can add the Data Source, but not any Data Cards. Forms are designed to read and write from an actual connected data source.

[dungar]

Right, ok, that's kinda what I thought. So what's the proper way to do a multi stage workflow that submits to a database table? 

[WarrenBelz]

@dungar ,

You would need to Patch each field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[dungar]

I understand that.  I'm asking what the proper tools for the user to fill out the fields are.

I need to be able to allow the user to enter employees, then allow them to select those employees and enter more data for each, and then enter it to the db.

So is there any point in using a form for this flow, or do I do it with collections and galleries?  This cannot be an uncommon workflow for powerapps, and I'm trying to get a better understanding of how other people are solving this issue.

[WarrenBelz · Accepted solution]

@dungar ,

The best approach is probably an editable gallery as a collection can be used as the Items of this.