1) What version of FM are you using?
2) How many maximum Transaction records might you expect from a single ISIN Security? Would it be 5,000, 50,000, 800,000? The best method to suggest will depend upon the answers to these two questions.
Hi, I am using FM 11. The max. Number of Transaction records expected per ISIN security would certainly be much lower, below 100 per ISIN.
I thought it easier to provide a sample file but my provider has been down so I've had to type it out. If, after working through this information, you still want the sample file, private message me your email and I'll send it directly to you. Here goes:
Since you are using vs. 11 and the number of related records per ISIN is very small, you can filter your portal to nicely handle the job. You will not need to add another table occurrence or another portal. Here is how I would handle it:
Be sure that you have a value list of Types. Then create three global fields. They can be in any table and that table doesn’t have to relate to either Securities nor Transactions. For these instructions, I will assume you put them in a table named ‘globals’. Be sure they are all set to global and each of them will have an auto-enter calculation applied to them. Right below the auto-enter by calculation, uncheck ‘Do not Replace Existing Value’ on all of them. And when you create the calculation, be sure and uncheck at the bottom of the calc dialog, ‘do not evaluate…’. The fields and their calculations should be:
gFrom (date, global): Case ( not Self ; Date ( 1 ; 1 ; 1 ) ; Self )
gTo (date, global): Case ( not Self ; Date ( 12 ; 31 ; 3999 ) ; Self )
gType (text, global): Case ( IsEmpty ( Self ) ; ValueListItems ( Get ( FileName ) ; "Types" ) ; Self )
Then go to your portal setup (on your existing portal) and filter it by this calculation:
Transactions::date ≥ globals::gFrom
Transactions::date ≤ globals::gTo
not IsEmpty ( FilterValues ( Transactions::type ; globals::gType ) )
Place all three of these globals above your portal. Create a script called Refresh which is Refresh Window [ flush cache join..] On the two date fields, attach script trigger OnObjectExit and specify Refresh script. Also set the two dates to Inspector > Data Tab > Behavior to ‘select entire contents upon field entry. I would set up your Types global as checkbox so User can select several at once. Attach script trigger to this field OnObjectModify and again specify Refresh script. What this will all do: If the fields are empty, it will automatically fill them with the smallest start date and largest end date and ALL types, thus giving you the same results as if the filter didn’t exist. But if you put in anything in any of the filters, it will adjust accordingly.
Now to the total which you want: In Transactions, create a field called sTotal (which is summary of your transaction amount). Now below your portal, create another identical portal as your main portal (you can copy it). Set it to one row only. Inside this portal, place this new summary field. Be sure it is 1 px lower and 1 px to the right from the left/top border of this portal so it displays. Now your total will adjust according to the filter.