cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

16 REPLIES 16
Anonymous
Not applicable

@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
Dual Super User III
Dual Super User III

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?



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

@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
Dual Super User III
Dual Super User III

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.



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

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
Not applicable

@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
Dual Super User III
Dual Super User III

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



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

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (897)