11 Replies Latest reply on May 3, 2010 11:50 AM by philmodjunk

    Help - Inserting data from one table into a portal in another table

    Shoreline

      Title

      Help - Inserting data from one table into a portal in another table

      Post

      Hi,

      I am busy developing a logistics program.

       

      Orders are raised in anothe program and imported into filemaker. Into my orders table.

      Shipping is booked from Filemaker where as I have to link 10 orders to 1 shipment booking.

      I thought about creating a portal with an insert script that will bring up all available order, click on insert to import each order but my import script does not want to work.

       

      Any other suggestion on how to acomplish what I am trying to do?

      Im using FMPro10 Adv running on Windows

        • 1. Re: Help - Inserting data from one table into a portal in another table
          philmodjunk

          More detail about your orders would help. Does each order have a unique ID? Is there a customer ID so that you can easily pull together a group of order records for a given shipment?

           

          You may be able to write a script that runs through the newly imported orders records and creates matching Shipping records automatically from the order records, but can't give you specifics from the information you've given in this post.

          • 2. Re: Help - Inserting data from one table into a portal in another table
            Shoreline

            Hi Phil,

             

            Yes the orders have an order no which is an unique ID. They also have a Customer ID.

             

            I've created a portal with an insert button which work, it brings up the list of orders in a smaller window.

             

            Enter Browse Mode []

            Set Error Capture [On]

            Allow User Abort [Off]

            Commit Records/Request [Skip data entry validation; No dialog]

            If [Mastersheet::Count_Items = 15]

                  Show Custom Dialog ["Error"; " You can not add more than 15 orders."]

                  Exit Script[]

            End if

            Set Variable [$$Master_Sheet_ID; Value:Shipping::Shipping_ID

            Set Variable[$Order_No; Value:Master_Sheet::Order_No

            Adjust Window[Resize to fit]

            Freeze Window

            New Window [Name: "Insert"; Top: 50; Left: 100]

            Go to Layout["Purchase_Order_Insert" (Master_Sheet)]

            Adjust Window[Resize to fit]

            Show/Hide Status Area[Lock; Hide]

            Enter Find Mode[]

            Set Field [Shipping:Master_Sheet::Order_No; $Order_No]

            Perform Find[]

             

            Next to each order there is an insert button which doesnt work, script to follow:

             

            Set Error Capture[On]

            Enter Browse Mode[]

            Set Variable [$Order_No; Value:Master_Sheet::Order_No]

            Set Field ["Shipping:Master_Sheet::Order_No; $Order_No]

            Set Field [Shipping::Shipping_ID; $Master_Sheet_ID]

            Commit Records/Request [Skip data entry validation; No dialog]

            Perform Script ["Close Pop Up']

            Adjust Window [Maximize]

            Halt Script

             

            Have I missed anything?

            Im new at Filemaker and still learning.

            Thanx for your help.

             

            Roxy

            • 3. Re: Help - Inserting data from one table into a portal in another table
              philmodjunk

              What is your current layout at the beginning of the first script?

               

              What relationship have you defined linking Shipping and MasterSheet?

               

              Is it Shipping:: OrderNo = MasterSheet:: OrderNo?

               

              These two steps:

              Set Variable [$$Master_Sheet_ID; Value:Shipping::Shipping_ID

              Set Variable[$Order_No; Value:Master_Sheet:: Order_No

               

              Tell me that one of the two is referring to a related table and your relationship will control the value set to one of these variable.

               

              I'd also remove these two steps:

              Set Error Capture [On]

              Allow User Abort [Off]

               

              Once you have your script working, you can put Set Error capture [on] back but put it just before Perform Find and use Set Error Capture [off] to restore this immediately after.

               

              Since Allow User Abort [Off] can trap you in a situation where you have to force quit filemaker instead of pressing Esc or command period to halt a run away script, I only use it where it is absolutely necessary.

              • 4. Re: Help - Inserting data from one table into a portal in another table
                Shoreline

                Hi,

                 

                Yes I have Shipping:: OrderNo = MasterSheet:: OrderNo & Shipping::Relatedtable_ID = Mastersheet::Mastersheet_ID

                Is this wrong?

                 

                Ive made changes to user abort and so on.

                Im getting a message:

                There are no valid criteria in request. Type a valid request before clicking Find.

                 

                This is before it goes to the insert list.

                 

                Roxy

                • 5. Re: Help - Inserting data from one table into a portal in another table
                  Shoreline

                  I've just realised that It creates a new shipment booking everytime I try to insert an order.

                  • 6. Re: Help - Inserting data from one table into a portal in another table
                    philmodjunk

                    "Im getting a message:  There are no valid criteria in request. Type a valid request before clicking Find"

                    That's exactly why I suggested taking this step out. That tells us that $Order_No is empty when this script step executes:

                     

                    Set Field [Shipping:Master_Sheet:: Order_No; $Order_No]

                     

                    You haven't answered one of my questions and it's key here: What layout is the current layout when the first script starts to execute?

                    • 7. Re: Help - Inserting data from one table into a portal in another table
                      Shoreline

                      Hi,

                       

                      Sorry about that. On my shipment booking page I have a tab that has a portal on it with and insert orders button that executes the first script. That brings up the insert list with all orders. I need the second scrpt to insert the orders into that portal.

                       

                      Roxy

                      • 8. Re: Help - Inserting data from one table into a portal in another table
                        philmodjunk

                        When you check Layout Setup... for the shipment booking page, what do you see in "show records from"; MasterSheet or Shipping?

                        • 9. Re: Help - Inserting data from one table into a portal in another table
                          Shoreline

                          Layout set up - Show records from  - Shipping

                          • 10. Re: Help - Inserting data from one table into a portal in another table
                            Shoreline

                            My email adress is ### if you wouldn't mind replying to there, then I can answer you quicker. Its difficult with me being 6hrs before USA time.

                             

                            Thanx Roxy!

                            • 11. Re: Help - Inserting data from one table into a portal in another table
                              philmodjunk

                              I don't think the email will help, they're still be a major time zone difference and I'd recommend editing your post to remove the email address.

                               

                              Here's the first area of concern:

                               

                              Set Variable [$$Master_Sheet_ID; Value:Shipping::Shipping_ID

                              Set Variable[$Order_No; Value:Master_Sheet:: Order_No

                               

                              The second line is referencing a related table (Master_Sheet). It will put the Order_No of the first such related record to match your current Shipping record in the $Order_No variable. If there are no matching records, $Order_No will be empty and that would appear to match the error message you report. If you have filemaker advanced, run this script with the debugger enabled and watch what happens as you reach this point.

                               

                              If you don't have advanced, Make this the very next step:

                              Show Custom Dialog [Quote ($Order_No)]

                               

                              I suspect that you'll see an empty pair of quotation marks.