8 Replies Latest reply on Oct 4, 2016 11:04 AM by CICT

    Set Foreign Key - No Portals

    jschmo

      I have been given the task of learning how to use Filemaker and I haven't been able to figure out how I can set the foreign key field on a table. The getting started PDF mentioned portals but I don't think the server is using portals because when I try to get related layouts it returns nothing.

       

      Here is a snippet of the code I have been trying to get to work.

       

      http://pastebin.com/raw/t8qj2NHN

       

      When I execute this code I get Error: Field cannot be modified. Is there a specific way to set the id of the foreign key field? Here is also an image of the configuration for the foreign key field.

       

      Screen Shot 2016-10-03 at 11.38.23 AM.png

        • 1. Re: Set Foreign Key - No Portals
          CICT

          If you wish to set the foreign key using this method, ensure the relationship link has the option to create records on the foreign table side and then use a script to set the value of another field in the foreign table, this will generate the foreign key automatically.

          • 2. Re: Set Foreign Key - No Portals
            coherentkris

            start with the filemaker training series and work it through.

             

            If you are on a layout based on the lead table (infered from the name of the fk in appointments from your ss) with a portal to the appointments table...and the join in the relationships graph is Lead::_pk_LeadID to appointments::_fk_LeadID and you have create records turned on...

            you can create records in the portal and the fk_LeadID will be automatically filled in.

            If you want to do it via script then set field or set field by name from the context of the lead table will work:

            Set Field ( appointments::__fk_LeadID ; lead::pk_leadID)

             

            Error: Field cannot be modified is being thrown because of the checkbox at the bottom of the screenshot that prohibits modification during data entry

            1 of 1 people found this helpful
            • 3. Re: Set Foreign Key - No Portals
              CICT

              Just to clarify, you don't need to set the field from a portal, this is an option but not essential.

              • 4. Re: Set Foreign Key - No Portals
                David Moyer

                Hi,

                If "Set Foreign Key - No Portals" means that you don't want to use a portal (which is one route), then you need to write a script.  It looks like you already know your foreign key, so the script step Set Field (mentioned above) is what you need to use.  If you don't know your fk, then, in your script, you would:

                - start in the parent table layout

                - set variable $pk = parent key

                - go to the foreign table layout

                - create a new record

                - assign $pk appropriately (via set field)

                - $fk = newly created foreign key

                - go back to the parent table layout

                - assign $fk to _fk_LeadID

                1 of 1 people found this helpful
                • 5. Re: Set Foreign Key - No Portals
                  jschmo

                  I was just given access to the DB and told to interact with it via PHP API so I said "Set Foreign Key - No Portals" just because I wasn't sure if there was or wasn't a portal setup. As far as I can tell its just layouts with very basic fields in them. When I try to do

                   

                  $relatedSetsArray = $currentLayout->getRelatedSets();

                   


                  it doesn't return anything which makes me think that portals weren't used/setup. I tried what you recommended and I wasn't able to set the foreign key unless I remove the option "Prohibit Modification of value during data entry." Is there a way to use SetField with that option checked? Thanks for your time/response. I only ask if it can be done with that option checked because I do not have the ability directly to change the way the database is configured. it was configured by a client.

                  • 6. Re: Set Foreign Key - No Portals
                    CICT

                    Don't try to set the foreign key. As long as the relationship allows creation of records, set another field in the foreign table and the foreign key will be set automatically.

                     

                    Kind regards

                    Andy

                    1 of 1 people found this helpful
                    • 7. Re: Set Foreign Key - No Portals
                      jschmo

                      So I enabled the "Allow creation of records in this table via this relationship" on the Appointments table. How would I set a field on the foreign table? My guess was this

                       

                      http://pastebin.com/raw/yHiz0a6j

                       

                      but it gives me the error Error: Field Not Found

                       

                      And here is my relationship settings:

                       

                      Screen Shot 2016-10-04 at 10.44.24 AM.png

                       

                      Thanks for taking the time to answer my post,

                      -Justin

                      • 8. Re: Set Foreign Key - No Portals
                        CICT

                        Hi Justin

                         

                        Simply use the script step when running in a layout based on the Leads table (TO) and use the  script step Set Field and pick one of the fields in Appointments TO, but not the foreign key, e.g. Set Field ( Appointments::Date ) or one of the others fields listed.

                         

                        Regards

                        Andy