Getting Started

Expand all | Collapse all

Force Prefix on User's Data Entry

  • 1.  Force Prefix on User's Data Entry

    Posted 12-23-2020 13:34

    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
    ------------------------------


  • 2.  RE: Force Prefix on User's Data Entry

    Posted 12-23-2020 13:43

    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
    ------------------------------



  • 3.  RE: Force Prefix on User's Data Entry

    Posted 12-24-2020 10:31

    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"? 




  • 4.  RE: Force Prefix on User's Data Entry

    Posted 12-24-2020 10:48

    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
    ------------------------------



  • 5.  RE: Force Prefix on User's Data Entry

    Posted 12-24-2020 10:49

    No. You would only have the Data Entry field - which would only show in Add/Edit mode of the form - and the Formula field, which would be used in View/Edit mode of the form and anywhere you need to use the PO info.



    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------