filtering a datagrid based based upon a specified total

[Kevmeister]

Hi all,

I am trying to filter a datagrid based upon stepping through records and suming the contents. What i am trying to do is get to select records that equal (or if the sum of records exceed the entry they revert to the previous record) or less than the entries specified in a combobox on a form.

I think i need a loop next statement for this but if anyone can give me some pointers be much appreciated, as i am really struggling with this.

Kev

[jmcilhinney]

I don't really know what you're asking for. Can you provide a clearer explanation? Maybe include an example or two?

[Kevmeister]

No problem, i have a combobox from which i specify a financial amount say £10,000. What i want to do is step through a specific field in the records in the datagrid starting with record 1 and add the contents see if its less than the sum specified and if not move to the next and add the total of both records see if its equal to the figure specified and so on. When it reaches the figure or more likley exceeds it i want to revert to the previous record which would be under the figure specified. Hence the filter will consist of records which are either equal or less than the figure specified in the combobox.

I am not sure if this makes sense but hopefully it will.

Many thanks for taking time to help, i do try and solve these things for myself but i start loosing days!!

Kev

[jmcilhinney]

You say that you want to "filter", which implies that you want to remove some rows and keep others. Is that the case or do you just want to select the last rows that falls under your total?

[Kevmeister]

Yes what im after is to end up with only the rows that together either are cumilatively equal or don't exceed the £10,000 figure specified in the combobox.

[jmcilhinney]

I'm assuming that your data is in a DataTable with ascending IDs and bound to the grid via a BindingSource. The BindingSource is optional but preferable, but the DataTable is essential or else you'll will have to do a lot more work manually.

1. Declare a variable to contain a running total.
2. Loop through the rows in your DataTable.
3. For each row, add the value to your running total.
4. Compare the running total to the upper limit.
5. If the running total exceeds the upper limit, assign the ID to a variable and exit the loop.
6. Set the Filter of your BindingSource to include only records with an ID lower than the one you stored.

[Kevmeister]

Many thanks, this is really helful in focusing my efforts. Much appreciated

Kev

[Kevmeister]

Sorry to ask a further, but related question. How might i achieve the same thing if the dataset has already been filtered on a further field? As this would take them out of id order.

Kev

[jmcilhinney]

It doesn't matter if the data has already been filtered. You can include as many criteria as you like in the filter. It just means that when you loop through the data, you'd loop through the BindingSource rather than the DataTable, so that you only include rows that have not already been filtered out.

What could be a problem is sorting. If the data is sorted then you would have to use the sort column as the condition by which to filter rather than the ID. As long as you don't have two identical values in the sort column there's no issue. If you do have duplicates then you'd have to find another criterion to use in addition, e.g. the ID, to distinguish the duplicates.

[Kevmeister]

Many thanks, for all your help on this. Its most appreciated.