Hey Simon,
My Concatenation field is what identifies the 2 records that should be counted as 1. Its the ID that is the same between the 2.
I tried counting with the Distinct Count function on the summary field while summarizing on my Concatenated field, and it's still counting each record individually.
In my example below there are 10 Transactions but when Combined via the Concatenated field there should only be 6 Transactions.
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,";"))
)
Qrew Legend... Mike's solution should work because the concatenated field roll up will only roll up Unique children.
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.
