4 Replies Latest reply on Jun 20, 2011 7:27 PM by williamho6307

    How to select a record from table B and fill it into field of table A.

    williamho6307

      Title

      How to select a record from table B and fill it into field of table A.

      Post

      Hi all FM experts,

                    I'm beginner for FM, I would like to ask something pertaining the  window pop up selection , for example, if I have 2 tables, the first table is inventory table and the second one is purchasing table,  let's say instead of keying in the inventoryID manually into the purchasing details form there,  may I know is there anyway to pop up a window with the inventory details and allow user to  select the inventory item that they going to purchase? 

      thanks in advance. 

      regards

      william

        • 1. Re: How to select a record from table B and fill it into field of table A.
          philmodjunk

          What you describe can be set up with either a pop up menu or drop down list. Typically, you'd use three tables for this:

          PurchaseOrders----<LineItems>-----Inventory

          PurchaseOrders::PurchaseOrderID = LineItems::PurchaseOrderID
          Inventory::InventoryID = LineItems::InventoryID

          You'd fill in your list of items for a purchase order on your purchase order layout by adding records in a portal to the LineItems table. You'd format the LineItems::PurchaseOrderID field with a value list that lists the ID's and Inventory descriptions from the Inventory table. When you select a value from the value lists, the PurchaseOrderID is entered into the field and other fields in LineItems can copy data from Inventory via a looked up value setting.

          Here's a simple demo file created by Comment. It's an invoicing system, but the tables, relationships and layout design are the same:  http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: How to select a record from table B and fill it into field of table A.
            williamho6307

            Hi PhilModJunk,

                       Thank you for your reply , I really appreciate it. I have a query here, if I have 1000 items in the list , is it possible I can create a pop out form/menu to allow user to search for the item and return the selected item to the original form.

            Thanks again!

            regards

            william

            • 3. Re: How to select a record from table B and fill it into field of table A.
              philmodjunk

              Yes, you can set up such a thing with the New Window script step. The trick is to make it work for your platform (windows systems running with Maximized windows have an issue you have to script around for this to work) and to make the new window "modal". A "modal" dialog is one that stays to the front and you have to dismiss it before you can interact with any other windows in your system. This can be done by diabling user aborts and trapping FileMaker in an infinite loop. Then set up all buttons and/or scripts on the window that close that window to also use Halt Script to halt the infinite loop when you finish and dismiss the floating window.

              My Known Bugs List database file uses such floating windows and their scripts include code for handling maximized windows in windows systems. It also uses a custom menu to enable the close button on the corner of the window to work. (Creating custom menus requires FileMaker advanced.) (Just be careful when testing your scripts as you can get stuck in a situation where you have to force quit the file due to the Allow User Abort step preventing you from using Escape (windows) or Command Period (Mac) to halt the script with the infinite loop.

              There are also ways to make such large value lists easier to manage. You can set up a conditional value list so that you select a product category and then the list of Product ID's filters to only list products from the selected category. There are also enhanced value selection techniques--some of which would work well in your floating window--that can allow you to enter all or part of an item's description and then you can select from just those items that match that partial text that you've entered.

              You can see some of those techniques in thei demo file: 

              http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

              • 4. Re: How to select a record from table B and fill it into field of table A.
                williamho6307

                Hi PhilModJunk,

                                    Thanks for the detail explanation, I managed to get the thing work now!!! thanks a lot.