Forum Discussion

AmyMigliore's avatar
AmyMigliore
Qrew Member
12 months ago

Auto correct fields based on formulas?

I have data that is for the same company but is manually put in and is sometimes misspelled and not the same way it is entered. Is there a way for QB to look up the misspelled entries and autocorrect that field to be the correct spelling of the company and therefore match all the entries up to the one correct company? Can this be done with a formula?



------------------------------
Amy Migliore
------------------------------

3 Replies

  • Spell check auto correct is a tough ask.  Is there some way you can control the data entry by making the users select off a list that comes from a relationship. 

    If you trust the users and you do it via our relationship then you can allow the users to add new choices to the master table if they don't find the company they want on the drop-down list.  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • AmyMigliore's avatar
      AmyMigliore
      Qrew Member
      So unfortunately the data is not being added to the quickbase first. It's coming from an excel spreadsheet and we can't have a list for them to choose from. I'm hoping that quickbase could correct or match the company names based on some logic?

      Amy Migliore | Communications Production Manager, Resources For Living

      https://www.resourcesforliving.com/

        A  company  

       

      CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by email or telephone and destroy all copies of this communication and any attachments.



      NOTICE TO RECIPIENT OF INFORMATION:

      This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.

      This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.

      Thank you, Aetna



      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        OK, so no problem.  I've had to deal with similar situation's with data feeds from outside sources.  

        One solution is to create a table called corrected companies. The key field of the table will be set to be the [company entered]  and then you will also have a field for [corrected company].

        You would initially load up that table with all of the correct company names with the same spelling in both columns.  

        you would then make a relationship where one corrected company has many data feed record based on a reference formula field equal to the data as entered.

        You would have a look up of the corrected company name and use that in any reporting that you do in the data feed table.

        Then make a report of data feed records where the corrected company is missing sorted by company as entered, and you would subscribe yourself or some designated person to receive a report of data feed records where there is no corrected company.

        Make yourself a formula url button button to API_GenAddRecordForm to create a new corrected record. 

        So the point of all this is you only have to correct I misspelled word once. So if the data comes in regularly as 

        FedX and it should be FedEx, then you only need to make the correction once in your life. 

        Another thought is that you probably want to have the field for the company as entered in that master table be in upper case and the reference field on the data feed table be

        Upper( [company name as entered]).  That will handle all the situation's where the non-standardization of the [company as entered] is due to undesirable use of upper and lower case.  

          



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------