Issue with portal not displaying desired related record

[btmeacham]

I’ve got a database of film screenings, and am encountering some confusion when it comes to displaying data in a portal.

I have a layout that displays a director’s name and then lists each of the films by that director that I’ve screened. Next to the title of each film, the portal displays the number of screenings of that title, and data about the earliest screening. My problem comes when there has been more than one screening of a particular title, and I’m unable to get the portal to properly display the correct city/state associated with the displayed venue for that earliest screening. In the case of a film with more than one screening, I can get the portal to display the venue of the earliest screening, but then it seems to pull city and state data from a venue related to a later screening, even thought the tables are linked. 

One added wrinkle: the venue data is actually in two tables, one for a master name for the venue (Venues), and one for the actual name at the time of the screening (Venue Name), which I initiated to take into account the fact that cinemas change names over time. Most of the records have the same value in both, but for some venues, I have two or three names in the Venue Name table that correspond to the cinema name for a particular date range. 

Here's the relevant section of the graph:

From Person (director) to Title, connected by the join table Role, and then from Title to Screening of that Title, and on to the Venue of that Screening, through Venue Name for the version of the name at that time. 

In most cases, things show up fine, as in this entry under Steven Spielberg for E.T. One screening, with matching venue, city, state, and date..

When I've seen a film more than once, though, some error in the way I've connected the data together means that while the first Venue Name shows up (Greenwich Twin, in Greenwich, CT) and the correct date, 8/2/98, displays, the city of the Venue for a later screening (Los Angeles) follows.

Here's a view of the portal row in Layout mode.

It's unclear to me how FileMaker decides which set of information to display when there's more than one related record. I've fiddled with sorting the portal records by various fields, but with no luck. Any assistance in fixing this issue would be much appreciated! Thanks.

[btmeacham]

I can't tell if I haven't stated my problem clearly enough, or if those who have looked at it don't think there's a solution? I'd love any feedback and would be happy to provide more info if my issue isn't clear. Thanks!

[Steve Martino]

There's always a solution.  The problem is one of obvious structure & context, and your understanding of table occurrences (TO's) and table occurrence groups (TOG's).

Not all TO's have to be connected in one continuous string, and they usually should not be connected that way on any but the simplest database.

Using TOG's enable you to show how data is displayed and presented, and how it relates to the other TO's.

It's also important that the relationships are set up properly, and the method you are using to create related records.

Venue data in two separate tables sounds problematic.

I also don't understand why all your TO's start with 'PER', but I can't see your entire graph.

To me this set up is more like a basic Invoice solution set up, where the customer is the director, the line items are the screenings.  The venue, movie title, etc are products.  It's obviously more involved than that.  

It also matter from what perspective you want to view the records (reporting).

Do you want to go to a Director's layout, and create a screening record by selecting a movie, a venue (date, time, notes)?

Do you want to look up movies and see all the screenings?

Do you want to look up venues and see all the screenings?

A copy of your database file (with personal data deleted) would be the quickest way to get some help, and prevent myself and others from the back and forth that will occur with the first 20 questions popping up into my head.

[btmeacham]

Thanks, Steve. Sorry for any lack of clarity in articulating my question. My understanding of TOs and TOGs is fairly rudimentary, but I think I grasp the concepts. Here's a shot of the entire graph:

These particular TOs are prefixed PER_ as a way to remind me that they're related to the Person table; they're occurrences of other tables that are used to view things through the director of a film, so for ease of organization, I've preceded them with PER_. 

The Venue data in two tables might be problematic, and I'd be open to a better solution, but I think about it as akin to a Name and Aliases, or a Name and a Display Name; they're two ways of referring to the same entity, the Venue, that are valid or true for different historic periods. I need to be able to see a list of all Screenings at a single Venue, no matter what it might have been named over the years, but prefer to keep the name of the Venue accurate for each screening, hence the two tables.

The heart of the database is the Title table, which lists film titles, and connects to the Person table through the Role table, for directors, and the Screenings table, for instances of screenings of each film. Various other tables like Country of origin and Keyword and others are related to Title, and others like Guest and Projection Type and so on are related to Screening.

I have working setups for creating and viewing records in Title, Screening, Person, and so on, but the issue that I'm having trouble with is displaying the proper Venue data when listing Titles and Screenings by director. In viewing the Person layout for a particular director, I have a portal displaying each of their Titles for which I have a Screening in the database. The portal displays the Title, Year, and other film data, and the the number of Screening records associated with that Title, as well as the Venue, City, State, Projection Type, and Date it was first screened.

When there is more than one Screening of a Title, I run into an issue: the portal displays the Venue, Date, and Projection Type from the earliest Screening record, as designed, but then City and State from another, seemingly random Screening of the same Title, neither the earliest nor the most recent. I'm not certain how to get the portal to pull or sort the data correctly so it lists Venue, City, State, Projection Type, and Date from the same, first screening. It's displaying a Venue based on a Screening of a Title, but then displaying City and State information from a different Venue associated with that same Title through a different Screening. That's the part that has me scratching my head. Thanks.

 

[Steve Martino]

Keep in mind, only you know all the terms and minutia of your database.  For others looking at it cold, it's very confusing.  I kind of understand what you are trying to accomplish, and that seems like it could be sort of straight forward.

For me, quicker way to help would be a copy or sample database.  Seems like you have way to many TO's, some probably unneeded as you could most likely cover some of these tasks with filtered relationships or filtered portals.

You can also clean up your graph to show just the TO's, and the fields that connect them, and collapse the rest for easier readability.  And you can group them by color too.

All the TO's starting with PER and GUE are suspect, and what's a Guest, and how are they important to the overall scheme of things.

In general, it's just a matter of figuring out what TO you want to start with (standing on) and connecting to the proper child TO to view the related records, in its easiest form, in a portal.

I'm also curious about the Countries TO's.  If needed for reporting, can probably accomplish more simply with scripted finds.

Overall it's a very complicated database, or you made it that way.  Which can easily happen.  It's not too hard to run amok with adding more and more TO's or TOG's and going 'too far down the rabbit hole', so to speak, just to try to accomplish each new task you want to add.

Sometimes a re-build could be in order, then transfer (import) the data into the new one.

Also, it's helpful to amend your profile for FM version, and Operating system/version.

Edited December 11, 2019 by Steve Martino

[btmeacham]

Thanks, Steve. You're right about it making sense to me and being confusing to others—it has been cobbled together and modified bit by bit over the years, and looks unwieldy now, though it all functions as expected...except for this one weird bit.

To answer your other questions, Guest isn't particularly important; it's just a table that joins a Person to a Screening to note that the Screening had a special guest in person. Countries is a table of countries of origin, which is joined to the Title table by the Origin join table, to track the country or countries of origin of the films. And I have updated my OS and FM version info, thanks.

In the case of my particular issue, I want to stand in the Person TO and view a list of Titles associated with (directed by) that Person, as well as Screenings of those Titles, and see data about the first of those Screenings for each Title, including Venue, City, State, and so on. All of the rest of my database is noise when it comes to this, and 99% of it is irrelevant to this particular little issue, which has something to do with my Venue and Venue Name setup, I think. I'll look deeper into it and I'll see if I can make a trimmed-down copy of the database to upload. Thanks.

[btmeacham]

The root of the problem seems to be that when having the portal display the chronologically earliest Screening associated with any one Title, it lists the Venue in order by Venue_ID, the unique identifier field, rather than by earliest Screening Date, and I can't find any way to change this sorting. I've decided to add a field to my Title table that indicates the Venue of the Title's first Screening, and just have it display Venue data related to that Venue, which works fine. Thanks for looking over my issue and giving me some new things to think about.

[comment]

Person --< Roles >--Titles --< Screenings >-- Venues

What you describe is the expected behavior. Filemaker will not see another many-to-many relationship starting from a portal row. it will only see a series of independent relationships. It will populate each portal row with data from a record in Titles. Then it will populate the fields from the other tables with data from the first related record in the other table, starting from the record in the portal row. This is done independently for every field - so the screening date field will be populated by the date of the first related record in Screenings, and the venue name will be populated by the name of the first related record in Venues, regardless of where the first screening took place.

Note that the above applies only to TOs that are "behind" the portal TO relative to the context TO. If you place a field from Roles inside the portal, it will be populated by data from the first related record in Roles, but this time starting from Person.

 

[btmeacham]

Thanks for articulating this. This is the behavior I saw, but didn't understand, and was trying to work against, as I worked through the issue. I'm glad to know that it's expected behavior and I wasn't crazy for being unable to find a way to get the portal to work the way I wanted. I've arrived at a solution that required some going back to old records and adding data, but that will work as I wanted going forward. Thanks!