Dashboard - Help - Linq SUM

Epicor ERP 10
,
1

Hello all,

Please can someone help or atleast start of the code I will need to use to get this working? I can’t seem to figure it out or even get started.

I have this list based on a date selection or by ticking “Today” on a dashboard. I would like to gather the dashboard generated list. I’m guessing it’s just a filter or does the dashboard actually ask the BAQ to filter the records?

image
image1913×1049 87.8 KB

I hope this makes sense, I know you can summerise the two fields using the dashboard but can you pick up the sum totals and use them in code?

Kind regards,
Aaron.

2

Are the sums that you are picking always a single day? if they are, then you can use windowing functions to do the math in a calculated field.

(Sum(field1) over (partition by dateField))/(Sum(field2) over (partition by dateField))

This will give you a column with the efficiency in that day for each row.

3

Hi Banderson,

Thank you I was able to achieve this but I need the efficiency for the row selection. If 04/04/2022 to 11/04/2022 was 20 rows. I need to summerise the minutes and divide them then add the final figure to a CallContext field.

Any ideas? I’m guessing I need to loop through the visible rows using ListChange event but I’m not sure of the code to use.

4 
             EpiUltraGrid yourGrid= (EpiUltraGrid)csm.GetNativeControlReference("GridGuidHere")
            foreach (var row in yourGrid.Rows)
            {
                //do stuff here
            }
1 Like
5

Thank you, can I select and sum up a single column then fill a CallContext field with the summed value?

6

Yes you can. Set a variable, add to it in the loop, then after the loop set the callContext field.

1 Like
7

Thank you.

I am probably being so stupid but are you able to show me? Once I get a rough idea i will be ok.

Thank you and sorry for taking up some of your time.

8

Something like that may work, hope it helps.

	private void edvV_Transaction_BAQ_1View_EpiViewNotification(EpiDataView view, EpiNotifyArgs args)
	{
		// ** Argument Properties and Uses **
		// view.dataView[args.Row]["FieldName"]
		// args.Row, args.Column, args.Sender, args.NotifyType
		// NotifyType.Initialize, NotifyType.AddRow, NotifyType.DeleteRow, NotifyType.InitLastView, NotifyType.InitAndResetTreeNodes
		if ((args.NotifyType == EpiTransaction.NotifyType.Initialize))
		{
			if ((args.Row > -1))
			{
				EpiUltraGrid grid = (EpiUltraGrid)csm.GetNativeControlReference("0bf0b691-0667-499c-a0d1-1d8341d2a69d");		
				decimal sum = 0;

				foreach(UltraGridRow row in grid.Rows)
				    {
				        sum += Convert.ToDecimal(row.Cells["Calculated_RunQty"].Value);
				    }
				txtTotal.Value = sum;
				}
			}
		}
	}
1 Like
9

Love this!

I cannot seem to get the two variables to divide, any ideas?

			EpiUltraGrid grid = (EpiUltraGrid)csm.GetNativeControlReference("303bb63f-021a-46f9-8cb4-5c7ce101afbf");		
				decimal estMins = 0;
				decimal actMins = 0;

				foreach(var row in grid.Rows)
				    {
				        estMins += Convert.ToDecimal(row.Cells["Calculated_EarnedMinutes"].Value);
						actMins += Convert.ToDecimal(row.Cells["Calculated_LabourRecordedMins"].Value);
						
				    }
				txtTotal.Value = Decimal.Divide(actMins, estMins);
	}

I have tired actMins / estMins but just comes up with a blank text box but if I remove the divide and just display one of the variables… Works perfectly.

Bit confused…

10

Maybe try a cast before each item in the division.

(Decimal)actMins / (decimal)estMins
Or
(Double)actMins / (Double)estMins

1 Like
11

Make sure that txtTotal is EpiTextBox.

image
image1799×428 104 KB 

private void edvV_Transaction_BAQ_1View_EpiViewNotification(EpiDataView view, EpiNotifyArgs args)
	{
		// ** Argument Properties and Uses **
		// view.dataView[args.Row]["FieldName"]
		// args.Row, args.Column, args.Sender, args.NotifyType
		// NotifyType.Initialize, NotifyType.AddRow, NotifyType.DeleteRow, NotifyType.InitLastView, NotifyType.InitAndResetTreeNodes
		if ((args.NotifyType == EpiTransaction.NotifyType.Initialize))
		{
			txtTotal.Value = 0.ToString("P2");			

			if ((args.Row > -1))
			{
				EpiUltraGrid grid = (EpiUltraGrid)csm.GetNativeControlReference("0bf0b691-0667-499c-a0d1-1d8341d2a69d");		
				decimal a = 0;
				decimal b = 0;

				foreach(UltraGridRow row in grid.Rows)
				    {
				        a += Convert.ToDecimal(row.Cells["Calculated_DoneQty"].Value);
						b += Convert.ToDecimal(row.Cells["Calculated_RunQty"].Value);
				    }
				txtTotal.Value = div_result(a,b).ToString("P2");
				}
			}
		}
	
	public decimal div_result(decimal a, decimal b)
	{
		if (a > 0 && b > 0)
		{
	    return a/b;
		}
		return 0;
	}
}