Discussions

Expand all | Collapse all

Removing unnecessary blank spaces at the end of an entry...

  • 1.  Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    OK this is going to sound completely crazy, just hang with me for a minute...

    I need to eliminate unnecessary blank spaces at the end of an entry. Here's why... we had issues with our employees creating new records every time a customer called - for instance, John Smith calls and wants to purchase more of our services, and they just create a new "John Smith" and enter all his info again, rather than search for him in the database and then add a new job (we call it "opportunity" under his existing record). In an attempt to combat this, I made a formula field which combines the first and last name and then is required to be unique; i.e., a second "John Smith" would not allow the new record to be saved - it kicks back an error. This worked for a short time, until the lazy employees figured out that they could just add a blank space to the end of either name and the system would see this as a unique record. 

    This is also plaguing our "opportunities" field - we do custom closets, so when we first started, everyone was naming opportunities "master closet" which then became a real mess when looking at reports, etc., so we required the opportunity to be unique, which prevented "master closet" until they learned they could just add a space at the end. By now I imagine they must have to just hold the spacebar down for a minute to bypass this rather than just do it the correct way. (I know, I know...)

    Any ideas on eliminating the spaces upon a save, so that I can take this cheat away from them? I cannot simply just count, as the # of words is not fixed, (for instance, "Van Halen" as a last name) and especially in the "opportunity" name field, as we use the street address followed by the room (so that we can keep up as owners of the residence change).​

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------


  • 2.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    In your formula field that creates 'JohnSmith' add this

    Trim([Rest of Formula])

    The trim cuts off all spaces front and back.

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 3.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    so your suggestion is to just eliminate all spaces for the purpose of checking uniqueness? hmm... simple, I'll try that! (headslap) the best solutions are the simplest...

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------



  • 4.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    Edited by Mike Tamoush 14 days ago
    Not all spaces (I realized I wrote JohnSmith instead of 'John Smith'). Trim does not take spaces out from the middle of words (Like 'Van Halen'), It simply removes any leading and trailing spaces.

    So: 'Van Halen___' becomes 'Van Halen'.

    Though, what you mentioned isn't a bad idea either. You can use SearchandReplace and replace every space with a blank...and just have it be one run on word...that way lazy employees can't start adding extra spaces to the middle...

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 5.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    Great! This was a simple and elegant solution, I'll post back when I've implemented it, thanks!

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------



  • 6.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    So I'm curious, how would SearchandReplace work? I've never used that function before.

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------



  • 7.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 14 days ago
    This would replace spaces with nothing (note i Have a space in the first set of quotes:
    SearchandReplace([My Field], " ", "")

    So that says, search My Field for any space, and replace it with nothing.

    Quickbase's description below.

    SearchAndReplace (Text textToSearch, Text searchText, Text replacementText)

    Description: Replaces ALL occurences of a given search text with the replacement text. Search is case sensitive.

    Example: SearchAndReplace("John Smith", "John", "Jane") returns "Jane Smith"

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 8.  RE: Removing unnecessary blank spaces at the end of an entry...

    Posted 13 days ago
    Just a follow up... i made new fields (Customer Name Check & Opportunity Name Check) and set them to be unique, using the "SearchAndReplace" function as described above. It seems to work perfectly in my test drives. I guess now we're going to see if I get any more duplicates or employees complaining, and I'll know for sure. If anything seems to fail about this, I'll post here again, otherwise it worked great! Thanks for the simple solution!

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------