Searching using drop down menu

[Merlin80]

Hello. I have recently started developing databases in Filemaker after using MS Access (advanced user). Although this platform is MUCH better I am still a little stuck on how to navigate around my database (I am not a fan of the standard Filemaker menus). In Access I used to use a drop down menu to select a record i.e. you click on the field, a drop down menu appears and you click on the record you want to view and it brings up the info. I have spent HOURS trying to figure out how I can do this in Filemaker with no luck - I am probably missing something simple.

Can someone please help me?

Thanks.

PS if it involves a script please remember I am completely new to Filemaker and am not 100% sure on where or how to write scripts.

[Ocean West]

Welcome Merlin,

You have come to the right site for some advice. It's the holidays so you may not get as many eyeballs on your questions.

For starters check out these videos:

http://www.vtc.com/products/FileMaker-Pro-10-Beginner-Tutorials.htm

You can view several videos - for free ( John Mark Osborne - is an excellent instructor ) - but do recommend that if you wish to purchase VTC subscription or videos you may consider FMPug.com membership as being a member of FMPug provide discounts to FileMaker related products & services from community vendors.

Not being familiar with Access I am not sure exactly what you are trying to accomplish. But perhaps with after some more Egg Nog and or discussion between members we can get you on the road to becoming a proficient FileMaker Developer!

Cheers

Stephen

[IdealData]

Are you already familiar with the FM concepts of BROWSE and FIND mode?

In browse you can see the records that are stored, in find mode the fields are blank, but are inviting you to enter some search criteria. The first few letters of a name for example.

You can combine multiple field criteria and multiple find requests to be more specific or general. (There are many 'operators' that can be applied too. >, < etc.

FM also supports drop down lists in both browse and find modes, and any given field can be set to have a drop down list based on all the currently stored content for that field. As such the list will expand as new records are added.

In summary:

Enter find mode

Enter search criteria (from list or by hand)

Perform find

There's a very simple file attached to demonstrate.

[Merlin80]

Thanks for the response, I think you may have misunderstood my first post? I know how to use the browse and find views and I know how to set up a drop down menu. Let me try and explain in more detail. I have a table which gives the user details about a number of companies ie directors and shareholders. In the layout I want to create a drop down menu with the names of all the companies, when you select a company name using this menu I want the information to appear in the other fields that relate to that company. This is pretty standard in Access.

Hopefully this is a little clearer.

Thanks,

Darren.

[bcooney]

Maybe this description of a typical FM design will help.

By and large, when you set up a FM solution, you have a table for each entity. In your example, Companies. Each entity would have at minimum a list layout and a form layout (I usually add a dedicated Find layout).

From a Main Menu layout, the user would click the button, "Companies." That navigates them to the layout Company List. From here, they click a button on the row (typically far left), that switches to the Company_Form layout. This layout shows the details, related records in portals, for just that specific company.

At the top of these layouts there are typically buttons to return to the Main Menu, Find and a button to return to the list view.

No need for any drop downs.

You should grab a copy of one of the many free templates that are out there, and take them apart.

[Merlin80]

Thanks, that is a good suggestion. However, I really need a drop-down menu as opposed to a list of companies.

[LaRetta]

In the layout I want to create a drop down menu with the names of all the companies, when you select a company name using this menu I want the information to appear in the other fields that relate to that company.

I may be wrong but I believe you might want to assign a company to a record in another table such as Invoices and then have all that company information appear for you. We use value lists (check out the attached file below called InsertCustomer) on how the yellow field on invoices assigns the company (by attaching a value list of Companies to it) and this is the 'drop-down' with the list of customers (although I use pop-up). Create a new record and select a company in this yellow field and watch the company information in blue fill in. This inserts the CompanyID into the Invoices CompanyID field but displays the company name. Then the blue fields are actually cross-placed (they belong to the company table itself and they are not duplicated in Invoices; no need).

If, in some instances, you need to actually insert some information into Invoices (other than the CompanyID), then you can use a auto-enter on an Invoice field (either by calculation or by lookup). Sometimes this is required for historic purposes but is rarely required.

Since we all are coming up with different approaches, it suggests that we might benefit from more information but I hope we've given you ideas.

UPDATE: If your list of companies is quite large, I might suggest either a conditional value list or using a portal which can be filtered, such as 'user types F and all companies beginning with F appear for selection.

Edited December 25, 2009 by Guest
Added update

[Merlin80]

Whilst all the advice I am receiving is really good, I am not sure I have explained myself very well. Therefore I have made uploaded a demo here:

http://www.upload-depot.co.uk/menuexample/DropDownExample.htm

When I select one of the companies listed under the 'Search' drop-down menu all the other fields reflect the data held in the table. This does not involve creating an entry, it is merely a way of viewing the data without having to use Find Mode.

If I can replicate this in FM I would be a very happy man.

Thanks,

Darren.

PS HAPPY CHRISTMAS!

[LaRetta]

Hi Darren,

Then you will have only a global field which holds the CustomerID and a relationship to your customers table CustomerID. It would work exactly the same in that, when you select a customer and insert the CustomerID into the global, all the customer fields (from your customer table) will display on the form.

The global field can reside within a different table or within the customers table itself (using a self-join, ie create another table occurrence of your Customers table in your graph).

[Merlin80]

Hi LaRetta,

I am even more confused now, if I use a Global Field it merely adds the same data in for every record - I am puzzled as to how that will help me or the reason this function even exists?

Why is this so complicated, this is a 2 minute job in Access (although I am not saying it is better as I have saved hours elsewhere)? Maybe Filemaker want to force the user to use their 'Find Mode', it is a shame this is not acceptable to the people who I am working for?

Also, I can't open any of the files posted on this forum, it merely comes up with an error message when I click on the URL.

Thanks once again, I think I am going to get myself a very large drink.

Darren.

[LaRetta]

Hi Darren,

A different program can be frustrating when it is unfamiliar. Access does some things better than FM and the reverse is also true. Maybe it will help if you tell us what you plan to do with the information. We have been given no purpose nor context to understand. Once the User selects the Company in the search field and it displays the Company information on the form layout below, then what?

What I gave you (did you try it?) will give you exactly what you demonstrated in that link. It uses a relationship from the global CompanyID to the Companies::CompanyID. When there is a relationship, it displays the company information below.

So please provide further information so we can get on the same vision you are on. :smile2:

[Merlin80]

You guys are very patient indeed :-)

For some reason I cannot access your example, it gives me an error message when I click on the link?

Kind regards,

Darren.

[LaRetta]

Yes, I was just testing it. Currently, no attachments can be downloaded. I sent you a private message and it seems to work if I attach there. Check your My Profile (green tab top left) and I will let the Forum Administrator know.

[Merlin80]

LaRetta,

You are a true genius, I have your example database now and it does exactly what I want :-)

I am currently dissecting using your previous instructions and I will let you know how it goes. Thanks very much for taking the time over the holidays to help me out, it is much appreciated.

Thanks,

Darren.

[LaRetta]

I believe downloading from PM worked for you? Please note that the file I attached was the original demo so it doesn't use a global.

That global CustomerID (yellow) can be in any table. It will show the same value for every record because it isn't dependent upon the record. In this way, you will see your selected Company no matter where you are at until your User closes the file; then the global value will disappear. Globals are different for every User so different Users can be selecting and viewing different customers.

Again, knowing what you do with this customer information should allow us to step you through what to do with the selected Customer (if it is more than just view their data). Let me know how it goes. :smile2:

[LaRetta]

Wow. You are fast! The holiday isn't the best time to catch people but I'll try to check in when I can. :wink2:

[Merlin80]

Hi LaRetta,

I have taken a look at your example and there is only one problem, it assumes that I am making a new entry and therefore I am unable to modify existing records.

I have posted a much more detailed demo here:

http://www.upload-depot.co.uk/example2/untitled.htm

As you can see from this demo, I use the search drop down to locate a record and then I can modify that record by merely clicking on the field and changing it, once I have finished with one record I can move on to the next by merely selecting it from the drop-down. Although your example is great if you just want to look at the information within a record I cannot modify the data in the records.

I hope this is clear, please be patient when waiting for the video to load (it starts off white but it does clear up).

Thanks,

Darren.

[LaRetta]

it assumes that I am making a new entry and therefore I am unable to modify existing records.

Actually, no. That demo was based upon adding a Customer to an Invoice. But you could still change customer information directly in the blue fields.

However, I have provided a new demo. What you are showing (in your second link) is a form view based upon Search which shows Company information and then portals within tabs holding the related information (employees, notes etc). You can add related company information by typing directly into a portal row and you can change any information (including company information).

There were other approaches; however, you didn't want to perform a find to find the Company record so I used a relationship instead. But it still works the same as long as you don't try to add a Company without using the Add New Company button. If you manually create a record on that form, you will simply be creating a second search record (which is worthless).

The demo is simple and a bit rough. This being holiday, I'm trying to take advantage and NOT work (this is what I do every day for a living).

Downloads is still broken so I will attach this new demo in private message to you like before.

UPDATE: Oh, and data is saved in fields upon field exit so no problems there.

Edited December 28, 2009 by Guest
file reference

[bcooney]

This thread made me think of the SeedCode technique: fmsearchresults

[LaRetta]

Strange but now I can't edit my post here so I can't repair the attachment. Here it is attached again. I haven't bothered fixing the prior attachment although I may yet fix it also.

Hi Barbara, well usually I'll argue with someone when they don't want a script. After all, we know that we can silently run a search script and get the results requested, particularly with vs. 10 and script triggers (which could trigger based upon the global CompanyID in the pop-up). But Darren asked that we not use script and that we only search for Company name. He didn't want to search any other fields.

I could give him what he wished quite easily by using a global and relationship (which could just as easily been within the Customers table). As I said originally, there were several approaches here and I still think Darren will need to filter the Company list if it gets very large (using conditional value list or filtered portal instead of pop-up).

InsertCompanyREV2.zip