cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhuyser
Frequent Visitor

Failed Using HTTP request to create or modify Boolean field with CustomFormatter

I am trying to use a powerautomate flow to create or update a Boolean field in a SharePoint document library with custom formatting. I've tried multiple configurations using XML and JSON with no luck.

 

My first attempt was using a SiteColumn and adding to the SharePoint library per the procedures in Ludovic Perrichon's website. The SchemaXml was consistently thrown back as a JSON error. I then tried using the solution in this PowerAutomate forum post by copying the SchemaXml from the SiteColumn and inserting directly into the Body of the HTTP request, along with a modification to the Header. A key difference between my attempt and the forum post is that I'm trying to use a Boolean field while the post is using a Choice field. This configuration didn't generate any errors but the flow timed out after 15 minutes trying to run this connector.

 

Image1.JPG

 

Abandoning the XML, I tried to add the Boolean field using just the JSON first by referencing the ParseJSON from getting the SiteColumn and second by just adding the JSON script directly in the Body. This was copied from the "Not using Schema xml : Text" section in Ludovic Perrichon's page as well as this page on Michal Guzowski's site. This process failed to work at all and consistently threw up an invalid JSON error or a SP.Field error.

image2.JPG

 

My third attempt was to create the Boolean field and then edit it by populating the CustomFormatter property. The create Boolean field worked just fine using a Uri and Header identical to the previous image and then using only the following Body:

 

 

 

 

{ '__metadata': {'type': 'SP.Field'},
'FieldTypeKind': 8,
'Title':'[Redacted]'
}

 

 

 

 

 

Updating the field's CustomFormatter property did not work. I copied the Uri and Headers from the "Update Field into SharePoint List" section in Ludovic Perrichon's page while I copied the Body from a co-worker's flow who said that it worked perfectly for updating a choice field. The co-worker's flow used SP.FieldChoice and 'FieldTypeKind':6 so I changed those to SP.Field and 'FieldTypeKind':8 but the error I get is a JSON Object error in that it doesn't like the apostrophe in the CustomFormatter value but then it doesn't recognize it as a valid value when I remove the apostrophes.

 image3.JPG

 

Below is the custom format that I'm trying to apply:

 

 

 

 

{"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json","elmType":"div","style":{"display":"flex","width":"100%","height":"100%","align-items":"center"},"children":[{"elmType":"div","style":{"display":"flex","width":"60px","height":"30px","border-radius":"15px","align-items":"center","flex-direction":"row","justify-content":"=if(@currentField , 'flex-end' , 'flex-start')","cursor":"pointer"},"customRowAction":{"action":"setValue","actionInput":{"[Redacted]":"=if(@currentField , '0' , '1' )"}},"attributes":{"class":"=if(@currentField , 'ms-bgColor-themePrimary' , 'ms-bgColor-neutralTertiaryAlt')"},"children":[{"elmType":"div","style":{"width":"18px","height":"18px","margin-left":"6px","margin-right":"6px","border-radius":"50%"},"attributes":{"class":"ms-bgColor-white"}}]}]}

 

 

Edit 3/28/2022: I failed to give credit to the author of the column format that I'm using. This came from PnP.GitHub.io and is the yesno-toggle-format created by Tetsuya Kawahara

 

1 ACCEPTED SOLUTION

Accepted Solutions
mhuyser
Frequent Visitor

I finally got the right configuration. Here's the setup that works:

Capture.JPG

 

The body is pasted below. There are two locations for the field name which have been temporarily replaced with [ENTER FIELD NAME HERE]

{
"__metadata": { "type": "SP.Field"},
"FieldTypeKind": 8 ,
"Title":"[ENTER FIELD NAME HERE]",
"CustomFormatter": "{
  \"$schema\": \"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json\",
  \"elmType\": \"div\",
  \"style\": {
    \"display\": \"flex\",
    \"width\": \"100%\",
    \"height\": \"100%\",
    \"align-items\": \"center\"
  },
  \"children\": [
    {
      \"elmType\": \"div\",
      \"style\": {
        \"display\": \"flex\",
        \"width\": \"60px\",
        \"height\": \"30px\",
        \"border-radius\": \"15px\",
        \"align-items\": \"center\",
        \"flex-direction\": \"row\",
        \"justify-content\": \"=if(@currentField , 'flex-end' , 'flex-start')\",
        \"cursor\": \"pointer\"
      },
      \"customRowAction\": {
        \"action\": \"setValue\",
        \"actionInput\": {
          \"[ENTER FIELD NAME HERE]\": \"=if(@currentField , '0' , '1' )\"
        }
      },
      \"attributes\": {
        \"class\": \"=if(@currentField , 'ms-bgColor-themePrimary' , 'ms-bgColor-neutralTertiaryAlt')\"
      },
      \"children\": [
        {
          \"elmType\": \"div\",
          \"style\": {
            \"width\": \"18px\",
            \"height\": \"18px\",
            \"margin-left\": \"6px\",
            \"margin-right\": \"6px\",
            \"border-radius\": \"50%\"
          },
          \"attributes\": {
            \"class\": \"ms-bgColor-white\"
          }
        }
      ]
    }
  ]
}"
}

 

View solution in original post

4 REPLIES 4
Expiscornovus
Super User
Super User

Hi @mhuyser,

 

I have tested a couple of things with a Boolean field.

 

Can you try the example below and see if that works for you? I know it is not the formatting which you are looking for, but just want to make sure some random formatting works 😁

 

This example creates a boolean field with background colour formatting.

 

 

{"parameters":{"__metadata":{"type":"SP.XmlSchemaFieldCreationInformation"},"SchemaXml":"<Field DisplayName='@{variables('FieldName')}' Format='Dropdown' IsModern='TRUE' Name='@{variables('FieldName')}' Title='@{variables('FieldName')}' Type='Boolean' CustomFormatter='{\"$schema\":\"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json\",\"elmType\":\"div\",\"style\":{\"box-sizing\":\"border-box\",\"padding\":\"0 2px\"},\"attributes\":{\"class\":{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",true]},\"sp-css-backgroundColor-BgMintGreen\",{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",false]},\"sp-css-backgroundColor-BgCoral\",\"\"]}]}},\"children\":[{\"elmType\":\"span\",\"style\":{\"line-height\":\"16px\",\"height\":\"14px\"},\"attributes\":{\"iconName\":{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",true]},\"\",{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",false]},\"\",\"\"]}]},\"class\":{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",true]},\"\",{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",false]},\"\",\"\"]}]}}},{\"elmType\":\"span\",\"style\":{\"overflow\":\"hidden\",\"text-overflow\":\"ellipsis\",\"padding\":\"0 3px\"},\"txtContent\":\"@currentField.displayValue\",\"attributes\":{\"class\":{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",true]},\"sp-field-fontSizeSmall sp-css-color-MintGreenFont\",{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"@currentField\",false]},\"sp-field-fontSizeSmall sp-css-color-CoralFont\",\"\"]}]}}}]}'><Default>1</Default></Field>","Options":12}}

 

 

 

escapequotesinxml.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Thanks for the reply @Expiscornovus 

I tried your example boolean field and it worked. So apparently there really is something wrong with the particular format that I am tried to load. It works on it's own when pasted into column formatting on a library or list, just not in the HTTP request connector.

 

I did a side-by-side comparison between the two and found a few elements from yours to add to mine and others from mine that were probably unnecessary to remove. When I tried again I got the same "Not well formatted JSON stream" error.

 

Here's the new code that I entered:

 

 

 

{"parameters":{"__metadata":{"type":"SP.XmlSchemaFieldCreationInformation"},"SchemaXml":"<Field DisplayName='[redacted]' IsModern=’TRUE’ Name='[redacted]' Title='[redacted]' Type='Boolean' CustomFormatter=‘{\"$schema\":\"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json\",\"elmType\":\"div\",\"style\":{\"display\":\"flex\",\"width\":\"100%\",\"height\":\"100%\",\"align-items\":\"center\"},\"children\":[{\"elmType\":\"div\",\"style\":{\"display\":\"flex\",\"width\":\"60px\",\"height\":\"30px\",\"border-radius\":\"15px\",\"align-items\":\"center\",\"flex-direction\":\"row\",\"justify-content\":\"=if(@currentField , 'flex-end' , 'flex-start')\",\"cursor\":\"pointer\"},\"customRowAction\":{\"action\":\"setValue\",\"actionInput\":{\"NotifyEPA\":\"=if(@currentField , '0' , '1' )\"}},\"attributes\":{\"class\":\"=if(@currentField , 'ms-bgColor-themePrimary' , 'ms-bgColor-neutralTertiaryAlt')\"},\"children\":[{\"elmType\":\"div\",\"style\":{\"width\":\"18px\",\"height\":\"18px\",\"margin-left\":\"6px\",\"margin-right\":\"6px\",\"border-radius\":\"50%\"},\"attributes\":{\"class\":\"ms-bgColor-white\"}}]}]}’><Default>0</Default></Field>",“Options”:12}}

 

 

 

http4.PNG

It looks like I'll either have to pick out a simpler format or get a developer to work out a better JSON to load the one I want.  Thanks for the help.

 

Also, I edited my original post to give credit and links to where I got the formatting from

 

Expiscornovus
Super User
Super User

Hi @mhuyser,

 

I think in a couple of places you used single and double typographic quotes instead of single and double quotation mark characters.

 

“ ” vs   " "

‘ ’  vs  ' '

 

As far as I can tell it is only in your last line of code.

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


mhuyser
Frequent Visitor

I finally got the right configuration. Here's the setup that works:

Capture.JPG

 

The body is pasted below. There are two locations for the field name which have been temporarily replaced with [ENTER FIELD NAME HERE]

{
"__metadata": { "type": "SP.Field"},
"FieldTypeKind": 8 ,
"Title":"[ENTER FIELD NAME HERE]",
"CustomFormatter": "{
  \"$schema\": \"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json\",
  \"elmType\": \"div\",
  \"style\": {
    \"display\": \"flex\",
    \"width\": \"100%\",
    \"height\": \"100%\",
    \"align-items\": \"center\"
  },
  \"children\": [
    {
      \"elmType\": \"div\",
      \"style\": {
        \"display\": \"flex\",
        \"width\": \"60px\",
        \"height\": \"30px\",
        \"border-radius\": \"15px\",
        \"align-items\": \"center\",
        \"flex-direction\": \"row\",
        \"justify-content\": \"=if(@currentField , 'flex-end' , 'flex-start')\",
        \"cursor\": \"pointer\"
      },
      \"customRowAction\": {
        \"action\": \"setValue\",
        \"actionInput\": {
          \"[ENTER FIELD NAME HERE]\": \"=if(@currentField , '0' , '1' )\"
        }
      },
      \"attributes\": {
        \"class\": \"=if(@currentField , 'ms-bgColor-themePrimary' , 'ms-bgColor-neutralTertiaryAlt')\"
      },
      \"children\": [
        {
          \"elmType\": \"div\",
          \"style\": {
            \"width\": \"18px\",
            \"height\": \"18px\",
            \"margin-left\": \"6px\",
            \"margin-right\": \"6px\",
            \"border-radius\": \"50%\"
          },
          \"attributes\": {
            \"class\": \"ms-bgColor-white\"
          }
        }
      ]
    }
  ]
}"
}

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (1,994)