0

How to extract numbers from a field record

I need help extracting some information from a field.

I have a field the contents of which take the form of

X-x-x Text

Where x is a number consisting of 1 to 3 digits.

Example
1-2-10 Some Text

I want to extract the three numbers individually and insert each number into different fields.

So I would get

Field 1: 1
Field 2: 2
Field 3: 10

I'm half way to a solution.

Using Regex I can extract the numbers but, because of my lack of REGEX knowledge, I can't simply extract the pure number.
So typically I get

Field 2: -2
Field 3: -10

In order to get only the digits I thought it would be easiest to process the "found" results in Ninox using something like "extractx" but I can't find a way to make that work.
On the surface it seems that it should be easy to take "-2" and convert it to "2" using something like a Modulus function but I've not be able to find a workable solution.

Note that the format does not have to be exactly as presented. The original field content could be in the form of x-x-x Text as long as the three numbers can be easily found.

One example of the REGEX I'm experimenting with:

let x := extractx(text('Subject Name'), "\-[0-9]+\s");
Test1 := text(x)

This is what produces the -2 result.

Any suggestions on how I might extract the digits for each number?

Any help would be greatly appreciated.

Thanks.

7 replies

null
    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Regular expressions, and their use in Ninox, are a bit tricky. Here is a proposal for a solution, there are certainly many other possibilities:

    let tnumb := split(extractx('Subject Name', "(\d+)-(\d+)-(\d+) ", "$1-$2-$3"), "-");
    let 'Field 1' := number(item(tnumb, 0));
    let 'Field 2' := number(item(tnumb, 1));
    let 'Field 3' := number(item(tnumb, 2))

    • Dorich
    • 3 yrs ago
    • Reported - view

    @ Alain Fontaine

    Thanks. Interesting solution, way above what I would have been able to figure out.

    So far in testing it returns "Void". I can't immediately see a reason but I'll keep looking.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    You did not tell what you want to do with the three extracted numbers. So I submitted a code snippet that just sets three variables. If you allow the script to finish without doing anything with those values, poof, the variables simply vanish. When Ninox detects that a script terminates without any useful effect, it adds a "void" instruction as a sort of warning.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Sorry, I did not read your question correctly. Since it appears that 'Field 1' and all are actually fields in your table, just remove the three instances of "let", and those fields will receive the three values.

    • Dorich
    • 3 yrs ago
    • Reported - view

    @Alain Fontaine

    Yes, "let" was the problem.

     

    Since you got me this far I wonder if I could add some questions to this same issue.

    In your original suggestion you created three variables. I was trying to understand what the "life" of the variable would be, which perhaps brings me to the second question

    I'm wondering how to trigger the action that reads the "Subject Name" and inserts the derived values into the appropriate fields. In order to start experiments I attached the code that generates the value to the "Trigger after Update" field. Not a suitable trigger but it at least got me started.

    I can see that I use a "Button" and attach the code but I wondered if there is a more suitable way to trigger the action. I see that the three fields couldn't be filled until the "Subject Name" is entered so I was wondering if there is some way to update the three fields once the "Subject Name" has been entered. Perhaps its better to keep it simple and rely on a button?

    Your thoughts would be appreciated.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    The scope of a variable (its "life") depends on the context. In general, the scope of a variable declared (by "let" or "var") in a function field or of a button definition is that definition. Which means that the variable has no existence outside of that definition. To say it otherwise: when the script is executed, the variable is created when the "let" statement is executed, and vanishes when the end of the script (more exactlty, the current scope) is reached. Narrower scopes happen inside select, if .. then, switch, while .. do constructs.

    Back to the number extraction question. Why do you think that the script to set the three number fields cannot be used as the "Trigger after update" option of the 'Subject Name' field? That's indeed the right place, and the three number fields will be updated when 'Subject Name' changes.

    If you don't like to use a trigger, there is another solution. You may define the three fields that receive the extracted numbers as function fields, and put the three functions below as their definitions.

    number(extractx('Subject Name', "(\d+)-\d+-\d+ ", "$1"))

    number(extractx('Subject Name', "\d+-(\d+)-\d+ ", "$1"))

    number(extractx('Subject Name', "\d+-\d+-(\d+) ", "$1"))

    • Dorich
    • 3 yrs ago
    • Reported - view

    @ Alain Fontaine

    First thanks for the detailed response - very helpful.

    "Trigger After Update" : My reluctance comes from my experience with using that function. It "appears" to me that this will only work if you try to update the field (Field1) itself. With a newly created record the field (Field1) is blank. If I type something into the "Field1" then after exiting the field my typed value is changed to the value set by the formula. That works consistently in my experiments. To my mind that seems awkward and unnecessary. In the name of automation it would seem that in the process of creating the record the user should be able to cause the field to fill with a value from the formula.

    I did wonder if the word "update" applied to the field itself or the record.

    I tried changing the data in the "Subject Name" field and it didn't change the value in the "Field 1".

    However, I noted your comments and I assume that I'm in error somewhere. So if I change the "Subject Name" field then "Field 1" will change as a result.

    I'll do more experiments.

     

    Thanks for the other suggestion, I'll give that a try.

Content aside

  • 3 yrs agoLast active
  • 7Replies
  • 884Views