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.

[StalinPonnusamy]

@Ryan_B Are you trying to Patch multiple selected items as choice? If yes please use this syntax

Patch(
   'AMBER Test',
    Defaults('AMBER Test'),
   {
      'Affected Servers': 'System Collection'.AllItems     
   }
)

Here is my test one

MyChoices: DataCardValueMyChoices.SelectedItems.Value

[Ryan_B]

@StalinPonnusamy 

Maybe? I'm looking to make these values new choices on the SharePoint list.

So if I've got Choice 1, Choice 2, Choice 3 already as choices on the SharePoint list, in the gallery I've added Choice 4, Choice 5, Choice 6 and hit submit. The SharePoint list and my combo box of said choices should now have Choice 1-6 in it.

If that's what your above code is going to accomplish, then yes that's what I need. After trying to implement your code, I'm not really sure where I'm getting the ID from. For the Value I've got ThisRecord.'System Name', is this correct?

[RandyHayes]

@Ryan_B 

From your description, you are not dealing with Lookup columns, you are dealing with Choice columns.

Please consider changing your entire Formula to the following:

Collect('AMBER Test',
    {
        'Downtime Type': {Value: 'Type Input'.Text},
        'Downtime Start': {Value: 'Start Input'.Text},
        'Downtime End': {Value: 'End Input'.Text},
        'Affected Servers': {Value: ForAll('Server Collection'.AllItems, {Value: 'Server Name'} ) }
        'Affected Systems': {Value: ForAll('System Collection'.AllItems, {Value: 'System Name'} ) }
    }
)

The assumptions in the above are the following"

'Downtime Type' is a single choice column with defined choice values consistent with anything to be entered into Type Input
'Downtime Start' is a single choice column with defined choice values consistent with anything to be entered into Start Input
'Downtime End' is a single choice column with defined choice values consistent with anything to be entered into End Input
'Affected Servers' is a multiple choice column with defined choice values consistent with anything to be found from the 'System Collection' gallery items Server Name column value
'Affected Systems' is a multiple choice column with defined choice values consistent with anything to be found from the 'System Collection' gallery items System Name column value.

I hope this is helpful for you.

[Ryan_B]

@RandyHayes 

You are correct Randy, these are Choice columns. All of your assumptions are also correct.

When trying your code (with the comma missing after the Affected Servers line), it states:

Incompatible type. The 'Affected Systems' column in the data source you're updating expects a 'Table' type and you're using a 'Record' type.
Incompatible type. The 'Affected Servers' column in the data source you're updating expects a 'Table' type and you're using a 'Record' type.

[RandyHayes]

@Ryan_B 

oops sorry about the missing comma and also I see I had a function backward in the syntax - sure wish that the forum had a PowerApps editor built in to identify syntax issues 🤔

Formula should be:

Collect('AMBER Test',
    {
        'Downtime Type': {Value: 'Type Input'.Text},
        'Downtime Start': {Value: 'Start Input'.Text},
        'Downtime End': {Value: 'End Input'.Text},
        'Affected Servers': ForAll('Server Collection'.AllItems, {Value: 'Server Name'} ), 
        'Affected Systems': ForAll('System Collection'.AllItems, {Value: 'System Name'} ) 
    }
)

[Ryan_B]

@RandyHayes 

So all that actually worked once I wrapped the Value in Text(), so the new code is:

Collect(
    'AMBER Test',
    {
        'Downtime Type': {Value: 'Type Input'.Text},
        'Downtime Start': {Value: 'Start Input'.Text},
        'Downtime End': {Value: 'End Input'.Text},
        'Affected Servers': ForAll(
            'Server Collection'.AllItems,
            {Value: Text('Server Name')}
        ),
        'Affected Systems': ForAll(
            'System Collection'.AllItems,
            {Value: Text('System Name')}
        )
    }
);

But all this did was actually add the values as an entry, not to the actual choice field itself. Also it didn't actually add the Downtime fields for some reason. Here's some screenshots of what happened and what I was looking to happen:

Is it even possible to add these values as actual Choices from Power Apps? This way these new values could then be used within a form on the previous screen.

[RandyHayes]

@Ryan_B 

You do not have the "Can add values manually" option turned on, so without that on, no you cannot add new items to the list.

I would highly recommend NOT using a choice column for this as you will overcomplicate your app and cause yourself a lot of extra work for maintaining the choice column all the time.

Also, what is "system name" and "server name" in the context of your Gallery?  Is that a control or a data column?  Adding text around a value that should already by text is not right, so something is missing there in my understanding of what you are pulling from the gallery.

[Ryan_B]

@RandyHayes 

I didn't realize that was even an option for columns. I wonder if that will allow me to achieve what I'd actually like to achieve then.

I'm not entirely sure what "Server Name" and "System Name" are in this context. They are being populated into the gallery (Server Collection and System Collection) from a text input field (Server Name and System Name) that creates a collection (ServerCollection and SystemCollection), which the gallery's Items property is set to that collection. As for not using a choice column, what would you recommend instead? Below is the concept of my app.

Basically my app creates an email template dynamically based on a series of combo boxes. These combo boxes could potentially have quite a lot of values in them based on user needs. I was going to pre-populate the choice column with as many of the values I know as possible and allow users to add in new ones if they are either created (such as servers and systems) or not already part of the list.

As the user selects values from the form on one side of the screen, it fills in a preview of the email on the other side. But if the option they need isn't listed there, I was hoping that the input could just instead use the SearchText value, but I couldn't get that to work. So I moved on to a whole new screen with inputs that would allow them to enter new values that would then be populated into the form on the previous screen.

[RandyHayes]

@Ryan_B 

I'd reduce the amount of collections (to 0 if possible) to avoid the confusion and added performance hit to your app.

What though, is the foundation of the collection used by the Gallery?

As for not using choice, I would utilize either a separate list if the amounts of items will be large, or simply have a separate list of system names and server names and then use that from your selections in your app to populate a semicolon (or other) separated list of values in your primary list.

Based on what you mention your app is doing, this should work just fine.  

As for the concept of allowing the user to then just type in a value and if not there in the search, add it - this can all be easily achieved.  If the user is (for example) putting in a Server Name that is not present, then this can be added to the list of servers and systems and then be available for your email as well.