cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryangoderre
New Member

trying to update a sharepoint list with workflow to a more readable value

 

i have a API GET request that populates my sharepoint list.  But some values are not so readable to our customer.  i want to come in after the fact with a workflow to update the values.  see example values below

 

{"type":"workspace_user","id":"6008"}

{"type":"workspace_user","id":"6009"}

 

I want to perform a update sharepoint list item, which would come in and say if column owner, contains 6008, replace string with Ryan. 

 

I however have been unable to create an expression that isnt invalid.  Do i need to initizlie variables and then insert the formula in the update sharepoint item tile in my work flow?  i tried that with the below formula and Owner as a initialized variable and it is still invalid.  

 

if(variables(Owner),contains'6008',)replace(variables(Owner),variables(Owner),'Ryan')

 

I may be completely off base here but any help is appreciated.  i attached a picture of my workflow current and the field i want to update

workflow.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @ryangoderre,

 

I would start with an initialize variable action and leave the value of that action empty.

 

And replace the initialize variable action in your apply to each loop by a set variable action and set that to the owner value like you were doing before.

 

So the order would be something like this

1. Initialize variable
2. Get Sharepoint List items
3. insert your name value mapping
4. parse json
5. Apply to each loop with the following actions
5a. Set variable action. Owner = Sharepoint value for owner
5b. Filter the array
5c. Update sharepoint list items with FriendlyName value for owner

 

View solution in original post

6 REPLIES 6
Expiscornovus
Super User
Super User

Hi @ryangoderre,

 

For one string value replacement you could use this expression:

if(equals(variables('Owner'), '6008'), 'Ryan', 'Not Ryan')

 

But my guess is that you don't want to replace just one value right? You want to have a solution for all the owners, correct?

 

Btw, is that Owner field a person column in SharePoint?

Thanks for the quick reply!

 

For the expression you pasted, is that if the value in the sharepoint list is equal to 6008?  would i need a contains function as well since the value in my list is {"type":"workspace_user","id":"6008"}?

 

And yes you are correct, i have over 30 different options in the Owner field that i would need to account for and change.  But they all have unique IDs, so 6008, 6009, 6010 etc.  So they look like this in my sharepoint list. The field is a single line of text type field.  

 

Capture.JPG

Expiscornovus
Super User
Super User

Hi @ryangoderre,

 

I thought so. In that case ignore the last expression.

 

You could try and use the following approach.

 

1. Add an compose action and define a mapping array for your 30 options.

[
  {
    "id": "6008",
    "FriendlyName": "Ryan"
  },
 {
    "id": "6009",
    "FriendlyName": "Pete"
  },
 {
    "id": "6010",
    "FriendlyName": "Jane"
  }
 ]

valuemapping.png

 

2. Add a parse json action. Use the output of the compose action as input and use the following schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "id": {
                "type": "string"
            },
            "FriendlyName": {
                "type": "string"
            }
        },
        "required": [
            "id",
            "FriendlyName"
        ]
    }
}

parsejson_valuemapping.png 

 

3. Add an Filter Array action within the Apply to each 3 loop after the set variable action. Filter the array based on your Owner variable.

filterarray_owner.png

4. You can use the following expression to get the first FriendlyName result from that Filtered Array output

body('Filter_array')?[0]['FriendlyName']

ok i think i understand.  I apologize if i didnt follow correctly.  

 

From the way i understand it i need to do the following actions:

 

1. Get Sharepoint List items

2. Initialize variable Owner = Sharepoint value for owner

3. insert your name value mapping

4. parse json

5. Filter the array

6. Update sharepoint list items?

 

I guess i dont understand how to order all of this to update the current column values with the friendly name?

Also in your example you are putting ID as 6008, but does that need to be the full string value? or just the ID part?  Ex. should it be what you have ID = 6008, or should it be ID = {"type":"workspace_user","id":"6008"}.

 

Here is what i have so far.  Looks like i get an error on the initialize variable because it is inside of apply to each, but it added that automatically?

 

I apologize if i making this way harder than it needs to be.  but i really do appreciate your help

 

1.JPG

 2.JPG

Expiscornovus
Super User
Super User

Hi @ryangoderre,

 

I would start with an initialize variable action and leave the value of that action empty.

 

And replace the initialize variable action in your apply to each loop by a set variable action and set that to the owner value like you were doing before.

 

So the order would be something like this

1. Initialize variable
2. Get Sharepoint List items
3. insert your name value mapping
4. parse json
5. Apply to each loop with the following actions
5a. Set variable action. Owner = Sharepoint value for owner
5b. Filter the array
5c. Update sharepoint list items with FriendlyName value for owner

 

View solution in original post

Wonderful work !!!!  you are absolutely a genius.  I really appreciate your help and quick replies on this!

 

Just an FYI i had to Compose name value mapping like the below.  but other than that it worked perfectly.  So once again i really appreciate the help!

 

[
{
"id": "{\"type\":\"workspace_user\",\"id\":\"6008\"}",
"FriendlyName": "County of San Diego"
},
{

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,881)