BAQ to Replicate Excel Formula

Epicor ERP 10
, ,
1

I am trying to create a BAQ with a calculated field that does this Excel Formula. I want it to count when the field is the same and reset the count when the field is different. Is this possible in a BAQ?
image

2

Use a case statement inside your count or sum aggregate function. I am assuming you understand that to do this you need to check the Group By checkbox in the Display tab for the other columns in your query.

Example:

sum(
  case
    when Column1 = 'this'
    then 1
    else 0
  end
)
1 Like
3

Sorry I wasn’t very clear.

I want to keep the sort order from the baq and count them in the order they appear.

This is for our allocation process as I want the count to represent the order in which the Sales Order will be allocated, and not an aggregate of all orders with that part.

Thank you for the suggestion!

4

@tgeels I would make the baq updatable and do this postprocessing on getlist with something like this.


int count = 1;
string myPart = string.Empty;

foreach (var ttr in ttResults)
{

	if (myPart = ttr.Part_PartNum)
	{
		count ++;
	}
	else
	{
		count = 1;
	}

	myPart = ttr.Part_PartNum;
	
	ttr.Calculated_Count = count;

}
2 Likes
5

Still need more info. See Row_Number … Over or Rank in sql.

6

@gpayne Thank you for the tip! I tried using your code, however the calculated fields aren’t available to be referenced. I’ve created the calculated fields and tried making those fields updatable but still aren’t showing up in the custom code editor. Any ideas as to what I’m missing?

7

@tgeels It is possible Count is a reserved word. If you want to upload the baq I can take a look.

8

It’s working now…not sure what finally got it to show up. The code you provided was exactly what I was looking for and works perfect! Thank you!!

1 Like
9

I agree with Loius, Count (RowNum ) over ( column to count on) as Calculated field. ( Syntax off the top of my head). Update-able dashboard way to complicated for simple count.

Dean