Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
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?
You can easily do this in QuickBase. 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 about creating fields, read https://www.quickbase.com/help/adding_a_field.html. Once you've created this field, click its name to open field properties. Then turn on the "Require unique values for all entries in this field" 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 see: https://www.quickbase.com/help/creating_a_relationship.html)
3. In the Customer table, create a summary field that captures "# of quotes".
In the table bar on any application page, click one table in the relationship. Within the menu that appears, select Customize --tablename-- table > Relationships. Locate the relationship you created and click its Edit link. 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 QuickBase 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, under "Details Table - Quotes" 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 Options select the lookup field from the Snapshots dropdown list. (For more on snapshots see https://www.quickbase.com/help/setting_up_snapshot_fields.html)
6. Now, tie it all together by creating a formula field in the Quotes table.
In the Quotes table, create a new field called QuoteID (or whatever you wish). Click the field's name to open its properties. Select the field type "Formula-Text" and then enter the following in the formula box: [Related Customer] & "-" & [Snapshot] (See https://www.quickbase.com/help/using_formulas_in_quickbase.html for more on using formulas.)
From this point on, QuickBase 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.
Browse by Category
Knowledge Base Categories
- Accounts (35)
- Application (45)
- E-Mail (7)
- Fields (41)
- Forms (20)
- Formulas (18)
- General (68)
- Other Product (7)
- Pages (1)
- Product (5)
- Reports (15)
- Roles (4)
- Search (1)
- Tables (6)