9 Replies Latest reply on Feb 28, 2011 8:44 AM by brian.curran

    Use a portal to update a table?

    brian.curran

      Title

      Use a portal to update a table?

      Post

      Hi,
      I'm new to FM but have built some basic databases using MS Access. I'm using FM Pro 11 Advanced and FM Go (iPad).

      I'm struggling to get my head around Portals, as I'm unsure how to use them to update tables. They look great for updating related data though. I have the following structure so far:

      ARCS
      _kp_arcs_id
      company
      address
      city

      SITES
      _kp_site_id
      _kf_arcs_id
      company
      address
      city

      The Relationship is 1-Many from SITES to ARCS. 1 site can only have 1 arc but each arc can have many sites.

      My Issue:
      In the Sites form, I want to select a 'company' from the 'ARCS' table using a drop-down list. This action should then copy the _kp_arcs_id into the _kf_arcs_id. Then, a portal would show the 'address', 'city' and any other data fields from the relevant ARC entry.

      Is this possible? Any tips/suggestions on how to achieve this would be great, many thanks...
      Brian. 

        • 1. Re: Use a portal to update a table?
          Sorbsbuster

          It would be more conventional in a one-to-many like that to add a Site from the Arc, rather than create a Site and then try and attach it to an Arc.

          The way you would do that is to have a portal to Sites in the Arc layout and set the relationship to 'Allow Creation Of New Records'.  The last line of the portal will be blank, and you can add the info directly there to create a new site for that Arc Record.

          If you want to see the Arc data on the Site record you can simply add the fields of detail that you want to see from the Arc record.  As there is only one match then showing the data from its relationship will show you the data from the only record that matches - its parent.

          There are other ways to create a child Site from a parent Arc if that way doesn't suit you.

          • 2. Re: Use a portal to update a table?
            brian.curran

            Thanks for the quick response Sorbsbuster.

            Maybe I'm approaching this from the wrong angle but my end goal is to have a multi-tabbed form for each customer 'Site'. One of the tabs would be for displaying the 'ARCS' details, it's rare for a Site to change its ARC but it is a possibility.

            I think your suggestion means that the user would have to exit the current Form and enter the details in another Form, which isn't ideal as I would like them to be able to do everything from the one form if possible?

            • 3. Re: Use a portal to update a table?
              Sorbsbuster

              "The Relationship is 1-Many from SITES to ARCS. 1 site can only have 1  arc but each arc can have many sites." - this seems to be a contradiction, but I don't think it makes any difference here.

              It seems that you want to create a new Site within the Site table, and then allocate it to an ARC.  You could:

              - create a value list of all the ARCs, using the ArcID as the first value, the ARC name as the second value, and only display the second value (to make it easier for users to choose the right ID.)

              - in the Site table set this pop-up value list on the _kf_arcs_id field and let the user select.

              • 4. Re: Use a portal to update a table?
                brian.curran

                "The Relationship is 1-Many from SITES to ARCS. 1 site can only have 1 arc but each arc can have many sites." - this seems to be a contradiction, but I don't think it makes any difference here.
                Yes sorry, my mistake. 

                It seems that you want to create a new Site within the Site table, and then allocate it to an ARC.
                Correct (and then later, use this field as part of a portal to retrieve the other ARC details such as address etc.)

                You could:
                - create a value list of all the ARCs, using the ArcID as the first value, the ARC name as the second value, and only display the second value (to make it easier for users to choose the right ID.)
                Ok 

                - in the Site table set this pop-up value list on the _kf_arcs_id field and let the user select.
                This is where I start to get lost.

                File | Manage | Database | Sites | Fields | _kf_arcs_id
                The only option I have here is to tick the Member of value list: and choose "ARCS"

                If I then go to my multi-tabbed form (Dashboard) and add a new field, I can set that to have a drop-down list but nothing appears when I click into it.

                I think it could be something to do with the Relationship, I have a 1-Many from ARCS to SITES but nothing ticked in the 6 check boxes when I double click the line to edit the relationship, any suggestions?

                Many thanks
                Brian. 

                • 5. Re: Use a portal to update a table?
                  Sorbsbuster

                  "...later, use this field as part of a  portal to retrieve the other ARC details such as address etc." - If you want to see the Arc data on the Site record you can simply add  the fields of detail that you want to see from the Arc record.  As there  is only one match then showing the data from its relationship will show  you the data from the only record that matches - its parent.

                  Don't set the field definition to validate it against the pop-up list (which is what you are doing in File - Manage..etc) put the field on the layout and in Inspector - Data tab set it to have a pop-up, with that Value List.

                  You don't need to set any of the check boxes (yet, anyway) in that ARC -> Site relationship.

                  • 6. Re: Use a portal to update a table?
                    brian.curran

                    Thanks again Sorbsbuster, I'm starting to understand it better and think it's working now.

                    In the layout, I was adding a field from the ARCS table instead of the SITES table! Now, when I choose a named ARC it stores the ID in the Sites table. I added a Company field from the ARCS table to the layout but that only updates when I click out of the layout to exit the record.

                    Is there a way for this field (and the others) to update immediately each time I choose another ARC? 

                    • 7. Re: Use a portal to update a table?
                      brian.curran

                      Ah, just realised that if I change the field from Drop-Down to Pop-Up, there is no need to add the Company field as it now shows the ARC name even though it stores the ARC ID.

                      Could still do with some kind of 'update fields after selection' fix if possible...

                      • 8. Re: Use a portal to update a table?
                        Sorbsbuster

                        That is a difference between drop-down and pop-up.  To give the user friendly names was why you created the value list as lisiting from 2 fields.

                        "Could still do with some kind of 'update fields after selection'" - what fields need to be updated?  You have simply tagged this Site as belonging to that ARC.  If you want any fields from that ARC to display in the Sites records, just show them on the layout, and they will be there, live.

                        • 9. Re: Use a portal to update a table?
                          brian.curran

                          Yes, the Pop-Up menu is much better as it also works well on FM Go for the iPad.

                          When creating a new Site record, I can choose the ARC company name from the Pop-Up menu and tab out of it. However, the address and contact details for that ARC remain blank until I click away to commit the record.

                          Just thought it would be more user friendly if the relevant fields updated each time the ARC name is changed...

                          Thanks for all your help once again, much appreciated.
                          Brian.