3 Replies Latest reply on May 23, 2011 6:21 AM by LaRetta_1

    Calculation using a subset of related records

    mp548

      Title

      Calculation using a subset of related records

      Post

      Hi folks, I am new to FM and at the moment frustrated with my own inability to manipulate the stored data.  Specifically, my greatest problem seems to be "fetching" the right records from related tables, on which to perform slightly more complex calculations. I have two tables:  a table Transactions with the fields ISIN (text), Date (date), Transaction Type (list of text entries), and Amount (number); as well as a table Securities with the fields ISIN (text) and a portal showing related records from the Transactions table based on ISIN as break field. Now, I would like to create in the table Securities calculation fields showing (i) the sum of values in the filed Amount for all records matching the ISIN and a certain value in the Transaction Type field, as well as (ii) the same, further restricted on a certain date range. I really would be grateful for any help on how to accomplish this with a calculation and possibly without any scripting.

        • 1. Re: Calculation using a subset of related records
          LaRetta_1

          Hi Martin,

          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.

          • 2. Re: Calculation using a subset of related records
            mp548
            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.
            • 3. Re: Calculation using a subset of related records
              LaRetta_1

              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
              and
              Transactions::date   ≤ globals::gTo
              and
              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.