Patching gallery items to a choice field in a SharePoint list

[Ryan_B]

I'm looking to patch a gallery of items into a choice field on a SharePoint list.

The control I'm using is a combo box with an "Add" button next to it. User types in some text, hits the add button, and it goes into the gallery. When the user is done adding items they hit the submit button which will patch it to the SharePoint list.

I'm binding these items to a collection and trying to use that collection to patch to the SharePoint list. Here's what I've got so far:

ForAll(
    'Server Collection'.AllItems,
    Collect(
        ServerCollection,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            'Affected Servers': Text(ThisRecord.'Server Name')
        }
    )
);
ForAll(
    'System Collection'.AllItems,
    Collect(
        SystemCollection,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            'Affected Systems': Text(ThisRecord.'System Name')
        }
    )
);
Patch(
    'AMBER Test',
    Defaults('AMBER Test'),
    {
        'Downtime Type': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Value: 'Type Input'.Text
        },
        'Downtime Start': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Value: 'Start Input'.Text
        },
        'Downtime End': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Value: 'End Input'.Text
        },
        'Affected Servers': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Value: ServerCollection
        },
        'Affected Systems': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Value: SystemCollection
        }
    }
)

The last two blocks "Affected Servers" and "Affected Systems" are the pieces that I'm referring to and can't get to work. I'm getting an error stating:

The type of this argument 'AffectedServer' does not match the expected type 'Table'. Found type 'Record'.
The type of this argument 'AffectedSystem' does not match the expected type 'Table'. Found type 'Record'.

 I'm not entirely sure why it's throwing this error as 'AffectedServer' and 'AffectedSystem' is part of the previous screen in a form that's being pulled from the SharePoint list, which I'm not referencing at all in this patch.

[Ryan_B]

@RandyHayes 

Honestly I don't entirely follow. Maybe images of the app will clarify: 

The Add New Values button sends you to this screen:

My original idea was to just have the first screenshot only. When they fill in the combo boxes it'll either find the value they're looking for, or it'll take the text they searched for and add it as a new value. But I didn't really understand how to accomplish that. I tried some solutions I found online but couldn't get them to work one way or another. Not to mention the right-side piece you see is an HTML Text box, which I would then need to find a way to place an If statement into that which uses either Selected.Value or SearchText.

That's when I moved on to what you see in the second screenshot. They add the values here then go back to the previous screen to use their new values.

The goal of the app isn't to add stuff to lists, that's just a necessity to get what the user wants.

[RandyHayes]

@Ryan_B 

Okay, thank you, a picture is worth a thousand words.  2 pictures is double!  

In general I see where you are going. And, there is no particular need to have a second screen for what you want - it can all be done on the first.

So some questions on this then.

- What was the concept of the Choice column to begin with?  The choices would imply a need to "store" information.  But as I am hearing it, there is really no need to store the information in another list as it will only be relevant to "this" record.  

In other words, it sounds like you are really looking for the user to just enter the names of the systems and servers.  The only value that an additional list would bring to the table is to allow the user to have, perhaps, pre-populated values to choose from.  If so, then this would still be an option and a separate list would be a preferred method.

- What is your scenario if a user selected some names in the combobox and then has another name that is not listed and they type it in.  I know you then want that to be part of the email, but the question is this...what if they have 2 or more names to add that are not listed?

- Are the server names and systems part of a global set of names for servers and systems, or are they related to something?  I am not sure if you are tracking server names and systems for internal devices or if this is client based.

Sorry for all the questions, just want to give you a great solution!

[Ryan_B]

@RandyHayes 

Since pictures worked so well, here's a GIF of how the app actually works to hopefully answer some of your questions:

To answer your questions if the GIF didn't:

• The choices were just a way to allow me to have a searchable combo box. I wanted to eliminate as much typing as possible for the user, so I figured the combo box would be perfect. I don't necessarily need to store the information on SharePoint for any purpose other than so that the app can use the values. If it would be better to say "hard code" it to the Items property and remove SharePoint entirely, I'm fine with that so long as it can also add new values to it with user input. But as you can see in the GIF, the email preview doesn't show text input. I'm not sure if I can do anything about that as I'm using an HtmlText input to populate the preview. I tried to just place an If statement in there but it wasn't being recognized, so maybe I have the syntax wrong considering the values being used look like this: "&ValueHere&"
• If they have two or more values to add, I would think that maybe they could hit Enter and the value would be added as if it were selected from the combo box. Or if that's not possible, have some sort of pop-up that would ask if they want to add that value to the table, they hit yes which then adds it, clears the input, and they select it from the drop down.
• The server and system names are just for internal devices. This app will only be used by IT personnel to send out company emails regarding things being down or unavailable for a specific reason. Right now they just manually type up an email for each instance - there's no consistency, template, or email design.

I appreciate you asking questions and spending time on this!

[Ryan_B · Accepted solution]

Update:

Solved: Powerapps add new value to choice column - Power Platform Community (microsoft.com)

Thanks to the above topic I've figured out how to basically do what I'm trying to do and have implemented it. It's working almost completely except in two places:

1. When I select multiple servers or systems from the combo box it will only take the latest value and duplicate it in the email preview. Since I implemented the new solution I had to change some of the syntax/formulas to get the new values to appear, but this one just wouldn't work as it previously did.

Previous code:

"&Concat(
    AffectedSystem.SelectedItems,   
    "&Value&"
)&"

New code:

"&Concat(
    AffectedSystem.SelectedItems,   
    "&AffectedSystem.Selected.Result&"
)&"

2. Adding new values to Affected Servers and Affected Systems works temporarily, but when I submit the form they get wiped and don't save to the SharePoint list. So they don't appear in the combo box like the other values.

Add button (successfully adds the option to the combo box to be selected):

Collect(ServerOptions, TextInput1.Text); Reset(TextInput1);

Combo box - Items property:

Distinct(ServerOptions, Value)

Affected Servers DataCard - Update property:

AffectedServer.SelectedItems

[Ryan_B · Accepted solution]

Update:

I resolved the problem with issue #1 above. Went back to my previous code and swapped out "&Value&" for "&Result&" and it works properly again. Came across another topic about Distinct and Sort needing to use Result instead of the actual value, and since everything else started using ".Result" I put two and two together.

[Ryan_B]

@RandyHayes 

Here is a GIF of my updated progress. As you can see I've got almost everything working the way I wanted it to. When I entered "Test 3" and submitted the form it was then there in the combo box to select from again. But both "Server 1" and "System 1" that I had entered were only available before I submitted it. The code for these pieces are below.

Add button (successfully adds the option to the combo box to be selected):

Collect(ServerOptions, TextInput1.Text); Reset(TextInput1);

Combo box - Items property:

Distinct(ServerOptions, Value)

Affected Servers DataCard - Update property:

AffectedServer.SelectedItems

I'm guessing the problem lies somewhere with the Update property as the value entered in the combo box (Test 3 in this case) has been added to the SharePoint list, thus why it's showing up in the combo box as a new value after submission. The columns are set to allow manually added values as well.

[RandyHayes · Accepted solution]

@Ryan_B 
The problem is that you are trying to use the SelectedItems property of the combobox to update the underlying Choices column.  These will NOT have the correct signature.

A multi-choice column wants a table of records with a Value column.  Your Items property is producing a table with a column called Result.

Change your Items property to:

RenameColumns(Distinct(ServerOptions, Value), "Result", "Value")

[Ryan_B]

@RandyHayes 

Looks like that resolved the final problem, awesome! Thank you very much Randy, this last piece has been driving me crazy trying to figure out.