Page 1 of 1

[Solved] Search functionality in one SubForm

Posted: Sat Mar 04, 2017 5:22 pm
by preks
Hello to all,

I have created a CRM database to store my customers' company details, personnel, visits on their companies etc.

I have made a form in which I have included some SubForms and a filter table which I use to jump from record to record.

Now, is it possible to create a search function (using a text box maybe) to search in one SubForm? Specifically, I want someone to be able to search for a person's name in People SubForm. If it finds it, it must jump to the current company record and show the related company's details, the people working in this company and the visits made to this company. Does it need a second filter table for this action?

I wuold use the default search in navigation bar but it returns me no results when not searching the MainForm.

Thank you in advance.

Re: Search functionality in one SubForm

Posted: Sat Mar 04, 2017 10:56 pm
by Villeroy
The built-in filter is case sensitive because VARCHAR fields are case sensitive. Change the type of the name fields to VARCHAR_IGNORECASE in order to filter in a case-insensitive way. With or without this change, you can set the "Filter Proposal" property of the grid's name column. Now the text field will turn into a list box when you switch to form filter mode.
The filtering main form should have everything disabled except "Allow modifications". A single record does not need any navigation toolbar. The criteria row must not be deleted from the table. The user must not tab into the next (new) record.

Re: Search functionality in one SubForm

Posted: Sun Mar 05, 2017 12:21 pm
by preks
I tried all the suggestions you told me. I turned to IGNORECASE the name and surname, I left only the Allow modifications in filter form enabled and I enabled the Filter Proposal value to both Name and Surname.

Still "No records corresponding to your data found." when using the built-in search. I think this issue derives from the fact that when I choose a company, I see the record of this company and in the navigation bar it says "Record 1 of 1". Indeed, I'm able to search only within the chosen record, but if i search for a person existing in another record it will appear this warning message.

That's why I think I need a new custom text box with a search button, I just don't know how to create it. What do you think?

Re: Search functionality in one SubForm

Posted: Sun Mar 05, 2017 2:43 pm
by preks
Well, I just copied the current listbox and changed the List content field to:

SELECT "Name" || ' ' || "Surname", "CompanyID" FROM "People"

Now, whichever listbox selection was made last, that company will be displayed when the button is pushed.

No text box, but it works fine too. :)

Oh, only one more tiny detail. In the Companies list box (and the new list box I placed) there is in the top, above all companies, an empty row. Do you know why does this row appear and how I can get rid of this?

Re: Search functionality in one SubForm

Posted: Sun Mar 05, 2017 4:17 pm
by Villeroy
Well done. The empty entry is always there when the field content is optional, when the table field is nullable.

Re: Search functionality in one SubForm

Posted: Sun Mar 05, 2017 8:03 pm
by preks
Thank you very much for your effort. I'll mark it as solved.