4 Replies Latest reply on Jul 18, 2013 12:12 PM by terencetcm

    Retrieve data from one portal to another portal?

    terencetcm

      Hello,

      I'm new in Filemaker database. I have created a database to manage custom formulation of products. The database contains many tables, such as ingredients, customers, invoices, formulation. Formulation table contains a protal which showing records of specific 'formulation name' with many ingredients in it. When a customer ordering that particular formulation which most likely will add or subtract some of the ingredients already input in the 'Formulation table'. In the customer order table/layout, I would like to first retrieve all the ingredients from the portal of 'formulation table' layout and input into a new portal in the customer order table layout, then from there, I can add or delete specific ingredient, and finally base on the final ingredients listed in the portal in the customer order table, calculated the cost of that custom formulation and send out invoice.

      Can you show me how to do it?

      Thanks,

      Terence

        • 1. Re: Retrieve data from one portal to another portal?
          taylorsharpe

          There are a number of ways to do this from manually jumping from portal to portal and maybe including a layout change in there if the portal is in another layout.  This is VERY slow because user interface changes are notoriously slow in FileMaker. 

           

          Another way is to go to a layout based on the items in the portal and find those records in the portal, export them, and then go to the new portal and import them.  But exporting and importing is always messy and often leaves behind a file on your computer that you don't want or maybe has confidential information. 

           

          The way I prefer is to go to the portal by object name, go to the first portal row, and create a variable of the first portal row stringing the fields together and using a field seperator (e.g., "|" ).  Then go to the next portal row, append to the row the second record in the portal and continue stepping through the portal row.  After the last row, you now have an array with a portal row's info on each row with all of the fields.  Then go to the the Orders portal and reverse the process using the GetValue and Looping through to paste the info back into the 2nd portal. 

           

          I'm sure I raised more questions than I answered, but there are a number of ways to do this.  The Array method performs the quickist, which is why I use it. 

           

          Additionally, you may not need to loop through the first array if you can pull an ExecuteSQL of the first portal while you are in the 2nd portal and looping through those results to paste into the 2nd portal. 

          • 2. Re: Retrieve data from one portal to another portal?
            terencetcm

            Thanks Taylorsharpe, sounds like I have to read more, or if any expert in Toronto, or Waterloo Ontario area, I would like to meet them in person and learn.

             

            Thanks,

            Terence

            • 3. Re: Retrieve data from one portal to another portal?
              taylorsharpe

              I may have time this evening to give you a quick example file if I remember. 

               

              You can always look for developers in your area by searching http://developer.filemaker.com/search/

               

              Or if you come to Devcon, I'll be glad to meet you and show you there!

              • 4. Re: Retrieve data from one portal to another portal?
                terencetcm

                Thanks for your help again.

                 

                I am not going to Devcon, I am a cancer research scientist, trying to develop a database to organize the research data.

                 

                I am going to watch more training sessions, hopefully can solve my current problem.

                 

                Terence