0

Formatting a field

Can someone help me with formatting in a field. I want to have a captial letter first and lower case after, also phone number, and ss#, etc.

15 replies

null
    • Nick
    • 5 yrs ago
    • Reported - view

    For upper first letter:

    https://ninoxdb.de/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/first-letter-upper-case-5afad40d3940a83b6588f1ab

     

    For phone format use this code in the Trigger After Update box:

    ---

    let myNumber := trim(text(Phone));
    let p := length(myNumber);
    let myCars := "";
    var myStart := 0;
    var myEnd := 1;
    for i in range(0, p) do
    var myNum := substring(myNumber, myStart, myEnd);
    if myNum = "0" or myNum = "1" or myNum = "2" or myNum = "3" or myNum = "4" or myNum = "5" or myNum = "6" or myNum = "7" or myNum = "8" or myNum = "9" then
    myCars := myCars + myNum;
    myStart := myStart + 1;
    myEnd := myEnd + 1
    else
    myStart := myStart + 1;
    myEnd := myEnd + 1
    end
    end;
    let Area := substring(myCars, 0, 3);
    let Num := substring(myCars, 3, 6);
    let Num2 := substring(myCars, 6, 10);
    Phone := "(" + Area + ")" + " " + Num + "-" + Num2

    ---

    * You can change the format to your needs by changing the last fout lines

    • Joe_Cirillo
    • 5 yrs ago
    • Reported - view

    Thanks, I was able to figure out the text field, but I can't seem to figure out the phone number field.

    I would like all my phone numbers to look like this "555-555-555" no parenthesis around area code.

    • Nick
    • 5 yrs ago
    • Reported - view

    Replace the last line with this:

    Phone := Area + "-" + Num + "-" + Num2

    • Joe_Cirillo
    • 5 yrs ago
    • Reported - view

    Thank you that worked perfectly. I would like to do the same thing for SS#, can you help me with that please.

    • Nick
    • 5 yrs ago
    • Reported - view

    Hi Joe,

     

    what is SS#?

    • Joe_Cirillo
    • 5 yrs ago
    • Reported - view

    Social Security Number, it is used in the US for tax perposes. it is always written out like this "123-45-6789"

    Thanks for your help I realy appreate it.

    • Nick
    • 5 yrs ago
    • Reported - view

    No problem @Joe.

    Here is the code for SSN field:

    ---

    let myNumber := trim(text(SSN));
    let p := length(myNumber);
    let myCars := "";
    var myStart := 0;
    var myEnd := 1;
    for i in range(0, p) do
    var myNum := substring(myNumber, myStart, myEnd);
    if myNum = "0" or myNum = "1" or myNum = "2" or myNum = "3" or myNum = "4" or myNum = "5" or myNum = "6" or myNum = "7" or myNum = "8" or myNum = "9" then
    myCars := myCars + myNum;
    myStart := myStart + 1;
    myEnd := myEnd + 1
    else
    myStart := myStart + 1;
    myEnd := myEnd + 1
    end
    end;
    let Num1 := substring(myCars, 0, 3);
    let Num2 := substring(myCars, 3, 5);
    let Num3 := substring(myCars, 5, 9);
    SSN := Num1 + "-" + Num2 + "-" + Num3

    ---

    Enjoy

      • rev_fernandovivas
      • 2 mths ago
      • Reported - view

       Hello Nick, I am trying to use your code for SSN but I only need it to show XX-XXXX and not the whole XXX-XX-XXXX how can I do that? Thanks for your help!

      • Fred
      • 2 mths ago
      • Reported - view

      Since separated the numbers into easy to use variables, you can just change the last line to:

      SSN := Num2 + "-" + Num3
      • rev_fernandovivas
      • 2 mths ago
      • Reported - view

       Hi Fred, so this is my issue and the error it is giving me. @Nick formula presupposes that you will write 9 numbers XXX-XX-XXXX and the formula will use those numbers and separate by dash, I need the formula to accept 6 digits only and not the full 9 digits. So using the change you game as SSN := Num2 + "-" + Num3 it only yield a a problems, because I type 6 numbers and it show only weird numbers and not the ones I ente. I want to input 6 digit and the formula to show XX-XXXX. can you help me?

      • Fred
      • 2 mths ago
      • Reported - view

       play around with the substring() command and see if you figure out how to get the first two digits into a variable. Then apply it to the last 4. Then you can put the two variables together. 

      • Nick
      • 2 mths ago
      • Reported - view

       Try this code

      let myNumber := trim(text(SSN));
let p := length(myNumber);
let myCars := "";
var myStart := 0;
var myEnd := 1;
for i in range(0, p) do
    var myNum := substring(myNumber, myStart, myEnd);
    if myNum = "0" or myNum = "1" or myNum = "2" or myNum = "3" or myNum = "4" or
                        myNum = "5" or
                    myNum = "6" or
                myNum = "7" or
            myNum = "8" or
        myNum = "9" then
        myCars := myCars + myNum;
        myStart := myStart + 1;
        myEnd := myEnd + 1
    else
        myStart := myStart + 1;
        myEnd := myEnd + 1
    end
end;
let Num1 := substring(myCars, 0, 2);
let Num2 := substring(myCars, 2, 6);
let Num3 := substring(myCars, 5, 9);
SSN := Num1 + "-" + Num2
    • Joe_Cirillo
    • 5 yrs ago
    • Reported - view

    Thanks Nick, But I must be doing something wrong because after I put in a SS# and tab to the next field the number I entered disapears from the field.

    The field name I'm using is "SSN Number" and it is a Number field.

    IN the code you gave me I changed the first line to - " trim(text(SSN)) to trim(text('SSN Number')) "

    and the last line to - " 'SSN Number' := Num1 + "-" and so on...

    Please tell me what I'm doing wrong.

    • Nick
    • 5 yrs ago
    • Reported - view

    @Joe, a number field can't accept dashes (-).

    So you have two choices: convert it to a text field

    or

    create a new formula field e.g. 'SSN Display' and put the code there.

    • Joe_Cirillo
    • 5 yrs ago
    • Reported - view

    Oh Ok, now I get it, I did not realize that a number field would not accept dashes.

    Thank you so much for your help.

Login to reply

Content aside

  • 2 mths agoLast active
  • 15Replies
  • 2856Views
  • 3 Following