Forum Discussion

MikeMike1's avatar
MikeMike1
Qrew Cadet
8 years ago

Pick List

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.

Mike

4 Replies

Sort By
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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?
    • MikeMike1's avatar
      MikeMike1
      Qrew Cadet
      8 years ago
      Matthew,
      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.
      Thanks,

      Mike
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      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

      #NoCodeRequired
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago

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

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=558

    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.