I read some posts about invoking a BAQ in an Epicor Function, but I can’t seem to figure out how to set a value from that BAQ to a variable that I create.
There is a ud_Select_c field on the JobOper table. I want to bring in only the JobNum, AssemblySeq, and OprSeq when the Select_c is checked, in order to perform additional processing in my function. I was able to invoke the BAQ in my function but am having trouble retrieving any data from the <return value> dataset.
How do I retrieve data from the return dataset? For example, when I try to assign the JobNum from the BAQ to another variable, I get nothing from the dsBAQResults table. Does anyone know what might be the issue? I suspect I might be missing something important.
So your variable dsBAQResults is a System.Data.DataSet type, which is a collection of DataTables. In the case of BAQs, it should contain only 1 table which will be named “Results”. The table will contain a collection of rows and their fields (named exactly as they’re shown in the BAQ Display Fields table).
So if you want to see the JobNum from the first row of the BAQ from the dsBAQResults dataset, it would look like this:
Thank you, Kevin! I realize I need to convert the variables to their correct data types when looping through my results.
So, I made some updates to the code here:
foreach (DataRow row in dsBAQResults.Tables[“Results”].Rows )
{
string JobNum = Convert.ToString(row[“JobOper_JobNum”]);
int AssemblySeq = Convert.ToInt32(row[“JobOper_AssemblySeq”]);
int OprSeq = Convert.ToInt32(row[“JobOper_OprSeq”]);
}
Let’s say I want to display only the JobNum in a message box from the dsBAQResults table. How do I do that? Alternatively, how can I assign the value from JobNum to another variable outside of dsBAQResults.Tables?
To assign it to a variable that can be used outside of the dsBAQResults, all you need to do is declare the variable outside the scope of the Loop and then assign it within the loop.
string DisplayJobNums = string.Empty;
foreach ( DataSet row in dsBAQResults.Tables["Results"].Rows )
{
string ThisJobNum = (string)row["JobOper_JopNum"];
DisplayJobNums += $"{ThisJobNum}, ";
}
// result of DisplayJobNums: "JobNum0, JobNum1, JobNum2, JobNum2, "
I apologize if I haven’t been clear about my question.
Once the columns are created and stored in dsBAQResults (in this case, JobNum, AssemblySeq, and OprSeq), how can I use these fields?
For example, if JobNum equals 12345, and I display dsBAQResults.JobNum, I want the message to show 12345. I understand it might not be this straightforward, but I hope you get my point lol
Do I need to convert the dataset to a table and set tableset.columns equal to dataset.columns?
@kve Hi Kevin, I might be missing something here. I am trying to automatically create Labor Transactions on Time Entry based on the jobs selected by the user from a BAQ.
I called this BAQ in my function, but for some reason, it only captures one job instead of all the jobs that are being selected. I think my code is missing some key elements since it seems to only grab one row from the data set.
try
{
if(dsMyQueryResults.Tables.Count > 0 && dsMyQueryResults.Tables["Results"] != null)
{
foreach (DataRow row in dsMyQueryResults.Tables["Results"].Rows )
{
// variables are defined inside the function designr.
iJobNum = Convert.ToString(row["JobOper_JobNum"]);
iAsmSeq = Convert.ToInt32(row["JobOper_AssemblySeq"]);
iOperSeq = Convert.ToInt32(row["JobOper_OprSeq"]);
}
}
}
catch
{
}
Do you know what the issue might be? I can share my function if needed. Thank you in advance.
I’m not sure what might be missing, except that it looks like your variables are defined outside of the foreach loop. Because of that, they’ll be replaced after each loop in the foreach, so that the variables would always be the values from the last row only. If you want to do a transaction for every row returned by the query, you’ll need to put the code to do that inside the foreach loop.
Thank you for your quick response, Kevin. This is exactly what I’m seeing: the query only returned the last row. If I define my variables inside the foreach loop in the custom code widget, how can those variables be used outside of that widget?
For example, I don’t see the JobNum variable, which I defined in a custom code widget, when I try to use it to invoke a BO method.
Set up a separate function taking EmployeeID, JobNum, AssemblySeq, and OprSeq as input parameters. (You can do this in the same library if you’re in Kinetic, it will have to be a different library if you’re in 10.2.X00).
Add your Invoke BO Method bit using those input parameters in the Setup Method Parameters dialog.
Remove your Invoke BO Method from the original function.
Inside the foreach loop on the BAQ results, call the new function using the variables after defining them, like this:
try
{
if(dsMyQueryResults.Tables.Count > 0 && dsMyQueryResults.Tables["Results"] != null)
{
foreach (DataRow row in dsMyQueryResults.Tables["Results"].Rows )
{
// variables are defined inside the function designr.
iJobNum = Convert.ToString(row["JobOper_JobNum"]);
iAsmSeq = Convert.ToInt32(row["JobOper_AssemblySeq"]);
iOperSeq = Convert.ToInt32(row["JobOper_OprSeq"]);
this.ThisLib.InvokeLaborBO( iJobNum, iAsmSeq, iOperSeq ); // Replace ".InvokeLaborBO" with the name of your new function
}
}
}
catch
{
}
We are on Kinetic, so no need for additional library.
I created a separate function and defined EmpNum, JobNum, AssemblySeq, OperSeq, and some other variables as input parameters. However, to invoke the Erp.Labor.DefaultJobNum and Erp.Labor.DefaultOprSeq, I will need to know the JobNum and OprSeq, which are coming from the BAQ. Otherwise, I will receive an error: ‘A valid Job Number/Job Operation Sequence is required’ on the REST API.
Would it be possible to get the results from the BAQ first and then define my variables to tie to the JobNum, AssemblySeq, and OperSeq for BO method calls? Or is there a better way to do this? I want this function to automatically create new labor transactions on Time Entry when users select a Job/Assembly/Operation.
I apologize, as I am learning as I go, and I really appreciate your time helping me out.
Yes that’s exactly what we’ll be doing. So when you create your new function, you can set Input parameters. You will only call that function from inside the loop, and you’ll pass the Job, Assemply, and Operation in from the original Function
Thank you so much for pointing it out! I got it to work. You are absolutely correct; the variables for the BO calls need to be defined inside my loop. This is why I wasn’t getting all the BAQ results.
