Custom grid in tracker

I have created a baq that uses 4 fields basically a vendor ID two dates and displays invoice numbers to keep it simple it displays a few more but those are irrelevant. When you run the baq it runs another baq that that populates the vendor IDs in the first drop down, then it has a start and end date field where the user can put a range of dates to filter invoice that match the supplier ID. I would like to use this baq to populate a grid in an existing tracker I created a new grid inside a new form inside the existing tracker called supplier tracker in the accounts payable module. What is the most logical way to run this query and match the vendor ID to the supplier the tracker is tracking/subscribed to, and display in the grid I have a button that I have used some code but it does not work:

Blockquote
// **************************************************
// Custom code for VendorForm
// Created: 3/9/2022 9:33:42 AM
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.UI;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.Adapters;
using Ice.BO;

public class Script
{
// ** Wizard Insert Location - Do Not Remove ‘Begin/End Wizard Added Module Level Variables’ Comments! **
// Begin Wizard Added Module Level Variables **

// End Wizard Added Module Level Variables **

// Add Custom Module Level Variables Here **
Ice.Lib.Framework.EpiUltraGrid dateRangeGrid;
public void InitializeCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
	// Begin Wizard Added Variable Initialization

	// End Wizard Added Variable Initialization

	// Begin Wizard Added Custom Method Calls
	this.btnAddDates.Click += new System.EventHandler(this.btnAddDates_Click);
	// End Wizard Added Custom Method Calls
	//dateRangeGrid = (Ice.Lib.Framework.EpiUltraGrid)csm.GetNativeControlReference ("92393695-0603-49cb-8ad3-5f91647c0dc7");
	//dateRangeGrid.DipslayLayout.Bands(0).Columns.Add("InvoiceDate", "Invoice Date");
	//dateRangeGrid.InitizializeRow += new Infragistics.Win.UltraWinGrid.InitializeRowEventHandler(grdMatLst_InitializeRow);
}
private void grdMatLst_InitializeRow(Object sender, Infragistics.Win.UltraWinGrid.InitializeRowEventArgs e)
{
}


public void DestroyCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
	// Begin Wizard Added Object Disposal
	this.btnAddDates.Click -= new System.EventHandler(this.btnAddDates_Click);
	// End Wizard Added Object Disposal

	// Begin Custom Code Disposal

	// End Custom Code Disposal
}

private void btnAddDates_Click(object sender, System.EventArgs args)
{
	// ** Place Event Handling Code Here **
	DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
	dqa.BOConnect();
	QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("JBIS-SupInvLkUpDates");
	qeds.ExecutionParameter.Clear();
	qeds.ExecutionParameter.AddExecutionParameterRow("@FromDate", dtStartDate.ToString() , "date",false, Guid.NewGuid(),"d");
	qeds.ExecutionParameter.AddExecutionParameterRow("@ToDate", dtEndDate.ToString() , "date",false, Guid.NewGuid(),"d");
	ugInvRange.DataSource = dqa.QueryResults.Tables["Results"];
}

}

Blockquote

Made some changes and still nothing shows up in the grid the grid btw is unbound… But if I bind it to an existing bind say in another tab that uses a query called invAll it looks like it tries to run but only one row is returned and I don’t believe it is correctly looking for the invoice dates between the two dates I entered in the epidatetimeeditor:

Blockquote
private void btnAddDates_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
dqa.BOConnect();
QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID(“JBIS-SupInvLkUpDates”);
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow(“FromDate”, dtStartDate.Text, “date”,false, Guid.NewGuid(),“d”);
qeds.ExecutionParameter.AddExecutionParameterRow(“ToDate”, dtEndDate.Text, “date”,false, Guid.NewGuid(),“d”);
dqa.ExecuteByID(“JBIS-SupInvLkUpDates”,qeds);
ugInvRange.DataSource = dqa.QueryResults.Tables[“Results”];
}

OK that actually worked had to remove a case statement from the query to show dates between the two dates, the only thing I need now is to assign the vendorNum to this query so that it only shows the invoices from this vendor ID from the main dock.

I have a video on YouTube hiw to replace a grid on a screen and use publish and subscribe via BAQDataView
Take a look at that one

1 Like

What I ended up doing was the following made 2 baqs
1.to filter the VendorIDs from the vendor table.
2. to filter the Invoices based on dates I made with parameters
3. made a custom sheet added two epidatetime fields, a button and a text field for the vendor id to be passed from the initial vendor id the customer selects from the main tracker then I hid that field after I verified it was updating within the sheet. (I know its probably not the best way to pull the vendorID in and feels like a hack job, but it gets it done and wouldn’t work when I tried to use the field from another sheet.)
used videos from @josegomez above^ and Calling BAQ from Code Epicor ERP 10 - YouTube