1 2 Previous Next 19 Replies Latest reply on Mar 24, 2010 5:05 PM by Heatherrd

    Bug? Join Table and Portal - how to add extra related records in the portal?

    daveslc

      Title

      Bug? Join Table and Portal - how to add extra related records in the portal?

      Post

      I have three tables:

      Events, Contacts, and the join table EventContacts

       

      In the relationship graph with:

      Contacts and EventContacts 

      allow creation and don't allow creation [of new records]

       

      Events and EventContacts

      don't allow creation and allow creation  

       

      [that is, in ht event layout, I want to be able to create contacts or add them to the event; but in the contact layout I don't want to be able to create events] 

       

      In the layout for Events, I have a portal to both Contacts and EventContacts.

       

      I want users to be able to enter (or associate existing) contacts into the portal and have the contact_id field as a popup menu.

       

      So far so good, but when I enter an existing value into the contact_id field in the portal, and click out of the portal, the first record is populated by the proper info, but a new record is also created with just the contact_id field filled in (and when I go to the related record in Contacts, a new blank record has indeed been created.

       

      a. The problem  seems to be that when a new portal row (contact record) is associated (using a value list) with the current event record, that in turn creates a new record in the EventContacts table, which in turn creates a new blank record in the Contacts table.

       

      b. If I turn off the 'allow creation of records' in the Contacts table, I can no longer enter enter or associate records through the Contacts portal, but have to do this in the join table (which has no way to create a new contact record).

       

      Not sure what to do to at this point.

       

      thanks,

      Dave

       

        • 1. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
          philmodjunk
            

          I'm assuming the following fields and relationships:

           

          Events::EventID = EventContacts::EventtID  (Allow creation enabled for EventContacts)

           

          Contacts::ContactID = EventContacts::ContactID

           

          Your portal on your Events layout should be based on EventContacts. Your pop-up menu should be your EventContacts::ContactID field. I suspect that your filed is actually Contacts::Contact ID and this is why you are getting new records in the Contacts table when you don't want them.

           

          This portal will work well for assigning existing contact records to your current Event record. It won't work well for entering a new contact. That would require you to use your contacts portal first, or some other approach. (I sometimes add a button in the portal row that creates a new matching record in the third table--contacts in your case.)

           

           

          • 2. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
            daveslc
              

            Thanks, this helps a lot!  

             

            in the contacts portal I have buttons  to go to related record and add new contact (which also adds a new record in the EventContacts table). These work well. And I have another button that doesn't work as well. 

             

            Two more questions: 

            I have added buttons in the EventContacts portal to 'remove association' (which is set to delete the EventContacts portal row, but then does not refresh the contacts portal after the association is deleted so the old data is still visible even if not 'really there').

            Question 1. How do I get the contacts portal to refresh? Is there a script step for this?  Refresh window doesn't do anything.

             

             

             

            Question 2. What is the cleanest way for users to associate an existing contact with the event in the EventContacts portal?

             

            I have the field associated with EventContacts::ContactId in the EventContacts portal as a value list that has as its values "All Contacts", with the contactID and the email address as the two fields that comprise the value list (not all of my event contacts have names).

             

            Since I currently have 300+ contacts (and this will grow to 1000+), this is a long list to scroll through if I use a pop-up window.

            If I have the window pop up, it doesn't seem to work to type 'g' and go to the first email that starts with g.

             

            thanks,

            Dave

             

            • 3. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
              philmodjunk
                

              Q1: See what happens if you add Commit Record, that step should force everything to update correctly.

               

              Q2: I find that issue to be something of a catch-22 in current versions of Filemaker. Auto-complete is a very nice feature that enables you to have a drop down list of names that quickly filters down to a manageable size when the user types in a few letters, but ID numbers are always the best type of field to use as a relational key and a value list based on a number field (even if hidden to show names in column 2) can't have the auto-complete feature. (In my opinion, adding the option to enable auto-complete on column 2 would be a nice new feature for upcoming releases of filemaker.)

               

              Here's what I currently use to enable name based searches to enter an ID number: 

              1. Add a global name field for searching for contacts by name.
              2. This field can be formatted with your name list and auto-complete can make even extremely long lists practical.
              3. Write a search script that uses the entered name to perform a find on the contacts table.
              4. If only one record is found, enter that contact record's ID in the appropriate field in your portal.
              5. If more than one record is found, display the list of names in a list either on a different layout or a popped up window so the user can click one of the names to select it. This click, of course, then enters that contact's ID number into your portal's Contact ID field.

               Note that this trick deals with contacts with similar or identical names and keeps your relationships based on ID numbers rather than names.

              • 4. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                daveslc
                  

                Q1: I changed the button to refer to a script which now has:

                delete portal row

                commit record

                refresh window  (this last step was necessary as just committing the record didn't refresh the other portal window).

                 

                This works!  Thanks!!

                 

                Q2: I will report back when I have a chance to write this script.

                 

                 

                • 5. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                  Heatherrd
                    

                  Hi there, I'm so new to FMP that I'm confused by simple things... My situation is similar to the above scenario, with Many to Many relationships. I have to understand a few more things before I can use the info above.   I haven't been able to connect the info in the manuals and help with the specific advise given on the forums because I'm confused on a few basics.

                   

                  1. Is a join table created as an individual table and then linked to at least one other table on each "side" so that it becomes the "child" with a "parent" linked and a "grandchild" linked as well? 

                   

                  2. Why do people use  these field names: pk_OrderID and fk_OrderID?  What is pk and fk?

                   

                  Thanks,
                  Heatherrd

                  • 6. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                    comment_1
                      

                    Heatherrd wrote:
                    1. Is a join table created as an individual table and then linked to at least one other table on each "side" so that it becomes the "child" with a "parent" linked and a "grandchild" linked as well? 

                    Almost: a join table is a table of its own, but it is actually a child of two parent tables.

                     

                     


                    Heatherrd wrote:

                     

                    What is pk and fk?


                    Primary key and foreign key. You don't have to use them in field names (I don't), but you should be familiar with the terms.

                     

                     


                    Heatherrd wrote:
                    I haven't been able to connect the info in the manuals and help with the specific advise given on the forums

                    Perhaps the demo here might help:

                    http://www.fmforums.com/forum/showpost.php?post/246136/


                    • 7. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                      Heatherrd
                        

                      Heatherrd wrote:
                      1. Is a join table created as an individual table and then linked to at least one other table on each "side" so that it becomes the "child" with a "parent" linked and a "grandchild" linked as well? 

                      Almost: a join table is a table of its own, but it is actually a child of two parent tables.

                       

                      Clarification: do I create the join table in the manage database Relationships by copying one of the parent tables or do I create the table in manange database Tables?  What makes it a child? The linking or the source of the fields?  I thought it was a unique table because of the new serial ID.

                       

                      about the demo...

                       

                      How do I figure out what is going on in the demo when I don't have access to the Manage Database Relationships, Tables or Fields?  I assume that Affiliations is the Join Table.  To make the drop down menu for contacts and Organizations, are the fields using value lists or Portals? 

                       

                      In my database, I have Garments with 2-5 Materials in unique Locations on the Garment.  Each Location refers to the Garment's pattern pieces.  Each of the Materials are used many times in different places on different Garments. For each Location on each Garment, I will enter the quantity of Material required for 1 Garment.  Then when the Orders come in, I will multiply the quantity of Garments Ordered by the quantity of Material required for each Location of the Material on each Garment.  

                       

                      Tables: Garments, Material_Lines, Materials, Orders, Order_Lines (All have serial numbers in their corresponding _ID fields which are used to link them)

                       

                      I don't understand how to write the relationships in symbols.  Here is my best explaination:

                       

                      Garments is linked to both Material_Lines and Order_Lines.  Material_Lines is linked to Materials.  Order_Lines is linked to Orders. 

                       

                      Material_Lines is supposed to be the Join Table where I have the Garment, the Location on the Garment and the Material for that Location.  My Portal on Garments shows the Material_Line with the Location and Material for each of between 2-5 Materials/Location combinations.  There is never more than 1 Material per Location.  On the Material table, I have a Portal which should show all the Material_Lines for that Material.  The Material_Line should have the quantity of Material required for 1 Garment, so that if 1 Material is used on 5 Garments, (each Garment will require a different quanity of Material), then the Quantity of Garments Ordered, multiplied by the quantity required for each Garment will give me the total quantity of Material that needs to be Purchased to fill production of the all 5 Garments, so that we can fill the Orders from the Customers. 

                       

                      I would like to be able to enter the quantity of Material required into the Portal on the Garment table.  Better yet, I'd like to enter Material_Lines in the Portal on Garments so that I can select the Material from a drop down list, select the Location from a Value List and then add the quantity of Material I need for that Garment, in that Material and that Location.  Materials get entered first, then "attached" to Locations on Garments.  Each unique Location on each Garment would be the Material_Line.

                       

                      If I get these relationships figured out, can I make scripts and buttons later, to protect myself from errors in data entry?  I haven't learned scripts or buttons yet.

                       

                      Thanks for your help!

                       

                      • 8. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                        comment_1
                           The join table is a child of two parents, because it is on the 'many' side of two one-to-many relationships, e.g.:

                        Students -< Enrollments >- Classes

                        where Enrollments is a join table. You can create a join table from scratch or from a copy of an existing table - that's purely a matter of convenience.


                        The demo is completely unlocked. The account name is Admin and the password is blank.


                        Re your specific situation, this is a rather standard model:

                        Orders -< LineItems >- Products -< Quantities >- Materials

                        However, summarizing quantities by material from the point-of-view of Orders is far from trivial - see:
                        http://fmforums.com/forum/showtopic.php?tid/192677/post/285457/#285457






                        • 9. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                          Heatherrd
                            

                          Considering how newbie I am, should I even attempt to do this?  I read the thread and don't understand enough to even start to get an idea about how to set up the Grandchild summary. 

                           

                          If I paid someone to help me, how much might it cost me?

                           

                          Thanks again!

                          Heather

                          • 10. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                            comment_1
                              

                            Heatherrd wrote:

                            Considering how newbie I am, should I even attempt to do this?


                            Hard for me to say. It's not something I'd recommend to a beginner, but it all depends on how much you want to put into it.

                             

                             


                            Heatherrd wrote:

                            If I paid someone to help me, how much might it cost me?


                            That depends, too.  :P



                            • 11. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                              Heatherrd
                                 Where would I begin to learn how to do this?   Do you think it can be done in 3-4 weeks if I put 15-20 hrs a week?  Can you recommend a book or training? 
                              • 12. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                                comment_1
                                  

                                I don't know. Basically, you're asking how long does it take to learn to swim...

                                 

                                 

                                There are several books out there, but since I haven't read them I don't feel qualified to make a recommendation.

                                • 13. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                                  Heatherrd
                                    

                                  Hi Comment,

                                   

                                  You gave SurferNate a solution, Post #285472http://fmforums.com/forum/showpost.php?post/285472/. Then you both tossed around other options.  Ridgid, recursive, Ugos, etc.

                                   

                                  Are you able to tell me which would work for my situation?  If there are several workable solutions, do you have a recommendation?

                                   

                                  recap: I would like reports that show:

                                  1. all materials on each Garment (by garment) for costing the garment

                                  2. all items for each customer's Orders (by order) for making a packing list

                                  3. all the orders of each Garment (by garment) for production work orders

                                  4. all garments that use each material (by material) for purchasing materials

                                   

                                  What is OTOH?

                                   

                                  What is GTRR?

                                   

                                  Many, many thanks!

                                  Heatherrd

                                  • 14. Re: Bug? Join Table and Portal - how to add extra related records in the portal?
                                    comment_1
                                      

                                    1. Costing a garment is easy: in the Quantities table, define a calculation field cExtendedCost =

                                    Quantity * Materials::Cost

                                    and another calculation in the Garments table =

                                    Sum ( Quantities::cExtendedCost )

                                    will return the total cost of the garment.

                                    Alternatively (or in addition) you can produce a report from the Quantities table, summarized by GarmentID.



                                    2. This too is not difficult: just find the relevant orders, then use GTRR (i.e. the Go to Related Record script step) to create a found set of their items.



                                    3. A calculation in the Garments table =

                                    Sum ( LineItems::Quantity )

                                    will return the total ordered quantity of the garment. However, this needs to be filtered in some way to exclude finished orders - I am not sure how you intend to handle the shipping and purchasing parts, so I'll leave this open for now.



                                    4. This too should be relatively easy, once 3 is worked out: since you now have the ordered quantity in the Garments table, you can multiply it by the used quantity in the Quantities table, and sum it up in the Materials table (or in a report produced from the Quantities table, summarized by MaterialID).



                                    None of these involve the difficulty discussed in the other thread. As long as you don't need to summarize the materials used in each order (i.e. give the customer a bill of materials), you can ignore it.

                                    However, you do need to consider the "timeline" element here: say you produce a report of materials that need to be purchased for a certain group of orders. Then some materials are purchased, and some new orders come in. Now they want an updated report of what needs to be purchased. This needs to be worked out as a workflow first, before you can figure out how to track it.

                                     

                                     

                                    ---

                                    OTOH = on the other hand
















                                    1 2 Previous Next