E9 700C: BAQDesignerAdapter (E9)vs. DynamicQueryAdapter (Vantag


(system) #1
FYI
I did get my example to handle the "no row.." condition - by enclosing it with a  "Catch, Try.."

I'd still be interested If anybody can explain how to check dynamic queryresults for the existence of a row.
I had tried a few without success.

 



---In vantage@yahoogroups.com, <bruce.d.ordway@...> wrote:

Hi,

I think I can add a condition that tests for the existence of a row in the dynamic query results?

But... I'm having a brainlock this afternoon.

Maybe someone can help? I suspect I'm missing something really obvious.


 

The sample code below is working if there is a row, but errors out if the BAQ results are empty.

I am using a BAQ that either returns one row or no rows.

I've been  try to display a result in a textbox instead of the grids.

I get a "no row.." error in the cases where my BAQ isn't returning anything.


'// Dynamic Query – E905 where clause testing

            Dim BaqID As [String] = "XXX-PartWhse"

            Dim adDynamic As New DynamicQueryAdapter(TimePhasForm)

            adDynamic.BOConnect()

            adDynamic.GetDashboardQuery(BaqID)

            Dim ds As Epicor.Mfg.BO.QueryDesignDataSet = adDynamic.RuntimeQuery

            Dim dt As DataTable = ds.Tables("QueryTable")

            If xPart > ""

            If xPlant > ""

           

                        For Each lo_row As DataRow In dt.Rows

                                    If lo_row("DataTableId").ToString() = "PartPlant" Then

                                                lo_row("WhereClause") = "PartPlant.PartNum = '" + xPart + "'and PartPlant.Plant = '" + xPlant + "'"

                                                Exit For

                                    End If

                        Next

            End If

            End if

            adDynamic.ExecuteDashboardQuery(ds)

                        ‘// The grids work, even when the BAQ doesn’t return a row

'// Place results of BAQ in the epiUltraGrid controls added to the form

                        '//epiUltraGridC1.DataSource = adDynamic.QueryResults.Tables(0)

                        '//epiUltraGridC2.DataSource = ds.Tables(0)

 

If xxxxxxxx ‘//how to determine if the BAQ returned a row to the dataset?

            rABC =  adDynamic.QueryResults.Tables(0).Rows(0)("PartWhse.SystemAbc").ToString()

            txtSystemABC.Text = rABC

            adDynamic.Dispose()

            xPart = ""

            xPlant = ""

            rABC = ""

 

else

 

adDynamic.Dispose()

            txtSystemABC.Text = ""

            xPart = ""

            xPlant = ""

            rABC = ""

 

 End IF



---In vantage@yahoogroups.com, <vantage@yahoogroups.com> wrote:

Here is the new way in Epicor ERP (E9) to get a BAQ, manipulate it (add whereClause), and run it. I got this from Epicor Support. I have not implemented it yet, but the code looks sound.

Dan


PROBLEM:

Custom code that calls the AddWhereItem method from the Dynamic Query Adapter will not work after 9.05.605.

This was reported to development on CR 11055ESC. Development did not assign an SCR to this issue. They indicated that there are two different business objects to consideration. They are the BAQDesigner and DynamicQuery business objects. The BAQDesigner business object does have a method AddWhereItem which is used to add a where clause in the Phase Builder. This method is supported in the current and future releases of the product.



However in the DynamicQuery business object, which is used to execute a BAQ, the AddWhereItem method was obsolete as of 9.05 and will be removed from the product permanently with ICE 3.0. The business object now expects the where clause to be passed through the QueryTable row.



To clarify the following code was obsoleted in 9.05:

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();

adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);



It was replaced by:
adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];

foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}



//Here is the script code that development gave us that worked:
private static void ProjectEntryForm_Load(object sender, EventArgs args)
{

// Add Event Handler Code
DynamicQuery();
}



private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];


foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}

// Return records base on BAQ using whereclause
adDynamic.ExecuteDashboardQuery(ds);

// Place results of BAQ in the epiUltraGridC1 control that was added to the form
epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];

// Place string passed to DynamicQuery using the Query Table row into epiUltraGridC2
epiUltraGridC2.DataSource=ds.Tables[0];
}




Here is the customer's DynamicQuery subroutine that used the older format and is obsolete in 9.05.

Private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
QueryDesignDataSet ds;

// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "UD16";
String pcField = "KEY1";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;

// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.

DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

// this would be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);

adDynamic.Execute(ds);

epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];
epiUltraGridC2.DataSource=ds.Tables[0];
}

(system) #2
I have a customization that utilizes the DynamicQueryAdapter in Vantage 8.03.409C. When I tried to validate the customization in E9, it errored saying that the AddWhereItem function in the DynamicQueryAdapter is obsolete and that QueryDesignData DataSet is now a Public ReadOnly Property.

Does anyone have any examples in E9 on how to call up a BAQ, add some conditions, and execute the query? I want the same functionality as in Vantage, meaning that I want to add conditions but not permanently change the BAQ. I have searched through the ICE tools manual and found no examples. I have a call in but I need to keep moving.

Following are two errors:
'Public Sub AddWhereItem(ds As Epicor.Mfg.BO.QueryDesignDataSet, pcQueryID As String, pcTable As String, pcField As String, pcCondition As String, pbIsConst As Boolean, pcValue As String, pcToTable As String, pcToField As String, pcLeftParentheses As String, pcRightParentheses As String, pcAndOr As String, pbNeg As Boolean)' is obsolete: 'Use BAQDesigner class method instead'



'Public ReadOnly Property QueryDesignData() As Epicor.Mfg.BO.QueryDesignDataSet' is obsolete: 'The instance QueryDesignData is not valid. For design, use BAQDesigner.BAQDesignerData, for execution, use RuntimeQuery'

(system) #3
Here is the new way in Epicor ERP (E9) to get a BAQ, manipulate it (add whereClause), and run it. I got this from Epicor Support. I have not implemented it yet, but the code looks sound.

Dan


PROBLEM:

Custom code that calls the AddWhereItem method from the Dynamic Query Adapter will not work after 9.05.605.

This was reported to development on CR 11055ESC. Development did not assign an SCR to this issue. They indicated that there are two different business objects to consideration. They are the BAQDesigner and DynamicQuery business objects. The BAQDesigner business object does have a method AddWhereItem which is used to add a where clause in the Phase Builder. This method is supported in the current and future releases of the product.



However in the DynamicQuery business object, which is used to execute a BAQ, the AddWhereItem method was obsolete as of 9.05 and will be removed from the product permanently with ICE 3.0. The business object now expects the where clause to be passed through the QueryTable row.



To clarify the following code was obsoleted in 9.05:

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();

adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);



It was replaced by:
adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];

foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}



//Here is the script code that development gave us that worked:
private static void ProjectEntryForm_Load(object sender, EventArgs args)
{

// Add Event Handler Code
DynamicQuery();
}



private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];


foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}

// Return records base on BAQ using whereclause
adDynamic.ExecuteDashboardQuery(ds);

// Place results of BAQ in the epiUltraGridC1 control that was added to the form
epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];

// Place string passed to DynamicQuery using the Query Table row into epiUltraGridC2
epiUltraGridC2.DataSource=ds.Tables[0];
}




Here is the customer's DynamicQuery subroutine that used the older format and is obsolete in 9.05.

Private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
QueryDesignDataSet ds;

// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "UD16";
String pcField = "KEY1";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;

// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.

DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

// this would be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);

adDynamic.Execute(ds);

epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];
epiUltraGridC2.DataSource=ds.Tables[0];
}

(system) #4

Hi,

I think I can add a condition that tests for the existence of a row in the dynamic query results?

But... I'm having a brainlock this afternoon.

Maybe someone can help? I suspect I'm missing something really obvious.


 

The sample code below is working if there is a row, but errors out if the BAQ results are empty.

I am using a BAQ that either returns one row or no rows.

I've been  try to display a result in a textbox instead of the grids.

I get a "no row.." error in the cases where my BAQ isn't returning anything.


'// Dynamic Query – E905 where clause testing

            Dim BaqID As [String] = "XXX-PartWhse"

            Dim adDynamic As New DynamicQueryAdapter(TimePhasForm)

            adDynamic.BOConnect()

            adDynamic.GetDashboardQuery(BaqID)

            Dim ds As Epicor.Mfg.BO.QueryDesignDataSet = adDynamic.RuntimeQuery

            Dim dt As DataTable = ds.Tables("QueryTable")

            If xPart > ""

            If xPlant > ""

           

                        For Each lo_row As DataRow In dt.Rows

                                    If lo_row("DataTableId").ToString() = "PartPlant" Then

                                                lo_row("WhereClause") = "PartPlant.PartNum = '" + xPart + "'and PartPlant.Plant = '" + xPlant + "'"

                                                Exit For

                                    End If

                        Next

            End If

            End if

            adDynamic.ExecuteDashboardQuery(ds)

                        ‘// The grids work, even when the BAQ doesn’t return a row

'// Place results of BAQ in the epiUltraGrid controls added to the form

                        '//epiUltraGridC1.DataSource = adDynamic.QueryResults.Tables(0)

                        '//epiUltraGridC2.DataSource = ds.Tables(0)

 

If xxxxxxxx ‘//how to determine if the BAQ returned a row to the dataset?

            rABC =  adDynamic.QueryResults.Tables(0).Rows(0)("PartWhse.SystemAbc").ToString()

            txtSystemABC.Text = rABC

            adDynamic.Dispose()

            xPart = ""

            xPlant = ""

            rABC = ""

 

else

 

adDynamic.Dispose()

            txtSystemABC.Text = ""

            xPart = ""

            xPlant = ""

            rABC = ""

 

 End IF



---In vantage@yahoogroups.com, <vantage@yahoogroups.com> wrote:

Here is the new way in Epicor ERP (E9) to get a BAQ, manipulate it (add whereClause), and run it. I got this from Epicor Support. I have not implemented it yet, but the code looks sound.

Dan


PROBLEM:

Custom code that calls the AddWhereItem method from the Dynamic Query Adapter will not work after 9.05.605.

This was reported to development on CR 11055ESC. Development did not assign an SCR to this issue. They indicated that there are two different business objects to consideration. They are the BAQDesigner and DynamicQuery business objects. The BAQDesigner business object does have a method AddWhereItem which is used to add a where clause in the Phase Builder. This method is supported in the current and future releases of the product.



However in the DynamicQuery business object, which is used to execute a BAQ, the AddWhereItem method was obsolete as of 9.05 and will be removed from the product permanently with ICE 3.0. The business object now expects the where clause to be passed through the QueryTable row.



To clarify the following code was obsoleted in 9.05:

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();

adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);



It was replaced by:
adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];

foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}



//Here is the script code that development gave us that worked:
private static void ProjectEntryForm_Load(object sender, EventArgs args)
{

// Add Event Handler Code
DynamicQuery();
}



private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];


foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}

// Return records base on BAQ using whereclause
adDynamic.ExecuteDashboardQuery(ds);

// Place results of BAQ in the epiUltraGridC1 control that was added to the form
epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];

// Place string passed to DynamicQuery using the Query Table row into epiUltraGridC2
epiUltraGridC2.DataSource=ds.Tables[0];
}




Here is the customer's DynamicQuery subroutine that used the older format and is obsolete in 9.05.

Private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
QueryDesignDataSet ds;

// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "UD16";
String pcField = "KEY1";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;

// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.

DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();

// this would be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);

adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;

// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with

pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);

adDynamic.Execute(ds);

epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];
epiUltraGridC2.DataSource=ds.Tables[0];
}