To pass data from a DataView into a function, where you can then manipulate as needed, and back out again, use kinetic-rest widget instead of kinetic-function widget, configure it like this:
In App Studio, we assume you have two DataViews set up, “inDV” which is the input DataView to the function, and “outDV” which receives the output data from the function. “inDV” and “outDV” could be the same DataView if you want to change the data in-place.
Assuming “inDS” is your Input DataSet, coming from a DataView named “inDV”.
“inDV” will become “inDT” contained within “inDS” (your DataView will become a DataTable inside a DataSet)
Assuming “outDS” is your Output DataSet, “outDT” is your Output DataTable, “outDV” is your Output DataView.
Inside Application Studio, in an Event, add a rest-kinetic (rest-erp) widget, inside “Service Operation” put your Function Name.
In rest-erp → Rest Services → Call Options set “Epicor Functions Library” to the name of your Function Library.
In rest-erp → Rest Services → Kinetic Rest Arguments → Request Parameters, set it up like below (this is your input DataView from Application Studio becoming an input DataSet/DataTable in the function:
In rest-erp → Rest Services → Kinetic Rest Arguments → Response Parameters, set up like below: (this is the output DataSet from the function, containing an Output DataTable, which will become an Application Studio DataView)
In your function, you need an input signature with “inDS” (System.DataSet), and an output signature with “outDS” (System.DataSet)
You can access the input DataTable (AppStudio DataView) in the function like this (assuming your DataView has columns (string)“PartNum”, (decimal)“QtyShipped”, (decimal)“QtyReceived”), and perform your needed calculations inside the function (assuming you want to create a new column “QtyLost” which is the difference of QtyShipped and QtyReceived)
DataTable inDT = inDS.Tables["inDT"];
outDS = new DataSet();
DataTable outDT = inDT.Copy();
outDT.TableName = "outDT";
outDT.Columns.Add("QtyLost", typeof(decimal));
foreach (DataRow r in inDataTable.Rows) {
r["QtyLost"] = (decimal)r["QtyShipped"] - (decimal)r["QtyReceived"];
}
outDS.Tables.Add(outDT);
Now, if you wanted to also write this data out to .csv inside the function in addition to adding a calculated column, do this:
string filename = "somefile.csv";
var filetext = new StringBuilder();
DataTable inDT = inDS.Tables["inDT"];
outDS = new DataSet();
DataTable outDT = inDT.Copy();
outDT.TableName = "outDT";
outDT.Columns.Add("QtyLost", typeof(decimal));
string headers = "";
foreach (DataColumn c in outDT.Columns) {
headers = headers + c.ColumnName + ",";
}
headers = headers.Substring(0, headers.Length -1);
filetext.AppendLine(headers);
string values = "";
foreach (DataRow r in outDT.Rows) {
r["QtyLost"] = (decimal)r["QtyShipped"] - (decimal)r["QtyReceived"];
values = "";
foreach (DataColumn c in outDT.Columns) {
values = values + r[c] + @",";
}
values = values.Substring(0, values.Length -1);
filetext.AppendLine(values);
}
outDS.Tables.Add(outDT);
var folderPath = new FilePath(ServerFolder.CompanyData, "SubFolderYouWantToWriteTo");
var filePath = new FilePath(ServerFolder.CompanyData, @"SubFolderYouWantToWriteTo\" + filename);
this.Sandbox.IO.Directory.CreateDirectory(folderPath);
this.Sandbox.IO.File.WriteAllText(filePath, filetext.ToString(), System.Text.Encoding.Unicode);
That should both write out your modified DataSet/DataTable to an output DataView, and simultaneously save the output dataview as a .csv.
Lastly, if the function returns no error, but does not output desired results, wrap your code in a try/catch block to handle any exceptions that may occur, and check epicor’s help for “Application Logging” - add some detailed application logging where you “talk to yourself from inside the function” and tell yourself the state of the data at various locations. This can give you more information to diagnose the problem and arrive at a solution.
To add detailed logging to the function above, I would do this, which will show the output in f12 dev tools console when running the function, in addition to writing a log file you can find in Server File Download \ UserData:
References: Assemblies\Microsoft.Extensions.Logging.Abstractions.dll
References: Assemblies\Microsoft.Extensions.Logging.dll
using Microsoft.Extensions.Logging;
using Ice.Logging;
string lfn = "MyFunctionLibrary-MyFunctionName.log";
string timestamp = DateTime.Now.ToString("[yyyy/MM/dd HH:mm:ss] ");
var logger = ApplicationLoggerBuilder.CreateBuilder().SetMinimumLevel(LogLevel.Information).AddFile(options => {
options.Session = this.Session;
options.FileName = lfn;
options.Folder = LogFolder.User;
options.MessageOptions.QuoteValues = false;
options.MessageOptions.ShowLogLevel = false;
options.MessageOptions.TimestampFormat = TimestampFormat.DateTime;
options.TruncateFile = true;
}).Build();
var log = new Action<string>(msg => {
logger.LogInformation(msg);
outResult += timestamp + $"{msg}\n";
});
using (logger) {
try {
string filename = "somefile.csv";
var filetext = new StringBuilder();
DataTable inDT = inDS.Tables["inDT"];
outDS = new DataSet();
log("output dataset initialized");
DataTable outDT = inDT.Copy();
outDT.TableName = "outDT";
outDT.Columns.Add("QtyLost", typeof(decimal));
log("added QtyLost column to outDT");
string headers = "";
foreach (DataColumn c in outDT.Columns) {
headers = headers + c.ColumnName + ",";
}
headers = headers.Substring(0, headers.Length -1);
log($"adding csv header line: {headers}");
filetext.AppendLine(headers);
string values = "";
foreach (DataRow r in outDT.Rows) {
r["QtyLost"] = (decimal)r["QtyShipped"] - (decimal)r["QtyReceived"];
values = "";
foreach (DataColumn c in outDT.Columns) {
values = values + r[c] + @",";
}
values = values.Substring(0, values.Length -1);
log($"adding csv values line: {values}");
filetext.AppendLine(values);
}
log($"Adding DataTable: {outDT.TableName} to DataSet outDS");
outDS.Tables.Add(outDT);
string folderName = "SubFolderYouWantToWriteTo";
var folderPath = new FilePath(ServerFolder.CompanyData, folderName);
var filePath = new FilePath(ServerFolder.CompanyData, @"SubFolderYouWantToWriteTo\" + filename);
log($"Writing CSV: {filename} to CompanyData\{folderName}");
this.Sandbox.IO.Directory.CreateDirectory(folderPath);
this.Sandbox.IO.File.WriteAllText(filePath, filetext.ToString(), System.Text.Encoding.Unicode);
} catch (Exception ex) {
string exinfo = $"Exception: {ex.GetType().FullName} HResult: 0x{ex.HResult:X} Text: {System.Runtime.InteropServices.Marshal.GetExceptionForHR(ex.HResult).Message}\nMessage: {ex.Message}\nSource: {ex.Source}\nHelpLink: {ex.HelpLink}\nTargetSite: {(ex.TargetSite != null ? ex.TargetSite.ToString() : "")}\nStackTrace: {ex.StackTrace}\nData: {(ex.Data != null && ex.Data.Count > 0 ? string.Join("; ", ex.Data.Cast<System.Collections.DictionaryEntry>().Select(d => d.Key + "=" + d.Value)) : "")}\n";
if (ex.InnerException != null)
{
var inner = ex.InnerException;
exinfo += $"Inner Exception: {inner.GetType().FullName} HResult: 0x{inner.HResult:X} Text: {System.Runtime.InteropServices.Marshal.GetExceptionForHR(inner.HResult).Message}\nMessage: {inner.Message}\nSource: {inner.Source}\nHelpLink: {inner.HelpLink}\nTargetSite: {(inner.TargetSite != null ? inner.TargetSite.ToString() : "")}\nStackTrace: {inner.StackTrace}\nData: {(inner.Data != null && inner.Data.Count > 0 ? string.Join("; ", inner.Data.Cast<System.Collections.DictionaryEntry>().Select(d => d.Key + "=" + d.Value)) : "")}\n";
}
log(exinfo);
} finally {
log("Finally");
}
}
