How can I set up a numbering system to track quotes that numbers each quote for a customer sequentially, and includes the customer ID and quote number?
[The Quick Base Knowledge Base is your library of frequently-asked questions that help you better customize your apps to solve your business problems.]
You can easily do this in Quick Base. You'll need to create a few new fields in both your customer and quotes tables, relate these tables and then set up a special field to capture this combination number. To do so:
1. In your Customer table, create a Numeric field called "Customer Number".
To learn more, please see our Add a New Field help topic. Once you've created this field, click its name to open field properties. Then turn on the "Values in this field must be unique" checkbox and click Save.
2. Create a relationship between the Customers table and Quotes table.
In this relationship, each Customer can have many Quotes. Therefore, the "one" is Customers and the "many" is Quotes. In other words, Quotes will be the details table that features a lookup to Customers, the master table. (To learn how to create a relationship, please see this help topic.)
3. In the Customer table, create a summary field that captures "# of Quotes".
From the Customer table, click Settings, and then Table-to-table relationships. Locate the relationship and click on its name to edit it. On the Relationship Properties page that opens, click Add summary field, make sure that the "Number of Quotes" radio button is selected and click OK. Accept the name Quick Base gives to the field (or type in your own) and click OK.
4. In the Quotes table, add a lookup field that captures the summary field you just created.
You can do this from within the Relationship Properties page you accessed in the previous step. On the right, click Add lookup field. Within the Lookup field dropdown list, select the summary field you just created (called something like "# of Quotes") and click OK.
5. Create a Snapshot field to snapshot (or capture in time) the value from the lookup field you just created.
In the Quotes table, create a Numeric field called "Snapshot". Click on this field's name to edit its properties. Under Advanced, check "Get this field's value from a lookup field and don't allow the value to change." From the dropdown list, select the lookup field. (To learn more, please see our Set Up a Snapshot Field help topic.)
6. Now, tie it all together by creating a Formula field in the Quotes table.
In the Quotes table, create a new Formula - Text field called QuoteID (or whatever you wish). Click the field's name to open its properties. Enter the following in the formula box: [Related Customer] & "-" & [Snapshot] (For more on using formulas in general, please see this help topic.)
From this point on, Quick Base will sequentially generate a number for each Quote which includes the customer number. So if customer number 101 had two Quotes, these Quote records will be numbered 101-1 and 101-2.