I created another function to translate the deptID (JCDept) into the department key that I need to lookup. Now, my productivity UBAQ BPM GetList Post-Processing method looks like this:
And I am trying to set the department ID to the local variable with:
I need to reference the currentRecord when pulling this data from the BAQ so that I can pull the correct row, and then populate the hours in the same row.
I don’t do widgets, that looks like complete gibberish to me.
How do you refer to the BAQ row when using GetList instead of RunCustomAction? I always used queryResultDataset.Results in the past.
let me bring one up
result.Results
So far, this is working great! I have one small issue where I want to pull data from the timeclock, where there are no records in Epicor. For example, our Drivers punch in and out, but don’t use Epicor. My BAQ that pulls the data from the API is based on the JCDept and a filter. But the JCDept for drivers, doesn’t exist in Epicor. I have set the two departments that are not in Epicor to 100, and 101. I think I need to switch around my base query to pull based on just the list of JCDepts that I give, but not have it be restricted to only the JCDepts in Epicor. Basically, here is a list of JCDeptIDs. Get the internal Epicor data for them, and the timeclock data. If one or the other doesn’t exist then just show a blank or 0.
Base UBAQ:
select
[DeptEff].[LaborHed_Company] as [LaborHed_Company],
[DeptEff].[JCDept1_JCDept] as [JCDept1_JCDept],
[DeptEff].[JCDept1_Description] as [JCDept1_Description],
(EarnedHours.Calculated_ProdEH) as [Calculated_DayEH],
(DeptEff.Calculated_LaborHours) as [Calculated_DayLab],
(DayEH/ DayLab) as [Calculated_DayProd],
(0) as [Calculated_TCHrs],
(@EndDate) as [Calculated_myDate],
(0) as [Calculated_pulled]
from (select
[LaborHed].[Company] as [LaborHed_Company],
[JCDept1].[Description] as [JCDept1_Description],
(sum(LaborDtl1.LaborHrs)) as [Calculated_LaborHours],
[JCDept1].[JCDept] as [JCDept1_JCDept]
from Erp.LaborDtl as LaborDtl1
inner join Erp.LaborHed as LaborHed on
LaborHed.Company = LaborDtl1.Company
and LaborHed.LaborHedSeq = LaborDtl1.LaborHedSeq
inner join Erp.EmpBasic as EmpBasic on
EmpBasic.Company = LaborHed.Company
and EmpBasic.EmpID = LaborHed.EmployeeNum
inner join Erp.JCDept as JCDept1 on
LaborDtl1.Company = JCDept1.Company
and LaborDtl1.JCDept = JCDept1.JCDept
where (LaborDtl1.JCDept in ('11', '12', '15', '16', '2', '3', '4', '5', '8', '9', '100', '101') and LaborDtl1.PayrollDate >= @StartDate and LaborDtl1.PayrollDate <= @EndDate)
group by [LaborHed].[Company],
[JCDept1].[Description],
[JCDept1].[JCDept]) as DeptEff
inner join (select
[JCDept].[Description] as [JCDept_Description],
(sum(LaborDtl.EarnedHrs)) as [Calculated_ProdEH]
from Erp.LaborDtl as LaborDtl
inner join Erp.JCDept as JCDept on
LaborDtl.Company = JCDept.Company
and LaborDtl.JCDept = JCDept.JCDept
where (LaborDtl.PayrollDate >= @StartDate and LaborDtl.PayrollDate <= @EndDate)
group by [JCDept].[Description]) as EarnedHours on
DeptEff.JCDept1_Description = EarnedHours.JCDept_Description
order by DeptEff.JCDept1_JCDept Desc
Here is the GetList Method, with my custom Directive GetHours:
And here are my two functions inside the library:
GetDeptKey(in DeptID, out DeptKey):
//use the epicor dept id code to lookup the dept key from asure
Dictionary<string, string> departmentKeys = new Dictionary<string, string>
{
{"9:Quality Control", "791866ff-f69d-4217-b744-3270a06ce846"},
{"11:Sheet Metal", "9736c126-e903-42fc-b943-6518b62bbec3"},
{"10:Shipping", "5edc6acb-176a-4f8a-b7b9-6974cb8e9eb4"},
{"15:Horizontal", "8157cce9-133e-4f0e-917c-b17857e92512"},
{"8:Burr Bench", "00916604-403f-433a-84eb-c9c9b93dbd76"},
{"100:Maintenance", "8d94bf71-6734-4990-96c7-da4f41cea9d9"},
{"4:N.C. Mill", "ffdd73c8-e840-4641-9b9c-e49901c65b33"},
{"101:Drivers", "7e3000a3-6315-4527-8fab-efad773c0ae5"},
{"2:Lathe Department", "04e6a140-12f1-4a92-aab5-f625737a5cc2"},
{"5:Bridgeport", "9c230560-724e-40e1-8ebd-fa970b9eb00d"}
};
foreach (var department in departmentKeys)
{
var deptIDName = department.Key;
var deptKey = department.Value;
// Split the input data based on ":"
string[] parts = deptIDName.Split(':');
// Extract department code and name
var deptCode = parts[0];
if (DeptID == deptCode)
{
DeptKey = deptKey;
}
}
GetDeptHrs(in deptKey, in myDate, out deptHours):
try
{
// Base URL for the API
var URL = "MYURL";
// Initialize RestClient
var client = new RestClient(URL);
// Parse myDate to match the expected format for the API call
if (!DateTime.TryParseExact(myDate.ToString(), "M/d/yyyy h:mm:ss tt", null, System.Globalization.DateTimeStyles.None, out DateTime dateTimeValue))
{
// Debug message for invalid date format
myDebugMsg += "[ERROR] Invalid date format";
return; // Exit the try block as date parsing failed
}
string formattedDate = dateTimeValue.ToString("yyyy-MM-dd");
// Debug message for formatted date
myDebugMsg += "Formatted Date: " + formattedDate + " | ";
// Construct the API request URL
var myRequest = "webapi/timecards/level/" + deptKey + "/payrolldata/" + formattedDate;
myDebugMsg += myRequest;
// Create a RestRequest with the constructed URL and GET method
var request = new RestRequest(myRequest, Method.GET);
// Add authorization header
request.AddHeader("Authorization", "Basic MYAUTHSTRING");
// Execute the request
IRestResponse myIResponse = client.Execute(request);
// Check if request was successful
if (myIResponse.StatusCode != System.Net.HttpStatusCode.OK)
{
// Debug message for failed API request
myDebugMsg += "[ERROR] API request failed: " + myIResponse.ErrorMessage + " | ";
return; // Exit the try block as API request failed
}
// Extract response content
string responseData = myIResponse.Content;
// Debug message for successful API response
myDebugMsg += "API Response: " + responseData + " | ";
// Parse the response data into a JSON array
JArray jsonData;
try
{
jsonData = JArray.Parse(responseData);
}
catch (Newtonsoft.Json.JsonReaderException ex)
{
// Debug message for JSON parsing error
myDebugMsg += "[ERROR] JSON parsing error: " + ex.Message + " | ";
return; // Exit the try block as JSON parsing failed
}
// Calculate the total hours for selected PayTypeIDs
int totalMinutes = 0;
List<string> excludedPayTypeIDs = new List<string> { "JURY", "HOL", "BER", "SICK/VACA" };
foreach (JObject record in jsonData)
{
// Extract report date and hours
string reportDate = DateTime.Parse(((string)record["ReportDate"]).Split('T')[0]).ToString("yyyy-MM-dd");
if (reportDate == formattedDate)
{
string payTypeId = record["PayTypeId"].ToString();
int minutes = Convert.ToInt32(record["Minutes"]);
if (!excludedPayTypeIDs.Contains(payTypeId))
{
totalMinutes += minutes;
}
else
{
totalMinutes -= minutes;
}
}
}
// Calculate department hours
deptHrs = ((decimal)totalMinutes / 60);
}
catch (Exception ex)
{
// Debug message for other exceptions
myDebugMsg += "[ERROR] Exception: " + ex.Message + " | ";
}
How can I base my initial UBAQ on the dept IDs that I care about?
Does this make sense? I know it is a deep rabbit hole… Thanks for taking the time to look!
Nate
I rearranged the BAQ a bit and solved my last problem in this project (for now). Now this whole thing works for me. The API integration does not seem like such a brick wall anymore.
I would like to sum up everything for anyone else wanting to integrate an API. What missing steps have I left out? Ah right… The security. I still haven’t pulled out my basic auth string (or the user/pass creds) and put them in a table. Then reference the secure data using Convert.ToBase64String(). How important is this really?
I couldn’t have done any of this without @klincecum. Thank you!
Security by obscurity? 
You’re better off storing the creds in a secret vault instead of a table in the database if you stay with Basic Authentication. Speaking of, this is what ChatGPT says:
Basic authentication is an outdated industry standard that lacks robust security features, such as multifactor authentication (MFA). The threats posed by basic authentication have only increased over time. Here are some reasons why you should consider moving away from basic authentication for your personal apps:
- Security Risks: Basic authentication sends a username and password with each request, making it vulnerable to interception by attackers. Stolen credentials can be reused against other endpoints or services1.
- Lack of MFA: Basic authentication doesn’t support MFA, which is crucial for enhancing security. MFA adds an extra layer of protection by requiring users to provide additional verification beyond their password.
- Phishing and Attacks: As cloud services like Office 365 become more popular, they also become attractive targets for hackers. The sheer volume of phishing attempts and login attacks against users has risen significantly. Implementing stronger authentication methods is essential to safeguard against these threats2.
- Azure AD Security Defaults: Microsoft introduced Azure AD Security Defaults, a configuration that enforces several preconfigured controls for tenants. These controls include requiring MFA registration for every user, enforcing MFA for privileged tasks, and blocking legacy authentication. Microsoft is even removing basic authentication for email connection protocols starting October 1, 202223.
In summary, it’s time to move away from basic authentication and adopt more secure alternatives. Consider using modern authentication methods that provide better protection for your personal apps and data.
Is this even possible? I don’t have control over the security of the API. I can’t add MFA to it. Realistically, how would you move from what I have now, to a secret vault? Do I have to integrate another third-party program?
This is what I mentioned further back in this thread.
Tightly coupling the integration brings these kinds of problems. If we use an API between the calls, then we’ve separated the implementation details of the service from Kiinetic. If you had an internal API to call, it can handle all that security and Kinetic doesn’t worrry about it.
The BEST reason to follow this pattern is that it makes testing SO much easier. You beat up your API first getting that to work with the service then you have Kinetic call that well-tested interface. Also, if the service changes, you don’t have to touch Kinetic. You fix your API and any all places it is used in Kinetic are fixed. 
Just when I thought I had a handle on it… Mark, it sounds like greek to me!
Does this mean I have to create an API?
Why does entra have anything to do with it? I don’t use Entra for Epicor, or our Asure timeclock.
Maybe I need a new topic for how to do BFF in Epicor. I am definitely missing something. You make it sound easy!
I’m working on one.
Possibly, or you could use a service maybe.
It all boils down to the level of complexity and risk you are willing to assume for a given project.
If I leave my basic auth string inside my BPM custom code, what is the attack that could compromise the auth string? I guess I don’t see how the auth string could be compromised, so it seems like a lot of extra work to secure the creds when it may not be necessary. Security by obscurity can work sometimes… right?
Let’s assume a bad actor reads this whole thread (a stretch, I know). Is there enough information in here to attack my timecard software? They know I use Asure. They can look up the API syntax for the calls. What does it take to get from here, to finding my auth string and then utilizing it? The only protection I can put in place on the Asure API side are IP restrictions.
I don’t want to assume any risk if I can help it. I have to understand the risk here before I can decide if it is worth mitigating it.
Like Kevin said, you need to balance the risk level that you’re willing to accept. If someone knows the password, can they skip work and punch in remotely?
There are certainly tools that do nothing but sniff out usernames, passwords, connection strings, shared secrets, etc. Do they exist for Epicor? Depends on who you or your customers are. It might be worth the effort if the target is juicy enough.
If you’re saving passwords in a table, there may be other usernames and passwords in the same place since that has become the practice of the organization. Now other databases will be combed for secrets in the databases too. It’s a slippery slope. Why do some network devices keep getting hacked? They start with relaxed security practices and it spreads throughout the codebase.
Yes! I want to avoid this for sure.
Put it this way, I’ve seen enough to know that if someone is inside Epicor, as in having a compromised login, it’s just a hop, skip, and a jump away from pulling down your code.
Something that is not normally exposed, is now fair game.
For what I store there, I don’t worry with it, and I’m willing to accept the risk. That doesn’t mean that I will do so in the future. And if or when that changes, or a new project comes along with a higher risk score, I’m prepared to put in more work to mitigate said risk.
Don’t be scared, just develop the adequate skills to evaluate risk, or pass that responsibility off to someone who can, and go from there.
