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 II
Super User II

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 II
Super User II

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 II
Super User II

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 II
Super User II

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
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (71,627)