3 Replies Latest reply on Dec 16, 2016 2:51 PM by philmodjunk

    Engineering a Calculation Field from a Portal Filter

    wackarac

      We have a table that details income eligibility for a number of weatherization and utility payment subsidies available to low income families. Eligibility stats change periodically, and each agency operates on it’s own timetable.  The goal is to name the household's income eligibility category for each program application by Inquiry Date, and depending on which agency sets the limits for that type of funding.

       

      (( Programs::Agency = Income Eligibility::Agency ) and

      ( Programs::Total in Household = Income Eligibility::HouseholdSize ) and

      ((Programs::Monthly Income /12 ≤ Income Eligibility::IncomeLimit ) and (Programs::Monthly Income /12 ≥ Income Eligibility::IncomeBase)) and

      (( Programs::Date of Inquiry ≥ Income Eligibility::DateStart ) and ( Programs::Date of Inquiry ≤ Income Eligibility::DateEnd ))

       

      This works great as a portal filter, but what we need for reporting purposes is a calculation field.  When I experimented with an If statement, only 1 record in the database registered a value.

       

      If ( all of the above is true ) ; Income Eligibility::IncomeCategory ; "" )

       

      Can someone explain my faulty logic and suggest a fix or an alternate approach? 

        • 1. Re: Engineering a Calculation Field from a Portal Filter
          philmodjunk

          in a calculation, any reference to data in a related table can only access data from the "first" related record. Any other related records might as well not exist as far as the calculation is concerned.

           

          There are ways to deal with this:

           

          Use ExecuteSQL to get the desired data.

           

          Build the logic into the relationship so that an unfiltered portal shows the desired records. Of course, this isn't always possible.

           

          Don't use a portal. Use the "logic" of your expression in a find that only finds records that meet the needed criteria. Sometimes multiple finds or a loop that omits records has to be part of that process.

           

          These are just very general descriptions of options. It would help to describe your report and why the filtered portal itself can't be used as part of that report.

          • 2. Re: Engineering a Calculation Field from a Portal Filter
            wackarac

            Thank you for your suggestions. I’ll look for some ExecuteSQL examples; all new to me but definitely worth learning.  Not sure what building the logic into the relationship would look like. And your third suggestion, assigning the value with a script is probably something I can manage.

             

            The reporting is simple. We just need to summarize the number of clients that fall into each category.  I can display the filtered portal on a report of Programs, but I can’t figure out how to count incidences in each category. Switching it around, I could probably count Programs from the perspective of the category, if I make those categories into a related table.  Is that on the right track?

            • 3. Re: Engineering a Calculation Field from a Portal Filter
              philmodjunk

              To count related records in a filtered portal, you can add a "count of" summary field to the portal's table (set it to count a field that is never empty) and then make a one row copy of your filtered portal and put your summary field in that row. The "Found Count" layout object could also be placed inside that one row portal to show the number of records. Other aggregate values such as a total, average, Maximum, etc can also be done in the same manner.

               

              But what you describe also sounds like something that might be managed with a summary report where you use sub summary layout parts and sorting to group your records. The same "count of" summary field, if placed in such a sub summary part could also provide your counts for each category. Such a report can be set up to list every record in each group that is counted in that fashion, or you can just show a row and a count for each group.