Forum Discussion

GerardLibby's avatar
GerardLibby
Qrew Member
4 years ago

Force Prefix on User's Data Entry

New QuickBase user, trying to figure out how to replicate something I was used to from MS Access.

I have a text "Purchase Order" field that will store a unique entry as "PO-XXXX" where X is a number. I'd like the user to be able to type "PO-1234" and have that data in the table as "PO-1234" or if they type "1234" then I automatically add the "PO-" prefix and the data again gets stored as "PO-1234." 

I can figure out the logic of testing and adding the text, but I can't figure out where this formula should live or how to implement it. 



------------------------------
Gerard Libby
------------------------------
Forms
Formulas and functions
Tables and fields

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago

    No Problem.

    You will want to have two fields.  One for data entry and one for the formula to calculate the correct value for display purposes.

    I suggest try this as a formula.

    "PO-" & NotLeft([PO data entry field],"0123456789")

    The function for NotLeft is like a Pacman character coming at the data entry field from the Left and discarding everything until it hits any of the values 0123456789.  Regardless if your Users enter the PO# perfectly starting with PO-, it will simply discard that and then put it right back in as a prefix.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------

    • GerardLibby's avatar
      GerardLibby
      Qrew Member
      4 years ago

      Thanks! So if I'm understanding this correctly, my table will have a field that's mostly junk data (values both with and without the PO prefix) as well as the "good field" that's been manipulated by the formula? Also, would I have to put another formula on the data entry box on the field so that it is apparent to users that the data has been auto switched to adding "PO"? 

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago

        The data entry box would only show in Add and Edit mode on the form. It will never be used on a y reports. I would name that field like [PO# Data Entry].  Then I would show the would show the [PO#]  (ie the formula field)  in View mode.  You can decide if you also want  it to show in Add Edit mode.

        So there will just be two fields, one for data entry and one for display purposes.



        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------