Aggregate fields in forms

The attached image 1 shows the form used to display transaction information from a stock portfolio filtered according to the Security Symbol, Activity Description and Date interval. The form is a standard filtered form, what makes it different is the addition of calculate fields.

Here the calculated fields are the sum of the values returned in the sub-form columns Quantity, CAD_Amount and USD_Amount. Searching the internet on how to do that did not provide a solution, so this is my version of a possible solution. Anyone with a better suggestion, I would very much appreciate knowing about it.

Once I stopped locking for a silver bullet (a form object that would automatically allow aggregation of a specified field in the sub-form), the solution came as a work around.

I created a Filtered_Query_Table (Attached image 2) where the Data_Table and the Filter_Table are linked through the filter fields of the Filter_Table.
Note: The link for the FromDate and ToDate are invisible since those two fields cannot be aggregated

Then I included a second form in the filtered form linked to the Filtered_Query_Table and added to this new form formattedfieds with their data entry being each of the aggregated fields of the Filtered_Query_Table.
This works fine as long as non of the filter fields in the filtered form, are left blank. A blank value in any of the filter field will return all the values of that field in the Data_Table as if no filter was set for the field. But, in the Filtered_Query_Table, the field will be set to blank thereby returning no results.

Though it would be nice at times to have, for example, all securities sold in a given year and aggregate the amounts for each security in one form for comparison, I have not yet been able to do so on a filtered form. Any suggestions?

P.S. I haven’t been able to either insert or attach the images mentioned above. Sorry.

Hello @GuidoGuerra,

First please do not post as Wiki. It helps no one. If you don’t have enough karama (points received for activities such as accepting answers, having answer accepted, upvotes) there are free outside services where you can post & reference here.

Not sure if your post is a question or a statement or both. Whatever, it seems rather complex as described for what you need to actually do. Getting totals from a table grid is a sub form to the grid form & using a table filter (as it seems you mentioned). My answer in this post ( Adding 6 Fields for Total…) includes a sample with what I believe you are mentioning about totaling grid columns. It is based upon a table filter which is originally used for listbox filtering.

For your last question, it seems you just need a second main form with selection of from/through dates. You should be able to do that with a simple query for displaying the results.

Thank you for your answer. I’m new at this and have no clue about KARAMA. I don’t think your link is actually useful for my project but I will have a closer look. Meanwhile how do I set this post to answered?

@GuidoGuerra If you don’t believe your question was answered, please specify why. It is best not to close a question unless some answer is acceptable even if it means there is a bug in the system.

As for closing a question & Karma this is all on the link on the main page - How to use the Ask site.