My Solution for Using a Function to Generate a CSV with BAQ Data for Bartender

Kinetic ERP
, ,
1

I have devised a way to generate a CSV file with BAQ data in a Function and then outputting that to an Azure File Share for Bartender. If you have questions or suggestions let me know. I hope someone finds this useful (most likely my future self).

The reason why I went down this rabbit hole is that Epicor’s built in tools for Bartender file generation was to rigid for my liking, such as not being able to use Auto-Print in a Function which means I can’t set a label to print after whatever process I want and am limited to existing Data tables.

Below are my reasonings on why some of the customizations are that way:

  1. I linked a UD table (In the example UD06) to a Function to act as a rudimentary print history of all the labels so that if a user says something didn’t print you could check the table to see the time it printed if at all.

  2. I created an Image called “NoImageFound” to display on custom label menus which call for an image but none are found. If you try to pass an empty value into the Picture BLOB it will be ignored which means if you were to switch between records with images and to one without Epicor would still display the old image. There is probably a better way to handle that but that’s what I came up with. Also the NoImageFound is just for displaying, that image would not be passed to the label unless you want to.

  3. In the Function that gets the Image Content for a Label it is using Widgets and Custom Code instead of pure Custom Code. This is because the Image.ImageContent field is the devil and I was not able to get it to cleanly be called if using pure code. I have a BO call for the Image service which then populates a parameter with the BLOB data as a String.

  4. You don’t need to create a separate menu for each label, if you know exactly what record you want to filter your BAQ to then you can just tie a process to the Function and pass the data in a variable directly.

  5. If you don’t want a printer to show up in the Printer List BAQ then you set the SSRS Printer value to True. Also if you are running multiple companies and don’t want a printer that’s in one plant to show up for another then you can uncheck All Companies in Printer Maintenance.

I am using the Bartender settings that Epicor specifies in their setup docs with the following Actions setup:
File Path
Search for: /AF=“epi/fs/Bartender/Templates/
Replace with: /AF=”\\{Put_Your_Azure_ID}.file.core.windows.net{Put_Your_Azure_ID}\Bartender\Templates\

image
image903×629 29.4 KB

You will put in your own Azure folder ID which Epicor provides.

Trigger File

Search for: /D=“”

Replace with: /D=“%Trigger File Name%”

The Fun Stuff

Attached are the main Function Library and example setups for how you could use this customization. If installing the Function Library you would need to add fields to the table UD06 since it is referenced in the Function.

Bartender-Label-Example.efxj (25.0 KB)

Example_Label_1.baq (7.6 KB)

Example_Label_1_All.baq (7.2 KB)

Example_Label_1_App_Layer.zip (4.9 KB)

Example_Label_2.baq (6.9 KB)

Example_Label_2_All.baq (6.4 KB)

Example_Label_2_App_Layer.zip (5.3 KB)

Label_Printer_List.baq (6.6 KB)

UD06_Label_Print_History.zip (6.4 KB)

NoImageFound
NoImageFound500×500 7.96 KB

Printer_Maintenance_Label_Printer_Setup
Printer_Maintenance_Label_Printer_Setup1867×649 35.3 KB

Custom Code from the Functions for People who don’t want to install random files on the Internet

Example-Label-1

Request Parameters:

jobNum : System.String
labelsToPrint : System.Int32
printerName : System.String

Response Parameters:

generatedFilePath : System.String

// ===== Label-specific configuration - change this block when copying the Function for a different label =====
string labelName = "Example Label 1";
string bartenderLabelName = "Example_Label_1.btw";
string baqName = "Example_Label_1";
string filePrefix = "Example_Label_1";

// BAQ execution parameter IDs mapped to the values passed into this Function
System.Collections.Generic.Dictionary<string, string> baqParameterValues = new System.Collections.Generic.Dictionary<string, string>()
{
//{ "BAQ_Parameter_Name", "Function_Request_Parameter" },
  { "jobNum", jobNum },
};

// Full ordered list of columns written to the .bt file - a mix of BAQ output columns
// and app-layer (non-BAQ) columns supplied directly by this Function's inputs
string[] columnNames = new string[]
{
//"Table_Field",
  "JobHead_JobNum",
  "JobHead_PartNum",
  "JobHead_RevisionNum",
  "JobHead_DrawNum",
  "JobHead_PartDescription",
  "JobHead_ProdQty",
  "Print_Quantity"
};

// Values for the app-layer (non-BAQ) columns above, keyed by column name.
// Any column in columnNames NOT present in the BAQ result set must have an entry here.
System.Collections.Generic.Dictionary<string, object> appLayerColumnValues = new System.Collections.Generic.Dictionary<string, object>()
{
//{ "Field_Outside_BAQ", "Data_Input" },
  { "Print_Quantity", labelsToPrint }
};
// ===== End label-specific configuration =====

generatedFilePath = ThisLib.Generate_Bartender_Label_File(
  labelName,
  bartenderLabelName,
  baqName,
  filePrefix,
  printerName,
  labelsToPrint,
  System.Text.Json.JsonSerializer.Serialize(baqParameterValues),
  System.Text.Json.JsonSerializer.Serialize(columnNames),
  System.Text.Json.JsonSerializer.Serialize(appLayerColumnValues));

Example-Label-2

Request Parameters:

partNum : System.String
labelsToPrint : System.Int32
printerName : System.String

imageID : System.String

Response Parameters:

generatedFilePath : System.String

// ===== Label-specific configuration - change this block when copying the Function for a different label =====
string labelName = "Example Label 2";
string bartenderLabelName = "Example_Label_2.btw";
string baqName = "Example_Label_2";
string filePrefix = "Example_Label_2";

// BAQ execution parameter IDs mapped to the values passed into this Function
System.Collections.Generic.Dictionary<string, string> baqParameterValues = new System.Collections.Generic.Dictionary<string, string>()
{
//{ "BAQ_Parameter_Name", "Function_Request_Parameter" },
  { "partNum", partNum },
};

// Full ordered list of columns written to the .bt file - a mix of BAQ output columns
// and app-layer (non-BAQ) columns supplied directly by this Function's inputs
string[] columnNames = new string[]
{
//"Table_Field",
  "Part_PartNum",
  "Part_PartDescription",
  "Part_UnitPrice",
  "Print_Quantity",
  "Image_Content"
};

// Image content (Base64 string) retrieved from the Get-Image-Content Function for the given imageID
string imageContent = ThisLib.Get_Image_Content(imageID).imageContent;

// Values for the app-layer (non-BAQ) columns above, keyed by column name.
// Any column in columnNames NOT present in the BAQ result set must have an entry here.
System.Collections.Generic.Dictionary<string, object> appLayerColumnValues = new System.Collections.Generic.Dictionary<string, object>()
{
//{ "Field_Outside_BAQ", "Data_Input" },
  { "Print_Quantity", labelsToPrint },
  { "Image_Content", imageContent }
};
// ===== End label-specific configuration =====

generatedFilePath = ThisLib.Generate_Bartender_Label_File(
  labelName,
  bartenderLabelName,
  baqName,
  filePrefix,
  printerName,
  labelsToPrint,
  System.Text.Json.JsonSerializer.Serialize(baqParameterValues),
  System.Text.Json.JsonSerializer.Serialize(columnNames),
  System.Text.Json.JsonSerializer.Serialize(appLayerColumnValues));

Generate-Bartender-Label-File

Request Parameters:
labelName System.String
bartenderLabelName System.String
baqName System.String
filePrefix System.String
printerName System.String
labelsToPrint System.Int32
baqParameterValuesJson System.String
columnNamesJson System.String
appLayerColumnValuesJson System.String

Response Parameters:

generatedFilePath : System.String

string labelFolder = "epi/fs/Bartender/Templates/";
string outputFolder = "/epi/fs/Bartender/In/";
string companyID = Session.CompanyID;
string btwTemplatePath = labelFolder + bartenderLabelName;
string columnPrefix = "_" + companyID + "_" + baqName + "_";

System.Collections.Generic.Dictionary<string, string> baqParameterValues = System.Text.Json.JsonSerializer.Deserialize<System.Collections.Generic.Dictionary<string, string>>(baqParameterValuesJson);
string[] columnNames = System.Text.Json.JsonSerializer.Deserialize<string[]>(columnNamesJson);
System.Collections.Generic.Dictionary<string, object> appLayerColumnValues = System.Text.Json.JsonSerializer.Deserialize<System.Collections.Generic.Dictionary<string, object>>(appLayerColumnValuesJson);

Func<object, string> csvQuote = value =>
{
  string s = value == null || value == DBNull.Value ? string.Empty : value.ToString();
  return "\"" + s.Replace("\"", "\"\"") + "\"";
};

System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.AppendLine("%BTW% /AF=\"" + btwTemplatePath + "\" /D=\"<Trigger File Name>\" /PRN=\"" + printerName + "\" /DBTEXTHEADER=3 /R=3 /P ");
sb.AppendLine("%END%");

string[] headerValues = new string[columnNames.Length];
for (int i = 0; i < columnNames.Length; i++)
{
  headerValues[i] = csvQuote(columnPrefix + columnNames[i]);
}
sb.AppendLine(string.Join(",", headerValues));

CallService<DynamicQuerySvcContract>(dq =>
{
  Ice.Tablesets.QueryExecutionTableset qets = dq.GetQueryExecutionParametersByID(baqName);

  foreach (var p in qets.ExecutionParameter)
  {
    if (baqParameterValues.ContainsKey(p.ParameterID))
    {
      p.ParameterValue = baqParameterValues[p.ParameterID];
    }
  }

  System.Data.DataSet ds = dq.ExecuteByID(baqName, qets);

  foreach (System.Data.DataRow row in ds.Tables[0].Rows)
  {
    string[] rowValues = new string[columnNames.Length];
    for (int i = 0; i < columnNames.Length; i++)
    {
      string columnName = columnNames[i];
      if (ds.Tables[0].Columns.Contains(columnName))
      {
        rowValues[i] = csvQuote(row[columnName]);
      }
      else
      {
        rowValues[i] = csvQuote(appLayerColumnValues[columnName]);
      }
    }
    sb.AppendLine(string.Join(",", rowValues));
  }
});

string fileName = filePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bt";
string fullPath = System.IO.Path.Combine(outputFolder, fileName);

System.IO.File.WriteAllText(fullPath, sb.ToString());

generatedFilePath = fullPath;

ThisLib.Log_Print_History(companyID, labelName, labelsToPrint);

Generate-Bartender-Label-Debug

I set this to be disabled. It will output a message of what processed from the linked label function, either the error message or the name of the file that was generated.

Request Parameters:
labelName System.String
bartenderLabelName System.String
baqName System.String
filePrefix System.String
printerName System.String
labelsToPrint System.Int32
baqParameterValuesJson System.String
columnNamesJson System.String
appLayerColumnValuesJson System.String

Response Parameters:

generatedFilePath : System.String

try
{
  string labelFolder = "epi/fs/Bartender/Templates/";
  string outputFolder = "/epi/fs/Bartender/In/";
  string companyID = Session.CompanyID;
  string btwTemplatePath = labelFolder + bartenderLabelName;
  string columnPrefix = "_" + companyID + "_" + baqName + "_";

  System.Collections.Generic.Dictionary<string, string> baqParameterValues = System.Text.Json.JsonSerializer.Deserialize<System.Collections.Generic.Dictionary<string, string>>(baqParameterValuesJson);
  string[] columnNames = System.Text.Json.JsonSerializer.Deserialize<string[]>(columnNamesJson);
  System.Collections.Generic.Dictionary<string, object> appLayerColumnValues = System.Text.Json.JsonSerializer.Deserialize<System.Collections.Generic.Dictionary<string, object>>(appLayerColumnValuesJson);

  Func<object, string> csvQuote = value =>
  {
    string s = value == null || value == DBNull.Value ? string.Empty : value.ToString();
    return "\"" + s.Replace("\"", "\"\"") + "\"";
  };

  System.Text.StringBuilder sb = new System.Text.StringBuilder();
  sb.AppendLine("%BTW% /AF=\"" + btwTemplatePath + "\" /D=\"<Trigger File Name>\" /PRN=\"" + printerName + "\" /DBTEXTHEADER=3 /R=3 /P ");
  sb.AppendLine("%END%");

  string[] headerValues = new string[columnNames.Length];
  for (int i = 0; i < columnNames.Length; i++)
  {
    headerValues[i] = csvQuote(columnPrefix + columnNames[i]);
  }
  sb.AppendLine(string.Join(",", headerValues));

  CallService<DynamicQuerySvcContract>(dq =>
  {
    Ice.Tablesets.QueryExecutionTableset qets = dq.GetQueryExecutionParametersByID(baqName);

    foreach (var p in qets.ExecutionParameter)
    {
      if (baqParameterValues.ContainsKey(p.ParameterID))
      {
        p.ParameterValue = baqParameterValues[p.ParameterID];
      }
    }

    System.Data.DataSet ds = dq.ExecuteByID(baqName, qets);

    foreach (System.Data.DataRow row in ds.Tables[0].Rows)
    {
      string[] rowValues = new string[columnNames.Length];
      for (int i = 0; i < columnNames.Length; i++)
      {
        string columnName = columnNames[i];
        if (ds.Tables[0].Columns.Contains(columnName))
        {
          rowValues[i] = csvQuote(row[columnName]);
        }
        else
        {
          rowValues[i] = csvQuote(appLayerColumnValues[columnName]);
        }
      }
      sb.AppendLine(string.Join(",", rowValues));
    }
  });

  string fileName = filePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bt";
  string fullPath = System.IO.Path.Combine(outputFolder, fileName);

  System.IO.File.WriteAllText(fullPath, sb.ToString());

  generatedFilePath = fullPath;

  ThisLib.Log_Print_History(companyID, labelName, labelsToPrint);
}
catch (Exception ex)
{
  generatedFilePath = "DEBUG ERROR: " + ex.ToString();
}

Log-Print-History

Request Parameters:
companyID System.String
labelName System.String
quantityPrinted System.Int32

Response Parameters:

DateTime printedOn = DateTime.Now;

try
{
    var newUD06 = new Ice.Tables.UD06();

    newUD06.Company = companyID;
    newUD06.Key1 = labelName;
    newUD06.Key2 = printedOn.ToString();
    newUD06.Key3 = quantityPrinted.ToString();
    newUD06.Key4 = "";
    newUD06.Key5 = Guid.NewGuid().ToString();

    // Copy standard UD columns as needed.
    newUD06.Label_Name_c = labelName;
    newUD06.DateTime_Printed_c = printedOn;
    newUD06.Quantity_Printed_c = quantityPrinted;

    Db.AddObject(newUD06);
    Db.SaveChanges();
}
catch (Exception ex)
{
    throw new Ice.BLException(
        "UD06 insert failed from Log-Print-History Function. " +
        "Company=" + companyID +
        ", LabelName=" + labelName +
        ", QuantityPrinted=" + quantityPrinted +
        ". Error: " + ex.Message
    );
}

Get-Image-Content

Request Parameters:

imageID System.String

Response Parameters:
imageContent System.String
fileType System.String

Widget flow

[Start]
   │
   ▼
[Invoke BO Method 0]  — Erp.BO.Image.GetByID
   │   Input:  imageID  ← imageID (Function input param)
   │   Return: imageResult (ImageTableset)
   ▼
[Condition 1]  — imageResult.Image.Count > 0
   │
   ├── True ──▶ [Execute Custom Code 1]
   │
   └── False ──▶ (end, no widget connected)

Custom Code

imageContent = imageResult.Image[0].ImageContent;
fileType = imageResult.Image[0].FileType;

If you made it to the end congratulations, here is a cookie.

cookie
cookie740×493 39.2 KB

2

I like cookies.

was this done a differrent way before, or was this your first csv for Bartender? we have ubaq code that does this that I have been meaning to move to functions.

3

This is my first CSV for Bartender. I am implementing Bartender at the shop I work at so wanted to try something new.