I apply several filters to a datatable, then bind a datasource to it. This is a list of Policy renewals.
So my current code looks like
' stFilter contains all the filters
Me.BroomsDataset.Renewals.DefaultView.RowFilter = stFilter
' Set the sorting, then bind
Me.BroomsDataset.Renewals.DefaultView.Sort = "Rdat"
' Filters only apply when datasouce set
Me.RenewalsBindingSource.DataSource = Me.BroomsDataset.Renewals.DefaultView
Now I have to do one additional step. Add an extra column which counts the number of policies for a Client. Which I can do like this
For Each rowRenewal In Me.BroomsDataset.Renewals
stCalc = "Count(PolicyRef)"
stPolFilter = "ClientRef = '" & rowRenewal.ClientRef & "'"
rowRenewal.PolicyCount = Me.BroomsDataset.Renewals.Compute(stCalc,stPolFilter)
However, this count will include all policies for a Client - I only want to count the filtered policies. For example, Client has 5 policies overall, 2 are in March. The filter is for March policies only, so only those two rows will be in RenewalsBindingSource.Data
I'd like the PolicyCount to be 2 in that case, instead its 5.
What should I change to count only the filtered rows?