tylertul Posted May 29, 2008 Share Posted May 29, 2008 I am really lost on this one... I have a layout. on the layout is a portal to a 2nd table "Animals" I have been successful in creating filters that show only Cats or Cats and Horses but how can I create a filtered portal that will show me only records matching Cats, Horses and Big and white as additional filters so that only Big, White, Cats and Horses show in the portal? The issue is that there are separate tables with pk and fk for Cats and for Horses, etc. In the portal I just reference the fk for Cats and can show additional info about the cat on the same portal line from the Cats table based on the relationship. I just can't figure out how to specify criteria that is a the root level "Animals" based on one table and also one level deep "Cats" (Skinny ones, White ones, etc) at the same time in the same portal from the same main layout. Thanks! Link to comment Share on other sites More sharing options...
Ender Posted May 29, 2008 Share Posted May 29, 2008 Perhaps you can clarify the structure. What are the tables and how are they related? Which table is the interface layout based in? Link to comment Share on other sites More sharing options...
tylertul Posted May 29, 2008 Author Share Posted May 29, 2008 Thanks, Here is a text version of the relationships and tables. I want to build a layout based on SCHOOL and on it have a portal based on CLASSES. I then want to filter the portal to show me only what classes are about the subject "History" that are in a ROOM with a projector in it. I know I need another g_equipment and it seems like it should be in the SCHOOL table but I am not sure how to relate it to the ROOMS table to be able to display and filter data from the CLASSES table and related info (equipment) from the ROOMS table on the same portal row on the layout based on the SCHOOL table. Thanks in advance! I am a functional hack at Filemaker SCHOOL TABLE g_subject CLASSES TABLE subject (many to many related to SCHOOL by g_subject) ROOMS TABLE pk_rooms (related to CLASSES by fk_rooms) equipment Link to comment Share on other sites More sharing options...
tylertul Posted May 29, 2008 Author Share Posted May 29, 2008 Oops... The table was missing a field SCHOOL TABLE g_subject CLASSES TABLE subject (many to many related to SCHOOL by g_subject) fk_rooms ROOMS TABLE pk_rooms (related to CLASSES by fk_rooms) equipment Link to comment Share on other sites More sharing options...
Ender Posted May 29, 2008 Share Posted May 29, 2008 So your structure is: School -- Room where Class is a join between School and Room. You might extend that to have tables for Equipment and AssignmentEquipment: School -- Room -- Equipment this way you could have multiple pieces of equipment assigned to each room, and a particular piece of equipment might be assigned to different rooms over time. Now, filtering the Classes by the AssignedEquipment will require a little effort. My first thought is to pull the RoomIDs from the AssignedEquipment table into a list in the interface table, using List() (FM8.5 or later). Then you can use that as a parent key for a new relationship to Class or Room. This is very similar to what I've been working with Fred on in this thread: http://www.filemakertoday.com/com/showpost.php?p=73658&postcount=6 Your case is a little simpler as you're just building the parent key from the List(); there's no case() involved. Let me know if you get stuck. Link to comment Share on other sites More sharing options...
tylertul Posted May 29, 2008 Author Share Posted May 29, 2008 Thanks, I'll give it a try tonight and tomorrow! ~ Tyler Link to comment Share on other sites More sharing options...
Ender Posted May 29, 2008 Share Posted May 29, 2008 And hey, thanks for using a real example. Those Skinny Cats and White Horses weren't making any sense! I was like, "What's he modeling, an Ark?" Link to comment Share on other sites More sharing options...
tylertul Posted May 31, 2008 Author Share Posted May 31, 2008 Ender, I am still stumped on this. I really appreciate your help and have been doing tons of searching, reading and trying different tests, examples. I am not looking for someone to do the work for me. I am missing a concert here. Lets say I have CATEGORY catID SUB_CATEGORY subcatID SUB_SUB_CATEGORY Link to comment Share on other sites More sharing options...
tylertul Posted May 31, 2008 Author Share Posted May 31, 2008 Dang, that post button is easy to push Lets say I have CATEGORY PK_CATEGORY -------------------------- SUB_CATEGORY PK_SUB_CATEGORY FK_CATEGORY -------------------------- SUB_SUB_CATEGORY FK_SUB_CATEGORY Each of the 3 levels of categories are related as: PK_CATEGORY = FK_CATEGORY = FK_SUB_CATEGORY How do I create a layout based on Category, a portal based on SUB_CATEGORY then filter to only show matches in SUB_CATEGORY and SUB_SUB_CATEGORY fields. Back to my earlier question: Like if I want to show only schools that have a history class with a projector in them. It is like a filter across 3 different tables that are linked hierarchically showing total results in a single portal. If you know of any web links that might clear up this concert for me that would be fantastic! Thanks again for your very valuable time! Link to comment Share on other sites More sharing options...
tylertul Posted June 1, 2008 Author Share Posted June 1, 2008 Anyone? Help would be much appreciated. I can't seem to find the answer anywhere. I have been searching for days. Thanks! Link to comment Share on other sites More sharing options...
Ender Posted June 2, 2008 Share Posted June 2, 2008 Hey Tyler, Still scratching your head on this, eh? In order to filter multiple levels deep, you could apply the filter at the intermediate level, either with an unstored calc or a globally stored field. But the trouble with this is that changes to such filters usually have refresh issues (meaning they don't refresh unless you use a Refesh Window[flush cached join results] script step). What I was getting at is a different way using the list() function to pull all the relevant related values into an unstored calc in the interface's table, then using that as a parent key to a new relationship to the target table. There's no refresh issue, and as long as the related sets aren't very big, it can work quite well. If you were to upgrade your membership and post a clone of a sample of what you're working on, I'd be happy to poke at it a bit. Link to comment Share on other sites More sharing options...
tylertul Posted June 3, 2008 Author Share Posted June 3, 2008 I got it! I go it! Thanks a bunch. That last explanation got the rocks in my head to all line up. Thanks again and I will join the forum in the near future. It has been very helpful to me. Best wishes, Tyler Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts