Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

How can I create a report that lets users search for a value in ANY of several fields? In other words, a report that returns records where the name a user enters occurs in the "Contact1" field OR the "Contact2" field OR the "Contact3" field?

To accomplish this, you'd design your report with a custom formula field that combines values in all three contact fields and then (though they won't know it) let users search that field.

To do so, create a new report (to learn how, please see our Create a New Report from Scratch help topic), and select Table report. In the Report Builder, under the "Columns" section, select 'Custom columns'. Once you have added the columns you'd like to appear on the report, check the box marked 'Define a calculated column.' (This feature creates a custom formula field that is part of the report, but not part of the application.) Select the field type "text", and enter a label (in this case "Any Contact Field" might do nicely).

Next, in the formula box, enter a formula that concatenates (links together in a chain) all the fields you want the report to search. This view should look something like this (insert your own field names within the [] brackets):

[contact1] & " "& [contact2] & " " & [Contact3]

This formula creates a new field that contains all the values in all three fields. If any of these fields are user fields, you'll need to convert the values to text within your formula. You do so using the ToText() function on each user field, producing a formula that looks like this:

ToText([contact1]) & " "& ToText([contact2]) & " " & ToText([Contact3])

Next, create your user search box:

Within the 'Filters' section, click the right radio button to enable filtering. From the dropdown on the left, select "<Custom Column>." In the Operator dropdown that appears in the middle, select "contains." Then, in the box on the right, type:

_ask1_

which tells QuickBase to ask the user for this value.

Save the report.

To learn more about formulas in general, please see our Using Formulas in QuickBase help topic.

For a comprehensive list of QuickBase formula functions, please see our Formula Functions Reference.

Comments

searching by state and zip code

Hi

as a sales person we create call lists.  I need to know how to use MYCRM in a way where I can create report OR sort by State.  Your online help is was not helpful

thank you

John

john.andren@tnsglobal.com

Hi John,I'm sorry you didn't

Hi John,
I'm sorry you didn't find the online help useful...can you give me some more detail about what you're trying to accomplish? I might be able to help--if you're asking a question specifically about your application, you may need help from our support team...if so, you can enter a support case here:

https://quickbase.intuit.com/support/portal/#YWN0aW9uPXN1Ym1pdGNhc2U=

Hi John,I'm sorry you didn't

Hi John,
I'm sorry you didn't find the online help useful...can you give me some more detail about what you're trying to accomplish? I might be able to help--if you're asking a question specifically about your application, you may need help from our support team...if so, you can enter a support case here:

https://quickbase.intuit.com/support/portal/#YWN0aW9uPXN1Ym1pdGNhc2U=

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