6 Replies Latest reply on Nov 9, 2011 12:12 PM by disabled_morkus

    Portals and Lookups for larger child related tables (with many records)

      Title

      Portals and Lookups for larger child related tables (with many records)

      Post

      I have a portal defined for my "many" records in the data entry layout below the main parent record.

      This works fine for a few child records, but I see some future issues I don't know how to address yet:

      1. Once I have 10,000-50,000 child records for a given parent, I'd have to scroll all the way to the bottom to add a new child record in the portal. Plus, I might not want to wait or display all the child records for a given parent.

      What to do....?

      2. I was thinking to maybe create a separate layout for just entering child records, but I can't seem to create a separate child table data entry layout lookup where I would be showing the "vendor name", but STORING the vendor ID. Again, this lookup might not work since it's matching index between the VENDOR and child table.

      So, in 2. above, I was thinking that I would have a "add child record" button or something that would take me to a separate layout. There, I would have a pop-up that would let me pick the vendor name, but populate that field (the linking, indexed, vendor_id field in the child table) with the vendor_id.

      What is the best practice is for this type of situation (larger databases) using portals?

      Thanks in advance.

      -m

        • 1. Re: Portals and Lookups for larger child related tables (with many records)
          philmodjunk

          1a You can put a global text field at the top of your portal or in a dialog that you pop up and then use a script to add the new related record. The script can move the data from the global field or fields into the new related record.

          1b You can use portal filtering to work with subsets of the total set of related records, or it may work better to switch to a list view layout based on the portal's table with fields from the parent record displayed in the header or even a sub summary part. Each approach has its strengths and weaknesses, so you have to select the method best suited for your database design and user needs.

          2 both a drop down list or a pop up menu can be used for this. Using a layout to the child table to enter new records (what I just described in 1b), is a common place layout option. You can use the specify field option in Manage Value Lists to list ID codes from Vendor (the parent table) in field 1 and a name or description field from the same table in field 2. You can hide the first field's values or not as you choose. With a pop up menu, the 2nd field's value will appear when you exit the popup if you choose to hide the field 1 values in the value list. The drop down list will show the ID number. There are ways to set up the layout to hide the ID number if you want that and prefer the drop down list.

          If you are starting from the Vendor (parent) table and want to create a new related record, there's no need to make the user select the same vendor all over again. You can put the VendorID of the current Vendor record into the Foreign Key field of the new child record:

          Set Variable [$VendorID ; Value: vendors::VendorID]
          Go to Layout [Child layout]
          New record/request
          Set field [child::VendorID ; $VendorID]

          If you were to add Go to layout [original layout] to the end of this script, you could use it to add a new record in your portal. by adding a sort order to the portal, this newly created record can automatically appear in the first portal row as a way to avoid scrolling the portal to add a new record...

          • 2. Re: Portals and Lookups for larger child related tables (with many records)

            Wow, great information! Thank you!!!

            I tried your script approach, but the line of code: "New record/request" generates a new VENDOR_ID when I end up in the detail data entry layout. I'm confused why this happens since you capture the vendor ID in the first step and then set it in the last step.

            Also, surprisingly, the vendor_id is still incremented to the next auto-incremented value in the "child" layout. Not sure why this is, but I think the auto-increment is (somehow) overriding the (later) set field command.

            I guess this is one of thoese cases where I need thes script debugger?

            ------

            So, on the main form, I just added a button that says: "Add Detail Record". The script it runs is nearly identical to yours above except that I added a "goto field" command so I wouldn't be in the VENDOR_ID on the child (detal record) page.

            When I click the button on the main form, I do indeed go to the child form, but as I wrote above, the vendor_id put into the child's VENDOR_ID linking field has the next auto-incremented value, not the vendor_id from the parent form.

            Also, I'm adding a brand new parent record, not just a child record which may be what's messing things up even though in the child view I only see the child records.

            Hmmm.....

            Thanks again in advance.

            - m

             

             

             

            • 3. Re: Portals and Lookups for larger child related tables (with many records)
              philmodjunk

              I tried your script approach, but the line of code: "New record/request" generates a new VENDOR_ID when I end up in the detail data entry layout. I'm confused why this happens since you capture the vendor ID in the first step and then set it in the last step.

              I always strongly recommend using the script debugger, but in this case the child table's foreign key field should not be an auto-entered serial number. The parent record's matching Primary Key should be set up this way and the Child table's Foreign key should then just be a data field of the same type.

              I added a "goto field" command so I wouldn't be in the VENDOR_ID on the child (detal record) page.

              That works, but you can also remove change the tab order of the fields so that the cursor automatically appears in a different field if you wish.

              Also, I'm adding a brand new parent record, not just a child record

              If you want ot add a brand new parent record at the same time as you add the child record, use new record on the parent layout to create the new parent record, copy the Primary Key into a variable like I showed in my script example, then switch to the child layout and do the same as the original script. The only change made is to start the script with an additional New Record step before capturing it's ID number.

              I very rarely find it necessary to automatically create a related child record at the same time that I create the parent. Instead, it usually works quite well to generate related child records on an "as needed" basis from subsequent user actions--but this doesn't mean that it's a bad idea or difficult to do.

              I should also add that ID number based value lists in FileMaker can easily become very cumbersome for the user as the number of values in the value list increase. You can help the user get a better handle on it by using conditional value lists where the user selects a category in field one and the list of values in the ID based value list then only shows the items from that category. 

              With scripting, there are also several methods you can use where you type in all or part of the item's name, and a script finds the record and puts a copy of its ID code into the appropriate field for the user. This script can even open up a list of values matching the partial text entered for the user to refine their selection down to a single item. I've shared a demo file that shows off several such approaches:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

              • 4. Re: Portals and Lookups for larger child related tables (with many records)

                Sorry for the confusion here.

                I don't want to add a new parent record. That's the problem.

                When I click the "add detail" button from the parent page, I only want to add a new child record in that (child's) view I navigate to, but what seems to be happening is that it's adding an entirely new (parent) record.

                The matching key between the two tables is the VENDOR_ID, which is the "primary key" (auto-increment) in the parent table (but not in the child table).

                In the child table, I'm running the code you suggested, but the vendor_id is getting the next auto-generated value, not the one from the curent parent's layout record (that is, the vendor ID for the current record in the parent layout).

                The child talble's VENDOR_ID foreign key is not set to auto generate. It's just "indexed" (by FMP, automatically)

                Wouldn't the "Add record" command respect the layout it's in?

                Not sure what I'm doing wrong.

                Thanks,

                -- m

                • 5. Re: Portals and Lookups for larger child related tables (with many records)
                  philmodjunk

                  I'm not sure either, but the script I suggested would be run from the Parent layout, not the child layout. It captures the primary key value of the current parent record, changes layouts to the child record and creates a new record in the Child table on this layout, then assigns the value from the variable.

                  New Record does respect the "layout it's on". In fact one of the key debugging issues with FileMaker scripts is to double check the current layout and the table occurrence to which the layout refers in Layout Setup | Show Records From as the only way the script can access data is through the current record of the current layout.

                  I'd check and make sure your Child layout actually refers to the child table in Layout Setup | Show Records From. If the script is creating a new record in the parent table, then, at the time that script step executed, it was on a layout that referred to the parent table instead of the child table.

                  • 6. Re: Portals and Lookups for larger child related tables (with many records)

                    It was my fault. I had the detail view marked as the parent table even though when I clicked the fields they "said" they were from the child table.

                    In any case, it's working now.

                    FMP rocks!

                    I appreciate all your help.

                    Thanks.

                    - m