cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoeColvile
Frequent Visitor

Use individual results from SQl query

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:

JoeColvile_0-1626837401259.png

[
  {
    "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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nived_Nambiar
Resolver I
Resolver I

Well i got answer for your question 

 

see the result 

 

Nived_Nambiar_0-1626959835688.png

 

 

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
 
Nived_Nambiar_1-1626960123331.png

 

 

And it worked successfully as required!!!!!

 

Let me know if you need any further help around this 

 

screenshot of entire workflow

Nived_Nambiar_2-1626960192127.png

 

Nived_Nambiar_3-1626960207241.png

 

 

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

 

Regards,

 

Nived N

View solution in original post

10 REPLIES 10
Nived_Nambiar
Resolver I
Resolver I

Hi @JoeColvile 

are you using PAD or microsoft flow?

I am using Power Automate - triggered flow

Nived_Nambiar
Resolver I
Resolver I

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,?

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
Resolver I
Resolver I

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?

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
Resolver I
Resolver I

Hi @JoeColvile sorry to ask you again, 

Nived_Nambiar_0-1626893542436.png

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

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
Resolver I
Resolver I

Well i got answer for your question 

 

see the result 

 

Nived_Nambiar_0-1626959835688.png

 

 

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
 
Nived_Nambiar_1-1626960123331.png

 

 

And it worked successfully as required!!!!!

 

Let me know if you need any further help around this 

 

screenshot of entire workflow

Nived_Nambiar_2-1626960192127.png

 

Nived_Nambiar_3-1626960207241.png

 

 

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

 

Regards,

 

Nived N

View solution in original post

This works, thank you!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,551)