10.1.600.37
Hello EpiUsers!
I am tasked with emailing out estimating information once a week.
I know that I can use Windows Task Scheduler to make the once a week email execute. And I know I can trigger PowerShell code from the Scheduler.
âInvoke-RestMethodâ can be executed in PowerShell so I figured this was a good path to pursue.
I reading that the output from REST data is JSON. I want to have the data be the body of the email. Iâm not having luck getting with the code Iâm trying. Iâm not very well versed in JSON.
Can someone point me to a good resource? Is there a better way to accomplish this?
Iâve adjusted the end of my Invoke to match yours:
âapi/v1/BaqSvc/EstimatorEmail/ -Credential $Cred -ContentType âApplication/Jsonââ
Then I need to make the $message.Body like this?
â$message.Body = $data.valueâ
My test returns a new email with a completely blank Body.
I know I have data:
{
âodata.metadataâ: âhttps://XXXXXXXXXXXXXXXXXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/$metadata#Epicor.DynamicQuery.QueryResultsâ,
âvalueâ: [
{
âJobAsmbl_JobNumâ: âSO12054-002-10â,
âJobHead_ProdQtyâ: â5.00000000â,
âJobHead_PartNumâ: âDouglas County Courthouse DPV Name Insertâ,
âJobHead_RevisionNumâ: âAâ,
âJobHead_PartDescriptionâ: âReady to apply digitally printed vinyl graphic.â,
âJobHead_ProdCodeâ: âVinylâ,
âCalculated_Total_Est_Hoursâ: â0.28â,
âCalculated_Total_Actual_Hoursâ: â0.66â,
âECORev_ECOâ: âGregâ,
âRowIdentâ: âfc745497-9b00-4168-8262-cf19ea77b3b2â
I see that I can get the data into a CSV file following your example.
Is that the viable solution? Iâm asked to fill the body of the email. Is that can of worms Iâm not aware of?
JobAsmbl_JobNum JobHead_ProdQty JobHead_PartNum JobHead_RevisionNum JobHead_PartDescription JobHead_ProdCode Calculated_Total_Est_Hours Calculated_Total_Actual_Hours ECORev_ECO
SO12054-002-10 5 Douglas County Courthouse DPV Name Insert A Ready to apply digitally printed vinyl graphic. Vinyl 0.28 0.66 Greg
SO12469-038-1 4 DOTSQBAP A 2" x 2" x 12â square DOT post with 2.25" x 2.25" x 3â breakaway base post with breakaway hardware. Post price includes cast hardware for double street blade on top DOT 2.12 0 Eric
SO12651-002-0 1 Cortland Partners Corp - Wall 6 - Led test A âREVISED LED -
lighting and components required to custom color the lensed lighting at the corporate office accent wall to achieve the color effect closer to the PMS value.â Neon 1.25 0 Eric
SO14326-007-0 2 Overlook Parkway Monument Rework A Install NEW channel letters onto a 3/16" painted aluminum cover to go over existing concrete base. FABLET 76 69.16 bert
SO14357-024-0 1 Gateway Center Arena - Other 1 - Brackets A BRACKETS -(10) aluminum clips to be painted and mounted to existing sign. PANFAB 7.9 8.55 jb
Jose,
Again, thank you!
I sent out the email with an attached CSV file for the data. I did NOT receive smiley faces for accomplishing my task. I figured as much but was willing to try with the easy solution you provided. They wanted the body of the email.
None of this is your problem, of course.
The problem is that Iâve duplicated a formatted email body from Epicor data for Receipts (from a post from @hkeric.wci ) and they expect something similar for this email. @hkeric.wciâs cool PO Email
My adaptation results in this:
So how screwed am I? Can I unpack that PowerShell object to a formatted table in an email?
I searched all that I know what to search for. Iâm not sure I know the correct terminology to hack this together myself.
If anyone could point me to some resources, Iâll dig in on my time.
Help is greatly appreciated,
Ben
Youâre already setting your email to be html, so you can loop through the results and build an html table with your results. $data.value is an array of objects. For each object in the array, create a table row and table data cells for the values you need displayed. Youâll have to look up the syntax for looping in powershell. Iâm not fluent in powershell syntax, but I can usually get it to work with a bit of trial and error, and Iâm sure youâll be able to, too.
The double-edged sword of âwowingâ people strikes again!
In case this helps anyone else that is trying to use REST data from a BAQ into a email body using Powershell and doesnât like the crappy column header names. I figured out how to rename them.
Replace the $message.Body⌠line in the above code with this: