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.

[Anonymous]

@Pstork1 Well maybe I spoke a little too soon. 

I was using a default Batch_ID as my parameter in my stored Procedure.  I have changed my flow around slightly.  When My Green Bean Loader submits a batch I want that to be the trigger to kick off the flow so the Roast Tag can be 'printed'.  The flow looks like it runs successfully but there is nothing in the ouput of the table.  Whereas before with the hard coded '25' there was.  The flow is obviously getting the correct parameter passed to it but not pulling the table (record in this case) from the stored procedure.

SELECT bd.ID, ss.SKU_Number, bs.Blend_Number .... and a few others
FROM Batch_Data bd

JOIN SKU_Source ss ON bd.SKU_ID = ss.ID
JOIN SKU_Blend_Join sbj ON ss.id = SKU_ID
JOIN Blend_Source bs ON bs.ID = sbj.Blend_ID
And few other join statements

WHERE bd.id = @Batch_ID

GROUP BY ............

GO

@Batch_ID is getting the right integer it just seems like it isn't getting passed into the SP.  Any Ideas?

[Pstork1]

Was it hard coded as '25' or 25? one is a string and the other an integer.  Are you sure the proper BatchID is actually getting fed to the stored procedure?  What data type in SQL is bd.id?

[Anonymous]

@Pstork1 it was hard coded into the flow action of execute a stored procedure.  It is an INT and the primary Key in teh Batch_Data Table with the Identity (feature?), i just did quotes for emphasis(sorry for the confusion).  When I had the procedure execute upon a new item being created in the Green_Data Table it is passing the correct value and I could see it in the flow after it ran.  However this is the output parameters:

{
  "OutputParameters": {},
  "ReturnCode": 0,
  "ResultSets": {
    "Table1": []
  }
}

Before when hard coded the Table1 would have all corresponding info in the query.

[Pstork1]

Have you tried running the stored procedure manually with a BatchID of 156 just to make sure the stored procedure is returning the values for that specific batch?  Otherwise it looks to me like the BatchID is being passed correctly.  Although some of the connectors do have issues with numeric data types.  YOu might want to try passing the parameter as a string (varchar) and then converting it in the stored procedure to the right data type.

[Anonymous]

I found the issue with some things not showing up.  In my query I also join on Flavor_ID, well not every SKU is flavored so therefore those queries will not return any values.  I tried it with different Batch_ID's for the ones that are flavored and everything passes correctly.  

I guess my next step is to figure how to pass null values to flavor when that SKU isn't flavored.

My tables are set up as such:

SKU Source:

ID

SKU_Number

SKU_Description

SKU_Flavor_Join:

ID

SKU_ID

Flavor_ID

Flavor_Source

ID

Flavor_Number

Flavor_Description

Since not every sku is represented int eh SKU_Flavor_Join Table the query results are empty.

[Anonymous]

@Pstork1 i changed my joins to Full Outer Joins and now even those UnFlavored batches now produce a Roast Tag.  

Thanks for your help

[Pstork1]

Glad you found the issue.  SQL stored procedures can sometimes be really tricky.