Database Help
David Crowe, Version 12.04, July, 2016.
Quick search for quote records
The quick search field allows you to do many searches by simply entering your search term in the quick search field and pressing RETURN or ENTER. Some hints for more effective searches are:
- If you enter a number, only numeric fields will be searched unless you enclose it in double quotes. You can precede a number with an inequality or equality symbol (<, <=, >, >=, =).
- If you enter a date of the format mm/dd/yyyy (numeric month), only date fields will be searched unless you enclose it in double quotes. You can also precede a date with an inequality or equality symbol.
- You can indicate a range of dates or numbers by putting '..' between two values.
- You can look for records that do not match your search term by preceding it with !=.
- The way to initiate a quick search is by pressing RETURN or ENTER when your cursor is in the quick search box.
- Many of the advanced FileMaker search symbols are available for use, such as //, * and @.
- If you cannot get what you want with Quick Search, use the more sophisticated and flexible Advanced Search.
Advanced search for quote records
This web page uses FileMaker search conventions (because it is searching a FileMaker database). These allow for the simple creation of very powerful queries. Note that the search is additive, quote records are returned that match all the criteria that you specify (or if you specify != queries, that match none of the criteria).
- Entering a single "word" (which means a contiguous series of characters without spaces or punctuation) matches all quote records containing that word or one starting with it in the specified field. Entering Smith will find quote records containing Smith and Smithers but not Psmith.
- Entering multiple words will match all quote records containing all words, in any order. Entering John Peter will find quote records containing John Petersen, Peter Johnston and Peter John Smith but not John Smith or Peter Smithers.
- Entering == (double equal sign) into a field matches quote records where this field is empty. Entering * (asterisk) matches quote records where this field is not empty.
- Entering = followed by a word matches the word exactly. =John matches a quote record with John but not one with Johnston (unless it also contains John).
- Entering == followed by a phrase matches quote records where the field contains that exact phrase so ==John Smith matches only when the field contains John Smith and nothing else.
- Putting a phrase in double quote marks matches it exactly even if it contains spaces or punctuation. "10 Downing" matches 10 Downing Street but not Downing 10 pints.
- Using the "at" sign (@) matches a single character. Crow@ matches Crowe but not Crow . Similarly, a # matches a single digit. 1## matches any number in the hundreds.
- * matches zero or more characters so Pe*n matches Pen or Petersen. You can use this in a date, so 2/*/2020 is any date in February of 2020.
- Inequality operators match fields containing data that is less than (<) something specified, less than or equal to (<=), greater than (>) or greater than or equal to (>=). This is most useful with number or date fields but can also be used with text fields. <10 matches quote records containing a number less than 10. >=1/1/2015 matches all dates in 2015 or later.
- The two character prefix "!=" matches quote records that do not have this value in this field. For example, !=Alpha would match all quote records that do not contain Alpha in this field.
- Two periods (..) finds a range of numbers (e.g. 10..20), dates (e.g. 1/1/2010..5/31/2010) or text (e.g. A..B finds all fields containing words starting with A as well as the letter B, but not words starting with B).
- Two slashes (//) matches the current date. 1/1/2013..// matches all fields with dates between the beginning of 2013 and today.
- For most fields, you can search for multiple values by separating them with a comma so, John, Peter matches all quote records containing either John or Peter.
- You can issue queries from parameters attached to the URL. Type the URL and add ?Help to the end to get instructions for this. If you include SearchControl=Static then the search will occur without the query interface being displayed. To issue a query and export the results immediately include SearchControl=Export.
- Note that the characters = and & cannot be used in URLs except to identify URL parameters. The sequence %3D must be used instead of = and %26 must be used instead of &. So, for example, &Count=<=3 (search for field count less than or equal to 3) must be specified as &Count=<%3D3 (note that the first & and &eq; are interpreted as part of the URL so must not be substituted). It will be less confusing to use < and > instead of <= or >=, when possible.
- Special characters are special so you may have trouble if you try to search for a comma, at-sign (@) or other characters described above with specific meanings. You can usually avoid this. If you want to search for #123, 456 Main Street, NW, searching for "123 456 Main" will get the right record and probably nothing else.
Questions? Problems?
Email me.
Version 12 features and bug fixes
New features in this version include:
- Use of record id to make the application faster and more reliable.
- Button to copy records to the clipboard.
- Ability to add custom scripts.
- Ability to place buttons above, below, to the left, or to the right of each record.
Version 11 features and bug fixes
New features in this version include:
- Access to subsidiary records (portals) for query, modify, add.
- Better support for Pulldown menus (working around FileMaker limitations).
- Improved formatting of monetary amounts.
- Allow queries containing the '@' character.
Version 10 features and bug fixes
New features in this version include:
- Quick search, allowing most searches to be performed by just typing the search criterion in a single form field.
- Export button on Search tab allows an export without first displaying the results on the screen.
- Export is possible to a tab-delimited file (.tsv extension) as well as comma-separated, the default (.csv) if the 'exportfilename' is specified on the command line with the appropriate extension (.tsv or .tab).
- Better recovery from web timeouts.
- Making sure only one search can occur at a time for any application.
- Provide mouseover for Export button including the name of the export file.
- Control of initial field values from URL typed by user.
- Help for adding records, modifying records, and quick searches.
- Ability to run a query from URL parameters, and to either display the results on the screen, or export them, without seeing the query form.
- Ability to specify the name of the export file in the URL.
- Allow search with no criteria (to retrieve all records).
- Fewer problems with searches with special characters like @, #.
- Improved compatibility with Internet Explorer (but please avoid using this horrible piece of software).
- Slightly obscured email addresses to make it harder for spammers to harvest them.
Version 9 information
Version 9 contained the following new features and major bug fixes:
- Allow logout button for guest access when personalized login is also allowed.
- Allow application to block write access for all users regardless of the FileMaker privileges for the user.
- Log major events for statistical purposes.
- Adjust placement and adjustment of tabs based on screen size.
- Allow application to be split between two servers.
- Provide more granular access types for fields that are only used for searching, only for display or only for internal purposes.
- Graphical search progress histogram.
- Multiple values may be allowed in a field (depending on configuration), separated by a delimiter like a comma.
- Searches allowed with no constraints (i.e. to fetch every record).
- Adapted to the many idiosyncracies of Internet Explorer and Firefox.
- Deletion of record without needed to open it for editing.
- Intelligent handling of special characters due to http and FileMaker restrictions (e.g. #, @).
- Allow new records to be added, and edited after adding.
- Support summary fields (such as a total).
- Do not display break or summary fields if not sorted by the controlling field.
Version 8 information
Version 8 contained the following new features:
- Coexistence of a production version and a test version.
- Ajax (dynamic web) techniques for search, delete, export.
- Convert to multi-search format to allow more reliable negative checkbox searches.
- Move field descriptions to a separate file.
- Sort in descending order (checkboxes beside sort field menus).
- Tabbed interface, including help and logout as tabs.
- Negative searches with "!="
- Add/Search tabs to do the operation when the tab is already active.
- Customize help file for each application.
- Compress search form by putting title of field inside text entry box.
- Work with application distributed between two servers.
- Allow dividers in pulldown menus.
- Segregate multiple applications on a single page.