Pick List

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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
Photo of Mike

Mike

  • 554 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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?
Photo of Mike

Mike

  • 554 Points 500 badge 2x thumb
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
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb

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.