# Join BAQ and external BAQ in same datagrid?

**Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5)
**Created:** 2016-11-17 09:48 UTC
**Views:** 4528
**Replies:** 6
**URL:** https://www.epiusers.help/t/join-baq-and-external-baq-in-same-datagrid/36331

---

## Post #1 by @bmanners

Is it possible to join data from a BAQ and an External BAQ and display it in a datagrid? 

I have some data in an external dB that I want to display alongside some Epicor data.

thanks
Brett

---

## Post #2 by @josecgomez

Can't be done. You have to create a view on your external db that points to the epicor tables. Then bring them in with your external stuff

---

## Post #3 by @prakash


just some pointers @bmanners, 
probably..excute the baq using adapters, and export the data to a shared location in csv format. lets call that csv file as A. 
for external db data, run your query, export the data and merge it with the file A.
now on the epicor grid initalise event, read the csv file A, display the data.

---

## Post #4 by @bmanners

I ended up doing this as a customisation, on the Part Tacker in this case. I added a new tab and put a grid on it. Pulled the internal and external data using a DynamicQueryAdaptor and mushed them together into a new DataTable in C#. Finally I bound the DataSource for the grid to the new DataTable.

See video from Jose here on how to pull the data. https://www.youtube.com/watch?v=8u0504kqLM8

Cheers
Brett

---

## Post #5 by @A.Baeisa

Hi @bmanners,
is it possible to share your customisation code, just want to see how did you mushed the two data together into one table, thanks in advanced mate.

---

## Post #6 by @bmanners

Sure. I will extract the key parts for you here as the whole code has unrelated stuff that will just confuse things. Hopefully I haven't omitted anything important.

I placed button on the screen, labeled it Retrieve and triggered the code from that.

First I get the Epicor data using a BAQAdaptor.

    			// go get data for indented BOM for spares list
    			var BAQAdaptor = new DynamicQueryAdapter(oTrans);
    			BAQAdaptor.BOConnect();
    			// define BAQ parameters
    			var QEDS = new QueryExecutionDataSet();
    			QEDS = BAQAdaptor.GetQueryExecutionParametersByID("IndentedBOM");
    			QEDS.ExecutionParameter.Clear();
    			QEDS.ExecutionParameter.AddExecutionParameterRow("PartNum", partnum, "nvarchar", false, Guid.NewGuid(), "A");
    			// execute BAQ
    			BAQAdaptor.ExecuteByID("IndentedBOM", QEDS);
    			var indentedBOMResults = BAQAdaptor.QueryResults.Tables["Results"].Copy();


Next  I get the data from the external system into a DataTable. re-using the same BAQAdaptor here. The dataTable used (FikOnHandResults) is declared as a class level field and new'd up in the form load event. Not sure why I did it that way but that's why it is not declared in the code below.

			// Get Fik On Hand data using external BAQ
			BAQAdaptor.ExecuteByID("FikOnHand");
			FikOnHandResults = BAQAdaptor.QueryResults.Tables["Results"].Copy();


In my case I had to so some additional processing to the data. This really should be done in the BAQ, not sure why i did it here.  I made a dictionary and filled it with the data I need. You probably don't need to do this.

			// Summarise Fik On hand results
			var fikOnHandResultsSummary = new ConcurrentDictionary<string, double>();
			foreach (DataRow row in FikOnHandResults.Rows)
			{
				var currentpart = row["STORES-LINES_PARTNO"].ToString();
				var rowqty = (double) row["STORES-LINES_QTY"];
				fikOnHandResultsSummary.AddOrUpdate(currentpart, rowqty, (id, count) => count + rowqty);
			}


Next I setup a DataTable for the merged data. Copy the Epicor data in and add new columns for data from the external system.

			// Build a single DataTable to hold the combined results
			var mergedDatatable = indentedBOMResults.Copy();
			mergedDatatable.Columns.Add("Fik OnHand Qty", typeof(double));
			mergedDatatable.Columns.Add("Fik Prod. Qty", typeof(double));
			mergedDatatable.Columns.Add("Fik Offshore Qty", typeof(decimal));



Now step through each row in the Merged DataTable and add in the data from the external system. 

			// Update the onhand  Qty for each part into MergedDataTable
			foreach (DataRow sparePartRow in mergedDatatable.Rows)
			{
				var part = (string)sparePartRow["PartMtl_MtlPartNum"];

				// Update Fik onhand qty.
				double fikOnhandQty;
				fikOnHandResultsSummary.TryGetValue(part, out fikOnhandQty);
				sparePartRow["Fik OnHand Qty"] = fikOnhandQty;
			}

Next create a DataView. I also needed to do some filtering so that is done here along with removing some unwanted columns that came from the Epicor data. 

			// Filter out rows that are phantom BOM's
			var filteredView = new DataView();
			filteredView.Table = mergedDatatable;
			filteredView.RowFilter = "Part_PhantomBOM = 0";
					

			// remove unwanted columns
			mergedDatatable.Columns.Remove("PartMtl_Company");
			mergedDatatable.Columns.Remove("Part_PhantomBOM");
			mergedDatatable.Columns.Remove("PartMtl_PartNum");
			mergedDatatable.Columns.Remove("PartMtl_MtlSeq");
			mergedDatatable.Columns.Remove("Calculated_Indent");
			mergedDatatable.Columns.Remove("Calculated_Hierarchy");

			
Finally set the datasource of the grid to the dataView created eralier.

			GridSpares.DataSource = filteredView;

There we have it, data from 2 different systems in the same dataGrid.

Hope this helps
Brett

---

## Post #7 by @A.Baeisa

many thanks @bmanners, really appreciate your generosity

---


**Canonical:** https://www.epiusers.help/t/join-baq-and-external-baq-in-same-datagrid/36331
**Original content:** https://www.epiusers.help/t/join-baq-and-external-baq-in-same-datagrid/36331