Hi

Something I build a lot for our customers (in various guises). Often this is on request via an automated email (read by another AM task), or I add it in at the bottom of a large processing task as a confirmation of what was done in the ERP system.

Using a SQL query I pull out data to a dataset.
Write to a Text file and create a header step and add in the HTML Table header
Loop through Dataset
Write each line to Text file
End Loop
Write in footer (close off HTML tags)

An Advanced property would be the ability to add Text above and below the table. This created Text file would then be attached to an email as HTML and sent off to user.

I use SQL Server Reporting services a lot, but this is often a lot easier for users, especially if they are on the road. They send a quick email off and within 5 minutes they receive a reply with all the data they wanted.




Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
<AMFILESYSTEM ACTIVITY="write_file" AM_DESCRIPTION="Write Header" FILE="D:\VitraxImportFolder\ReconsEmailed\%Consignment%.TXT" APPEND="no">&lt;html&gt; &lt;head&gt; &lt;style&gt; table,th,td { border:1px solid lightgray; } &lt;/style&gt; &lt;/head&gt; &lt;p&gt; Hi&lt;br&gt; &lt;br&gt; Pre-Invoicing to Journals Recon &lt;/p&gt; &lt;br&gt; &lt;table&gt; &lt;tr&gt;&lt;td&gt; Consignment &lt;/td&gt;&lt;td&gt; Inv Value &lt;/td&gt;&lt;td&gt; Temp GJ &lt;/td&gt;&lt;td&gt; Variance &lt;/td&gt;&lt;td&gt; Exact GJ &lt;/td&gt;&lt;td&gt; Variance &lt;/td&gt;&lt;/tr&gt; </AMFILESYSTEM> <AMLOOP TYPE="DATASET" DATASET="Recon"> <AMFILESYSTEM ACTIVITY="write_file" AM_DESCRIPTION="Write Recon rows" FILE="D:\VitraxImportFolder\ReconsEmailed\%Consignment%.TXT">&lt;tr&gt;&lt;td&gt; %Recon.Docnumber% &lt;/td&gt;&lt;td&gt; %Recon.InvoiceTotal% &lt;/td&gt;&lt;td&gt; %Recon.PurchaseFruit% &lt;/td&gt;&lt;td&gt; %Recon.Variance1% &lt;/td&gt;&lt;td&gt; %Recon.ExactPurchFruit% &lt;/td&gt;&lt;td&gt; %Recon.Variance2% &lt;/td&gt;&lt;/tr&gt;</AMFILESYSTEM> </AMLOOP> <AMFILESYSTEM ACTIVITY="write_file" AM_DESCRIPTION="Write Recon Footer" FILE="D:\VitraxImportFolder\ReconsEmailed\%Consignment%.TXT"> &lt;/table&gt; &lt;/html&gt; </AMFILESYSTEM>

For those interested, see an interim solution based on a basic script function in this thread

-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4

We would like exactly the same thing

Hi Gavin,

I tried your solution above and it works great!!!

Thanks a lot for all your help, I will now spend some time testing Jean's task, but let's hope the guys can look at an automated action at some time in the future.

Thanks again,

Ken

My pleasure Ken. I didn't stick the code in expecting it to be used, but included it as an example of what I do. For instance the HTML table formatting wasn't there - needs visible column borders at times to make it easier to read. I haven't tried Jean's solution yet, but planning on giving it a go when I have to build another dataset to email block of code.