cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmanthey
Level 8

Using Flow in Powerapps to create any printable file

I have a coffee roasting application that I created in PowerApps that tracks all our batches and all other necessary data in an Azure SQL Database.  However we are still using excel to print out our roast tags.  The roast tags are placed on the bin and follow the batch through the production cycle.  So I create the schedule in PowerApps and then the crew can see what needs done but then I have to also put the schedule into an excel file to print the roast tags for them.  I would really like to be able to create a file when an item is added to the batch_data table (the schedule).  I see flow has that template already.  I have a screen that writes Bean Number, Lot Number and Weight to a table using a submit button.  I would really like that submit button to also fire off a query / stored procedure to grab all information and store it so it can be printed on the roast tag.  Note: the information required to go on the Roast Tag spans about 5 different tables.  

 

So in ending, how do I fire a stored procedure to collect the data and then palce the data within a template and save it then allow the crew to print the roast tag?  Also it doesn't matter the file type, can be a word document, excel, pdf, etc.  whichever is easiest.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Using Flow in Powerapps to create any printable file

Take a Peek at the code in the action being processed inside the Loop.  That will give you the JSON representation of the value the loop is acting upon.  Then put that inside a First() function.  That will give you the first record. Then append the field to the end of that string.  That will give you each field from the first record.  For example

First(body('Start_and_wait_for_an_approval')?['responses'])?['responder']?['email']

Approval Actions return responses similar to the SQL query you are running

body('Start_and_wait_for_an_approval')?['responses'] = is the responses collection object that the Loop runs on

?['responder'] - identifies the people object that is a field in each object in the collection

?['email'] - is the specific property I want.

 

Without playing aroudn with your code I can't tell you exaactly what the names of your objects are, but that is the syntax you want.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

16 REPLIES 16
Super User
Super User

Re: Using Flow in Powerapps to create any printable file

Assuming the number of fields are predictable you can easily do this using Flow and a Document template.  Here's a Youtube tutorial on how it works.

https://www.bing.com/videos/search?q=you+tube+flow+populate+word+template+jon+lev&view=detail&mid=ED...



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
rmanthey
Level 8

Re: Using Flow in Powerapps to create any printable file

That got me on the right track, however I think I am doing something else wrong.

 

I am trying to create a document from a stored procedure.  The stored procedure works as expected and outputs the correct data in my terminal.

 

I followed along with the video for the Word template and have attached my flow and document.  It is not passing the values from the query into the forms.

 

Here is the ouput for the stored Procedure:

{
  "OutputParameters": {},
  "ReturnCode": 0,
  "ResultSets": {
    "Table1": [
      {
        "ID": 25,
        "SKU_Number": "20750",
        "SKU_Description": "LC 15/10-oz MACADAMIA AD       ",
        "Blend_Number": "23042",
        "Blend_Description": "LC Flavor Blend",
        "Blend_Agtron": 62,
        "Grind_Profile": "AD",
        "Flavor_Number": "23103",
        "Flavor_Description": "FLAVOR - LC LION MAC",
        "Roast_Date": "9/17/2019",
        "Comments": "",
        "PackLine": "G14C"
      }
    ]
  }
}

 

Here are my attachments

Super User
Super User

Re: Using Flow in Powerapps to create any printable file

I think the problem is in your ForEach loop.  The Stored procedure is returning a collectoin of results.  In this case the collection only has one.  But because its a collection when you run it it processes the collection in a loop.  So you'll have to check the run to make sure the right values are being input and that they aren't then being overwritten in preparation for the next loop.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
rmanthey
Level 8

Re: Using Flow in Powerapps to create any printable file

I'm not sure how to check that but I downloaded all the run files and I can see the values associated with that specific Batch ID that was passed. I deleted the For Each block but we when I go to add my dynamic content to the word document template it automatically adds that for each layer.  Not sure how to get around that.

Super User
Super User

Re: Using Flow in Powerapps to create any printable file

Take a Peek at the code in the action being processed inside the Loop.  That will give you the JSON representation of the value the loop is acting upon.  Then put that inside a First() function.  That will give you the first record. Then append the field to the end of that string.  That will give you each field from the first record.  For example

First(body('Start_and_wait_for_an_approval')?['responses'])?['responder']?['email']

Approval Actions return responses similar to the SQL query you are running

body('Start_and_wait_for_an_approval')?['responses'] = is the responses collection object that the Loop runs on

?['responder'] - identifies the people object that is a field in each object in the collection

?['email'] - is the specific property I want.

 

Without playing aroudn with your code I can't tell you exaactly what the names of your objects are, but that is the syntax you want.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

rmanthey
Level 8

Re: Using Flow in Powerapps to create any printable file

So I did this is the Blend_Number box in the Populate a Microsoft Word Template 

first(body('Execute_stored_procedure_(V2)')?['resultsets']?['Table1'])?['Blend_Number']

and the context control on the template still shows Blend_Number. 

 

 

 

rmanthey
Level 8

Re: Using Flow in Powerapps to create any printable file

@Pstork1 Not sure what happened but everything works now.

 

Thank you

Super User
Super User

Re: Using Flow in Powerapps to create any printable file

The formula you posted above looks right.  Glad you got it working.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
rmanthey
Level 8

Re: Using Flow in Powerapps to create any printable file

Yea with this Roast_Tag now be automated and along with every other flow I made to automate other tasks i just freed up about 5 - 7 hours a week of my time.  

 

Once again thank you for your help

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 75 members 4,077 guests
Please welcome our newest community members: