3 Replies Latest reply on Dec 26, 2016 3:13 AM by David Moyer

    File

    Daveo

      Hi All

      I am new to FMP

      I have  3 tables  one is called  "Quote_Detail" in this  I have  a portal which is called "Budget"

       

      I would like to preload  the portal with a list of budgets names (pk) from the table called "Budget codes"

      in this table I have a field called default_load  which has a check box Value list of 1 .

      I am trying to copy the ones that have  a value of 1 in the  default_load field   to the budget table

      I only need to copy  the Pk number  to the Fk in the budget table .

      This is to happen when I add  a new quote

      How do i do this  please I have no idea ???

        • 1. Re: File
          erolst

          Easiest way to get the values: trigger a script that calculates

           

          ExecuteSQL ( "

            SELECT \"pk\"

            FROM \"Budget Codes\"

            WHERE default_load = 1

            " ; "" ; ""

          )

           

          and sets the field to that value.

           

          An easier way to merely display the flagged records from Budget Codes: in Quote_Detail, create a numeric toggle field, and to your existing portal add the portal filter calculation

           

          not Quote_Detail::toggleField or Budget Codes::default_load

           

          That allows you to switch the portal record display between all related records and just the flagged ones by toggling the switch field between 1 and 0/empty.

          • 2. Re: File
            Daveo

            Hi Erolst

            Thanks for the quick answer

             

            with the sql code

             

            where do I put the code  for it to run ??

            And  I see  pk in the code  put where will the code put the pk

            I need it  to go to the fk in the budget table !!!

            Sorry I'am only new to this 

             

             

            • 3. Re: File
              David Moyer

              Hi,

              here's a note from the FM SQL reference

              The FileMaker Pro ExecuteSQL function lets you retrieve data from table occurrences named in the relationships graph but independent of any defined relationships. You can retrieve data from multiple tables without creating table joins or any relationship between the tables. In some cases, you may be able to reduce the complexity of your relationships graph by using the ExecuteSQL function.

              The fields you query with the ExecuteSQL function do not have to be on any layout, so you can use the ExecuteSQL function to retrieve data independent of any layout context. Because of this context independence, using the ExecuteSQL function in scripts may improve the portability of the scripts. You can use the ExecuteSQL function anywhere you can specify calculations, including for charting and reporting.

              https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

              1 of 1 people found this helpful