Using Flow in Powerapps to create any printable file

[Anonymous]

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.

[Pstork1]

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...

[Anonymous]

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

[Pstork1]

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.

[Anonymous]

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.

[Pstork1 · Accepted solution]

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.

[Anonymous]

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. 

[Anonymous]

@Pstork1 Not sure what happened but everything works now.

Thank you

[Pstork1]

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

[Anonymous]

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