Use individual results from SQl query

[JoeColvile]

Hi all,

I have looked everywhere and can't find the answer to what I assume is something very basic that I am missing.

I have an sql query that runs in my flow, it pulls back 5 rows only. I want to use the outputs from this query in a post on teams. Which I believe would be best done through a variable. How would I initialise a variable for effectively each combination of message type and each other column? 

For example: 

SQL query results:

[
  {
    "MessageType": "Adjustment",
    "ErrorMessages": 0,
    "ErrorMessageLines": 0,
    "ReceivedLines": 2,
    "ReceivedMessages": 2
  },
  {
    "MessageType": "LoadReceipt",
    "ErrorMessages": 0,
    "ErrorMessageLines": 0,
    "ReceivedLines": 6,
    "ReceivedMessages": 2
  },
  {
    "MessageType": "Movement",
    "ErrorMessages": 0,
    "ErrorMessageLines": 0,
    "ReceivedLines": 91,
    "ReceivedMessages": 91
  },
  {
    "MessageType": "PickList",
    "ErrorMessages": 2,
    "ErrorMessageLines": 4,
    "ReceivedLines": 7,
    "ReceivedMessages": 3
  },
  {
    "MessageType": "TransferReceipt",
    "ErrorMessages": 0,
    "ErrorMessageLines": 0,
    "ReceivedLines": 13,
    "ReceivedMessages": 4
  }
]

What I want to do is be able to use each of the elements as separate variables (e.g. Transfer receipt error messages = 0)

How would I achieve this?

[Nived_Nambiar]

Hi @JoeColvile 

are you using PAD or microsoft flow?

[JoeColvile]

I am using Power Automate - triggered flow

[Nived_Nambiar]

May i know in what format u need to post this message in teams, should it be posted as every record or combination of all records in once,?

[JoeColvile]

The aim is to post a health update so I want to write something like:

Today there have been x amount of errors.

For each message type below are the messages received/ error messages:

Load receipt x/x

Pick registration x/x

X is my variable

[Nived_Nambiar]

Well we can do this as well, but i need a clarification on variable x what does it should contain, do it should all data corresponding to each message type?

[JoeColvile]

Variable should be a string. I want a variable containing the picklist  error message, then another for picklist received lines ect. So basically the individual results from the table I showed.

[Nived_Nambiar]

Hi @JoeColvile sorry to ask you again, 

As i highlighted in the above figure, do u need error messages indiviually and total too ? am i right?

[JoeColvile]

Yes I want each of those to be its own variable that I am able to use. For example Variable1 =  0 (Adjustment, error messages)

[Nived_Nambiar · Accepted solution]

Well i got answer for your question 

see the result 

What i did 

1. sql result is coming in json format , so the first step used Parse Json activity and parse the json and store in a variable.

2. Now using body parameter of Parse JSON action , extracted the required data for each type 

see below

Adjustment Error Message : 

body('Parse_JSON')?[0]?['ErrorMessages']

Load Reciept : 

body('Parse_JSON')?[1]?['ErrorMessages']

Movement : 

body('Parse_JSON')?[2]?['ErrorMessages']

PickList : 

body('Parse_JSON')?[3]?['ErrorMessages']

Transfer Reciept : 

body('Parse_JSON')?[4]?['ErrorMessages']

i used compose action for above 

Next i add these error message together to get the sum of error messages [ used add function, it can add only 2 numbers at a time.

Next, used the post message to team with the below format

And it worked successfully as required!!!!!

Let me know if you need any further help around this 

screenshot of entire workflow

Mark it as solution if it helps !!!!!!

Regards,

Nived N

[JoeColvile]

This works, thank you!