1 2 Previous Next 15 Replies Latest reply on Jun 16, 2011 11:57 AM by arthurewilliams

    Relationship problem

    arthurewilliams

      Title

      Relationship problem

      Post

      I have set up several tables in my student portfolio program and am having trouble pulling out the necessary data. Here's my setup:

      Each student has multiple exhibits in their overall portfolio. Each exhibit has a unique serial number. Students can create subsets of their portfolios and choose the desired exhibits from a drop down list. In the portfolio sub-set layout, I need to display each exhibit's serial number in its own field after the exhibit title.

      This seems like it ought to be a simple solution, but I can't get those serial numbers. Anyone who could point me to the solution would be greatly appreciated. Thanks.

      Table.jpg

        • 1. Re: Relationship problem
          philmodjunk

          All you do is place that field for the serial number where you want it on the layout and the number should then be displayed.

          What is keeping that from working for you?

          You might want to describe what you have designed in terms of tables, relationships and your layout in much more detail and exactly what happens when you try to put this field "after the exhibit title".

          • 2. Re: Relationship problem
            arthurewilliams

            I have eleven tables defined in the database. The two I am concerned about are Portfolios and ExhibitData. They are related by a student identifier, ID#. Each Portfolios record contains its exhibit titles, chosen from a value list defined from ExhibitData. Each ExhibitData record contains, among other things, the exhibit title and its unique ID. In the Portfolio layout, I would like to choose an Exhibit title and then have its ID auto-entered. I tried to set up another relationship (ExhibitID) to another copy of the ExhibitData table, but with no success.

            Thanks in advance for your help and feedback.

            • 3. Re: Relationship problem
              philmodjunk

              Using the relationships as currently designed, you should be able to select an Exhibit ID in Exhibit01ID and then place the Exhibit Data 2::ExhibitID field on your layout. You can format this field as a drop down that lists the exhibit IDs and Exhibit names in two columns with the ID's in column one so that you can see the names to select them and the value list enters the needed ID number. But I see problems with how you've set this up.

              I see a series of similar fields:

              Exhibit01
              Exhibit02
              Exhibit03

              etc.

              This suggests that you have many exhibit data records for a given portfolio record and you thus should link exhibits to Portfolios by a PortfolioID serial number defined in Portfolios. All Exhibit records would then store a matching PortfolioID number in order to link them to the proper Portfolio record. On your portfolio layout, a portal to exhibit records could then list all Exhibit data records that are part of that portfolio.

              • 4. Re: Relationship problem
                arthurewilliams

                Success! I have an ID field that co-ordinates with the title field. Thanks for your help.

                As to the latter part of your post, I have the same concerns. Your solution sounds interesting, and each portfolio already has a unique ID. However, an exhibit may be included in several portfolios, which seems to lead to the same problem, i.e., multiple fields (portfolio01, portfolio02, etc). 

                I'm not unwilling to create a series of fields with the same characteristics, but there's an easier solution, I'd welcome it. There's another flaw with my current setup, which is a limitation on the number of exhibits in each portfolio. A repeating field may do the trick, but then the web implementation becomes problematic. 

                • 5. Re: Relationship problem
                  philmodjunk

                  Aha! I wondered if that could be the case, but didn't want to "go there" unless needed. What you describe is a many to many relationship where you should use a join table so that you can link an exihibit data record to any number of portfolio records and any Portfolio Record to any number of Exhibit Data Records.

                  You'll need to use table occurrence names carefully here to integrate this into your existing solution:

                  Portfolios---<Portfolio_Exhibit>---Exhibit Data

                  Portfolios::PortfolioID = Portfolio_Exhibit::PortfolioID

                  Exhibit Data::ExhibitID = Portfolio_exhibit::ExhibitID

                  If you place a portal to Portfolio_Exhibit on your portfolio layout, you can use it to list and add Exhibit Data records. You format the Exhibit Data::ExhibitID field as a drop down list or pop up menu of ExhibitID numbers and ExhibitNames so you can assign an exhibit to your current portfolio record by selecting an exhibit from this value list. You can also add additional fields to this portal row from the related Exhibit Data table occurrence to display additional data about that exhibit record.

                  You can do the same type of set up on an Exhibit Data layout to view and work with the list of Portfolios linked to that Exhibit Data record.

                  • 6. Re: Relationship problem
                    arthurewilliams

                    I experimented with a number of configurations, and found that a repeating field solves the problem nicely. As noted earlier, the ID field is configured as a drop-down menu. When chosen, it displays the title and the ID number. Upon release, it displays the ID number only and inserts the exhibit title in the title field. I changed both to repeating fields, and it worked. I discovered that my web solution can display specific repetitions from repeating fields, so that seems to solve the issue of the over-abundance of similar fields.

                    Further on down the line, I'll have to re-visit the portal solution, since I know students will want to examine their individual exhibits and then return to a specific portfolio. But I think I'll save that for another day.

                    Thanks a lot for your help.

                    • 7. Re: Relationship problem
                      philmodjunk

                      There are a number of limitations you can encounter with a repeating field. The most obvious is that you are limited to the number of repetitions you define where there is no limit when using a related table of records. Certains reports where you may want to group and list the data you are entering in the repeating field in different ways will also encounter limitations.

                      • 8. Re: Relationship problem
                        arthurewilliams

                        I agree that those are important considerations, and I am going to try to follow your advice.

                        I have been having some issues, however, getting the join table to do what it needs to, and I feel it has more to do with my inexperience in creating relationships and using them effectively. Can you suggest tutorials that can help hold one's hand through the process?

                        • 9. Re: Relationship problem
                          philmodjunk

                          I have a demo file whose download link I frequently post here in the forum. It matches Companies to Contracts in a many to many relationship. You can download it and examine how the relationship and layouts are designed to see how this can be implemented.

                          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                          • 10. Re: Relationship problem
                            arthurewilliams

                            Fantastic. I am looking forward to delving inside the file. Thanks very much.

                            I will probably be prevented from getting back on the forum for about ten days, as vacation is looming. Thanks again for your (very timely) help.

                            • 11. Re: Relationship problem
                              arthurewilliams

                              I was impressed by your solution's simplicity and effectiveness. However, I must be tripping over some obvious error, as, in my solution, I cannot get the value list in the portals to display anything. I have noticed that your file displays a box which opens up to the desired list while mine, while defined as a value list, is blank and does not display the list when clicked. 

                              I would appreciate any help you could render. Thanks in advance.

                              • 12. Re: Relationship problem
                                philmodjunk

                                You can use Manage | Value list to examine how your value list is defined and also how the value lists in the demo file are defined. By comparing them, you'll hopefully be able to spot what is different.

                                • 13. Re: Relationship problem
                                  arthurewilliams
                                  Alas, I cannot find a difference between the two. I have uploaded a test file. Can you please tell what bonehead mistake I'm making?
                                  Thanks for your patience. The file can be found at http://www.4shared.com/file/DlPewYgC/Join_Test.html
                                  • 14. Re: Relationship problem
                                    philmodjunk

                                    Edited your link to make it clickable. (In internet explorer, put the cursor to the right of the pasted link text and press return. In other browsers, you have to use the link tool in the upper left corner of the tool bar.)

                                    In your relationships, "Allow Creation of Records via this relationship" was not enabled for Join_Test in the Exhibits--<JoinTest relationship.

                                    1 2 Previous Next