Using Wildcard Search in Form

[Shrimpette]

Hello again

Yet another question from me I am currently using a database I created myself to keep records for our business, but want to streamline it and make it more "user-friendly" (after 2 years, there are lots of "in working practice" things that I think would be helpful.) I am very new at this, but am trying to learn as I go

I have been struggling with this for approx. 2 months now (when I have had time), but would be very grateful for any tips or advice that you can provide. I am throwing my hands up and debating whether my expectations are just too high!!

Here is my new database relationship design:

We have several clients, who may have several sites. Each site will have different jobs (e.g. Call out, Service, etc) and different engineers may attend jobs. I am hoping I have created the relationship design correctly.

I have created a test form which I am currently playing with to get it to do the things I want (see my previous post, lol!) and have attached a copy of my draft blank database. My Test Form is a way for me to input data to all table quickly & efficiently (well, that's the idea, lol!) I will be extracting the data in a variety of different ways (reports, filters & queries). I would be very grateful if someone could look at it and help me with the following queries:

1) I have found that a slight difference in "Site" has caused issues (e.g. duplicate records for a site, where someone has spelled the road name incorrectly). To avoid this, is there a way to type a string of characters (or whatever) into the "Site" Combobox and have a dropdown list appear that shows all sites with those characters in it? My aim is for the user to select from a list of sites, so that I can avoid duplication - I think it is based on a wildcard expression but am not sure how to implement this.

2) I am struggling to get the FilePicker Dialog to populate the DocScan box (as with my previous post http://forum.openoffice.org/en/forum/vi ... 39&t=61156). I tried using a separate Form for this, but am still unable to populate it. Is there a better way to do this instead of macro-driven (I was intrigued by reading several of Romke's posts that say most things can be done without macros?!)

3) I will be creating separate forms to populate the "Client" Table, but I need the Clients, Sites, Engineers and Jobs table separate so that I can create appropriate queries (e.g. which engineers have visited Site A, how many jobs has Engineer B done this month, etc) As an example, I have created a blank version of my original database - but I have exceeded the file size limit for this post, so will post it separately (in case it helps to see my vision for the database).

I haven't got as far as using any other programs (e.g. Writer, etc) and have used only the Forms etc from within Base. Is this appropriate?

Thanks in advance for any help you can give me
Shrimpette x

[Shrimpette]

blank BKF Jobs.odb

(59.89 KiB) Downloaded 212 times

And here is a blank version of my existing database - I haven't got as far as using the "Originating Email", "Followup" or "Job Documents" Tables yet. I want to be able to see all aspects of a job, from the original job request, through to any documents related (e.g. site photos, letters, etc) as well as record any followup that is needed so that we can keep on top of site requirements.

[DACM]

1) I have found that a slight difference in "Site" has caused issues...type a string of characters (or whatever) into the "Site" Combobox and have a dropdown list appear that shows all sites with those characters in it? My aim is for the user to select from a list of sites, so that I can avoid duplication.

Shrimpette,

In your first question above, you're describing the normal function of a List Box, including the type-in auto-complete function that jumps to relevant entries assuming sorted List Content. A List Box doesn't allow the user to add new entries as with a Combo Box, but if duplication and/or accuracy are the primary goals, then a List Box is the answer.

[Shrimpette]

DACM,

Thank you for that tip - I will be playing with that suggestion later to see how it could work in practice? I think that would work perfectly in avoiding duplicate job records, however would it also work if I had partial matches for similar addresses, e.g. a) Sovereign House, Albany Street, Essex or b) Sovereign House, Breams Road, Yorkshire or c) 37 Sovereign Road, Isleworth? Would it show all the options available if I typed in Sovereign? (That's what I will be testing later, lol)

That's the general direction I was heading with this, as another problem is that we also have sites that sound similar but are in completely different places! From my searching and reading of threads on this site, I thought about using a wildcard SQL statement in the listbox but have no idea whether this is even the right implementation. Basically, I want to build checks into the data input process - I enter all the job records, but am getting overwhelmed with the amount of cross-checking I have to do each month to ensure no duplication. As the company grows, I want to avoid this getting out of hand!

I will probably be back later once I have tested a couple of versions of listboxes.

Thanks!

[DACM]

...would it also work if I had partial matches for similar addresses[?]
a) Sovereign House, Albany Street, Essex
b) Sovereign House, Breams Road, Yorkshire
c) 37 Sovereign Road, Isleworth

Yes and no. The List Box type-in search function operates from left-to-right. So if you begin typing "Sovereign" only (a) and (b) will be in view. If you begin typing "37" then only (c) will be in view among other addresses that begin with "37". The entire address may be displayed in the List Box using 'concatenation' as shown in the 'List Box' link in my first post. So hopefully your users will be able to pick the correct site from the displayed full-address.

Otherwise, a wildcard search can be implemented even across multiple fields. So your user could type "Sov" in one text box and "worth" in another text box and get only (c) above. See: [Example #3] Filter/Search with Forms (leveraging SubForms)
...

[Shrimpette]

Seriously - I think I might love you guys (DACM, Sliderule & RPG)!

No, I mean really love you.

I just had a quick scan through your links before starting the kids' bedtime, and wow - you have given me so much to think about!! (As always) I am now considering several text boxes for the site address, instead of one long box....

Here goes with even more experimenting!! Concatenate function, here I come

Shrimpette x

ETA: 3 hours later - I like the possibilities, and also the very clear explanations provided in your links!

[Shrimpette]

Hello again,

Thank you again for the fab idea - I have been playing with the tables all morning, and now have a wonderful list box, that concatenates an address string to provide a dropdown list! Woohoo!

I now have a "Sites" Form, which allows for the address entry and concatenation into a single string. This Single string populates the "Site" Field within the "Site" table. I then use a listbox in the "Test Jobs" Form to choose a site from the "Sites.Site" Field. As in my other example, I have some properties which have similar strings so I now need to be able to change that listbox into a textbox (or 2, as in your second link) that will display a list of addresses. I can sort of do that based on your link (leveraging SubForms) but am confused as to how that will populate the "Jobs.Site" Field. Not entirely sure I have understood the parameter query but am willing to experiment!

I am thinking a SubForm based on a query, to get a popup box for the user to type in a search string? Would this work (or am I making too big of a leap?)

Thanks
Shrimpette

[DACM]

Congrats on your progress! Base can be almost fun when you tap the power of SQL under-the-hood.

...I now need to be able to change that listbox into a textbox (or 2, as in your second link) that will display a list of addresses. I can sort of do that based on your link (leveraging SubForms) but am confused as to how that will populate the "Jobs.Site" Field. Not entirely sure I have understood the parameter query but am willing to experiment!

Here's the key points from the example:

...the complex SQL-logic and parameter-query variables do not require adjustment as we simply drag-&-drop an example Form (and the associated "Filter" table) to another Base application. Then to adapt one of these example Forms to search a different Table, simply replace the "Table" and the associated "Field" references ("T1", "T2", etc.) in the Query Designer (shown below)...

...And that's it!

So, I would literally drag-&-drop the appropriate example Form and the Filter table to your Base project. Open both side-by-side and drop the Form onto the Form icon, and the Filter table onto the Table icon respectively across instances of Base. Then if you run into problems connecting the Form's "Field" references ("T1", "T2", etc.) in the Query Designer, simply post what you've got and we can hook it up for you. Then build on that Form as a template.

I am thinking a SubForm based on a query, to get a popup box for the user to type in a search string? Would this work (or am I making too big of a leap?)

A popup is the traditional implementation of a Parameter Query. When you add a parameter to a SubForm query, without specifying the value through the SubForm linking mechanism (as in the example), you'll get a popup each time the Parent-Form record-pointer is changed (such as through normal record navigation). It's up to you, but the Base popup can be confusing to an end-user perhaps particularly when multiple fields are involved.

[Shrimpette]

Ha, well I think my ambitions are way higher than my current capabilities

But I am learning, especially with the support of this forum. 2 years ago I hadn't even heard of Base, nor created my own database before Everything I see here inspires me a little more - those glimpses of the possibilities have given me a vision, but whether I can learn enough to actually create it is another thing. I owe everything I have learned so far to the wonderful members here who spend your valuable time writing posts & tutorials helping newbies like me so THANK YOU! I have lost hours and hours reading here whilst experimenting, so it is kinda fun! Or maybe I am just weird but the end result has helped our business immensely. Now I want to take it further...

I have spent some time today playing with the "4 Text Box Search AND" Form - I think I need a LOT more time to understand the connections fully! Have converted the SubForm's Table reference, adapted the master/slave fields and changed the T references, but no luck so far I have a lot of questions though, which I am sure will be answered by my various experiments with the query in SQL view (e.g. Do I need the UPPER reference in the Query designer? I removed it, as my fields are VARCHAR_IGNORECASE but I am now realising it's not the same thing )

I am currently getting a popup box when I hit search (have absolutely NO idea how I have done that ) so am backing away slowly from the laptop and will work it out tomorrow... hopefully!

I will return once I have the connections clear in my head (so that I can understand your explanations when I am begging for help, lol!)

Thanks again
Shrimpette