Do you need to count the number of parts in a Combined Text Field? If so, this is on my cheat sheet, courtesy of Mark Shnier @yourquickbasecoach. This counts up to 20 parts, extend for the max amount you expect to have.
var text value = ToText([Combined Text Field]);
Count(
Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")),
Trim(Part($value,11,";")),
Trim(Part($value,12,";")),
Trim(Part($value,13,";")),
Trim(Part($value,14,";")),
Trim(Part($value,15,";")),
Trim(Part($value,16,";")),
Trim(Part($value,17,";")),
Trim(Part($value,18,";")),
Trim(Part($value,19,";")),
Trim(Part($value,20,";"))
)
Hey Mike,
No, I need to count the # of Transactions based on the Concatenated Field that I have created. Records where the value in the Concatenated Field are the same, I only want to count once.
Does that make sense?
I think that is the same thing as counting the parts of your concatenated field, right? If you use the formula below (in a formula numeric field) you should get '6' as your answer for the example above.
var text value = ToText([# of Note Payments (Concatenations)]);
Count(
Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")),
Trim(Part($value,11,";")),
Trim(Part($value,12,";")),
Trim(Part($value,13,";")),
Trim(Part($value,14,";")),
Trim(Part($value,15,";")),
Trim(Part($value,16,";")),
Trim(Part($value,17,";")),
Trim(Part($value,18,";")),
Trim(Part($value,19,";")),
Trim(Part($value,20,";"))
)
... Mike's solution should work because the concatenated field roll up will only roll up Unique children.
Hey Mike,
It worked! Thank you so much for responding. Much appreciated.
Ashley
To add to my reply, your concatenated field is a combined text field, which by default nature combines 'like' results. So in your example, though you have 10 values, you only have 6 different values which is show in your concatenatd field. The formula I posted simply counts up the number of those different values.
