Creating a Powershell script to run DMT

Epicor ERP 10
1

Good Day to all. First of all I want to thank the Epicor community for being so helpful in helping those of us who have been away from Epicor and are now back in it.

I have had a lot of help on this forum and I want to extend my appreciation and thanks to all who have helped and continue to help.

I have never really used Powershell, I have just taken a cursory look at it. I have seen some suggestions on how to use it here on this forum, but I am still a little foggy on how to go about what I am trying to do.

I want to write a script which I can have Windows schedular run at a certain time. I want the script to look in a directory for a file and use DMT to load the file into Epicor. I have looked online, but the instructions on how to find a file using Powershell is all over the place. I know I have seen how it’s done somewhere. Could anyone out there point me to where I can get started.

Thanks

2

I spent a lot of time on Powershell SYNTAX study, and then I made sure I understood how to do the DMT command line using a .BAT file. Then it was some trial and error to move it to PS. But I’d suggest you use the PS IDE, that way you can write/test/fix much more easily.

This works for us to do a GL Journal(Combined) import. It will take every file in the $Source Path, process it, and move it to the $Processed Path. It’s scheduled for every couple of hours in the Windows Scheduler as a Task Action.

#DMT Automation Example 1

$basePath = Get-Location
$logPath = “$basePath” + “\log”
$DataPath = “$basePath” + “\Data”
$logFile = “$logPath” + “\LoadLog_” + $(Get-Date -Uformat “%Y%m%d%H%M%S”) + “.txt”
$DMTPath = "C:\Epicor\ERP11\LocalClients\<InstanceName>\DMT.exe " #-DisableUpdateService "
$User = "<Username>"
$Pass = '<Password>'
$Source = "\\<EpicorServer>\EpicorData\CeridainDayforceGL"
$Processed = "\\<EpicorServer>\EpicorData\CeridainDayforceGL\Processed"
$dmttemplate = "`"GL Journal Combined`""
$EpicorConfig = “Ashworth”
 
Get-ChildItem -Path $Source *.xlsx |   #–Recurse
Foreach-Object { 
                    #Load Data
                    $FName = "`"" + $_.FullName + "`""
                    Start-Process -FilePath $DMTPath -ArgumentList "-NoUI -NoCompleteLog -NoErrorInput -DisableUpdateService -User $User -Pass $Pass -ConfigValue=$EpicorConfig -Add -Update -Import $dmttemplate -Source $FName  " -Wait 
                    Move-Item $_.FullName -Destination $Processed
                    #Write-Host $FName
                }

#Remove Older than 90 days
#Get-ChildItem $Processed | Where-Object {$_.CreationTime -lt (Get-Date).AddDays(-90)} | ForEach-Object { Remove-Item $_.FullName –whatif }
3 Likes
3

I just have one file in a directory I always put the source file. I’m taking baby steps in Powershell and I usually like to crawl first. Thanks for the suggestion.

I just want to go pick up the source file and process it. I t would be nice to move it to another directory when the load is completed. I am going to study what you have here and see if I can glean from it how to do that. Thanks. If you have any additional suggestions, it will be greatly welcome…

4

Hello.
I use Windows Scheduler to run a PowerShell script.

The Script runs a BAQ in the middle of the night.
The BAQ looks for sales staff who consistently neglect to clock out.
The BAQ result is a .csv file list of the offenders which sets the Activetrans of each Labor Header to false, and sets clock out hours.
The .csv file is saved to my designed file path location.
Then I run DMT to consume that .csv file to process the records with TE Labor Combined.
This essentially clocks them out.
The DMT result message is saved as a .txt file.
I email myself that .txt as the last step of the process.

All that is listed below in the PowerShell code.
I think all of what you said you want to do was included in my process.
You should be able to piece together what you are trying to from parts shown here.

I hope this helps you.
Ben

#Extract Data From From BAQ -> CSV File -> Load in with DMT

$DMTPath = "C:\Epicor\XXXXClient\Client\DMT.exe"
$datestring = (Get-Date -Format yyyyMMdd-HH)
$ToEmail = @("bstohr@XXXXXX")

$Source = "C:\XXXXXXXXX\SalesClockOut.csv_$datestring.csv"
$completeLog = $source + ".CompleteLog.txt"

Write-Output "Extracting Data via BAQ $(get-date)"

Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User XXXXX -Pass XXXX -Export -BAQ LateSales -Target $Source -NoUI -ConfigValue="""ServerName""" -DisableUpdateService"
Write-Output "Loading Data $(get-date) " $Source 

#Load Data
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User XXXXX -Pass XXXX -Update -Import `"TE Labor Combined`" -Source $Source "

#Check Results
select-string -Path $completeLog -Pattern "Records:\D*(\d+\/\d+)" -AllMatches | % { $_.Matches.Groups[0].Value }
select-string -Path $completeLog -Pattern "Errors:\D*(\d+)" -AllMatches | % { $_.Matches.Groups[0].Value }

Send-MailMessage -From admin@XXXXX -Subject "Labor Updated" -To $ToEmail -Attachments $completeLog -Body "DMT Completed" -SmtpServer  XXXXXX.com
2 Likes
5

Thank You. I’ll give it a stab. Really appreciate your help.

6

Here is this morning’s results - 5 sales people clocked out by this process.
The .txt file is attached to my email:

image
image716Ă—282 7.69 KB

Just as a follow up.

1 Like
7

Thanks. I hope to finish testing mine today.

8

Not sure if it still exists, but in DMT I thought there were some examples on how to do this. Sure I’ve going a version kicking around as well that parses a csv which you can maintain in a spreadsheet that calls specific VIews or stored procedures to create the DMT data from your source system then runs the related DMT.

Hmmmm now where did I put that… :thinking:

And I think I got my start with it from here…

2 Likes
9

Yeah, all my effort was built on the knowledge provided, here, by Rick Bird.
He’s the “solution” on that post.

1 Like