Forum Discussion

LanePemberton's avatar
LanePemberton
Qrew Cadet
7 years ago

Count characters in a string, find string location in a string, etc?

Is there still no way to count occurrences of a string within a string? Or to find the index of the first occurrence of a string within a string? The reason I ask is because this seems like a simple operation that should be included in the long list of functions available in Quickbase formulas. Also, since Quickbase has a "Mid" function why wouldn't they have a way to find the index of string? As I'm sure Dan would point out, those two are used very often together in programming languages. In fact, every time I've ever used Mid to extract a bit of string I've needed to dynamically break it up based on the index of another string.

Anyway, if someone could point me to a page to post this where it could be helpful for future Quickbase implementations let me know.  
App builders
Forms
Formulas and functions
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    7 years ago
    Hi Lane,

    Currently we do not have a function built into our formulas to find the index of a string. There are some work arounds using the Left() and Right() functions or Part() off of a recognize-able pattern that can chain together but they can get lengthy. I think a function to help count the occurrence of a string would make a great suggestion and our product development team uses our User Voice page to track user support for future features and enhancements. The Quick Base User voice can most easily be accessed from the My Apps page in Quick Base by clicking on the orange Feedback tab that appears on the right of the page or at http://quickbase.uservoice.com by signing in with your Quick Base credentials. 

    I usually suggest if someone from the Community adds a User Voice request that they post a link to it here. That way any other Community users that come across this post will be able to add their vote as well and show support. Thank you very much for your time today Lane. 
    • LanePemberton's avatar
      LanePemberton
      Qrew Cadet
      7 years ago
      Yeah, that's what I have now. A convoluted mess of Parts and If statements :) Thank you for the link, I'll make sure to post there as well.
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      If/when you do, be sure to post back here so we can add votes, please.
  • DavidBrogdon's avatar
    DavidBrogdon
    Qrew Assistant Captain
    7 years ago
    You can always pass the string via URL parameter to a custom code page and do the string coding there via JavaScript or whatever. You can then display the value on the code page if you are just needing to know the number. There are additional options for putting that value back into a record field but it is more lengthy. 
    • LanePemberton's avatar
      LanePemberton
      Qrew Cadet
      7 years ago
      Good suggestion David. I've used the IOL technique before but mostly as an action that runs off of a button click. I'm not sure the best way to run JavaScript from a formula and have it auto calculate (I guess that will be my next question). It would be nice to be able to use 'search', 'indexOf', 'match', etc.
    • DavidBrogdon's avatar
      DavidBrogdon
      Qrew Assistant Captain
      7 years ago
      You don't need the IOL technique for this. Just create a custom URL button that points to your custom code page. Pass the string value to the code page through the URL. Create the code page as an html document with a script tag that pulls a parameter value from the address bar and puts it into a variable. 


      URL Button Example: 
      https://[domain].quickbase.com/db/?a=[code page id]&[parmater=value]
      https://dummycompany.quickbase.com/db/xxxxx?a=showpage&pageid=1&mystring=thisstring

      HTML Document Example: 
      <script>
       function getUrlParameters() {
          var params = {};
          var parts = window.location.href.replace(/[?&]+([^=&]+)=([^&]*)/gi, 
      function(m,key,value) {
              params[key] = value;
          });
          return params;   }


      function doSomeCode(){
      var string=getUrlParameters() ["mystring"];  //do some code
      } 
      </script>

      This may take some tinkering but it works good for me so far. 
    • LanePemberton's avatar
      LanePemberton
      Qrew Cadet
      7 years ago
      That looks awesome. However, I want the script to execute automatically on every record (e.g. Formula - Text), not just when I click a button. Would it work the same in that instance?
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      Took me a couple minutes to scrounge up the full 3 votes but I'm on it. Thanks for putting that together.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    You can perform any manner of text manipulation you want using script. Here is a demo that expands TLAs (Three Letter Acronyms) using one Rich Text Field:






    Try it yourself:

    TLA Expander ~ Add New Record
    https://haversineconsulting.quickbase.com/db/bpb2mbnva?a=nwr

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=722

    Notes:

    (1) Any manner of text manipulation can be performed with script. I just happened to have this demo in the pipeline based on some other client work.

    (2) The script as written performs these substations:
        AMA:   'Ask Me Anything',
        BAE:   'Before Anyone Else',
        BFF:   'Best Friends Forever',
        BTW:   'By The Way',
        DM:    'Direct Message',
        FOMO:  'Fear Of Missing Out',
        FTW:   'For The Win',
        HW:    'Homework',
        ICYMI: 'In Case You Missed It',
        IMO:   'In My Opinion',
        IMHO:  'In My Humble Opinion',
        IRL:   'In Real Life',
        JIC:   'Just In Case',
        LMK:   'Let Me Know',
        MC:    'Man Crush',
        MT:    'Modified Tweet',
        NSFW:  'Not Safe For Work',
        NSFS:  'Not Safe For School',
        OMG:   'Oh My God',
        OOTD:  'Outfit Of The Day',
        OTT:   'Over The Top',
        RT:    'Retweet',
        SMH:   'Shaking My Head',
        TBH:   'To Be Honest',
        YOLO:  'You Only live Once',
        YSK:   'You Should Know'


    • LanePemberton's avatar
      LanePemberton
      Qrew Cadet
      7 years ago
      Is there a way I could run this so it functions as a Formula - Text? For instance, I want to parse text from a summary field that changes based on the child records related. This example only updates if you manually change a value while editing a form.