Discussions

Expand all | Collapse all

I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

  • 1.  I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 07-01-2015 14:52

    I would like to "grab" numbers from a text field and then use the numbers in a calculation.

    [Example text A] = "1 Pair per Bag, 12 Bags per Inner Bag, 12 Inner Bags per Case"

    [Example text B] = "10 Eaches Box, 100 Boxes per Case"

    I would like to create a formula field that grabs the above numbers in the sequence listed and multiple..

    Example A -- New formula field  [1 * 12 *12] = 144

    Example B -- New formula field  [10 * 100] = 1000

    is this possible?



  • 2.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 07-01-2015 15:20
    I don't think this is a good idea to actually implement this in the formula language - but it is technically possible to do by manually splitting apart the text field first on commas and then on spaces using Right(), Left() NotRight(), and NotLeft(). You will have to assume a maximum number of numeric values embedded in your text string and write the formula to parse out that many numbers and multiply them together. But I would suspect some user is going to deviate from the assumed format over time and the formula will eventually fail.

    On the other hand this can done using script in a reliable way. This code fragment splits the text field on non-digits using a regular expression and then reduces the array by multiplying all non-empty terms together.

    var field = "1 Pair per Bag, 12 Bags per Inner Bag, 12 Inner Bags per Case";
    var terms = field.split(/\D+/g);
    var product = _.reduce(terms, function(memo, item) {
      return item.length > 0 ? item * memo : memo;
    }, 1);
    console.log(product);

    logs 144


  • 3.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 07-01-2015 15:43
    If possible, consider splitting your numbers and unit values into separate fields; so you can more accurately capture the numeric values for more reliable calculations


  • 4.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 07-01-2015 15:46
    I even created a demo for you - just change some of the numbers in the text field and watch the numeric field update as you type:

    Questions, Answers and Points
    https://haversineconsulting.quickbase.com/db/bj3fd38iz?a=er&rid=1

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


  • 5.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 02-24-2017 19:16
    Nice one :)


  • 6.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 07-01-2015 17:46
    You Da Man! Dan.....Thank you.


  • 7.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 08-05-2016 09:47
    Hi Dan,

    instead of number can we pick usernames/email address from text field through this.

    can you help me with your code ?

    Thanks,
    Gaurav


  • 8.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 08-05-2016 12:01
    Emails could be pattern matched from the text easily. Usernames could appear indistinguishable from simple words so unless you had a list of usernames or some other unique characteristic they would be more difficult to pattern match.


  • 9.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 08-05-2016 13:14
    Ok..

    can you give me code of email pattern ?


  • 10.  RE: I would like to ""grab"" numbers from a text field and then use the numbers in a calculation.

    Posted 01-09-2019 13:56
    Did dan ever publish code for emails?