3 Replies Latest reply on Mar 10, 2010 10:47 AM by TenaciousG3

    Problem with fields not finding correct info

    TenaciousG3

      Title

      Problem with fields not finding correct info

      Post

      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?

       

      Thanks!!

       

      -George

       

      DBrelationships

        • 1. Re: Problem with fields not finding correct info
          fitch

          Not sure I understand, but if you have a portal of episodes on the usage layout, you should see related episodes for that usage. However, if you just have a field from episodes on the usage layout, not in a portal, you'll see the first related episode, so that might explain your search results.

           

          I'm thinking you might need a join table here, if you have multiple episodes related to multiple usages.

          • 2. Re: Problem with fields not finding correct info
            comment_1

            I don't understand this either. You should simply put the EpisodeTitle field - or any other field from the Episodes table, e.g. EpisodeNumber - on a layout of Usage (make them non-enterable in Browse mode, so users cannot modify them accidentally), and that's it.

            There is no need for users to enter anything that describes an episode while creating/editing a record in Usage, and there is no need to lookup anything from the Episodes table into a record in Usage.


            ---
            Tom: Usage IS the join table.


            • 3. Re: Problem with fields not finding correct info
              TenaciousG3

              Thanks for your responses :)

               

              Please let me try to clarify.  The portal is to the Usage table, not the Episodes table and appears on a layout based on the LineArt table.  I've attached an image so you can see the layout. Using this layout, we enter usages of artwork based on the Episode number, including info like act, scene, script page, etc. For one piece of art there can be many usages within one episode (hundreds even) and there are 26 episodes.  We enter these usages individually - as we read through the script and see the same model appearing multiple times we enter the information for each useage using that portal. The search I'm doing is from a layout based on the LineArt table.  I believe you were correct when you said that my search results are returning the "first related episode", because it always returns whatever information is on the first line of the portal list.  Looking at the example image, say I did a search for all usages in episode 105.  The search would return this model, but it would list the episode number that appears on the first line of the portal, in this case 103.  I do have a unique key created automatically for each usage record, so I don't understand why it would keep returning the info from first line of the portal. Obviously it's finding that there is a record for 105, so what's going wrong?

               

              As for putting the episode number in the usage field, I tried that, getting rid of the Episodes table altogether.  When I entered usage records through the portal on that same layout (after changing the field to the newly created one) the exact same thing happened - whatever info was in the first line of the portal is what always turns up.  I also want the Episode table to be there so we don't have to enter the eipsode title and other related data every time because we refer to it often. For example, I was hoping that when we enter the episode number it would automatically fill in the episode title field because it would be able to look that info up based on the Episode record.