Custom Search triggered by an Action Button

Question

I love the idea of this tool: To provide just enough access to tables in a database to facilitate basic database admin actions without having to develop a fully-featured frontend for the database.

However, the missing feature for me to be able to efficiently use this as part of my (and my co-workers) daily workflow is saving and naming oft-used searches/filters. Without this feature, I'm required to spend too much time searching for data to view or edit quickly and then move on to another task with the same table but working with a different resultset.

I should be able to save and name multiple searches as filters for the current table. For example:

Sales in a Zip code
Sales by a sales rep
Sales of a given product

Moreover, these saved filters should allow for simple parameter prompts. For example, the "Sales in a Zip Code" would allow either a constant entered in the Zip Code column parameter, or something like "[Enter Zip Code]" where, upon selecting the "Sales in a Zip Code" filter, a modal dialog opens and allows me to enter the zip code I'm seeking using the prompt I specified (look at how this feature is implemented in Microsoft Access). If I also have a "[Enter the Sales Rep ID]" in the Sales Rep search criteria then the dialog prompts me for both criteria.

I'll add that a similar feature of naming layouts (selections of visible columns), naming sort orders (eg. "Sort by Create Date", "Sort by Delete Date, Customer Number", etc would be especially useful.

Finally, I'd note that if the above features were present, and control of Search Filters, Layouts, and Sort Orders could be controlled on the user level, you would have a tool that many of the very large user base of users of services like Airtable would profoundly appreciate and pay for. Imagine: Airtable types of features without their limitations, using my own databases that likely supports other corporate functions or websites, without giving up control.

Until such time as these features exist, I suspect I won't be able to use this tool as much as I'd love having direct control over admin access to tables that are part of our corporate workflow.

Comments

Would custom Administer defined searches implemented as Action Buttons satisfy your needs?

---

Perhaps. It would depend on if you were able to also implement promptable variables (e.g. [Enter the Sales Rep ID]).

My fear about that implementation would be a cluttered UI unless there was some Action Button grouping mechanism.

Answer 1

As of dbFront 1.2.5, you can create Custom Search buttons in dbFront. Any permitted user can create their own Custom Search Button. Admin users have access to enhanced functionality (Custom SQL and Prompts).

Anyone can create a Custom Search Button simply by clicking on the "Save" icon on the Advanced Search.

The steps to create an Advanced Custom Search button are:

1. Create an empty Action Button,
2. Set the Action Type to: Custom Search,
3. Select any desired Prompted Filter Fields,
4. Select specific columns and set constant values,
5. Specify a SQL Expression.

Custom Search buttons will appear directly under the table and just above the Advanced Search panel. This makes it trivial to switch between different searches. The selected search will remain highlighted.

There is no automatic Clear Search button since there may be scenarios where a user must always select one of the specified searches. To create a button that clears the selected search, simply create a new button, name it something like "Clear Search" and don't specify any search parameters.

Answer 2

Having multiple Forms/Views is covered in: Multiple Forms

Using Existing Functionality

The advanced search does allow you to quickly move between simple searches. If both Zip code and Sales rep fields are available in the advanced search, then clearing the Zip code field and selecting a Sales rep is a simple and painless process.

It becomes more complex when looking at a product because you normally have multiple products (items) per individual sale. This could best be accomplished by switching to the product table, selecting a product, and then viewing all of the joined sales for that product. This could be made easier by allowing the advanced search to also look at child table fields.