Dynamic Parameters - AKA - Adding Variables To BAQs

[klincecum (Kevin Lincecum)]

Ok, So I added Variables To BAQs.

I’m going to do this one a little different, so first let me tell you a story.

No, I’m not full of myself. This isn’t the end all, be all project of the year. I’ve
decided to expand my writeups for a few reasons. One of these reasons is we are in
mixed company here on the forum, with different skill levels and interests. Another
is I’m trying to improve my communications skills in general. I’m also trying to
create the type of documentation that I like to read, and I feel lacks to some extent.

I’m never gonna be a legendary coder, that time has passed, but I can try to share
what I’ve learned with others, and maybe we can all learn something on the way.

The last thing is, I want people to look at the process, and see the power of what
Epicor has offered us here with being able to customize, and see how that can be
leveraged to add new features or work around issues.

Anyway, on to the story…

I was perusing the forum at work, looking for things I could help with. OK, Fine → I was
looking for a way to get out of work that wouldn’t make me feel so guilty.

Anyway, I came across this thread:

BAQ newbie question - constants and variables

OK, maybe I’m not that new to BAQs. But I feel silly to have never asked this. Is there not a way to declare a variable or constant in a BAQ? Yes, I use parameters, but it prompts the user. I don’t need that. The most ideal use case would be to grab a value from a table, store it as a variable, and then reference that instead of querying the table thousands of times. Here’s a simple example of this. To grab some data from PartDtl, I want to exclude forecast data per the company configuration…

It peaked my interest, and I participated. I had also pondered these same questions
before, and I had a need, and a desire for this feature. I hacked at the BAQ Designer
for a few days when I had time, trying to find a more elegant workaround, and I didn’t
come up with anything better than we had come up with in the thread.

So I started thinking, looking at the BAQ Designer, and the data structure of how a BAQ
is actually defined, and started running some tests. I asked myself a few questions, some
of those were:

* "What is a variable?"
* "In this context, is it the same as a parameter?"
* "Oh wait, can I add those dynamically?"

So I did a bunch of tracing and decided it was feasable. And then in some really ugly
test code, I made it work. WooHoo!

So then I asked myself a few more questions. I said to myself:

* "Ok smart ass, so what, you can add parameters.. but how do you define them?"
* "Where should you define them?"
* "Can we use the built in tools?"

I had a good look at the BAQ Designer again and the DataSet and had a few ideas.
“Define Parameters” was out. I couldn’t take it over with code, or with BPMs, it’s
not customizable at all, and some other issues.

Then a thought hit me. Subqueries not referenced, are in the DataSet, but not included
in the query… Hmmm, that will also give me an “Editor” to define variables.
(The Calculated Field Editor)

So I decided on my path. I had rules.

I decided to add Variables or should I say “Dynamic Parameters”, to queries, and this
is how I would do it:

* Store the variables in a subquery named "Variables" inside the BAQ itself.
* The "Variables" subquery is not referenced at all in the BAQ. It just exists.
* The "Variables" would be defined as "Calculated" Fields in the query.
* The "Values" of these queries would be stored in the "Formula" field. (This became more flexible later)
* This would allow me to define the "Data Types" of the variables as well!

So now I had a plan, using built in tools, to add a neat and useful feature.
Now we just needed some backing code to make this work.

The place to do this code was the DynamicQuery BO. So I worked up version one with my rules in mind.
First I set up a test query, with variables defined in Calculated Fields, and referenced in the regular top query.
(Variables.Calculated_FieldName would be referenced as a parameter in the rest of the BAQ
as “@FieldName”)
Of course it didn’t work. Errors everywhere.

Then I started writing a pre-processing directive on DynamicQuery.GetList.

First check, does this DataSet have a subquery in it called “Variables”?

• Nope, move along.
• Yes?!, Ok, let the magic happen lol:

Ok, we have a Subquery named variables, now I looped through each field defined, and added the correct data to the datasets to make it work.

Time for truth.

I hit “Test” on the BAQ Analyze tab, and BAM! My test query returned my variable values. It was a little messy,
but it worked!

Of course, the “Analyze” button did not. So I copied the code over unchanged to the “Analyze” method.

Syntax OK

“Sweet.”

“Hmmm, this is going to have to be reused, should I move it to a function, or a UBAQ?”
“Yes, Yes I should.”
(For those of you who do not have functions, don’t worry, if you want this, it can be done without it.)

The rest of the story is where I clean it up, debug it, add add a few features. That information is below.

Ok, Now that story time is over, I’ll try to explain this as clearly as possible. Even if you do not need
a feature like this, the knowledge may be vaulable. I know it is to me. I have two other projects I’m now
working on from the knowledge I’ve gained from this one.

How BAQs Work - The Abbreviated and Highly Simplified Version

A BAQ is just a big DataSet, with Subqueries, Fields, Field Attributes, Links, Parameters, Control
Fields, Etc, that are read from a custom Epicor BO, that creates SQL for the backend. This allows a level of control over how the queries are formed, as well as a loose abstraction away from the underlying database.

Basically, The Dynamic Query Business Object reads the DataSet, and constructs a query to be run.

How can we use this?

We have virtually no access to the underlying database, other than some simple stored procedures and parameters we can pass on down through Execution Settings or Calculated Fields. For Better Or Worse, Opinion wise.

We do however have full access to the Dynamic Query DataSet and Execution Parameters. These can be used along with the BO methods to add features or manipulate behavior along the way. I don’t like everything in Epicor by far, but I LOVE THIS.

Technical & Implementation Details

Variables (Dynamic Parameters) are defined by simply adding a SubQuery To A BAQ
with the name “Variables”

Then, you have a few options to add variables.

Method 1:
* Simply Add a Calculated Field, and use the label caption, as the value.
* Example → Field: Calculated_VariableTest, Label Caption: “Hello World”
* Note: I would just set the “Formula” field here to “null”, no quotes
* You would use this as @VariableTest everywhere else in your BAQ.
* This has a limit of 50 Characters.
* DataTypes are defined in the Calculated Field Editor.

Method 2:
* As a convenience feature, you can add Tables to your “Variables” SubQuery
* These are added just to “steal” the names of these fields
* Example → Field: Part_Partnum, Label Caption: “I reused this field name lol”
* You would use this as @Partnum everywhere else in your BAQ.
* This has a limit of 50 Characters.
* DataTypes is the same as the DataType of the field you referenced.

Then, you have the Expanded methods. These are built off of Method 1.

Method 1 A:
* Simply Add a Calculated Field, set the label caption variable to “null” (literally, the text “null”, no quotes)
* Example → Field: Calculated_VariableTest, Label Caption: “null” (no quotes)
* You would define your variable value in the “Formula” Field in the Calculated Field Editor
* You would use this as @VariableTest everywhere else in your BAQ.
* This does not have the 50 character limitation.
* DataTypes are defined in the Calculated Field Editor.

Method 1 B:
* Simply Add a Calculated Field, set the label caption variable to “null” (literally, the text “null”, no quotes)
* Then set the “Formula” field to “null”, no quotes
* Example → Field: Calculated_VariableTest, Label Caption: “null” (no quotes), Formula Field: “null” (no quotes)
* You would define your variable value in the “Description” Field in the Calculated Field Editor
* Note: Due to a bug in the BAQ Designer, sometimes you will need to exit out of the Calculated Field Editor
* and save and put your data in the Description field to get it to stick. It’s annoying.
* You would use this as @VariableTest everywhere else in your BAQ.
* This does not have the 50 character limitation.
* DataTypes are defined in the Calculated Field Editor.

How to Use

In your BAQ, you would use these variables, just like you would use parameters in your BAQ.
Even in links, criteria, etc.
Note: In links and criteria, you would choose “Expression”, instead of “Parameter”
Just pop your parameter in the expression box, as the designer doesn’t know about dynamic parameters.

And that’s pretty much it for usage.

How it’s put together:

The main meat of this is run via an Epicor Function called:

• Function: BAQDynamicParameters.AddDynamicParameters
  • This function takes the information provided in the “Variables” SubQuery,
  • and adds Parameters to the BAQ in flight, with the values pre-filled.
  • No user interaction is necessary, it just acts as a variable.

This is called from a few method directives with some shim code to call the function.
Method Directives Pre

• AddDynamicParameters_Generic

  • Where Used:
  • Ice.BO.DynamicQuery.GetList
  • Ice.BO.DynamicQuery.Analyze, Ice.BO.DynamicQuery.Execute

• AddDynamicParameters_ExecuteByID

  • Where Used:
  • Ice.BO.DynamicQuery.ExecuteByID

There is also two helper functions for “Status” information.

• Function: BAQDynamicParameters.DynamicParametersStatus-Analyze
• Function: BAQDynamicParameters.DynamicParametersStatus

These are called from these method directives:
Method Directives Post

• DynamicParameters_Analyze

  • Where Used:
  • Ice.BO.DynamicQuery.Analyze

• DynamicParameters_Generic

  • Where Used:
  • Ice.BO.DynamicQuery.Execute
  • Ice.BO.DynamicQuery.GetList

Here are some screenshots:

And that’s pretty much it.
I will be packaging up the code after some final checks, and post the code, the functions,
the method directives, and a solution file today or tommorrow for your enjoyment.

[klincecum (Kevin Lincecum)]

I’m pretty sure this is the current code, if it isn’t, I’ll fix it

The Functions

BAQDynamicParameters.AddDynamicParameters

/**************************************************************
* Epicor Function
* Library: BAQDynamicParameters
* Name: AddDynamicParameters
* Description: Adding Variables To BAQs
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/

  List<string> functionErrorMessages  = new List<string>();
  List<string> functionStatusMessages = new List<string>();
  List<string> functionDebugMessages  = new List<string>();

  Action<string, Exception> AddErrorMessage = (methodName, exceptionObject) =>
  {string errorMethodName = "AddErrorMessage";
      try
      {//var x = 0;var y = 5 / x; //Testing Error Handler
          string functionID = this.GetType().Name.Replace("Impl", String.Empty);
          string innerMethodInfo =  $"Library: {this.LibraryID}{Environment.NewLine}";
                 innerMethodInfo += $"FunctionID: {functionID}{Environment.NewLine}";
                 innerMethodInfo += $"Inner Method: {methodName}";
          
          string errorMessage = innerMethodInfo;
          
          errorMessage += $"{Environment.NewLine}Exception:{Environment.NewLine}{exceptionObject.Message}";
          if(exceptionObject.InnerException != null) errorMessage += "{Environment.NewLine}InnerException:{Environment.NewLine}{exceptionObject.InnerException.Message}"; 
          
          functionErrorMessages.Add(errorMessage);
      }
      catch (Exception ex)
      {
          string functionID = this.GetType().Name.Replace("Impl", String.Empty);
          string innerMethodInfo =  $"Library: {this.LibraryID}{Environment.NewLine}";
                 innerMethodInfo += $"FunctionID: {functionID}{Environment.NewLine}";
                 innerMethodInfo += $"Inner Method: {errorMethodName}";
          
          ex.Data.Add("UserMessage", $"{innerMethodInfo}{Environment.NewLine}An error occured in the Error Handling Function, what did you do?!"); //Add Error Data to message if our error handler taps out lol.
          throw; //Rethrow Original Message
      }
  };

  //Serialize Shortcut Functions
  Func<object, string> Serialize = (objectToSerialize) =>
  {string methodName = "Serialize";
  
      try { return JsonConvert.SerializeObject(objectToSerialize, Formatting.None); }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
      return "Error Serializing Object";
  };
  Func<object, string> SerializeIndented = (objectToSerialize) =>
  {string methodName = "SerializeIndented";
  
      try { return JsonConvert.SerializeObject(objectToSerialize, Formatting.Indented); }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
      return "Error Serializing Object";
  };

  //Add QueryCtrlRow
  Action<QueryCtrlTable, QueryFieldRow> AddQueryControlRow = (queryControlTable, fieldRow) =>
  {string methodName = "AddQueryControlRow";
  
      try
      {
          queryControlTable.Add(
              new QueryCtrlRow()
              {
                  Company       = fieldRow.Company,
                  QueryID       = fieldRow.QueryID,
                  ControlID     = Guid.NewGuid().ToString(),
                  DataSource    = fieldRow.FieldName,
                  DataType      = fieldRow.DataType,
                  FieldFormat   = fieldRow.FieldFormat,
                  IsMandatory   = false, //true?
                  DefaultValue  = "",
                  ControlType   = "Standard",
                  SourceType    = 1,
                  ListSource    = "",
                  DisplayColumn = "",
                  ValueColumn   = "",
                  SysRevID      = DateTime.Now.Ticks,
                  SysRowID      = Guid.NewGuid(),
                  Seq           = 1,
                  BitFlag       = 0,
                  RowMod        = ""
              }
          );
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };
  
  //Add QueryParameterRow
  Action<QueryParameterTable, QueryFieldRow> AddQueryParameterRow = (queryParameterTable, fieldRow) =>
  {string methodName = "AddQueryParameterRow";
  
      try
      {
          queryParameterTable.Add(
              new QueryParameterRow()
              {
                  Company        = fieldRow.Company,
                  QueryID        = fieldRow.QueryID,
                  ParameterID    = fieldRow.FieldName,
                  ParameterType  = fieldRow.DataType,
                  ParameterLabel = "",
                  SkipIfEmpty    = false,
                  SysRevID       = DateTime.Now.Ticks,
                  SysRowID       = Guid.NewGuid(),
                  BitFlag        = 0,
                  RowMod         = "",        
              }
          );
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };
  
  //Add ExecutionParameterRow
  Action<ExecutionParameterTable, QueryFieldRow> AddExecutionParameterRow = (executionParameterTable, fieldRow) =>
  {string methodName = "AddExecutionParameterRow";

      try
      {
          //You have the option on Calculated Field Variables to use the FieldLabel Field, the Formula Field, Or the Description Field --> User Preference
          //The decision tree is as follows:
          //    FieldLabel Field not set to "null" (literal string) --> Use the FieldLabel Field as the Variable Value. (Limit 50 Characters)
          //        FieldLabel Field set to "null" (literal string) --> Use the Formula Field as the Variable Value.
          //            Formula Field set to null (real null in BAQ Designer) --> Use the Desciption Field as the Variable Value.
          //                Descrition Field set to "null" (literal string) --> Set the value as null (empty)
          
          string parameterValue = ""; //Null or Empty Value
          
          if(fieldRow.IsCalculated == true) //Variable is a Calculated Field
          {
              do
              {
                  //If the Field Label is not null (literally string "null" here), then use it as value for the variable
                  if(fieldRow.FieldLabel.ToLower() != "null") { parameterValue = fieldRow.FieldLabel; break; } //Exit Loop When Done
                  
                  //If the Formula Field is not null (literally string "null" here) (real null in BAQ Designer), then use it as value for the variable
                  if(fieldRow.Formula.ToLower()    != "null") { parameterValue = fieldRow.Formula;    break; } //Exit Loop When Done
                  
                  if(fieldRow.Description.ToLower() != "null" && !String.IsNullOrEmpty(fieldRow.Description) ) { parameterValue = fieldRow.Description;    break; } //Exit Loop When Done
                  
                  //parameterValue = ""; //Null Or Empty When Declared
              }
              while (false); //Just for "if" short-circuit (exiting early from if not allowed, but can exit from loop with "break") 
          }
          
          
          //You can also add a Database Table to your "Variables" SubQuery. This is very convenient if you would like Pre-Made Variable Names.
          //There are Limitations to this however. You must use the Field Label for you Variable Values, which limits you to 50 Characters.  
          
          if(fieldRow.IsCalculated == false) //Variable is a Database Field Added For Convenient Variable Naming. There are limitations.
          {
              if(fieldRow.FieldLabel.ToLower() != "null") parameterValue = fieldRow.FieldLabel;
          }
          
          executionParameterTable.Add(
              new ExecutionParameterRow()
              {
                  ParameterID    = fieldRow.FieldName,
                  ParameterValue = parameterValue.TrimStart('\'').TrimEnd('\''),
                  ValueType      = fieldRow.DataType,
                  IsEmpty        = String.IsNullOrEmpty(parameterValue) ? true : false,
                  SysRowID       = Guid.Empty,
                  RowMod         = "A"
              }
          );
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };

  //Add The Proper Data For a Parameter, based off data from QueryFieldRow
  Func<DynamicQueryTableset, QueryExecutionTableset, QueryFieldRow, bool> AddDynamicParameter = (dqDS, qeTS, fieldRow) =>
  {string methodName = "AddDynamicParameter";
      
      bool retValue = false;
      
      try
      {
          AddQueryControlRow(dqDS.QueryCtrl, fieldRow);
          AddQueryParameterRow(dqDS.QueryParameter, fieldRow);
          AddExecutionParameterRow(qeTS.ExecutionParameter, fieldRow);
          retValue = true;
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
      return retValue;
  };

  //We need to Resequence the QueryControlRows
  Action<QueryCtrlTable> ResequenceControlTable = (queryControlTable) =>
  {string methodName = "ResequenceControlTable";

      try
      {
          int newSeqNum = 1;
          foreach(QueryCtrlRow qcRow in queryControlTable)
          {
              qcRow.Seq = newSeqNum;
              newSeqNum++;
          }
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };


  //Called to begin processing
  Action ProcessAddingParameters = () =>
  {string methodName = "ProcessAddingParameters";

      try
      {//var x = 0;var y = 5 / x; //Testing Error Handler

          //Find "Variables" SubQuery In DynamicQueryTableset
          Guid variablesSubQueryGuid = (from QuerySubQueryRow subQ in queryDS.QuerySubQuery
                                      where
                                          subQ.Name.ToLower() == "variables"
                                      select
                                          subQ.SubQueryID).First();
                                          
          //Get List of all fields from "Variables" SubQuery
          List<QueryFieldRow> variablesFieldList = (from QueryFieldRow qfRow in queryDS.QueryField
                                                      where
                                                          qfRow.SubQueryID == variablesSubQueryGuid
                                                      select
                                                          qfRow).ToList();

          //Loop through and add each variable as a "Dynamic" Parameter
          foreach(QueryFieldRow fldRow in variablesFieldList)
          {
              //If success, get the values to add to the status messages for return
              if(AddDynamicParameter(queryDS, executionParams, fldRow) == true)
              {
                  string paramValue = (from ExecutionParameterRow pRow in executionParams.ExecutionParameter
                                          where
                                              pRow.ParameterID == fldRow.FieldName
                                          select
                                              pRow).First().ParameterValue;
                  //Status Messages
                  functionStatusMessages.Add($"Variable: {fldRow.FieldName} added with Value: {paramValue}");              
              }
          }
        
          //Query Control Sequence is changed, Reorder
          ResequenceControlTable(queryDS.QueryCtrl);
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };
  
  
  //Processing Begins Here -->
  
  //DebugInfo
  Action DebugBefore = () =>
  {string methodName = "DebugBefore";
      try
      {
          functionDebugMessages.Add("<BEGIN------------BEFORE MODIFICATION------------>");
          functionDebugMessages.Add("<BEGIN------------queryDS------------>");
          functionDebugMessages.Add( SerializeIndented(queryDS) );
          functionDebugMessages.Add("<------------queryDS------------END>");
          functionDebugMessages.Add("<BEGIN------------executionParams------------>");
          functionDebugMessages.Add( SerializeIndented(executionParams) );
          functionDebugMessages.Add("<------------executionParams------------END>");
          functionDebugMessages.Add("<------------BEFORE MODIFICATION------------END>");
      }
      catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };
  //DebugBefore();
  

  /******Entry Point******/
  ProcessAddingParameters();
  

  //DebugInfo
  Action DebugAfter = () =>
  {string methodName = "DebugAfter";
      try
      {
          functionDebugMessages.Add("<BEGIN------------AFTER MODIFICATION------------>");
          functionDebugMessages.Add("<BEGIN------------queryDS------------>");
          functionDebugMessages.Add( SerializeIndented(queryDS) );
          functionDebugMessages.Add("<------------queryDS------------END>");
          functionDebugMessages.Add("<BEGIN------------executionParams------------>");
          functionDebugMessages.Add( SerializeIndented(executionParams) );
          functionDebugMessages.Add("<------------executionParams------------END>");
          functionDebugMessages.Add("<------------AFTER MODIFICATION------------END>");
      }
       catch (Exception ex) { AddErrorMessage(methodName, ex); }
  };
  //DebugAfter();


  //Return Messages down the Call Context
  string errorMessages  = String.Join(Environment.NewLine, functionErrorMessages);  callContextBpmData.Character01 = errorMessages;
  string statusMessages = String.Join(Environment.NewLine, functionStatusMessages); callContextBpmData.Character02 = statusMessages;
  string debugMessages  = String.Join(Environment.NewLine, functionDebugMessages);  callContextBpmData.Character03 = debugMessages;

  bool DISPLAYERRORMESSAGES  = true; callContextBpmData.Checkbox01 = DISPLAYERRORMESSAGES;
  bool DISPLAYSTATUSMESSAGES = true; callContextBpmData.Checkbox02 = DISPLAYSTATUSMESSAGES;
  bool DISPLAYDEBUGMESSAGES  = true; callContextBpmData.Checkbox03 = DISPLAYDEBUGMESSAGES; 
  
  bool DISPLAYMESSAGESALWAYS_ANALYZE = true; callContextBpmData.Checkbox04 = DISPLAYMESSAGESALWAYS_ANALYZE;


  //For Standalone Use (You Wire it Up, Client Customization Etc)
  returnQueryDS = queryDS;
  returnExecutionParams = executionParams;
  returnCallContextBpmDataRow = Serialize(callContextBpmData);

  //End Processing
  

    
  
  

BAQDynamicParameters.DynamicParametersStatus-Analyze

/**************************************************************
* Epicor Function
* Library: BAQDynamicParameters
* Name: DynamicParametersStatus-Analyze
* Description: Status,Error, & Debug Messages
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/

    Func<string, char, int, string> AddLeftPad = (textToPad, padChar, numberOfChars) =>
    {
        string returnString = "";
        List<string> stringLines = textToPad.Split("\n").ToList();
        foreach(string stringPiece in stringLines)
        {
            returnString += new string(padChar, numberOfChars) + stringPiece + Environment.NewLine;
        }
        return returnString;
    };

    List<string> errorMessages = JsonConvert.DeserializeObject<List<string>>(errorMessagesList);

    //Display Messages Even if we pass syntax check. Will throw a fake error, but can't be helped.
    //This can be disabled by setting DISPLAYMESSAGESALWAYS_ANALYZE = false in the "AddDynamicParameters" Function
    if(callContextBpmData.Checkbox02 == true && callContextBpmData.Checkbox04 == true && !String.IsNullOrEmpty(callContextBpmData.Character02) == true && result == true)
    {
      result = false; //Throw Error Regardless
      errorMessages.Add("Syntax is OK");
    }

    //Always show errors
    if(!String.IsNullOrEmpty(callContextBpmData.Character01) == true)
    {
        //result = false; //We should have already failed here
        errorMessages.Add(Environment.NewLine + AddLeftPad( callContextBpmData.Character01, '\t', 1) );
        callContextBpmData.Character01 = "";
    }

    //Status Messages
    if(callContextBpmData.Checkbox02 == true && !String.IsNullOrEmpty(callContextBpmData.Character02) == true)
    {
        errorMessages.Add(Environment.NewLine + AddLeftPad( callContextBpmData.Character02, '\t', 1) );
        callContextBpmData.Character02 = "";
    }

    //Debug Messages
    if(callContextBpmData.Checkbox03 == true && !String.IsNullOrEmpty(callContextBpmData.Character03) == true)
    {
        errorMessages.Add(Environment.NewLine + AddLeftPad( callContextBpmData.Character03, '\t', 1) );
        callContextBpmData.Character03 = "";
    }
    
    
    returnResult = result;
    returnErrorMessages = JsonConvert.SerializeObject(errorMessages, Formatting.None);

    //For Standalone Use (You Wire it Up, Client Customization Etc)
    returnCallContextBpmDataRow = JsonConvert.SerializeObject(callContextBpmData, Formatting.None);

BAQDynamicParameters.DynamicParametersStatus

/**************************************************************
* Epicor Function
* Library: BAQDynamicParameters
* Name: DynamicParametersStatus
* Description: Status,Error, & Debug Messages
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/

    Func<string, char, int, string> AddLeftPad = (textToPad, padChar, numberOfChars) =>
    {
        string returnString = "";
        List<string> stringLines = textToPad.Split("\n").ToList();
        foreach(string stringPiece in stringLines)
        {
            returnString += new string(padChar, numberOfChars) + stringPiece + Environment.NewLine;
        }
        return returnString;
    };

    //Add The Status And Error Information To The Results DataSet Execution Info 
      
    if(callContextBpmData.Checkbox02 == true && !String.IsNullOrEmpty(callContextBpmData.Character02) == true)
    {
        DataTable errorsTable = result.Tables["Errors"];
        errorsTable.Rows.Add("Status Messages", Environment.NewLine + AddLeftPad( callContextBpmData.Character02, '\t', 1) );
        callContextBpmData.Character02 = "";
    }
    
    if(callContextBpmData.Checkbox01 == true && !String.IsNullOrEmpty(callContextBpmData.Character01) == true)
    {
        DataTable errorsTable = result.Tables["Errors"];
        errorsTable.Rows.Add("Error Messages", Environment.NewLine + AddLeftPad( callContextBpmData.Character01, '\t', 1) );
        callContextBpmData.Character01 = "";
    }
    
    if(callContextBpmData.Checkbox03 == true && !String.IsNullOrEmpty(callContextBpmData.Character03) == true)
    {
        DataTable errorsTable = result.Tables["Errors"];
        errorsTable.Rows.Add("Debug Messages", Environment.NewLine + AddLeftPad( callContextBpmData.Character03, '\t', 1) );
        callContextBpmData.Character03 = "";
    }

    returnResult = result;
    
    //For Standalone Use (You Wire it Up, Client Customization Etc)
    returnCallContextBpmDataRow = JsonConvert.SerializeObject(callContextBpmData, Formatting.None);

[klincecum (Kevin Lincecum)]

The Method Directives

Pre-Processing
Ice.BO.DynamicQuery… [Generic]

/**************************************************************
* Method Directive
* Method Code: Ice.BO.DynamicQuery... [Generic]
* Method Type: Pre-Processing
* Name: AddDynamicParameters
* Description: Adding Variables To BAQs
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/
  
  //Only proceed if we have a BAQ with a SubQuery named "Variables"
  if(queryDS.QuerySubQuery.Any(subQuery => subQuery.Name.ToLower() == "variables") == true)
  {
      try                    
      {
          //The Meat - Calls A Function and Does the Dirty Work
          InvokeFunction("BAQDynamicParameters", "AddDynamicParameters", queryDS, executionParams);
          
          //The Potatoes - This is Debug Info
          //    callContextBpmData is populated in the function, it is available here and passed down by default so we can pick it up in post processing
          //        callContextBpmData.Character01 is Error  Info
          //        callContextBpmData.Character02 is Status Info
          //        callContextBpmData.Character03 is Debug  Info
      }
      catch (Exception ex)
      {
          string errorInfo = callContextBpmData.Character01;
          string errMessage = ex.Message;
          
          if(ex.InnerException != null) errMessage        += $"{Environment.NewLine}{ex.InnerException.Message}";
          if(ex.Data.Contains("UserMessage")) errMessage  += $"{Environment.NewLine}{ex.Data["UserMessage"].ToString()}";
          if(!String.IsNullOrEmpty(errorInfo)) errMessage += Environment.NewLine + String.Join(Environment.NewLine, errorInfo);
          
          InfoMessage.Publish(errMessage); MarkCallCompleted(); //We dun messed up, exit.
      }
  }//End Processing

Pre-Processing
Ice.BO.DynamicQuery.ExecuteByID

/**************************************************************
* Method Directive
* Method Code: Ice.BO.DynamicQuery.ExecuteByID
* Method Type: Pre-Processing
* Name: AddDynamicParameters
* Description: Adding Variables To BAQs (Redirect to Execute)
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/
      
  using (DynamicQuerySvcContract dQ = Ice.Assemblies.ServiceRenderer.GetService<DynamicQuerySvcContract>(Db))
  {
      DynamicQueryTableset queryDS = null;
      
      try { queryDS = dQ.GetByID(queryID); } catch {} //There are hardcoded BAQs that don't exist and are hardcoded, this assures we don't error out here.
      
      //Only proceed if we have a BAQ with a SubQuery named "Variables"
      if(queryDS != null && queryDS.QuerySubQuery.Any(subQuery => subQuery.Name.ToLower() == "variables") == true)
      {
          try                    
          {
              //Redirect to Ice.BO.DynamicQuery.Execute              
              result = dQ.Execute(queryDS, executionParams);
              
              MarkCallCompleted(); //Do Not Continue, Ice.BO.DynamicQuery.Execute is in control now
          }
          catch (Exception ex)
          {
              string errMessage = ex.Message;
              if(ex.InnerException != null) errMessage += $"{Environment.NewLine}{ex.InnerException.Message}";
              InfoMessage.Publish(errMessage);
              MarkCallCompleted(); //We dun messed up, exit.
          }
      }//End Processing
  }

Post-Processing
Ice.BO.DynamicQuery… [Generic]

/**************************************************************
* Method Directive
* Method Code: Ice.BO.DynamicQuery... [Generic]
* Method Type: Post-Processing
* Name: DynamicParametersStatus_Generic
* Description: Status,Error, & Debug Messages
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/


  //Only proceed if we have a BAQ with a SubQuery named "Variables"
  if(queryDS.QuerySubQuery.Any(subQuery => subQuery.Name.ToLower() == "variables") == true)
  {
      try
      {
          object[] returnObject = InvokeFunction("BAQDynamicParameters", "DynamicParametersStatus", result);
    
          result = (DataSet)returnObject[0];
      }
      catch (Exception ex)
      {
          DataTable errorsTable = result.Tables["Errors"];
          errorsTable.Rows.Add("Debug Messages", Environment.NewLine + "\tError Calling Function: BAQDynamicParameters.DynamicParametersStatus-Analyze");
      }
  }

Post-Processing
Ice.BO.DynamicQuery.Analyze

/**************************************************************
* Method Directive
* Method Code: Ice.BO.DynamicQuery.Analyze
* Method Type: Post-Processing
* Name: DynamicParametersStatus_Analyze
* Description: Status,Error, & Debug Messages
*
* Author: Kevin Lincecum
* Contact: klincecum @ <moc.smlifesm> <--backwards lol
**************************************************************/


  //Only proceed if we have a BAQ with a SubQuery named "Variables"
  if(queryDS.QuerySubQuery.Any(subQuery => subQuery.Name.ToLower() == "variables") == true)
  {
      try
      {
          object[] returnObject = InvokeFunction("BAQDynamicParameters", "DynamicParametersStatus-Analyze", JsonConvert.SerializeObject(errorMessages, Formatting.None), result);
    
          errorMessages = JsonConvert.DeserializeObject<List<string>>(returnObject[0].ToString());
          result = (bool)returnObject[1];
      }
      catch (Exception ex)
      {
          errorMessages.Add("Error Calling Function: BAQDynamicParameters.DynamicParametersStatus-Analyze");
      }
  }

Deliverables:

Solution: (Everything You need) BAQDynParams_Customer Solution_4.2.200.0.cab (241.1 KB)

If you want individual files, here are the functions, method directives, and example query.

Functions (Library): BAQDynamicParameters.efxb (23.7 KB)

Functions (Individual)
Function_BAQDynamicParameters_AddDynamicParameters.cs (14.1 KB)
Function_BAQDynamicParameters_DynamicParametersStatus.cs (2.1 KB)
Function_BAQDynamicParameters_DynamicParametersStatus_Analyze.cs (2.5 KB)

Method Directives:
MD_Post_DynamicParametersStatus_Analyze.cs (1.1 KB)
MD_Post_DynamicParametersStatus_Generic.cs (1.0 KB)
MD_Pre_AddDynamicParameters_ExecuteByID.cs (1.6 KB)
MD_Pre_AddDynamicParameters_Generic.cs (1.8 KB)

Example Query: KEV_DQ_DynamicParameters.baq (22.1 KB)

Enjoy

[JasonMcD (Jason McDermott)]

My head hurts, but bravo. Pretty fascinatng.

@klincecum

OK, Fine → I was looking for a way to get out of work that wouldn’t make me feel so guilty.

No comment.

[klincecum (Kevin Lincecum)]

Ok code is posted.

I’ll post up the files and probably a solution file as well when I have a minute.

I’ll also include an Example BAQ, that has everything set up so y’all can test.

I got a couple other secret projects to post in the not too distant future as well.

[klincecum (Kevin Lincecum)]

@JasonMcD

My head hurts

Code is up now, go learn something Mr. Low Code…

[dr_dan (Dan Ramirez)]

Thanks for posting this. I actually had a similar thought generated from that same post… I stumbled upon it when I was looking for a way to use a parameter that was automatic (like current date). And I found I could hijack it by setting the paramter default using a BPM in a similar fashion. I want to try your version out, though, because I like how it incorporates into the BAQ and it’s a one time setup to check for that subquery.

[JasonMcD (Jason McDermott)]

@klincecum

#kittens is all yours, eh?

[klincecum (Kevin Lincecum)]

@JasonMcD

kittens is all yours, eh?

I’ve been outed!

[klincecum (Kevin Lincecum)]

@klincecum

Deliverables:

Solution: (Everything You need) BAQDynParams_Customer Solution_4.2.200.0.cab (241.1 KB)

If you want individual files, here are the functions, method directives, and example query.

Functions (Library): BAQDynamicParameters.efxb (23.7 KB)

Functions (Individual)
Function_BAQDynamicParameters_AddDynamicParameters.cs (14.1 KB)
Function_BAQDynamicParameters_DynamicParametersStatus.cs (2.1 KB)
Function_BAQDynamicParameters_DynamicParametersStatus_Analyze.cs (2.5 KB)

Method Directives:
MD_Post_DynamicParametersStatus_Analyze.cs (1.1 KB)
MD_Post_DynamicParametersStatus_Generic.cs (1.0 KB)
MD_Pre_AddDynamicParameters_ExecuteByID.cs (1.6 KB)
MD_Pre_AddDynamicParameters_Generic.cs (1.8 KB)

Example Query: KEV_DQ_DynamicParameters.baq (22.1 KB)

Enjoy

Files are posted above.

[klincecum (Kevin Lincecum)]

Anybody ever try it?

[Chrisw (Chris Were)]

I’m going to have to read it again tonight to follow what you are doing, but it sounds like a solution to eactly the issue I was hoping to solve:

A 3 level dashboard, customer, projects and cases:

Projects are to be filtered by selected customer, cases either by selected customer or project. I’ve got over 20,000 cases so was concerned about speed if the case BAQ downloaded all the cases then filtered out for display what it needed.
I’m a bit of a newbie so thought “simple, use a BAQ parameter to pass the selected customer or project as the parameter”. Tried it and of course every time I select a customer in my top grid I get asked to again select a customer interactively; not what I had intended.

As it is currently the dash is a little sluggish, and I’ve decided to limit my BAQ to the top 5000 most recent cases for performance reasons. I expect If I can cut off the unwanted cases at the BAQ level things should hum, and it looks like this might be the answer.

Footnote:
I would have thought there would be an easy, built-in way to do this.
If there is, somebody please stop me, before my head explodes trying to get to grips with Kevin’s work here!

[klincecum (Kevin Lincecum)]

@Chrisw

I would have thought there would be an easy, built-in way to do this.

Unfortunately not.

That being said, there are certain use cases where you could do other things.
Read the thread by @JasonMcD where we discussed it.

I did provide a solution file if you want to one-shot it. I’m hoping it’s plug and play.
If not, I will troubleshoot.

[klincecum (Kevin Lincecum)]

@Chrisw

before my head explodes trying to get to grips with Kevin’s work

It’s actually pretty simple, most of the code is just putting it all the common places, and
their associated differences, and some status features, and to make entering variables as
painless as possible.

[klincecum (Kevin Lincecum)]

@Chrisw

I’m a bit of a newbie so thought “simple, use a BAQ parameter to pass the selected customer or project as the parameter”. Tried it and of course every time I select a customer in my top grid I get asked to again select a customer interactively; not what I had intended.

After reading this, it sounds more like you are wanting silent parameters, than variables.
Is that correct?

[Chrisw (Chris Were)]

yes; I am just wanting the case BAQ to filter at the query level to cases for the selected customer.

Having said that, the distinction between “variables” and “parameters” is not clear to me; but certainly have used parameters in SQL queries in the past and the use case here is analogous.

[klincecum (Kevin Lincecum)]

Why yes, they are the same thing at the base level.

My distinction in my question was more along were these parameters to be static and hardcoded into the baq, or to be populated via dashboard in some fashion?

[Chrisw (Chris Were)]

Populated by the dashboard.

Currently I have this structure, to which I’d like to maybe add customer contacts also, if it can be done effifciently:

(machines are projects to us)

So here the Cases for Customer and Cases for Machine are currently filtering (cases for customer):

I am assuming this is inefficient with the grid level filtering, unless epicor is clever enough to pass the filter up.
But then I’m also wondering at which point all these views actually get populated. Do they all get loaded whether visible or not? I am creating a Kinetic app at the end of the day so only one is visible at a time, selected by the View combo.
(All Cases can probably get dumped and go into its own, separate be-slow-if-you-like dashboard)

Anyway, assuming epicor isn’t super smart with passing the grid filters up to the baq query level automatically, I thought I’d put an afterRowChanged event my customer views, and in that populate a parameter/variable in the cases query from there (similarly for machines) that would rerun the query, and i’d then get rid of my redundant grid filtering.

Having said that I’ve spent most of the day trying to get my head around Kinetic through the many and varied threads here; maybe I’m being over ambitious. Super helpful stuff, but so much I don’t yet get; my frazzled brain is going “[BPM].erp-rest’??{Transview.whaaaat}{BAQ,0}'erp.ice.bo.service@help!”

But don’t quote me on that.

[JasonMcD (Jason McDermott)]

@Chrisw

the distinction between “variables” and “parameters” is not clear to me

Parameters are built in to BAQs:

And they work great in (classic) dashboards:

But variables… don’t really exist in Epicor as @klincecum said.

It seems like this a wheel you are reinventing…

[Chrisw (Chris Were)]

The wheel seems a bit square, as you cannot set parameters silently through code/application studio. Or rather you can set them, but every time the BAQ parameterised query runs, it wants to interactively (and redundantly) ask the user for the parameters via a slider panel (at least in Kinetic, at least as far as I understand it).

I think Kevin’s project here is a workaround for that.