Portal refine - multiple field comparison

[Grant Cox]

Gday all,

I have two tables, one for Schools and one for Students, with a one-to-many relationship set up using SchoolID::SchoolID. I have a Portal in Schools displaying the students for each school, that's all working fine.

Now I need to be able to refine this list by various properties, a couple of which can be drop down lists (student classes, grading), a couple are wildcard text comparison (names etc).

I have read many many posts on this problem on this board and others, and I understand the issue as presented in djgogi's Article (http://www.fmforums.com/threads/showflat.php?Number=54888), that using Find in the portal will only search the Schools table for records containing acceptable students, not the Students table itself. Unfortunately I don't see a solution presented in that article.

My question is how can I do a search in the Students table and show the results in the Schools' portal? Can a portal only work off a single field comparison?

I have tried having a new relationship for the portal between a couple of Calculation fields (simply concatenating the dropdown search terms), however I have a couple of problems. It seems to get the right number of records in the portal, but always shows the first Student record (not the matching one). Also, it appears the relationship between the fields has to be exact, which doesn't work for wildcard searching (ie names).

I read a post which suggested I could do the search in the Students table, and return the results to the Schools table portal (using Copy All Records), however I don't know how to get these results back, or to create them for each portal (as there are up to 50 schools, each with its own portal with its own students).

Finally a couple of basic questions... Is it possible to use SQL commands in FMPro? Are there variables/parameters in the scripting addon? Can I simply type script rather than using that horrible ScriptMaker Wizard?

Regards,

Grant Cox

[Fenton]

I don't know if it was me who suggested the Copy All Records (I've written about it several times).

You're wanting to mix Finds and Relationships (portals). It can be done, but you have to know what you're doing; and you need to ask why, 'cause it's not the simplest structure.

Since it appears you want some flexibility, I would propose the following.

In Schools, create global fields to hold your search parameters. Take those over to the Student file. Do the search, filling the regular fields with the criteria. Copy All Records on the StudentID (these are unique throughout the system?). Paste those StudentIDs into a global field, in Schools.

It sounds complicated, but it's much faster than a Find on an unstored related field in a large file, in my experience.

There's a 64,000 character limit on the global field. [it's 2 GB in 7, but in 7 you could put a student table occurence, or the whole student table, in Schools, solving the problem, no portal needed. If you're used to SQL you'd probably be more comfortable with 7; it needs far fewer work-arounds. But Server is not quite ready, and it's still a little rough around the edges, IMHO.]

Create a relationship from the global StudentIDs to the StudentID in Students. Base your portal on that.

Or just do Finds in the Student file. That is also possible. I don't really see any great advantage to show the results in a portal in Schools. If there's no overriding reason, and you're having trouble with these more advanced methods, then why not use regular Finds?

[Grant Cox]

Thanks for your help Fenton.

With regards to the solution you have proposed, I'll give that a go, but I have a couple of questions. Are the global fields necessary to pass data between databases? I can see that this should work, but it seems rather messy to create additional table fields just to store temporary search terms.

Secondly, where you say to paste the resulting StudentIDs into a global in Schools, I will need to perform the students search for each school. Should this search be executed as each School record is retrieved and parsed, rather than when the user hits "Search"? And if I do this, when many School records are visible on the screen at once will the portals retain the values they were initialised with rather than what this global has changed to?

I'll also check with the client if they are prepared to upgrade to v7, that might be an option.

As for doing the Finds in the Students file. What I need is a list of all appropriate students, ordered by school, and I thought this would be easier done with each school record. Admittedly when I decided upon this layout I had not used FileMaker before, and I had assumed it would be trivial to implement.

Edit: Oh, and the search terms are for both school and student, so if it was searching in the Students it would need to each student's school anyway.

Edit2: Is it possible to have a script execute when a Layout is initialised? I can't find where to put it...

Regards,

Grant Cox

[Grant Cox]

Well, it's getting somewhere. The search is being performed in the Students file, the results are put back into a text field (not global) in Schools, with the appropriate results. This is feeding a portal in each school, which is working ok.

However, I did find that using Copy All Records would put the correct data in the clipboard, but I could not paste this to any field in another database, global or otherwise. Instead I had to paste it into a field in the same file, and use Set Field to transfer it to the other database. In the end I've scrapped that too; I'm manually looping through the found Student records and adding to the appropriate School.