[Solved] Search functionality in one SubForm

Creating and using forms
Post Reply
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

[Solved] Search functionality in one SubForm

Post 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.
Attachments
Customers sample.odb
(17.07 KiB) Downloaded 247 times
Last edited by preks on Sun Mar 05, 2017 8:04 pm, edited 4 times in total.
LibreOffice 5.2.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search functionality in one SubForm

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Search functionality in one SubForm

Post 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?
LibreOffice 5.2.5 on Windows 10
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Search functionality in one SubForm

Post 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?
LibreOffice 5.2.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search functionality in one SubForm

Post by Villeroy »

Well done. The empty entry is always there when the field content is optional, when the table field is nullable.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Search functionality in one SubForm

Post by preks »

Thank you very much for your effort. I'll mark it as solved.
LibreOffice 5.2.5 on Windows 10
Post Reply