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!