Expand all | Collapse all

Pick List

  • 1.  Pick List

    Posted 02-28-2017 18:12
    This is sort of two questions, though I only need one answer.  We use QB as our CRM.  One of the items we include for each new customer is a code name.  Each codename is required to be unique in the field properties.  I would like to do one of the following:

    1) Create a self-deleting pick-list whereas once a codename is selected, it is no longer available on the list; or

    2) Create a script where every new Customer record is automatically assigned a randomly generated alpha-numeric code name.

    Any help is greatly appreciated.


  • 2.  RE: Pick List

    Posted 02-28-2017 18:18
    You could do this through a "Code Name" table and relationship (Each Code has Many Customers).  Then you need to set the record picker to only show ones where the code has "0" customers.

    YOu then have a simple form rule, that will copy that code name to your unique code name field.

    can you give an example of some of your code names?

  • 3.  RE: Pick List

    Posted 02-28-2017 22:36
    I like this approach.  I've created the Code Name Table, and built the relationship.  The one thing I can't figure out how to do is to set the record picker to only show ones that has "0" customers.  I'm assuming that would be a formula of some sort?
    Sorry, I'm fairly new to all of this.


  • 4.  RE: Pick List

    Posted 03-01-2017 02:33
    Sorry, I should have explained that more.

    In the relationship between the two tables, create a new summary field.
    it will default to something like [# of Customers]  which is fine.
    On the "Code Name" table, make a new report called "Available Code Names"
    Add the 1 or 2 columns that you want to appear for the 'record picker'.
    (this is what will show in the dropdown)
    Add a filter to show the code names where [# of customers] is equal to 0.
    (This will exclude any code names that have been used)
    Go back to your customer table,
    click "Customize this form"
    Find the form element for the record
    Change the dropdown from 'use the standard record picker' to your "Available Code Names" report

    = Now you will be able to only select the available codes


  • 5.  RE: Pick List

    Posted 02-28-2017 20:01

    Just use script and don't even worry about collisions. Here are two methods:

    Pastie Database

    The first method just generates a random 8 character "password" while the second method generates a ob32 encoded string based on the current time. FWIW, ob32 encoding is the encoding used to generate dbid's.

    I seriously doubt you will have to worry about two people having the same code or that someone would attempt to discover the ob32 string by regenerating it through code based on the timestamp.