1 2 Previous Next 18 Replies Latest reply on May 9, 2014 9:45 AM by philmodjunk

    Checkboxes using Join Table

    HNG

      Title

      Checkboxes using Join Table

      Post

           I'm trying to set up something that should be very simple, but can't seem to do it.  There are two tables "Documents" and "Sites".  I want the Documents layout to display the available sites (currently only two) with the user able to select which site(s) apply via a checkbox. 

           Following other advice on this forum I've got this far: set up a join table (Doc_Site).  Checked "allow creation of records" in the Doc_Site table via the relationship.  Then set up a portal to the Doc_Site table on the Documents layout.  In the portal I included Site ID and Site name, both from the Sites table.  However, this portal does not let me enter new data.  I've also tried setting up value lists but this didn't work.  I set up the value lists through the "inspector".  Was that the wrong place? 

           Other advice on here linked to a Demo file.  Unfortunately I could not understand it.  The Check Boxes tab says to use a portal "using the Cartesion join operator "X".  I can't get past this step.  What exactly does "Cartersian Join Operator X" mean, and how to do it step by step?   Is there a simpler way to do this?

        • 1. Re: Checkboxes using Join Table
          philmodjunk

               This sounds like my demo file. But why do you need the join table to do this? There can be very good reasons to set up such a system, but I don't see a reason for it in the info that you have posted thus far.

               To set up a relationship between two tables using the Carteisan join operator instead of the default = operator in a relationship:

               Drag from a field in one table occurrence box to a field in the other to set up the typical relationship with the default = operator.

               Double click the relationship line that you just created.

               In the dialog that appears, there's a drop down that you can use to change the = to X.

          • 2. Re: Checkboxes using Join Table
            HNG

                 I thought it was necessary due to a one to many relationahip.  Some documents apply to both sites.  Others apply only to the first, or only to the second.  Doesn't this make the join table necessary?

            • 3. Re: Checkboxes using Join Table
              HNG

                   Sorry I meant many to many relationship

              • 4. Re: Checkboxes using Join Table
                philmodjunk

                     That's the missing piece of info. If you read over your initial post, there's no mention of the fact that sites to documents is a many to many relationship. wink

                     Let me know if my info on cartesian join operators was enough or if you have additional questions.

                • 5. Re: Checkboxes using Join Table
                  HNG

                       Also, I'd like the relationship set up in order to pull up information in different ways.  Especially, being able to filter for the documents list pertaining to site 1 or site 2 on different layouts.  Will this be possible without a join table?

                  • 6. Re: Checkboxes using Join Table
                    HNG

                         Sorry, I hadn't seen that first. So based on the many to many relationship, do you agree that I need a join table? 

                          

                         I see what you are saying about the Cartesion join and how to set it up,.  But If using a join table, do I still need the Cartesion Join Operator.

                    • 7. Re: Checkboxes using Join Table
                      philmodjunk

                           Well you do need the join table so whether or not it is possible without a join table is a question that is not relevant to your situation.

                      • 8. Re: Checkboxes using Join Table
                        HNG

                             OK, but I am still not having any success with this.  As mentioned, I have a join table.  I've set up a portal to the join table (Doc_Site).  I've put fields from the Site table in the portal.   But it will not allow creation of new records into the portal.  I tried setting up a value list with values from the Site ID field.  Can check off check boxes but it still doesn't create new records in the join table.    I'd like to know the simplest way to do this. 

                        • 9. Re: Checkboxes using Join Table
                          philmodjunk

                               Is this the demo file that you are looking at? https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                               If so, the portal is not to the join table. It's to an occurrence of the table on the far side of the join, but linked in a cartesian relationship to list all records from that table. In your case, that would involve linking Documents to a new occurrences of Sites via the Cartesian join operator (X) instead of the default = operator.

                               Then the check boxes shown are not actually check box fields. They are a rectangle object with a layout text object (the "X") placed on top. They are then set up as a single button to be clicked that performs a script. The script checks to see if a join table record for that document is already linked ot the current site record. If so, it deletes that record from the join table. If not, it adds a new record to the join table and links it to the current site record and the selected document record. Conditional formatting makes the X appear and disappear to complete the illusion that you are clicking a check box field.

                          • 10. Re: Checkboxes using Join Table
                            HNG

                                 Hi Yes, that's the demo I was looking at!  Your description above makes it a lot more clear.  In the demo, I didn't see the part about the extra occurrence of the table. 

                                 At this point it still looks to me very complicated to do- I am sure with enough time I could figure it out.  But I really want to know is this:  is there a simpler way to do this?  Maybe based on value lists?

                                  

                            • 11. Re: Checkboxes using Join Table
                              philmodjunk

                                   In the same demo, you'll see examples of working with a join table by using value lists.

                              • 12. Re: Checkboxes using Join Table
                                HNG

                                     This is what I've been trying to do, but it won't let me enter any data.  If I enter data via the actual join table, then I can go back to Documents and see the information on the portal.  But can't enter new site-document record via the portal.  Tried changing a bunch of settings and cannot get it to work.

                                • 13. Re: Checkboxes using Join Table
                                  HNG

                                       Are you referring to your first tab on the Demo, the tab called "Basic Setup" ?  I wasn't clear if the Tabs were referring to different steps of the entire process.  

                                  • 14. Re: Checkboxes using Join Table
                                    philmodjunk

                                         All of the layouts except the one that demonstrates simulated check boxes use value lists to add new join table records.

                                         If you have this relationship:

                                         Sites-----<Site_Document>-----Documents

                                         with these Match Fields:
                                         Sites::__pkSiteID = Site_Document::_fkSiteID
                                         Documents::__pkDocumentID = Site_Document::_fkDocumentID

                                         and you enable "allow creation of records..." for Site_Document in the Sites to Site_Document relationship,

                                         Then you can place a portal to Site_Document on your Sites layout and format _fkDocumentID as a drop down list to use for selecting an existing Documents record to link to the current Site record by creating a record in the Site_Document table that links the current Site record to the selected Document record.

                                    1 2 Previous Next