1 2 Previous Next 16 Replies Latest reply on Oct 18, 2013 1:54 PM by philmodjunk

    Related Tables problem

    jhc

      Title

      Related Tables problem

      Post

           I'm having trouble making my tables relate. I have attached my ERD (relationship tables). 

           I want the user to be able to enter info for a lake. And then enter info for one (or more than one) net site. The net site may have many fish in it. so it's a series of 1:M 

           I tried portals but my tables are too big - the fields just go way past the layout area (i'm trying to make this for an iPad). I also tried tabs and that seems to work but couldn't figure out how to make the user be able to pick which lake his/her net site belongs to or which net his/her fish was caught in.

           I thought I was making progress when I discovered portals but I feel like I've gone backwards now and am no closer to figuring this out. Anyone with any words of wisdom? 

           please & thank you

      lakeCatchDB_reltsp.JPG

        • 1. Re: Related Tables problem
          philmodjunk
               

                    the fields just go way past the layout area

               Did you know that portals are not limited to only one row of fields per portal record? You can reduce the number of portal rows and then increase the portal Height and drag fields around in order to have more than a single row of fields in the portal.

               Don't know exactly what you tried, but I'd use a layout based on NettingSites with a portal to FishCatches. _FK_LakeID can be formatted as a drop down list to link the record to a record in LakeInfo.

          • 2. Re: Related Tables problem
            jhc

                 Do you have any examples? So far I've only found examples of portals that have one row per record. 

                  

            • 3. Re: Related Tables problem
              philmodjunk

                   They are quite simple to create. Here's the steps:

                     
              1.           Enter Layout mode.
              2.      
              3.           Open portal setup for the portal and change the number of rows to 1.
              4.      
              5.           Click Ok
              6.      
              7.           Click on the portal to select it if you do not see selection handles due to it already being selected
              8.      
              9.           Drag a handle down to make the portal row taller. You can make the portal row as tall as you need--you are only limited by the amount of space available on your layout.
              10.      
              11.           Either move fields already in the row by dragging them or use the field tool to add more fields to the portal row.
              12.      
              13.           Return to portal setup and increase the number of portal rows to a size that fits the space on your layout.
              14.      
              15.           The resulting portal can be much more like a "mini-form view" than the default "mini-table view" if you work at it a bit.
              • 4. Re: Related Tables problem
                GuyStevens

                     Maybe you could create a portal with all the lakes. And when someone selects the lake then the info for that lake appears in the fields.

                     These fields can be organized in tab objects.

                     Look at this video for an example:

                     http://www.youtube.com/watch?v=VJqA03X33DQ

                     This does use two portals. But that second portal could just a easily be a bunch of fields.

                     I use this technique quite often.

                • 5. Re: Related Tables problem
                  jhc

                       i tried Phil's idea but the portal - being only one row, only allows me to enter data for one row. So far I haven't figured out how to make it enter a second row if I've caught another fish in the net. 

                       I'll have to give the youtube video a try next. 

                  • 6. Re: Related Tables problem
                    philmodjunk

                         You missed a step. You change the set up to one row while resizing the portal row to be taller. Once you have finished, you reset the number of portals out to a larger number of rows--more than one, but less than the number you had originally.

                    • 7. Re: Related Tables problem
                      jhc

                           thanks Phil 

                           that seems to have worked for my first layout but for some reason it's not working on my second layout. (Minnow Traps to Minnow Catches)

                           not sure what's going on. I deleted the old portal and started a new one. I started at zero rows. I resized everything and ended up just doing a regular table (removed a lot of fields because I still can't quite picture the layout you're describing). But so far I only get one row entered on the minnow trap / Minnow capture layout. 

                           The fish nets to fish capture one still works as you described and to my untrained eyes, they're identical. Any other things/settings/checkboxes I may be missing?

                      • 8. Re: Related Tables problem
                        jhc

                             p.s. - for my minnow form it is actually stranger than that. When entering data into the portal I get a duplicate row below my entry box. It auto fills from whatever I've just put into the box above it. But I cannot add a new record below it. 

                        • 9. Re: Related Tables problem
                          philmodjunk

                               In layout mode, what text to do see to the right of "Table:" in the status tool bar at the top of the screen? You should see "MinnowTraps" from what I see in your relationships graph.

                               If, while still in layout mode, you open portal set up for the portal, what do you see in Show Related Records From? You should see "MinnowCatches" there.

                          • 10. Re: Related Tables problem
                            jhc

                                 Sorry, I've been away for a while.

                                 The attached is what I see for my attempt at relating minnow traps with minnow catches. I'm not sure where to look for "Table:"

                            • 11. Re: Related Tables problem
                              philmodjunk

                                   Your screen shot was captured while in Browse mode. I specified: "In layout mode, what text to do see to the right of "Table:""

                                   Click "Edit layout" to enter layout mode and try following my suggestions again.

                              • 12. Re: Related Tables problem
                                jhc

                                     oops. wrong mode. the attached here is for layout mode. 

                                     so I've got the wrong table referenced in "Table" ? It is mentioning my lake one.

                                      

                                • 13. Re: Related Tables problem
                                  philmodjunk

                                       Your layout is referring to "A_Lakeinfo". This is a table occurrence not even shown on your original screen shot of Manage | Database | Relationships.

                                       And thus I have no way to know what table occurrences might be linked to it nor how. I'd need to see a screen shot of your current table occurrences and relationships to be able to suggest anything.

                                       And what is shown in Portal Setup | Show Related Records From for the portal on this layout? (Double click the bottom part of the portal while in layout mode.)

                                       The relationship that links your layout's Tutorial: What are Table Occurrences? to the portal's table occurrence is key to understanding how the portal works or fails to work.

                                  • 14. Re: Related Tables problem
                                    jhc

                                         ah you're right. My portal setup was showing related records from the wrong table even though the individual fields in the portal were pointing to the right table. 

                                         I changed that around and now it seems to be happier. At least I'm not getting triple entries every time I enter once. 

                                         thanks.

                                         I also posted my new ERD. I changed the names of my tables to make it easier to figure out what goes where. Obviously I've confused myself on more than one occasion already. 

                                    1 2 Previous Next