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

  • 0
  • 2
  • Question
  • Updated 2 months ago
  • Acknowledged
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.  
Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 2
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,416 Points 10k badge 2x thumb
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. 
Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb
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.
(Edited)
If/when you do, be sure to post back here so we can add votes, please.
Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
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. 
Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb
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.
Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
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/[table]?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. 





Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb
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?
Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
If you wanted the script to run on each load of a record you would need to use the IOL technique I believe
Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb
If everyone reading this could support the following feedback post with votes and comments that would be great: https://quickbase.uservoice.com/forums/111823-quick-base-product-feedback/suggestions/36563971-inclu...



Took me a couple minutes to scrounge up the full 3 votes but I'm on it. Thanks for putting that together.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
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`


Photo of Lane Pemberton

Lane Pemberton

  • 292 Points 250 badge 2x thumb
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.
Looks like some relevant functions have been added to the February 2019 release.

https://quickbase.uservoice.com/forums/111823-quick-base-product-feedback/suggestions/36563971-inclu...

Community forum post to UV suggestion to in production in about a month - that's gotta be the fastest I've ever seen. Lane, thanks again for driving this development.