AnsweredAssumed Answered

Using case function with portal data

Question asked by JanetLui on Jan 5, 2015
Latest reply on Jan 5, 2015 by philmodjunk


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!