Many-to-Many searches

[margita]

I would be grateful for your help.

I have a database of applicants. The tables I am concerned with are as follows:

table Applicants

table Specialisations

table Stages

table ApplicantStages

Applicants are related to Specialisations

by fkSpecialisationID. Table ApplicantStages is a join table between Applicants and Stages related by ApplicantID and StageID.

Applicants go through an interviewing process consisting of several stages (Sift Progress, Sift Reject, Interview Progress, Interview Reject and so on). They pass through each stage or they don't.

All this works well on the Data Entry layout with Applicant name and Specialisation.

There is a portal for ApplicantStages.

I need another layout for searching or filtering

ApplicantStages in list form. Ideally I need the following information on that layout: Applicant name, Specialisation and Stages. I would like to be able to search either by stages or specialisation to filter it down say to Sift Reject or expand to both Sift Progress and Reject, limiting it to one specialisation or include all specialisations.

I have tried everything possible, but have not got anywhere. I was thinking of a global field for searching and trying to design a layout in list form - but when I search for stages only the first record comes up as these records can only be shown in a portal.

Can you help?

Many thanks,

Margit

[Fenton]

I think this is possible. There are 3 principles. The first is that in FileMaker 8 you can, in a Value List, "Show only values from a 2nd field". This allows you to use a global field(s) for choices, showing only the text values, hiding the IDs. Which is necessary if you're trying to filter a join table.

The 2nd principal is that you can have compound relationships in 7/8 (duh, but I put it anyway :-).

The 3rd is that you can add an "All" choice via a calculation(s), to both the global choice field, and to the targeted indexed field. Using another "All" global field you can choose whether the "left" side of the relationship has "All" or not.

[An alternate method is Søren Dyhr's, which uses ValueListItems to show all if the 2nd choice is empty. It is, in a way, more intutive.]

http://www.fmforums.com/forum/showtopic.php?tid/144255/post/144580/hl/+Søren/#144580

In the attached example I've done much what you asked (I think), but I didn't do it in a join table; too lazy I guess. It would be much the same thing though.

Checkbox_2ndValue.zip

[margita]

Many thanks for your reply.

I had a look at all your suggested examples - it is all very interesting. I have to say that I still don't have much experience with globals and what I can do with them.

It seems to me that your examples would be difficult to fit in with what I am trying to do. I have managed to create a search layout with a global field at the top formatted as a standard field. When a search term is typed into the global field it does show relevant records on the layout. The script for the search is:

Allow User Abort [on]

Set Error Capture [on]

Enter Find Mode

Set Field[Myfield; gSearch]

Perform Find

Now my problem is that I would like to include an AND search in the same field. Say, I am searching

for Sift Progress, but I also want to include Sift Reject. When I do the search for Sift Progress and then another one for Sift Reject, it will erase the previous search. Is is possible to modify the script for this eventuality? Or does one have to create another global field? It would be nice to use only one global field for this purpose.

Looking forward to hearing from you.

[margita]

Actually, perhaps I didn't explain myself very well.

The layout I created shows 2 global fields at the top in the header in standard field format (one for Stages and the second one for Specialisations). I have created two possibilities for this on two layouts. One of the possibilities with the two global fields is working up to a point. The other one with only one global field is not working well.

In the body of the layout I have records in list view showing name of the applicant and specialisation. This is fine - however, it is impossible to add the stages in the body as well showing which stage they passed through. If I type in the second global field a name of the specialisation it will show records for relevant applicants, but it will not show what stages they've passed through in the body of the layout.

I hope I am not muddling this up. Would it be easier if I emailed you a test sample?

[Fenton]

The thing is, when you're dealing with checkboxes, or any other fixed lists, it is easier and more accurate to use relationships to narrow down the focus than it is to use Finds. Especially when you're combining 2 lists.

I tried to do the same thing with a Find. It was kind of long and convoluted, and was only partially successful. To get a a true "AND" Find with 2 lists, I have to would resort to capturing IDs, and using FilterValues() from one list to another; which is much the same as using a relational method to begin with.

If what you're saying is that you want to show the result in a List view, just add a Go To Related Record button, based on the global relationship of the portal. My example is perhaps a little confusing, because I did it all with 2 tables (no join table), and on one List view layout. That was just the easiest most compact way to do it.

If you post a zipped file of your own (not too large), I (or someone) could implement the method; if we understand your interface. I would use the relationship method however; the Find is just plain clunky.

Also, if you are wanting to view by both your criteria, you are going to be viewing the resulting join table records; either in a List view or in a portal. I don't see how you could see both results anywhere else. The portal would therefore be more portable; it could be in one of the other tables. A list view would have to belong to the join table, so it's less portable; fine if you just want to go there. Details of interface and navigation are hard for someone else to say, as there's more than one way to do it.

[margita]

Thank you very much for your reply. I will study it in detail to make sure I understand. In the meantime I would be happy if you could look at the enclosed file and see if it could be improved?

TestAdmis.zip

[margita]

All my heartfelt thanks for your latest input. This is exactly what I wanted. I confess that I could not really understand your previous sample - it didn't seem to show all records when I clicked on All.

I am now trying to understand the logic behind the latest sample you sent me. I have a lot to learn. Now that I have seen it and played with it a little, I can now see that I just need to put the portal into the body of the layout and view it in Form view - not List view which is not necessary as the portal shows everything that is necessary. I could leave the checkboxes in the header.

Could I ask a few questions to tidy up this issue:

1. If I wanted to use this particular layout without affecting the original Admissions Data Entry layout (what I mean is that if I use the button Go to related records for going to the Data Entry layout, I would want it to show all records in the Status area). Would I have to create another table occurrence of the Admissions table, called say Admis2 and relate this back to the original Admissions table by RefNo. and relate all your table occurences to Admis2, so that it does not affect the Data Entry layout?

2. If I wanted to create another layout for searching StatusDetails (which is another join table), I would have to create table occurrences for this purpose totally separate from your table occurrences, I suppose?

3. In order to learn this technique, I will try to create another layout for a straightforward relationship based just on Applicants and Specialisations. I take it that I would have to create another table occurrence for that?

4. I hope you won't mind if I ask this question which is not exactly part of this topic. You may have noticed that I also have a table Courses related to Specialisations. On the Data Entry layout I would like to have this field shown up - but I can't get this to work because Admissions is not directly related to Courses.

As I said I have a lot to learn about relationships and you have been wonderful. I agree with you that FileMaker offers a lot of possibilities in this regard. Looking forward to hearing from you and many thanks again for your help.

[margita]

Again many thanks for your help and your infinite patience. You've made me really happy.

Sorry for my confusing questions. Number 1 question is cleared up now that you've sent me another version of the sample file. I just wanted to make sure that if I go from StageSearches layout to Data Entry layout it would show all records, not just the related record.

As for the StatusDetails, this is another join table because Applicants can have more than one status - this is similar to Stages.

As for Courses, sorry for having asked you this question: it is just that I had great difficulty getting this field on the data Entry layout - I must have made a mistake somewhere because it is perfectly fine after your explanation. Yes, I just wanted a particular course to show up for the relevant Specialisation. So a portal is not necessary as the right Course shows up now, once I enter a Specialisation into its field.

So, I'll go on constructing this database. I hope you won't mind if I come back to you with some questions if I stumble over something else.

Many, many thanks for everything.

[margita]

Sorry, just a quick check:

I am now trying to construct a search layout for Status (I noticed that you already put some Staqtus global fields on the Admissions layout).

When I was looking at _cgStageIDs and _cgSpecializationIDs - one of them had Do not evaluate if all refrerenced fields are empty unchecked, but the other one had this option checked. Which would be correct for this type of field?

Many thanks.

[margita]

Absolutely mind-boggling. It does look wonderful and as you say very tidy. I now have to study this in great detail and I certainly would go for this method.

I confess that I am still having some problems. I do understand how the relationships work with join tables in your examples.

But ironically enough, if I were to construct a relationship based on your technique with say Courses, Specialisations and Applicants (Specialisations related to Courses by Course ID and Applicants related to Specialisations by Specialisation ID), I end up with a dreadful mess. And I am rather frustrated.

Can you bear with me and tell me how I should construct this search relationship with these three tables. Then I think I should be able to grasp it better.

I am so grateful to you for your help and patience. Looking forward to hearing from you.

[Fenton]

Upon looking at Courses and Specialisations I realized that I don't really know what their logical relationship is. Are some courses only offered for some Specialisations? Do some Specialisations require a certain course (or courses)? Otherwise what's the point in chaining them? They would just be independent. It sounds as if you want to filter courses by specialisation.

[margita]

Perhaps Courses and Specialisations are not a good example. Just to clarify matters: there are three courses - some specialisations belong to one of them. Courses are one-to-many specialisations. One applicant can do only one specialisation - specialisations are one-to-many applicants.

I mentioned this only because I would like to learn how to create a find layout based on your example with similar one-to-many relationships. Another example would be say a database of donors. So there would be a table Categories, table type of donors and table contacts. Categories would be one to many contacts and donors would be one to many contacts. Categories could be Corporate, Churches, Individual - donors could be funders or prospects. If I were to create a find layout showing Categories and Donor types - say finding either funders or prospects or both. I was trying to preactice on this, but created a big mess. If you could explain, that would be great.Just to show you what I tried to do, I enclose a copy of the sample you sent before your latest beautiful sample. I was trying to do a layout just for searching Applicants and Specialisations. I did not even attempt to add Courses as well. It is all wrong. Of course I prefer your latest sample, but I did not even attempt to do some experiments - at least not yet.

Could you bear with me - I really enjoy talking to you.

TestAdmis_fej_Copy.zip

Edited May 31, 2006 by Guest
Added file

[Fenton]

Just to clarify matters: there are three courses - some specialisations belong to one of them. Courses are one-to-many specialisations.

Actually that's not completely clear. Especially "some specialisations belong to one of them". Does that mean the rest have all courses? Or, can some Specialisations not belong to any Courses?

Which would make filtering Course by Specialisations different than if all spec. had one or more courses. It could still be done however. But ambiguity makes database design difficult. You need to know whether participation in the relationship is mandatory or not.

That would mostly affect how you would set up filtering for "choosing" a course. It wouldn't matter so much for a "search", using globals and portals.

But similar questions exists for Applicants and Courses. If any given Specialisation automatically means a certain Course (or courses), then there is no real need for an ApplicantCourses join table. And also no need for a "courses search"; it would be implied by the Specialisation search.

Otherwise it would be pretty much the same as the other searches. But I can't set it up unless I know the "business logic," which really only you know. I can guess, but I often guess badly, especially about businesses I don't really know.

[margita]

Thank you for your reply. Just to explain: each specialisation belongs to a course. Animation and Editing would belong to MA. Sound and Screenwriting would belong to Diploma. Post-Production would belong to Occasional. A course is one-to-many specialisations. A specialisation is one to one course. I don't want to create a join table in this case.

I am just trying to understand a little bit more about creating find layouts. I am interested in this subject because users mostly find Find and Browse modes confusing. So when you told me how to filter join tables - I thought of more possibilities for creating similar finds for three tables based on one-to-many relationships - not necessarily many-to-many relationships. One of the purposes for creating the find layout based on your example of ApplicantsStages is to create buttons for exporting to mail merge or to Excel on the same layout which would simplify tasks for the user.

I have been playing with your technique in a different scenario: table Category, table Donor types and table Contacts. Category is one-to-many Contacts - CategoryID is the foreign key in Contacts (for example Major Donor or Churches or Individuals etc. - each of these categories can have many contacts - one contact can have only one category). Donor types would be Funders or Prospects. Again Donor types would be one to many contacts (DonorID is the foreign key in Contacts). I managed to create a global relationship for filtering but I am not sure if it is correct and I would be grateful if you could tell me if I managed to get it right:

I created global fields Category and Donor Type in the Globals table. I also created the _cgCategory and _cgDonorType fields in the same table to get the Value lists CategoryIDs and DonorTypeIDs. Then I created a table occurrence based on Contacts called SearchCategoryDonor~_cgCategoryDonor. Then I created another three table occurrences: SearchCategoryDonor~ApplicID based on table Contacts related to SearchCategoryDonor~_cgCategoryDonor by ContactID, SearchCategory~Category based on table Category related to SearchCategoryDonor~_cgCategoryDonor by CategoryID and SearchDonor~Donor based on table Donor related to SearchCategoryDonor~_cgCategoryDonor by DonorTypeID. And finally the table SearchCategoryDonor~_cgCategoryDonor is related to the Globals table by DonorTypeID and CategoryTypeID to _cgCategory and _cgDonorType.

It does seem to work on the find layout I created for this. Could you, please, tell me if this is correct? The whole purpose of this is to make searches easier and simplify exports to mailmerge or Excel. Another purpose is that I would just like to get a bit more confidence and practice in creating similar layouts that are not just based on many-to-many relationships.

If this is correct, then I would create a find layout just for Specialisations and Applicants (I agree that Courses would not be necessary here) - the purpose for this would be purely for making searches easier. Would this be correct: create fields gSpecialisations and _cgSpecialisations in the Globals table. Then create a TO based on Admissions called SearchAdmissions~cgSpecialisations, then I would create anothet TO based on Admissions called SearchAdmissions~ApplID related to SearchAdmissions~cgSpecialisations by the ApplID, and another TO called SearchSpecialisations~Specialisations based on table Specialisations related to SearchAdmissions~cgSpecialisations by Specialisation ID. And finally relate SearchAdmissions~cgSpecialisations to the Globals table by Specialisation ID. Would I be on the right track?

Many thanks.

Edited May 31, 2006 by Guest

[Fenton]

First, it is hard to me to be sure whether what you've done is correct or not by reading; partly because I'm not very good at following such. The best is if you explain what it is you're trying to do, then upload a small example file. That way I can see it clearly, and also make corrections, if needed, or suggestions.

It sounds like you're going in the right direction. But a critical fact is that a portal will only show the resulting records from one relationship. So, if there are 2 filters, it would be a "compound" (AND) relationship to that table. On the "left" side of the relationship would be BOTH the calculation fields using ( the global or the value list items ). On the right side would be the ID fields.

The other further relationships are only need to show the names of the ID entities; which are probably necessary when you're doing compound filtering (each of which is using multi-value checkboxes),* so you can see which value(s) produced the match. Unless you're just going for all the matches, and don't really care to see which matched.

*Checkboxes add another level, almost like a join table. Each line is capable of an independent match.

These further relationships showing the names are not from the same relationship as the portal. But they are an "extension" of the relational line. This works as long as the relationship from the join table is either one-to-one or many-to-one. It would not work if it was one-to-many, you'd just get the 1st of many.

Further, using Go To Related Record [ further relationship ] produces a found set in the "further" table, shown on one of its layouts. In our case the Admissions Data Entry. That layout does NOT have to be connected, on the graph, to the table occurrences of the global searches. It is one of few places where FileMaker lets you "jump" between table occurrence groups, taking the found set and current record with you; a critical feature, else we'd be stuck.

[margita]

Many thanks again. Your explanation helped me greatly to understand the logic better. Theory and practice are not mutually exclusive and so I need both.

I have experimented a little with two databases. I hope you won't mind if I enclose both of them - they are small just for testing. One of them is the Admissions one which I expanded to Specialisations Search Layout and the other one is for searching donor categories (no password, user name Admin).

I would be grateful if you could check if this has been done correctly. Also, if more similar global search layouts are created, would anything need to be changed in the Globals@Open script? This script mentions a particular layout GlobalsSearch which you created and I created another one.

Looking forward to hearing from you.

FunFejTest_1.zip

TestAdmis_soren_Copy_1.zip

[Fenton]

"By George she's got it!" :-]

If you're developing for FileMaker 8 you could put the Specialisations search on another tab. Much better than another layout.

The Opening script just clears the globals. So they don't retain the last values you left in there. So any new globals would need to be added to that script; though you didn't add any. Alternatively, since navigation to the Search layout is scripted, you could clear them every time you went there. But that would make it annoying to someone who wanted to flip back and forth to Data Entry without losing their last choices.

[margita]

Well! What can I say - thank you so much for your infinite patience. I really wanted to get the hang of it so much.

Your suggestion for tabbed search layouts is absolutely brilliant. If this is the case, then I suppose the script for opening this particular layout would not have to change. It would only be the script for closing which would have to have all the relevant fields cleared?

Also, if I could ask: is there a really good book on FileMaker which would be useful? I have some, but they are for learning from scratch mostly.

Again, many thanks for being such a good teacher!

[Fenton]

Yes, the new tabbed layouts make it easier to make more available without the penalty of separate layouts (which are not difficult to create, but require much navigation). One thing about tabs; by default the layout opens with the tab which was last active while you were in Layout mode. There are other tricks for controlling this, which you can find here on fmforums (and other places).

The thing with clearing globals is also to deal with default behavior. So the globals are empty once you put the files on a host machine. It's annoying if they're not empty, and requires stopping the Server to clear. Be sure it's only globals you clear however (oops! some our data keeps mysteriously disappearing :-).

There is apparently a new "Special Edition usng FileMaker 8" book available. I've got the 5 and 7 versions. Very good intermediate instruction.

Though I don't know that you'd find something like the filtered portals using ValueListItems in it. Perhaps something similar. But that technique came from an example file from our own Søren Dyhr. Many very useful little tricks and tips come from files uploaded online.

Another great site for example files is John Mark Osborne's (who you'll also see here at Fmforums these days).

http://www.databasepros.com

http://www.databasepros.com/resources.html

This site is an old standby. Hours of fun. Home of the famous "Visibility" file, which, if you like great interface techniques, you'll want to learn.

[margita]

Thank you very much for the tips - I will certainly see if I can get the book you mention.

Just to make sure I fully understand re: the opening script clearing the globals:

1. Just to confirm that if it is a tabbed search layout it would not be necessary to add any more commands to the script for the creation of a new record?

2. Since you mention the network scenario: if the globals are cleared on exit when one user closes the file, will this affect another user who has the same file open? From what you say it seems not - but I'd like to make sure.

Many thanks.

[Fenton]

The opening script checks that there is at least 1 record in the Globals table. Because relationships don't work if there's no records, and users would not know to create that 1st record in that table. If you ever Save as Clone you'd have this problem. The closing script is the one that clears the globals.

1. If you create a new global field, for a new search, then you'd want to add that to the "clear globals" script. But your Specialisations only search used the same gSpecialisations field, so it wasn't needed this time.

2. Each user has their own independent globals when they're logged in. Also, host machine remembers the globals which were last in the fields when the file was opened as single user, and opens the files with those in place, for everybody. The only way to change those is to open the file again as single-user, which is awkward, especially if you're not even physically there, and have no access to the files. So, if you want to fields to appear as empty, after development, you need to clear them before deploying. This is hard to remember to do, hence the script.

[margita]

Many. many thanks for everything. Hope you have a nice weekend. I'll go on learning.

[Genx]

Im sorry, i just loved the "by george she's got it". And you would be professor higgins then Fenton? :

[margita]

I am sorry for having asked this question here - I realise that it should be in the Importing and Exporting forum. I am moving this there because I really would like to get to the bottom of this annoying problem.

Edited June 6, 2006 by Guest
Moved to Importing and Exporting

[Genx]

Are you sure that you haven't selected the globals rather than the real fields in your script?

[margita]

Yes, I am quite sure I have not selected the globals. I have selected the relevant fields from the portal. The same fields show up when I save the records via the automated menu command Save Records as Excel on the Excel spreadsheet. The name fields are populated correctly via both methods. It is just the Category fields which show up only in the first Excel cells - but are not populated below the first Excel cells.

Any idea why? By the way, if I selected only the global fields, nothing would show up.

[margita]

I hope it will not be too irritating to come back to you on a certain GTRR issue in this particular filtered search example you very kindly helped me with.

The filtered portal works fine when the button above the portal is clicked and I am then taken to another layout which shows related records in the found set. The button above the portal has a script attached for Go To Related Record with Show Only Related Records and Match the Current Record Only.

However, as soon as I scroll down the portal without clicking on anything or just click on the vertical scroll bar in the portal and THEN click on the button above the portal, the result is that it shows only one record on the destination layout which could be anything.

Presumably, the command for Match the Current Record Only is because the Global table itself has 1 record? As I tried to resolve this problem, I changed the command to Match all records in current found set. This seems to work in the situation I described above: when I scroll the portal or click in the vertical scroll bar and then click the GTRR button above the portal, I get shown all related records on the destination layout.

I would be grateful if you could let me know if this procedure is safe to use and that I would not be in for another surprise?

Many thanks and I do hope to hear from you.

[Søren Dyhr]

Indeed this is confusing, but the issue is that neither the parent record nor the child record is "Opened" according to p. 79 of:

http://www.filemaker.com/downloads/pdf/techbrief_fm8_migrtn_found.pdf

...and the cure seems counter intutive, namely to avoid the single line button and call a script that first "Commits" and then GTRR's ...but why first line in the portal is assumed the single one to jump to in the GTRR if you have fiddled with the scrollbar is a little weird, since it more or less contradict the whitepaper.

Perhaps this problem should find it's way into this:

http://network.datatude.net/viewforum.php?f=37

But for starters, should you make the buttons call two liner script where the first line is "Commit Record" just before the GTRR.

David Kaschel makes this advice, which really hit's it right on the nail:

Whenever you need a button, create a new script for it and set the button to Perform Script. Even if that script will contain only one step, follow this rule anyway. The primary reason for this is that virtually all single-step buttons end up requiring more steps, forcing you to create a script for the button anyway. This is simple labor saving advice born of experience. You cannot damage anything by creating single-step buttons. You can (and will) only make a lot of extra work for yourself.

Snipped from: http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

--sd

[Fenton]

Yes, the button should have a script, and it should have a Commit Records before the Go To Related Record. When you click in the portal's scroll bar, you are effectively locking the portal, and making the 1st record the source of the relationship. At that point you are no longer outside the portal. The Go To Related Record is going to work as if you clicked the "Go" button on the 1st row. So it is behaving reliably; it is me who is not behaving reliably -]

Do not put Commit Records before Go To Related Record when you want to evaluate inside a portal however. But do put it after editing data. Read Ilyse Kazar's article, as mentioned above by Søren. That chapter is essential reading (and well-written).

TestAdmis_soren2.zip

[margita]

Thank you both very much. Yes, I know David Kachel's article and I try to make scripted buttons.

I made your recommended change (Commit Records) in my database and now it works.

Many thanks again for the input from both of you.