3 Replies Latest reply on Aug 6, 2015 7:42 AM by philmodjunk

    Filtering related records using unstored calc fields

    TylerNelson

      Title

      Filtering related records using unstored calc fields

      Post

      I'm trying to calculate the sum total of Transactions that are in a related table, filtering certain types of transactions using the TOG, which works perfectly when I use stored Calcs. But I now want to filter the transactions using a related field (unstored calc) and it's not working. I'm wondering if there's a good workaround or another method I should use for this.

      So I have a "Months" table where each field represents a month, which I'm using to calculate the sum total of related "Transaction" records via a relationship where the MonthDateStart is less than or equal to the TransactionDate and the MonthDateEnd is greater than or equal to the TransactionDate, so as to summarize the transactions in a date range. This works perfectly.

      For one of my calculations in the context of Months, I calculate the sum total of Project related Transactions. Certain Transactions that have a foreign key called kf_ProjectID are Transactions associated with a Project. This is related to the primary key ProjectID. So I have a ProjectBoolean Case() calculation (indexed) that sets to 1 if there's a foreign key and 0 if IsEmpty. So in addition to the date range relationship filter, I have a relationship filter where a constant 1 calculation (zcConstant1) equals the ProjectBoolean. This works perfectly because both of the fields are stored calculations.

      What's giving me trouble is when I try to add another filter using an unstored calc in the context of Transactions. I have a boolean field in the Projects table (DevelopmentBoolean) for Projects that are "Development" projects (set by the user with a checkbox). I have a boolean calc field in the context of Transactions that is set to mirror the related DevelopmentBoolean field. So from the context of Months I want to be able to calculate the total related Transactions associated with "Development Projects", but since the "DevelopmentProject" boolean is a related field (in the context of Projects), I'm not able to use a calc stored in Transactions, which doesn't allow me to filter properly when I'm in the context of Months.

      I hope that makes sense. Any suggestions???

        • 1. Re: Filtering related records using unstored calc fields
          philmodjunk

          I think that you have these tables and relationships:

          Months-----<Transactions>------Projects

          When a relationship evaluates from the context of Months It uses the index of any specified match fields in Transactions to determine which records link to a given record in Months Thus an unstored field--which has no index cannot serve as a match field in transactions when the relationship is evaluated from the context of Months. But this "Development" calculation field in Transactions can be replaced by a simple number field that auto-enters the value from Projects using either an auto-enter calculation or looked up value. This assumes that a transaction record is created after the related projects record is created and not the reverse.

          Another suggestion. Instead of using inequalities in the months to transactions relationship. Define this calculation field in Transactions:

          TransactionsDate - Day ( TransactionsDate ) + 1

          This computes a date for the first day of the month for every date in the  same month. You can now use a date field with the first of the month date in Months and the = operator to match to records of the same month in Transactions.

          • 2. Re: Filtering related records using unstored calc fields
            TylerNelson

            Thank you!!!

            That date calculation hack works perfectly! Now I can use equalities in the relationships, which I imagine is easier on the calculation engine.

            I re-defined the field as an auto-enter calc, per your suggestion and used Replace Field Contents to update the values to match the related "Development" boolean values. That works. BUT, when I create a new Transaction and assign the kf_ProjectID foreign key, it doesn't set the new auto-enter calc field. I suppose I can create a script to run whenever the foreign key is modified to re-set that field. But this may be an overly complicated workaround.

            Any other suggestions???

            • 3. Re: Filtering related records using unstored calc fields
              philmodjunk

              Make it a looked up value instead of an auto-entered calculation. That will cause a relookup each time you modify the kf field.