Knowledge Base

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 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 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, 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, 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.

Comments

this was working great but

now it has stopped at 44. one thing we did not do upon creation is "require unique values" but i cant turn that on now or i get an error message.


this was working great but

Hi Drew,

 

My name is Brian from QuickBase Support. I just need a bit more information to investigate this:

 

- What table are you working on?

- Could you invite me to your app so I can take a look on my end?

 

To share your application, enter your application > Users > Share with a New User. On the next screen, click the Address Book icon.  Make sure the search box is set to "All QuickBase Users", then in the search box enter the screen name QB_Support_Brian.  This screen name should come up in the results.  Select it so it's shown in "Current Selections" and then click OK.  Please use the assign a role drop down to assign me the same role as you.  Finally, make sure "Send an email" is checked and then click the Send button. 

 

Thanks,

Brian

QuickBase Support

Numbering for existing records

I set up this formula and it is working for new records.  However, it has numbered all existing records the same.  For example, if there are 4 related records they all say 1234-4 instead of 1234-1, 1234-2, 1234-3, 1234-4.  How can I fix this for existing records?

Me too...ever figure this out?

I'm having the same issue; did you ever find out what the problem was? So far, the only way I've been able to address it is to delete the prior cases and input them again after resetting the counter.

RE: 'Me too...ever figure this out?'

Hello,

In this situation it would be best for you to open a support case by logging in to QuickBase and clicking Help from any screen, and then clicking Manage Support Cases, and New Support Case. That way one of our customer care reps can reach out to you and take a look at the setup in your particular app to see what's going on there.

Thanks!

Brian

Intuit QuickBase

Lookup field

Given how the formula was constructed, I suspect either the lookup field or the snapshot of causing the issue you're seeing. Look at the data by creating a report in the Quotes table showing the lookup field, the snapshot field, and the formula field. Then create a report in the Customer table to look at the Customers and the number of quotes they've been issued. If those investigations do not reveal the source of the behavior you're seeing, you may want to contact Customer Support for help in troubleshooting the formula.

Regards,

--Sybil

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

© 1997-2014 Intuit Inc. All Rights Reserved.

Online Database VeriSign Secured Web Based Software TRUSTe Certification Online Database SSAE Audit