power automate trigger conditions on modifying field

[SachinG31]

Hello All, i have a case where i want to trigger my flow "when an item is modified (V2)" SQL action , when the field "WORKFLOWSTATUS" modify as value 4.

I tried using this expression 

@equals(triggerOutputs()?['body/WORKFLOWSTATUS'],4)

after filed value become 4 and then there is any change in any field , flow trigger again since expressions checks for equal , but i want to trigger flow on modifying fled with a particular value 4 , Please advise  

2.

3.

[eliotcole]

Remove that last comma, @SachinG31 :-).

@equals(triggerOutputs()?['body/WORKFLOWSTATUS'],4)

That should do the trick.

[SachinG31]

Hello @eliotcole , thanks for looking into this , i dont have comma in my trigeer settings if you  will see the screen shot attached . in the text it pasted by misteak 😞

[eliotcole]

Ah, OK, my bad.

Would you mind editing your original question with a couple of things?

1. Change that formula 😅😉 ...
2. Paste a screenshot of the input when there is no Trigger Condition so that we can see what the input for the WORKFLOWSTATUS field looks like?

Just obfuscate (blank out) any sensitive information.

My suspicion is that the field that you are looking at is either:

• A string, so looking for the number value won't work.
  
  or
  
  
• A floating point number (4.0) which may not match either. This should still match if it is 4.0 based on a very simple test I just did.

If it's the string thing, then try this:

@equals(triggerOutputs()?['body/WORKFLOWSTATUS'],'4')

... or this:

@equals(triggerOutputs()?['body/WORKFLOWSTATUS'],'4.0')

or any number of zeros afterward.

[SachinG31]

Yeh Sure Buddy,

i have corrected the formula in question already 

the data type in SQL field WORKFLOWSTATUS is int

 

Required screen shot pasted here

i hope these details are enough

The issue is not with reading the condition. Flow is obaying the condition and does needful when status updated as 4.

but after status become 4, when changes made in any other column flow triggers again since it is satifying the trigger condition.

i need some formula which will applied on WORKFLOWSTATUS field modification , not on equal. 

[eliotcole]

That's great, @SachinG31, cheers. 👍

I'm not sure if I can help, but stick that image in to the original question just to help someone cleverer than me! 😅

[SachinG31]

Done as advised , appriciate your time 🙂

[v-yujincui-msft]

@SachinG31 ,

Maybe you could try to add another status column in your data source.

Then Change the Trigger Conditions as follows:

@and(equals(triggerOutputs()?['body/WORKFLOWSTATUS'],4),equals(triggerOutputs()?['body/OffOn'],'On'))

If the flow is triggered, you could try to change the value of this column to Off. The Flow should not be triggered the next time you change other columns of this record.

In addition, here is a link that might help you:

https://tomriha.com/how-to-trigger-power-automate-flow-on-specific-sharepoint-column-update/

Best Regards,
Charlie Choi

[SachinG31]

Hello @v-yujincui-msft , Thanks for the advise. i am applying this solution but i am getting error in update row action.

I am using a view and Row ID is a column RECID, which is unique and primary key. 

Please advise 

[v-yujincui-msft]

Hi @SachinG31 ,

Maybe you could try to create a new connection and try it again.

Best Regards,
Charlie Choi

[eliotcole]

I have an idea, @SachinG31, since your conversation with @v-yujincui-msft you mentioned that you are able to add columns to the SQL.

If SQL is able to recognise versioning of records, then you could use a computed column to add a copy of the previous version of the column to this one. So oldNew ... 34, or 44, or 45.

( you may be able to tell, I'm StartPage-ing all my SQL knowledge right now! )

Waitaminute. This is simple, 's answer should work for you, but if you get the trigger correct on this flow, then you should be OK.

Prep

So, up front do a bit of preparation which involves adding 2(!) columns to the SQL:

1. oldWrkFlwStatus - An integer field to store the previous value.
   
   
2. Now copy the ENTIRE db's records' values of WORKFLOWSTATUS into the new oldWrkFlwStatus column.
   
   
3. wrkFlwStChanged - Computed column which returns YES or true if WORKFLOWSTATUS and oldWrkFlwStatus do NOT match.
   So this should currently reading NO or false for all records.

So what you've done here, is instead of changing the change monitoring column with flow,  instead the database is monitoring the change. This means that when any flow is triggered it can check this computed column.

The Flow

Everything here should just be able to be pasted in to the relevant input field, if you have issues, reply with screenshots of the flow before it is run:

1. Add a trigger condition:
   

   @equals(triggerOutputs()?['body/wrkFlwStChanged'],TRUEVALUE)

   Ensure TRUEVALUE is entered correctly (see notes after this list).
   
   
2. The FIRST action in the flow is a Switch action, here's a rough idea of what that will look like with the On field containing the WORKFLOWSTATUS value:
   

   @{triggerOutputs()?['body/WORKFLOWSTATUS']}

   
   
   1. In the first Case, enter 4 as the equals, then add a Update row (V2) action to modify the oldWrkFlwStatus to @{triggerOutputs()?['body/WORKFLOWSTATUS']}
   2. In the Default case add 2 actions:
      1. Update row (V2) action first to modify the oldWrkFlwStatus to @{triggerOutputs()?['body/WORKFLOWSTATUS']}
      2. Terminate action set to 'Success'

Remember that I've used TRUEVALUE as a placeholder in the Trigger Condition, here. It will need to be whatever your computed column outputs when there's a change. For example if that is a string value of YES, you would place 'YES' there (quotes included).

Now the rest of your flow can continue as per normal, and you can add your email action that you wanted to add. It will only fire on Status = 4, and this flow will double up as a way to action other potential Status changes in the future.

That *computed* column is the key, here, though.

[SachinG31]

Hello @v-yujincui-msft , I tried creating connection multiple times . still the error is same. This is On premise SQL server , do we have some limitation with that?

[SachinG31]

Hello @eliotcole , Thank you so much for all the efforts and providing such a detailed solution. I am sure this will definitely work , but in my case i am unable to Apply this because of these limitations.

1.i can not ad new column in table , since the table is from D365 F&O ERP, and its not recommended to do any change on SQL level. That's why i was re using  a column "APPROVRLDATETIMEZID", As a update flag.

2.i dont know but update row V2 action is not working in my flow , it shows primary key error (Explained above) , even if there is primary key 

[eliotcole]

Well, that's a shame, @SachinG31, as you may have guessed, I'm not too experienced with SQL, so this is possibly where my help could end.

One possibility that I have for you is:

How big is the SQL DB/table?
and

How much 'new' traffic does it get?

Because if it is small, and relatively low traffic, then the limits on SharePoint lists are HUGE these days, and you could simply make a duplicate list in SharePoint to handle the information that you want to handle. I'm not going to go into THAT (because it would be too complex for right now) but feasibly you could mirror new/modified values to SharePoint, then make the columns that you need (as per my SQL suggestion, previously) then run a 'new/modified' on the SharePoint list.

Could that be an option?

[SachinG31]

Hello @eliotcole , Sorry for late revert. I have acheived this as workaround. i added a Share point create item action when i am storing my form number and i marked form number as unique field. 

so ay first attempt of approval it run and store data in SP list , on second attempt it gets fail as the formNumber column can not be duplicated. 

So far it is working fine as per my need. thats how flow looks now