Discussions

 View Only
  • 1.  Count the number of specific characters in text field

    Posted 10-31-2019 16:09
    Hello,

    Is it possible to count the number of specific characters within a text field? What I'm looking to do is count the number of delimiters (";") within a text field to determine the number of entries in a field (this isn't something that can be separated into different fields).

    Examples: 
    • hello;world;test;1;2;3; = 6
    • hello; = 1
    • ;;;; = 4

    I was looking into removing all text besides the delimiter and using the Length formula, but haven't had any luck!

    Thanks!

    ------------------------------
    Scott Robertson
    ------------------------------


  • 2.  RE: Count the number of specific characters in text field

    Posted 10-31-2019 16:19
    Try this, but it will not work for your last example as there is no text in between the delimiters.

    var text value = [my 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,";"))<>"",
    Trim(Part($value,21,";"))<>"",
    Trim(Part($value,22,";"))<>"",
    Trim(Part($value,23,";"))<>"",
    Trim(Part($value,24,";"))<>"",
    Trim(Part($value,25,";"))<>"")

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 3.  RE: Count the number of specific characters in text field

    Posted 11-01-2019 07:47
    Appreciate the input!

    ​The other issue I failed to mention is that the length of that field can range from 1 delimiter to 50,000+... would I need to complete 50,000 "trims" with this solution?
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    Trim(Part($value,50000,";"))<>"",


    ------------------------------
    Scott Robertson
    ------------------------------



  • 4.  RE: Count the number of specific characters in text field

    Posted 11-01-2019 09:07
    Yes, 50,000, which I'm sure would hit either Quick Base limits or limits of your patience.  This would require a JavaScript solution.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 5.  RE: Count the number of specific characters in text field

    Posted 11-01-2019 12:42

    Thanks Mark, that's what I was afraid of.

     

    This information is coming in through an API so I'm going to explore obtaining the counts before posting to QuickBase.

     

     

    Thanks again,
    Scott


    Confidentiality Notice:
    This e-mail, including any attachments is the property of Trinity Health and is intended for the sole use of the intended recipient(s). It may contain information that is privileged and confidential.  Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, please delete this message, and reply to the sender regarding the error in a separate email.





  • 6.  RE: Count the number of specific characters in text field

    Posted 11-03-2019 08:20
    Scott,

    If you have an external data source with the delimited text, you should transform the data before writing to Quick Base.  What ever code you are using to extract from the other DB and load to Quick Base is undoubtedly more powerful than the formula fields in Quick Base.  Quick Base is great but is deliberately kept simple for the Citizen Builder to be able to make business process applications for users. 

    Your case above is outside the box for Quick Base but not that difficult with a Loop in your external code.   Then you can add a second field in Quick Base that is the Count of Delimiters as a Numeric field instead of a Formula Numeric.

    I assume with the text field and numeric field you can then get on to the business case for why you wanted to import the data to begin with.

    Good luck and happy building.

    Don


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 7.  RE: Count the number of specific characters in text field

    Posted 11-01-2019 02:07
    It looks like Mark got the solution you need.

    ------------------------------
    Babi Panjikar
    ------------------------------