Saving unmapped form fields to the data source

[Baltner]

Greetings. I am fairly new to Power Apps, having spent many years developing business process apps using SharePoint and Nintex forms and workflows. One of the first most significant differences I've encountered between these two platforms is in persisting "unmapped" form data. (By "unmapped" I mean data in form controls that are not explicitly connected to a table or list column.) Nintex does this out-of-the-box by creating an XML snippet containing all the unmapped fields and their values and saving this to a special multi-line column in a SharePoint list called "FormData." I did not find this capability in Power Apps so I made up my own version of it (see below). However, although it works it is, admittedly, a hack and I was hoping that someone in this Community may have found a better way to do this, or that there is indeed a way to do it out-of-the-box but I may have missed it.

The  workaround that I came up with, in a nutshell, is this:

• Create a multi-line text field in the SharePoint list. I called it PA_FormData.
• Put all of the "unmapped" field controls and their labels into one or more custom data cards in the form that is being submitted. There can be as many of these fields as needed. Some of my forms have dozens of such fields, of many different types.
• Add a (hidden) button to the screen, outside of the form and set the OnSelect property of the button to do two things: a) create a collection containing all of the unmapped fields and their values. The names of the columns are arbitrary but will be referenced in a later step when the data is retrieved; and b) create a JSON string of the resulting single-row table. This string will be saved to SharePoint in a later step.
• Set the default values of all of the unmapped field controls to be the value extracted from the PA_FormData field, for example, for a text input field it might look like this: 

If(
    Form1.Mode = FormMode.New,
    Blank(),
    Text(First(Table(ParseJSON(PA_FormDataField.Text))).Value.TextInput1)
)​

where TestInput1 is the name I gave to the column for this field in the collection. This is how the field value is retrieved from the persisted JSON string. The formula will be slightly different for different data types, but the idea is the same.

Saving the data involves three steps executed by the "Submit" control:

• Selecting the hidden button that creates the JSON
• Submitting the form (saves the mapped fields to their respective list columns, including the prior PA_FormData, if any) 
• Using Patch to update the PA_FormData column with the contents of the JSON string created by selecting the button

In practice, I found that I had to add a short delay with a timer control to allow the button to finish updating the JSON, so the last two steps are actually executed in the OnTimerEnd formula of the timer control. Also, some conditional logic is needed for Patching new vs. existing records, but the idea is basically that the PA_FormData field is updated in a separate step.

The above is a condensed version of my "hack." I'm OK with it but it gets kind of ugly when you have tons of checkboxes, dropdown menus, radio buttons, etc., so I'm hoping that someone can suggest a better process. Thanks.

[Scott_Parker]

On the whole, if you want to store unstructured data in SharePoint without fiddling with add-ins and whatnot, this method seems reasonable to me.

As far as I know, Power Apps just isn't setup to handle unstructured data conveniently. It would be nice if there was a Form property that returned a record of all unmapped cards within the form, as well. But, I could see that it would go against the design philosophy of Power Apps. So, there is no magic bullet where you won't have the map the fields to a record yourself.

That said, a couple improvements:

In your "code" button, you should assign your JSON() text output to a variable. I used UpdateContext(). Set the PA_FormDataField's card.Update property to that variable. The have then run SubmitForm() on a subsequent line of code. This way you won't have to do a separate Patch(). Really just swapping the order you're doing it currently, but it lets you do a single write to SharePoint.

Earlier I specified to use UpdateContext(); I'm guessing you're using Collect() to prepare your unstructured data? I suspect this because you are having to use a timer to give time for the Collection to be created. You're having this issue because a Collection is treated more like an internal data source than a variable. If you use UpdateContext() (which creates a variable in the scope of Screen), it will act in a deterministic way where your data will be there when you expect it on the next line of code when you submit the form.

I tested this setup to confirm it works.

Good question, I appreciate it.

[Baltner]

Hey Scott:

Thanks for the thoughtful reply. I'm not sure what is meant by "design philosophy" that would prevent a solution like this from being provided without a lot of fuss and bother but I accept that it's not there so a custom solution is needed.

As to your comments on my approach:

• Yes, I do name the JSON variable. I used Set() for this but UpdateContext would work as well and that makes more sense as the scope is not global.
• I'm using Patch because the button that creates the JSON text lives outside the form and therefore I cannot update a form data card with the most current values. I suspect that your button is in a separate hidden data card within the form to allow this and that's a good suggestion too. I will try it. (I've found that you can get around this limitation by using an external button to select an internal button that updates/resets a form field but  that wouldn't be necessary with an internally triggered button).
• Yes, I'm using Collect() (ClearCollect(), actually) to store the values but that's only because I thought that was the only way to store many values at once (in an array). Your suggestion to use UpdateContext({}) instead is also interesting because, to date, I've only used that for scalar values. After reading your reply I see how it now could handle records, or even tables, just as well and if that means there would be no delay then I can just chain the formulas together in one step. Much better!

I will try your suggestions (already verified, which inspires confidence!). Your method for doing this does sound much better than mine so I'm glad I posted the question here. Thanks again.

One final thought...this is a useful approach for complicated forms that have many fields that need to be persisted so I can see lots of use cases for it....of course that suggests making it a component. But I'm not sure how I would do that given the need to store case-specific keys and values in the JSON.

[Scott_Parker]

By the design philosophy of Power Apps, I mean what Microsoft is trying to achieve with Power Apps. As a low-code/no-code app platform, unstructured data diverges from what a broad swath of the intended audience would be familiar with. Defining all fields in the data source is in line with keeping things simple and harder for the developer to mess-up.

---

@Baltner wrote:

• I'm using Patch because the button that creates the JSON text lives outside the form and therefore I cannot update a form data card with the most current values.

---

Double-check this, this isn't my experience. There shouldn't be any problem with referring to a variable from within a form (or on the Form control itself)

Ah, components, so much potential, but also annoying limitations. In particular, they don't work with forms. You can't have components inside of a form and you can't have forms inside of components. You could potentially create something based on a gallery so that you can vary the number of fields. I tried to do something similar and the problem I encountered is that Power Apps requires that field names are string literals, i.e. hard-coded in the editor. Field names can't be variables. You couldn't save the fields properly together as a record, but as individual records in a table. Something like:

ForAll(Gallery1.AllItems({Name: NameControl.Text, Value: ValueControl.Text}))

 Which went against my sense of aesthetics, lol. So, I can't recommend trying to generalize too much in Power Apps because of the limitations of the software, but if you get something working I would be curious about it!