Forum Discussion

AshleyHarris's avatar
AshleyHarris
Qrew Trainee
2 years ago

Counting records with the same Concatenated Values

I have a Transaction Table that breaks out some payment into 2 records (Principle & Interest). 

In another table (parent), I need to count those 2 records as 1 Transaction when I am summarizing up so that I have a correct calculation for # of Transaction over a period of time. 

I created a Concatenation (formula field), and have tried Summarizing but I can only use the Combined Text function. I need the Count function. 



What is the best way to count records where the value in the Concatenate field is the same, so I can Summarize up to the Parent with a number value?

Thanks in advance for the help. Ashley



------------------------------
Ashley Harris
------------------------------
  • SimonH's avatar
    SimonH
    Qrew Cadet
    2 years ago

    When a payment is split in 2 do they each have some sort of ID number that is the same?
    If so you can use the summary type "Distinct Count" on that field to get your number of transactions.



    ------------------------------
    Simon H
    ------------------------------
    • AshleyHarris's avatar
      AshleyHarris
      Qrew Trainee
      2 years ago

      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. 



      ------------------------------
      Ashley Harris
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        2 years ago

        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,";"))
        ) 



        ------------------------------
        Mike Tamoush
        ------------------------------