BAQ View in Grid - Issue with Subscribe

[Alice_Elizabeth (Alice Elizabeth Smith)]

Hello all,

I recently used this awesome video by Jose to get a BAQView into the Part Transaction History grid, using C#. And I know there’s probably a better way to link to it so forgive me but…

https://www.youtube.com/watch?v=FYLPgCbQSDc

I thought I had everything set up properly, and I can see the grid when I open the customizations, but the subscribe function doesn’t seem to work and I can’t pass the grid data from UI. It just spins.

Here is my code:

extern alias Erp_Contracts_BO_Part;
extern alias Erp_Contracts_BO_PartPlantSearch;

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;
using Ice.Lib.Broadcast;

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 **
	BAQDataView baqViewPO;
	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


		// End Wizard Added Custom Method Calls
		CreatePOBAQView();
	}

	public void CreatePOBAQView()
	{

		baqViewPO = new BAQDataView("Part_Transaction_History");
		oTrans.Add("ShowPOBAQ",baqViewPO);

		string pubBinding = "Part.PartNum";
		IPublisher pub = oTrans.GetPublisher(pubBinding);
			if(pub==null)
		{
			oTrans.PublishColumnChange(pubBinding, "MyCustomPublish");
			pub = oTrans.GetPublisher(pubBinding);
		}
		
		if (pub !=null)
			baqViewPO.SubscribeToPublisher(pub.PublishName, "PartTran_PartNum");
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
	}
}

Any obvious issues that anyone can help me with? I feel like I’m so close yet so far…

Thanks,

Alice

[Alice_Elizabeth (Alice Elizabeth Smith)]

And if anyone can guide me as to how to format my code better for forum - I think i may have it all distorted.

[Banderson (Brandon Anderson)]

It’s just 3 backticks (the key to the left of the 1 on the keyboard) before and after your code.

I updated your post for you, if you go to edit it, you can see where I added the 3 ticks. One line before your code started, and one line after your code started.

[josecgomez]

@Alice_Elizabeth

Part.PartNum

Are you sure there is a view called Part.PartNum in PartTransactionHistory? (I haven’t looked) now I looked… hmm yes there is.

[Alice_Elizabeth (Alice Elizabeth Smith)]

The grid function (w/ subscribe) worked for a bit. But then I went back to make some changes to BAQ since I was struggling with Running Total. Got that figured out and when I went back to the customization, I see the grid but no subscribe. Here is my query…

select 
	[PartTran].[TranDate] as [PartTran_TranDate],
	[PartTran].[TranType] as [PartTran_TranType],
	[PartTran].[TranQty] as [PartTran_TranQty],
	[PartTran].[UM] as [PartTran_UM],
	(SUM(SignedTranQTY) OVER (Order By PartTran.TranNum)) as [Calculated_RunningQOH],
	[PartTran].[InvtyUOM] as [PartTran_InvtyUOM],
	[PartTran].[JobNum] as [PartTran_JobNum],
	[PartTran].[PackNum] as [PartTran_PackNum],
	[PartTran].[OrderNum] as [PartTran_OrderNum],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[PartTran].[TranReference] as [PartTran_TranReference],
	[PartTran].[EntryPerson] as [PartTran_EntryPerson],
	[PartTran].[ActTranQty] as [PartTran_ActTranQty],
	((case  
     when PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1 
     when PartTran.TranType IN ('ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')  then 1 
     else 0
 end) * PartTran.TranQty) as [Calculated_SignedTranQTY],
	[PartTran].[PartNum] as [PartTran_PartNum]
from Erp.PartTran as PartTran
left outer join Erp.Customer as Customer on 
	PartTran.Company = Customer.Company
	and PartTran.CustNum = Customer.CustNum
left outer join Erp.Vendor as Vendor on 
	PartTran.Company = Vendor.Company
	and PartTran.VendorNum = Vendor.VendorNum
left outer join Erp.OrderHed as OrderHed on 
	PartTran.Company = OrderHed.Company
	and PartTran.OrderNum = OrderHed.OrderNum
order by PartTran.TranNum Desc

[Alice_Elizabeth (Alice Elizabeth Smith)]

I figured out my issue but can’t seem to resolve it - It is this calculated field in the BAQ that prevents subscribe function from working correctly:

I have the baq sorted by TranNum:

So I went and changed calculated field to order by TranNum DESC:

Now is hangs up and takes forever, but eventually loads.

Is there anything I can do with this calculated field (Running Total of Parts OH) to keep it from bogging down the grid?

Thanks,

Alice

[Alice_Elizabeth (Alice Elizabeth Smith)]

Here is the approach I used to calculate the Running Total:

PartTran BAQ: Calculating the "Running Total" of the History Tracker

Ignore the Before and After Qty’s. You’ll have to use some moderately tricky techniques in the BAQ to make a running sum. Something like a “Sum over Partiton” Search this site there’s some examples on running sum.

[Alice_Elizabeth (Alice Elizabeth Smith)]

Turns out my syntax was not quite right. Subscribe function works using:

SUM(SignTran) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)

Thank you all.