Search records based on a value list?

[Mitesh]

Hi,

I have Customer ,Divison & Contacts Tables.

Customer can have many Division and many contacts can exists under this divisions.

I have create a little demo file for review.

What i want to have is in ContactSearch is have a drop down list of company & Division. User will select Company and Division will be filled with all the division of that company.

I want to display all the contacts as soon as user select the company and then narrow it down by division.

Thanks in advance.

Mitesh

Mitesh.zip

[dreamingmind]

Mitesh,

Here is your file with a working system

I made a new table for the search relationships. It has two globals, company and division that show pop-up lists of appropriate values. Once set, a link is established to show all the contacts.

A couple of scripts keep you from having inappropriate data displayed, and insure that your button clicks get you into the proper pop-up fields.

I also build the relationship set and layout necessary to put the whole thing into action.

I comandeered your ContactSearch script to get things rolling (I just disabled the steps you had in there).

Hope this helps you.

Regards,

Don

Mitesh.fp7.zip

[dreamingmind]

By the way Mitesh,

As you'll notice, I didn't do a find at all! This is all done live with relationships.

If you want a found set of the actual contacts, you could add a go-to-related-record button that pulls those for you.

Don

[Mitesh]

Don, I cant thank you enough for the solution.

Can i be cheeky and ask you how to show all the contact records that dont have any division, i.e. show all the contacts based on company.

Thank you ever so much.

Mitesh

[dreamingmind]

mitesh,

here you go.

I made a change to the original relationship. I noticed you allowed div IDs to duplicate for different companies

comp 1 div 2

comp 2 div 2

this meant, with a div id only link we were getting other companies contacts. You see now i include company id

Regards,

Don

Mitesh.fp7.zip

[dreamingmind]

mitesh,

I looked a little further at your data. You may have already corrected this, but those duplicated division IDs should not exist because you have the field set to auto serialize.

This means, during development you have generated some bad data. Happens all the time, HOWEVER, you should delete this data and create a new, clean data set. The bad data will pop up in many, many unexpected ways to confuse your development process and debugging efforts.

Regards,

Don

[Mitesh]

Thanks you very much Don. I really appreciate your time and effort and you are absolutely right about the bad data.

[Mitesh]

I took you advice and started with a set of clean data and your solution works.

I was trying to get your solution working without portals, instead in a list like report, but couldnt get it working.

So basically i would have Company & Division Drop downs in header and body will display the Contact info., if clicked will take them to contact layout.

Being a newbie to filemaker, i am still trying to understand the TO sturcture you have created and way everything works. I feel embarrassed, asking for more help.

[dreamingmind]

Mitesh,

If you do the method your are describing you will have to do a find. Set the two fields and trigger a find. It will not be the instant veiw you have in the portal model.

Unfortunately the drop down lists don't operate in the find mode... So you won't have that feature to help you.

There are two ways you might try as alternatives:

One

Make the portal based layout look like the layout you've described. Put the two selection fields up top and expand the portal below this to look a bit like a list view. Remember, you might not have to have an actual header in this approach, you could design the page to LOOK like it has a header.

Two

Use the two drop down selection fields in the browse mode, just like the current example, but rather than showing the results in a portal, provide a button that will take those values and perform a find script to populate your list view.

A couple of points on TOs.

- Each table can have multiple TOs.

- A layout will be based on some TO and will be able to see all the related data only from that context.

If you examine the TOs i made in your example, you'll see I duplicated some relationships. Company relates to Div and Contact the same as in your TOs. The alternative, of course, was to add my relationships to your existing web. I didn't do this because I had a specific task (building this listing tool) and wanted a clean, to-the-point context to work from. I can then build my layout from this context and only have to worry about the relationships and whatnot that are specific to my goal.

I would recommend starting to think along these lines. It takes practice.

If you do follow this approach, you will come to a point where you are in one context and a scripted process requires a layout and context that is not directly related. There is a simple trick to move from context to context.

- Let's assume we're in some layout based on TO Company.

- Let's also assume these relationship webs are much more complicated and you REALLY don't want to add more to either one

- A script does something and finds a set of Divisions.

- Now you need proceed with scripts and layouts that require the search.DIV context

- If you try changing layouts (the obvious course) you'll find out each layout/context has its own found set an you'll loose the found set you created in part one.

- Instead, you'll want to arrange some method of pulling your Division records with a relationship.

- Then you can go-to-related-records (gtrr) through the relationship and specify the layout based on search.DIV as your layout!!

Well, I hope some of this is helpful.

Regards

Don

[Mitesh]

Don,

I really appreciate your effort and time you took to respond.

Most likely i will be proceeding with your suggested second option. I think i will have to use 3rd party plugin(DoScript) to execute the scripts on exit events of the drop downs, or put it behind a button.

Thank you very much again.

Regards

Mitesh

[dreamingmind]

Mitesh,

Another tool that will allow simple script triggering from a modified field is SmartPill. The prospect of adding the entire php language to your filemaker calculation engine may seem like overkill... and if you never plan to delve into php it may be.

Also, the script can only be triggered when the field is modified. DoScript probably does more than that.

I mention it because, if you do have any interested in php, you can add HUGE new capabilities to filemaker. As a proof of concept project I wrote a db that allowed me to navigate and access my file directory. I've downloaded and installed code that calculates holidays given the holiday name and a year (yes, easter and hanauka too). I can FTP from inside filemaker. I can do image manipulation, crop, rotate, change image type and write the file back out... but I rant.

Bottom line is, SmartPill adds an entire world class programming language into the FileMaker calculation engine.

Regards,

Don