finding with multiple requests

[msadesign]

I want to search my Plant List table by Project Number and Plant List Name.

Problem is this: all the plant lists for each project number are lumped into the same table.

Project 1408 has Preliminary Plants, or Final Plant List, for example.

I need to search for '1408 Preliminary', or '1408 Final', for example. But I don't know, before searching, what plant list names might be associated with each project number. Still, I need to search as above.

I've spent so much time: tried portals, and self-join on project number, and many other combo, but I cannot find a solution that is essentially a 'double find': I want to enter 1408 in a find field and tab to another field that shows the related plant lists.

I know I'm going in the wrong direction. The use of the word 'related' above is a sure indicator that something isn't formed correctly.

But what?

[Fenton]

We need to be told what the FileMaker structure is, in detail, before we can say how to do this. "Project 140 has Preliminary Plants, or Final Plant List" does not really tell us exactly what these things are; fields? related fields? 1 related field in another table?

[inevaexisted]

I'm having trouble grasping what your after but it sounds like the best option is to use a new field combined with a value list

However I believe what you are after is in the filemaker file attached

Plants.fp7

Ineva.

[msadesign]

OK, I haven't been clear and apologize [and btw the file kindly offered isn't available].

Just a flat data file, consisting of fields named project number, plant list name, and then scientific name of the plants.

The same project number can have several plant lists, and each list is given a name. The names might be 'Preliminary Design' and 'Final Design'.

I want to find, as an example, 'project 1408 preliminary design', and I don't want any records belong to the final design.

It's simple to find all of the records with a given project number. It's also simple to find all the records with a given plant list name.

It's not simple to find all the records with a given project number [which I know in advance] and a given plant list name [which I don't know in advance].

What I envisioned was something like this: you would show project number and plant list name on the layout. When you went to find mode, you would enter the project number, and then tab to the plant list name field, which would then, in a drop down menu or similar, show all of the available plant lists for that project number; you would select one, and then hit enter.

I think I have tried s million ways to do this, focusing on the plant list name field. I tried a value list that starts at the project number, and a value list that combines project number and plant list name.

But no matter what I do, I can't get the desired behavior, which is to show me what plant list names are available for a given project number, so I can chose.

I sure hope this is clearer as writing it out has focused me greatly. Frustrated, too -)

[Fenton]

Why not use global fields to set up the Find (in Browse mode), then Enter Find Mode and set the fields? I don't really see what the problem is.

[msadesign]

Fenton, thanks for your response.

I don't know how to do what you suggest? I know what a global is, have used them, but I'm not a developer [certainly not like you!], just a business person using the program. Can you be a little more specific?

How do I populate the global? is a good place to start?

Thanks so much.

[Fenton]

Create global fields for each of your "Find" fields. Enter the data in the global fields. Then Enter Find mode, set the regular fields to the globals (which are still available in Find mode), then Perform Find.

You wanted to filter the plant list name field to only those for a given project. This would be done with a relationship from the project global field to the project ID. Then create a value list filtered on this relationship.

Easier done than said -]

ProjectPlants.fp7.zip

[msadesign]

Thanks.

And now, perhaps, we have another example of the difference between an 'expert' and a 'dabbler'; being the latter, the solution offered will require some time by me to appreciate! Looking forward to it, though.

Thanks again.

[inevaexisted]

Unfortunatly i'm at uni so I cant fix the server...but follows is what it was...

Add a new field type - Calculation.

equate the calculation to

project_number & " " & plant

now in a layout that has this new field make the field into a drop down menu and define a value list...select project number for the first field and plant for the send sort by second field and that is basically the file I had.

Now to find the data all you have to do is enter find mode select the field and project_number/project from the list..

I'll look into that file when I get home...

Another way you could do what you want is to do a find for the project number, pause script, omit the record your after (a button will be required), show omitted records and then find again the project number....there is probably a cleaner/faster way to do that but I'm sure you would need to separate the data into a projects table and a plants table.

Ineva.

Edited October 22, 2007 by Guest

[msadesign]

Ineva, and Fenton,

Thanks so much to both of you.

I am using Fenton's approach because it has a side benefit of showing me all the lists, and I can add fields as need to see what is contained on each plant list. And it's elegant.

I also tried the value list approach. The biggest problem here, for me, is that the value list works but it shows every plant list for every project and this is what I was trying to avoid. It would be a great option if you could have the value list start from a desired project list [a welcome new feature in 9] but this doesn't work on calculated value. The number of plant lists is huge–more than 500– and while scrolling down to the project number works, it is cumbersome. And, you can't control sorts on value lists the way I would like.

So, in the end while the globals approach is a two step process– something I also wanted to avoid– it has added a useful feature and it is elegant.

Hope this thread helps others.

Michael