SharePoint List Update column

[powersportcp]

I'm not entirely sure this is possible, but maybe with a helper column?

My SP list has 2 columns:

Current User

Previous User

I was looking for a PA flow that, when the Current User is updated, puts the user that has just been overridden from the Current User column, into the Previous User column.

Ie, every time the Current User is update, the Previous User column shows the name that was previously the Current User.

Issue is (I can't work out how to build the flow'), but also, once the Current User is modified, the name that was deleted is no longer there to be 'got' and copied into the Previous User column.

Is this possible?

Do i need a third column so that the flow goes:

When Current User is modified, copy Helper Column to Previous User, then copy Current User to Helper Column.

So in effect the Helper Column is a duplicate of Current User that doesn't get updated until after the Previous User column is updated??

All advice welcomed.

THANK YOU 

[abouchaalaBAIT]

Solution 1 

Yes, Current user field must duplicated in a temp field (what you called Helper).

So we will have 3 columns in SharePoint : 

• Current user : X
• Temp user : X 
• Previous user : Y

When current user changes : 

• Current user : Z
• Temp user : X 
• Previous user : Y

=> Temp user value goes to Previous user and Current user value goes to Temp user

• Current user : Z
• Temp user : Z 
• Previous user : X

Next will be : 

• Current user : W
• Temp user : W
• Previous user : Z

Solution 2 

Use Rest API to get item version and update the item without the needs of a third column, here is the reference : Get a previous version of a SharePoint list record - OneDrive dev center | Microsoft Learn

----------------------------------------------------------
If I have answered your question, please mark your post as Solved. 🆗✔️
If you like my response, please give it a Thumbs Up. 👍
You can accept more than one post as a solution.

[grantjenkins · Accepted solution]

Below is how I would do it using just the two columns. Note that this assumes you are using major versions on your list which is normally the default setting. It also handles not getting into an infinite loop given you are updating the item in the flow which would trigger the flow again and again.

Below is the list I'm using for this example. Note that the internal column name for Current User is CurrentUser and Previous User is PreviousUser, and the name of the list is Keep Previous User.

Below is the full flow. I'll go into each of the actions.

When an item or file is modified will only trigger when an existing item has been modified - not when the item is initially created which is perfect since we don't have a previous user when we first create the item.

Send an HTTP request to SharePoint retrieves the previous version of the item that was modified. Note that versions behind the scenes go up in increments of 512 - so version 1 would be 512, version 2 would be 1024, etc. Below is the expression used in the Uri of the HTTP request. I'm only returning the previous Current User as this is all we need.

//Keep Previous User is the name of my list
//CurrentUser is the internal name of my Current User column

_api/web/lists/GetByTitle('Keep Previous User')/items(@{triggerOutputs()?['body/ID']})/Versions(@{sub(mul(int(triggerOutputs()?['body/{VersionNumber}']),512),512)})?$select=CurrentUser

//Headers
Accept
application/json;odata=nometadata

Condition ensures we only update the item if the Current User has changed so we don't get into an infinite loop. Below are the expressions used for both sides of the condition.

//Current User Email from the trigger
@triggerOutputs()?['body/CurrentUser/Email']

//Current User Email from the previous version
@body('Send_an_HTTP_request_to_SharePoint')?['body']?['CurrentUser/Email']

If the Current User Email is different (has been changed since the last version) then we update the Previous User with the previous versions Current User. ID is from the initial Trigger and Previous User Claims uses the following expression.

@outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['CurrentUser/Email']

[powersportcp]

Grant, thank you so much. Working this through was a great little challenge and something I really enjoyed. thank you for helping me expand my knowledge. After a bit of fiddling it works exactly as desired! 

[powersportcp]

That said, I do appear to be in some form of reoccurring loop.
Current user moves to Previous User.
Then the flow runs again and Current User copy's to become Previous User too. So essentially both users become the same. 
Actually the flow us running twice every minute on a continual run, presumably just continuing to copy the name, spotting the change and copying it again and again and again.
I've checked everything according to your steps. Any idea what might be causing it? Thanks 

[powersportcp]

THought I'd start again and rebuild in the hope of cracking the error.

Now however, I'm getting the following error message from the HTTP step in the flow:

Unable to process template language expressions in action 'Send_an_HTTP_request_to_SharePoint' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[powersportcp]

Fixed this, but back to the issue of it running continually and copying current user to previous user and running repeatedly when a change was made. Humm. Must be to do with the Condition?

[grantjenkins]

It should work the way I set it up - just tested again and working successfully. The flow will always trigger twice since it need to check the previous change. However, the second time it runs, the condition should evaluate to false and the flow would then stop not triggering another one.

Below is the second time the flow runs where it doesn't update the item again.

Can you show what you have for each of your actions, and also confirm that your Condition is set to is not equal to?

[powersportcp]

Hiya, Ah, yes that's helpful.
SO, I am getting a TRUE result every time, so it just keeps circling.

I can confirm it is set to IS NOT EQUAL TO and my expressions look the same.
Screenshot:

I can't work out how to expand the expressions to demonstrate what the code is. Is there a way to do that? Sorry for ignorance.

[grantjenkins]

Can you check to make sure you have Major versions turned on for your list?

[powersportcp]

Oh, interesting thought.
Not sure I have 'Major Versions' as an option in List Settings
But this is set to yes. = Create a version each time you edit an item in this list? = YES

[grantjenkins]

If you can expand out each of the actions and take a screenshot that would help. For the expressions, you should be able to click on each one and they copy it and paste in here. Might be worth checking the internal names of your columns to ensure they match up correctly with what you've used in the flow.

[powersportcp]

Thanks so much for looking at it for me.

_api/web/lists/GetByTitle('Sports Equipment Log')/items(@{triggerOutputs()?['body/ID']})/Versions(@{sub(mul(int(triggerOutputs()?['body/{VersionNumber}']),512),512)})?$select=CurrentUser

Field=CurrentUser

Field=PreviousUser

Field=Item

Field=Title

Code for the Condition is:

  "type": "If",

  "expression": {

    "not": {

      "equals": [

        "@triggerOutputs()?['body/CurrentUser/Email']",

        "@body('Send_an_HTTP_request_to_SharePoint')?['body']?['CurrentUser/Email']"

      ]

    }

  },

  "actions": {

    "Update_item": {

      "type": "OpenApiConnection",

      "inputs": {

        "parameters": {

          "dataset": "https://sportilyuk.sharepoint.com",

          "table": "76b67d2f-903c-49a5-97c6-efc995177de7",

          "id": "@triggerOutputs()?['body/ID']",

          "item/Item": "@triggerOutputs()?['body/Item']",

          "item/PreviousUser/Claims": "@outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['CurrentUser/Email']"

        },

        "host": {

          "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",

          "connection": "shared_sharepointonline",

          "operationId": "PatchItem"

        }

      },

      "metadata": {

        "operationMetadataId": "977bdfae-a112-4cb3-97a4-473a0fcb2c2f"

      }

    }

  },

  "else": {

    "actions": {}

  },

  "runAfter": {

    "Send_an_HTTP_request_to_SharePoint": [

      "Succeeded"

    ]

  },

  "metadata": {

    "operationMetadataId": "2cc9facd-13eb-4b1e-96d9-8f62a70fc70b"

  }

}

[grantjenkins]

I can't see anything wrong with your flow setup.

Are you able to go back into one of your flow runs and look at the output you got from the trigger and from the HTTP action for the Current User value - for some reason it's still seeing them as different on the second flow run which is really strange.

[powersportcp]

Oh I'm glad it doesn't look horribly incorrect!!

Looks like it only runs once actually, but then repeats every minute or so.

Step 1 > When an item or file is modified.
The Output is blank and just says click to download

Step 2: HTTP request:

It picked up 'CurrentUser = Nathan'

Condition was TRUE

Update item

input

'Previous User Claims'

NATHAN

output

'Current User Claims'

ADAM

'Previous User Claims' 

NATHAN

Next minute it runs again

It picked up 'CurrentUser lookup value= Adam'

Condition = true

'Currnt User Claims'

ADAM

'Previous User Claims'

ADAM

Repeat.

HTTP Output is

{

  "CurrentUser": {

    "LookupId": 2283,

    "LookupValue": "NAME",

    "Email": "EMAIL"

  }

}