1 2 Previous Next 20 Replies Latest reply on Sep 4, 2014 3:12 PM by philmodjunk

    Adding portal row doesn't work although creation is allowed

    StefanSaeys

      Title

      Adding portal row doesn't work although creation is allowed

      Post

      It seems that I have an issue with FM13 and portals.

       
      I’ve got three tables in a many-to-many relationship: <companies>, join table <companies_contacts> and <contacts>.
      The tables are allowed to create and delete records in both ways.
       
      I’ve added a portal in layout companies to join table <companies_contacts> and placed the fields contacts::FirstName and contacts::LastName from the table <contacts>
      In my first version this worked as expected: if I put contents in the field on the second line, a new record in <contacts> was created.
       
      But when I tried creating this again in the “master" database it doesn’t work.
      I can’t find any differences between the database that works and the new one that doesn’t. I’ve played around by adding fields in the portal, placing them back, delete my portal again…..
       
      The strangest thing that it suddenly worked again but I don’t know why. I didn’t change anything in the relationships, only in the layout.
      I tried to add some more fields and now it doesn’t work anymore -again….
       
      Does anyone knows why this is happening or what I am doing wrong?
      Any help greatly appreciated.

        • 1. Re: Adding portal row doesn't work although creation is allowed
          philmodjunk

          How exactly is if failing to work? A few possibilities come to mind and a more detailed description of what exactly is going wrong for you might indicate which option is a possible cause of your trouble.

          • 2. Re: Adding portal row doesn't work although creation is allowed
            StefanSaeys

            The behaviour is identical when "creation is allowed" is not selected in relationships.
            I can't type new data in the second field (or the field that is empty).

            I've placed a "goto record" on the same line that is duplicated to the next line. This looks ok and this works (for the field that contains data).

            • 3. Re: Adding portal row doesn't work although creation is allowed
              philmodjunk

              Everything shown in the screen shot looks correct. Buttons placed in a portal row will appear in the "add row" made possible by enabling "allow creation" in the relationship linking your layout's table occurrence to that of the portal's.

              I realize that's not the main issue, but since you commented on it...

              I am assuming that you can't put the cursor into the name field from the third related table in this add row in order to enter data. If you can but get an error message let me know as that's a different issue and I'd want to know what that error message says.

              I am also assuming these relationships, but with your field names in place of mine:

              companies---<+companies_contacts>-----+contacts>.

              With the layout showing "companies" in Layout Setup|Show Recors from, the portal setup shoing "companies_contacts" in Show Related Records From and that the first two fields show "Contacts::" as the first part of the text in "display data from" on the inspector's data tab when you select either of those two fields.

              ----< means "one to many" and the + indicates where you'd need to have "allow creation" enabled in order for you to be able to enter data into fields from Contacts when placed into the portal row.

              There are a number of possible issues that would keep you from entering data.

              While not likely from what you have described, the fields themselves might be formatted to prevent data entry. The behavior setting on the Inspector's data tab may have the "Browse Mode" check box cleared for these two fields, the might be set up as buttons or an OnObjectEnter script trigger may be tripped when you enter the field and the script performed by the trigger kicks you back out of the field. But note that these are unlikely as you would not have those settings in place by accident in most cases.

              I know that you've said otherwise, but if "allow creation..." is not enabled for Contacts in the Join Table to Contacts relationship, you won't be able to do this. (enabling Allow creation for the Join table in the Join to contacts relationship has no effect on what happens in this portal.)

              The fields placed in your portal row might be from the contacts table, but from a different table occurrence of contacts (and then won't show "contacts::" in Displayd Data from in the inspector. Since it's the relationship from layout's table occurrence to portal's TO to the TO of the field that controls access to the field, a wrong occurrence used here could make the fields inaccessible.

              PS. when working with Join Tables, I generally find it better to use a button for adding new records in the "3rd table" (Contacts in this example) rather than by using "allow creation" right in the portal row.

              • 4. Re: Adding portal row doesn't work although creation is allowed
                StefanSaeys

                Allow creation and deletion of records in this table is enabled on both sides of the <companies_contacts>. In total I've got 8 checkboxes enabled.

                I can only select any field from a row were at least 1 field is already filled in. In the screenshot earlier I can click in the fields "Anette", "Fonder-Humblet" and "French". I can't select or place my cursor in any field on the second row. I don't get any error trying to do so; it just is not possible, so it feels like the same behaviour as when the "creation of records" is not selected in the relationships.

                I am assuming that you can't put the cursor into the name field from the third related table in this add row in order to enter data. If you can but get an error message let me know as that's a different issue and I'd want to know what that error message says.

                The "goto" button brings me to the related record in <contacts>.

                My relationship is <companies+---<+companies_contacts+>-----+contacts>; not sure if my notation is correct, but I hope you know what I mean. But I've also tried <companies---<+companies_contacts>-----+contacts>

                The layout showing companies points to "show records from <companies>; the portal in the layout companies points to <companies_contacts>.

                If I select a field from <companies_contacts> in my portal then the functionality works as it should (but in the wrong table). I can select the second row (but only if the field relates to <companies_contact>. If I put in a field that refers to <contacts> it doesn't work for that field and the behaviour is the same as before.

                The fields in Inspector have field entry browse and find mode enabled. No scripts behind them. I just drag them from the tools menu.  

                I don't have any occurrences at present time but now that you mention it I've used to have one, but it was deleted.
                Could it be that I didn't check the box "remove reference" by doing this? If so, how do I remove it?

                • 5. Re: Adding portal row doesn't work although creation is allowed
                  philmodjunk

                  I don't have any occurrences at present time

                  Actually, you have at least three or you would not be able to get the layout that you describe. I will assume that you mean that you do not have any other occurrences of these three tables.

                  What are you using for the primary key field in contacts? How is it defined? This would be the field used as a match field to match contacts to companies_contacts.

                  • 6. Re: Adding portal row doesn't work although creation is allowed
                    SteveMartino

                    Might be quicker if you could post the file or a sample file.  This just happened about a week ago with a poster, and the problem was the relationship wasn't set up exactly right.

                    • 7. Re: Adding portal row doesn't work although creation is allowed
                      StefanSaeys

                      Yes, I don't have any other occurrences.

                      <contacts> __pkContactID, serial number
                      address info

                      <companies_contacts>
                      __fkContactID ; number field
                      __fkCompanyID ; number field
                      <companies>
                      __pkCompanyID ; serial number
                      company address info


                      mini version of the database can be downloaded at http://www.saeys.org/properties.fmp12.zip

                      • 8. Re: Adding portal row doesn't work although creation is allowed
                        SteveMartino

                        Take a look at this.....a little better.

                        You weren't making related records. In the portal I made the key field a dropdown, based on a value list.  But there is still a problem with your data.  You shouldn't have the exact amount of records on all three tables...

                        https://dl.dropboxusercontent.com/u/71328624/properties%20copy%202.zip

                        • 9. Re: Adding portal row doesn't work although creation is allowed
                          SteveMartino

                          Also, can you describe what your database is suppose to be doing.  In other words, it appears to be set up like an invoicing solution with your join table resembling line items.  Some additional questions:

                          What is the purpose of the contacts table, can all your contacts work for more then one company?

                          What is the purpose of the portal, to show all the contacts that work for that company?  If a contact can only work for one company, then I think I would structure the DB differently.

                          What kind of reports will you need to create based on this relationship?

                          Why are there two primary keys in the Company Table?

                          • 10. Re: Adding portal row doesn't work although creation is allowed
                            StefanSaeys

                            The companies could be linked to multiple contacts and one contact can work for multiple companies.
                            While the companies contain business addresses, the contacts contain private addresses.

                            The portal is there to show what contacts are working for a company.
                            There are two primary keys because on the other side there is another many-to-many relationship with properties. And one property could be linked to multiple companies...

                            I excluded the other side to make it more clear and easier (especially for me ;-))
                            The only difficulty that I'm facing setting that side up, is that the current REF field doesn't contain unique fields. It was exported as such from another database that isn't functional anymore. So for now, a property could be linked to multiple companies that have the same ID. When I start trying to do this, I will probably make a new post :-)

                            In the meanwhile I've checked my trial database that did work with the portal and it seems that __fkCompanies starts from 1 and the __fkContactsID start from 000001. This is probably why it works. 

                            I don't understand why I can't have the same amount of records in the three tables. They are separated anyway so why would it matter?

                            • 11. Re: Adding portal row doesn't work although creation is allowed
                              philmodjunk

                              In the meanwhile I've checked my trial database that did work with the portal and it seems that __fkCompanies starts from 1 and the __fkContactsID start from 000001. This is probably why it works. 

                              That should not affect what you report here.

                              Looking at your small file, the pkcontactsID field in Companies should be removed. And selecting "delete" on both sides of every relationship is very dangerous to your data integrity. Deleting one record will trigger a chain of cascading deletes that are likely to delete more than you wanted.

                              • 12. Re: Adding portal row doesn't work although creation is allowed
                                StefanSaeys

                                ok, I was already starting from scratch again just to verify.
                                I can't understand why the functionality doesn't work. Any other idea why it isn't working as it should? Is it only the pkcontactsID that is causing this? Because it is also present in my working trial version.

                                Thank you for the tip, I will see to it that all future relationships that I setup are allowed to create and delete on one side only.

                                • 13. Re: Adding portal row doesn't work although creation is allowed
                                  philmodjunk

                                  I don't know that contactsID has anything at all to do with it. It's just a field that I can see is in the wrong place in your test file. I'd need to see your actual file, or some detailed screen shots (such as one of your layout taken in layout mode and one of the dialogs where you've specified the relationship details) before I can make any further guesses as to why this isn't working for you.

                                  • 14. Re: Adding portal row doesn't work although creation is allowed
                                    SteveMartino

                                    The companies could be linked to multiple contacts and one contact can work for multiple companies.
                                    While the companies contain business addresses, the contacts contain private addresses.  The portal is there to show what contacts are working for a company.

                                    I recommend a different approach.  On what bases does a contact work for 2 companies.  Is this temporary work?  You may need a join table called "Employment" so you can see when a contact worked, for 1 specific company.  Just knowing a person worked for a company might not be enough information.  What if they leave and come back?, etc.


                                    There are two primary keys because on the other side there is another many-to-many relationship with properties. And one property could be linked to multiple companies...

                                      But 2 primary auto enter keys for the same table cant be right.  It might be, but I can't give you a correct answer.  You shouldn't need 2 primary keys to connect one table to 2 different tables. 

                                    The only difficulty that I'm facing setting that side up, is that the current REF field doesn't contain unique fields. It was exported as such from another database that isn't functional anymore. So for now, a property could be linked to multiple companies that have the same ID. When I start trying to do this, I will probably make a new post :-)

                                    This is going to create headaches.  So does the REF field hold the key for relationships, and do you need to capture this number and convert it to your other primary/foreign keys?

                                    I don't understand why I can't have the same amount of records in the three tables. They are separated anyway so why would it matter?

                                    I don't think having multiple copies of the same records in different tables can be needed for any reason.  Simply, it's too confusing.  Without constantly checking the pk/fk, how do you know which record the table is coming from when you view it?

                                    I think your portal should be Employment, from an Employment Table.  I think you should populate the portal (and hence the table) from a picker list based on Contacts.  The other fields in Employment (on the portal) could be job names, start/end dates etc. 

                                    If you need to see properties owned, that's another issue with another portal. 

                                    1 2 Previous Next