10 Replies Latest reply on Jun 22, 2018 12:46 PM by Deepak Rai

    Parsing Text String

    Kim Domholt

      Hello.

       

      I have a field which contains names of fundraisers tied to a proposal. As you can see sometimes there is more than one fundraiser always separated by a semi-colon.

       

      Fundraiser(s)

      Smith, Sally

      Smith, Sally; Jones, Karen; Johnson, Sara

      Johnson, Sara

      Johnson, Sara; Smith, Sally; Anderson, Berry

       

      I am trying to create a metrics report that would show the following:

                                      Amount       Number of Proposals

      Smith, Sally             $240,000    5

      Jones, Karen           $10,000      1

      Johnson, Sara         $50,000      3

      Anderson, Barry      $30,000      1

       

      I created a calculated field like below

      IF CONTAINS([CnPrProp 1 Solicitors], "Sally") THEN "Smith, Sally" ELSEIF

      CONTAINS([CnPrProp 1 Solicitors], "Jones") THEN "Jones, Karen" ELSEIF

      CONTAINS([CnPrProp 1 Solicitors], "Johnson") THEN "Johnson, Sara" ELSEIF

      CONTAINS([CnPrProp 1 Solicitors], "Anderson") THEN "Anderson, Barry" ELSE

      end

       

      When it totals I need all proposal amounts counted for each fundraiser. It seems my totals are only showing for one of the fundraisers not all.

       

      Any ideas?

       

      Thank you!

       

      Kim