3 Replies Latest reply on Jan 5, 2015 12:45 PM by philmodjunk

    Using case function with portal data



      Using case function with portal data


      I have a database where the outcomes of phone calls (field labeled ContactOutcome) are recorded within one portal (table: Booster Calls) on a specific layout, and I would like to use a case function to calculate the date of completion in a field (B1Complete) on a separate layout. For example, for this ContactOutcome field, the possible responses in the portal are "attempt 1", "attempt 2", "V1 completed", "V2 completed". A date is associated with each portal row, but I only want to pull the date when it is "V1 completed". If it does not have "V1 completed", I set it to put in "Incomplete."

      My issue is that the case function seems to only be looking at the top portal row to generate the B1Complete entry. Is there a specific way that I can set up my calculation to look at all rows in a portal, or is there another way I can do this?

      Here is my original calculation:

      B1Complete = Case (Booster Calls::ContactOutcome = "V1 completed" ; (Booster Calls::Date) ; Booster Calls::ContactOutcome  ≠ "V1 completed" ; "Incomplete")

      I attempted to input this to see if the brackets [1-5] would indicate a search in the first 5 portal rows but that didn't work:

      B1Complete = Case (Booster Calls::ContactOutcome[1-5] = "V1 completed" ; (Booster Calls::Date) ; Booster Calls::ContactOutcome[1-5]  ≠ "V1 completed" ; "Incomplete")

      Any thoughts or suggestions would be greatly appreciated!

        • 1. Re: Using case function with portal data

          Calculations in calculation fields evaluate at the Tutorial: What are Table Occurrences? level and not the layout level except for the use of specific functions that refer to the layout or objects on the layout. In cases where you have more than one occurrence of the same table, you can use the "context" drop down in Specify Calculation to select the desired occurrence.

          If a calculation (doesn't matter if it is or is not the Case function), that refers to a field from a related table occurrence (does not matter if you have a portal created to that table or not) will only refer to the value of the field from the "first" related record unless it is a special function like List, Sum, Count, etc that refers to the data as an aggregate of all the related records. (List, for example, produces a list from all related records with a return between each non-null value.)

          There are several ways to access data in a child table that only shows a related record if it meets specified criteria:

          You can add another table occurrence of Booster Calls, define a calculation field in your layout's table that always has the value "V1 completed" and then set up a relationship to the new table occurrence that matches by your portal's set of match fields plus this new field matching the calculation field to ContactOutcome. You can then refer to the date field from this new table occurrence to show the date. (This method makes the desired record the "first" related record.)

          You can set up a separate one row portal with a portal filter that limits the records to only those where Booster Calls::ContactOutcome = "V1 Complete". Then put the date field in this single portal row.

          ExecuteSQL can use both JOIN and WHERE clauses to return the date.

          Other methods are also possible...

          • 2. Re: Using case function with portal data

            Hi PhilModJunk,

            Thanks for your quick response! I just wanted to clarify from your response-- it seems to me that best (only?) option is to create a second table in order to pull that data into my calculated field?


            • 3. Re: Using case function with portal data

              None of the methods that I mention require using an additional data source table.

              The first option, and only the first of three options posted here, requires adding at least one additional table occurrence--which is not the same thing as adding a new table. See the link on table occurrences in my initial post if this is a new term.

              The filtered portal and executeSQL options do not require adding any additional table occurrences. The filtered portal option is pretty much a "display only" option as you can't really access the value displayed if you want to use it in additional calculations and ExecuteSQL requires a working knowledge of SQL so each option has it's pros and cons...