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:
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.
Browse by Category
Knowledge Base Categories
- Accounts (29)
- Application (54)
- E-Mail (7)
- Fields (40)
- Forms (19)
- Formulas (19)
- General (65)
- Other Product (7)
- Pages (1)
- Product (4)
- Reports (15)
- Roles (4)
- Search (1)
- Tables (5)