It's really hard to decipher all the alphabet soup in that post. I think this is the issue, but could be wrong...
When you enter criteria in a field from a related table, you are telling filemaker "find all the records in the current layout's table that have at least one related record with this value." You aren't finding records in any of the related tables at all, just finding records in the layout's table and then those records will display whatever records are related to them whether the related records match your criteria or not.
Yeah, sorry for the alphabet soup :-P Every time I try to explain it in a more detailed fashion nobody wants to read through all of that so nobody ever responds! I was hoping by simplifying it a bit that people might be more apt to read it. I can certainly provide a more detailed description if you'd like, pictures even!
So assuming your thoughts are correct, how do I fix that? Do I need a script in order to perform the search correctly?
First, let's confirm facts:
TableA:: PrimaryKey = TableB::FK_A
TableC:: PrimaryKey = TableB::FK_B
You are performing the find on a layout that refers to TableC with a portal to TableB on it displaying records from Table B. You enter find mode and specify a value in a portal field. The desired result is to see all the specified records from TableC but only see the portal records with the value you specified.
See, here's where it gets more complicated and I start losing people :-P It's also the part where I start annoying people, so I apologize ahead of time if I do so. Here's my relationship graph:
I have a layout based on "A" where we enter new objects as needed. On this layout there is a portal to "B" where we enter the usage of this object and new records are created in "B" through the portal. "B" contains a field that looks up the episode number value from a field in "D" (I couldn't find another way of being able to enter that value in usage without it creating a new record in "D"). I have a layout based on "B" that lists all the usages, getting info from "A" "C" and "D" and it works fine when I do a search. Then I have a layout based on "C" were we need to be able to see all the views along with relavent info, like the name of the object, the serial number, which view it is, which episode it's being used in, etc. I have fields from all 4 tables on this layout, including the Episode Number Lookup field I mentioned before. The problem is when I search in the "C" layout using that lookup field, it always returns the value from the first line of the portal used in "A", not the relavent record from "B" which is what I want it to return.
One other problem on a related note - I can't seem to get other info from "D" to pull in to layouts based on other tables (like episode title). Is this because I'm using the "lookup" thing and not the actual field?
Does that make any sense? Am I a giant pain in the ass? Should I just give up now? :-P
Then I have a layout based on Views were we need to be able to see ... which episode it's being used in,
The problem here is that the information you seek is not there. The way you have it now, the Usage table records the usage of an asset - not of any specific view. Your relationships need to be:
Assets -< Views -< Usage >- Episodes
That is true that usage records the asset not the view, but since the assets and its views are related and linked together through their respective tables, shouldn't it pull that information through anyway? Perhaps I have a misunderstanding of how info from related tabes is pulled though?
It cannot pull information that isn't there. The information that it DOES pull through is as follows:
Suppose you have an asset Alpha, with views Red and Blue.
Red was used in episodes 1 and 2, Blue in 2 and 3 - but we don't know that: we only know that Alpha was used in episodes 1, 2 (twice) and 3. These are the related episodes from the context of Alpha.
From the context of Red (or Blue), the only related record in Assets is Alpha, and the related episodes are 1, 2 and 3.
OK I see what you're saying, but the usages in our case don't work like that. We're dealing with many views of one single object, but every single view of an asset is used every time the asset it is connected to is used. That's why I set up the tables that way. There may be many views of one asset, but they will never be used in one instance and not in another, they're always part of a set. Now... One asset may have 5 views, one may have 3, one may have 20... but again, they will always be used as a SET, not individually.
I thought that since I have the AssetID present in the Asset table, the View table and the Usage table that all the records would relate to each other and would be able to get any related info from any of the tables since the AssetID is the same. Is this not correct?
every single view of an asset is used every time the asset it is connected to is used.
Well then the result above (Blue is related to episodes 1, 2 and 3) is correct, isn't it?
Well I suppose it is technically correct, but retrieving the correct views is not the problem I'm having. The problem is, if I perform a search using the EpisodeNumberLookup field, though it returns the correct views, the values that appear in the usage related fields after performing the search are always the values from the first row of the portal. So let's say an asset has records indicating use in episodes 1, 3, 4, and 12, and "1" is the episode number value that appears in the first row of the portal. if I search for views used in episode 12, I do get the correct views but the episode number field says "1" not "12". Same is true for the other usage fields in that row (like act, scene, etc. They are all returned as the first row's value, and not the value related specifically to episode 12.)
That is the intended behavior: if you are search the View table, using a portal to Episodes (or Usage), you end up with a found set of Views used in Episode 12. But the found view continues to be related to ALL episodes it's been used in - and that's what portal shows.
A better example: you search a text field for "cat" and you find two records; in one of these the word "cat" is the last word out of 2000, way out of view.
OK I get that, but the portal is not on the layout based on the "view" table that I am searching from. The portal is on a different layout based on the Asset table where I'm using the portal to create records in the Usage table from that layout. The layout I'm doing the search from has no portals, it just has a related field for "EpisodeNumberLookup" which is what I am entering my search data into.
Oh and by the way - THANK YOU for spending the time to help me figure this out... you've been very helpful so far in narrowing things down and I really appreciate it.
It makes very little difference: the related field shows data from the FIRST related record (same as what you would see in the first row of a portal). It doesn't "know" you have searched for 12 in the current table; it's only concern is the related set in the other table.
OK so that being the case, is there a way to correctly ask it give me the data I want - not the "first" related record it sees, but the actual record related to the criterea I asked for? I have another layout based on the usage table that only searches for the assets used in an episode. I put the episode number I want into the same field there, and it doesn't only find the first record of the portal, it finds the ones specific to my episode number search. I realize this is returning assets not views, but still - why does it work in that layout and not the other?