Problem with fields not finding correct info
Hello all - newbie here again with another question.
I have a database set up to track artwork for an animated TV show. I've attached an image at the bottom of how my relationships are set up.
I have a layout based on the Usage table that includes a field from the Episodes table on it which I want to automatically fill in when a record is created and it's not working properly. The Episodes table has 1 record for each show number which includes the show number, title and which studio is assigned to create the episode. When creating usage records, we enter the episode and scene information for each usage through a portal from the LineArt table. This creates an individual record in the usage table that includes episode number, act, scene, etc. for each piece of art used. I do not want a new episode record to be created every time I create a new usage record (the "create records via this relationship" button is checked off in that direction), so I created an "EpisodeNumberLookup" field in the usage table where the user enters a value based on the EpisodeNumber field in the Episodes table.
So - I have 2 problems. First, the Episode title is not showing up on any of the layouts I have set up based on the Usage table. When I go to the usage record, it shows the correct episode number, but the EpisodeTitle field is blank. Second, when I do a search for all usages in episode 106 for instance, all the correct usage records come up BUT the episode number field always has the value for the first time that model was used, not the value for the episode being searched. For example, if I originally used a model in episode 101 and now I am using it again in episode 106, when I search for all 106 models it comes up but it lists 101 in the episode number field, not 106.
So... any thoughts on what I did wrong? Please?