Adding a search field to the where dropdown on a search screen

By default, when you search for clients using the Clients search screen, you will be searching the client name. If you want to change this to use a phone number, you simply select the 'phone number' option from the 'where' dropdown. But what if there is no option for the field you want to search. For example, what if you want to be able to search all of your clients for some specific information in their memo field? (of course, it's not great to store lots of unstructured information in the memo field)

In this example, we will add memo to the Client screen's search fields:

1. Navigate to the Tools -> Table Definitions screen.

2. Scroll down to the record for the Clients table and double click it to open it.

3. Click on the 'search fields' tab and you will be presented the list of available searchable fields.

4. To add a new field, scroll to the bottom of the page and type in 'memo' for the name of the search field.

5. Change the type of the field to 'SQL where clause'. This means we'll be able to construct the where clause using SQL that will pull the records we are interested in. Creating a where clause can be tricky especially if more than one table is joined together but there are lots of where clauses built into phpBMS so whatever you need, you should be able to find something to work from.

6. The where clause we want for this is :

(clients.comments like '%{{value}}%')

Enter that in and click 'add search field'.

Now navigate back to the Client -> Clients screen and you'll see you can perform a search through all your clients memo fields. In this case since we added a % before and after {{value}}, this search will be able to match the text you're searching for no matter where it is in the memo field.

If you'd like to make this the default search field, just go back to the 'search fields' tab of the Clients table definition and click the up arrow next to your new memo field until it is at the top.