4 Replies Latest reply on Sep 23, 2011 11:04 AM by RichardGrant

    Linking Databases

    RichardGrant

      Title

      Linking Databases

      Post

       We have several products and each has it on Database with 1 to 3 tables. When we create an order to sell we can have several products in the order and there could be several of the same products.

      What I am looking to do is generate one document that lists all the Seral Numbers of all the products on the order.

      I have create a new Database that has the information I need from the original databases by linking in the relationship by order number.

      PROBLEM:

      when tring to bring in records for multiple products of the same type I only have the 1 order number so the relationship only brings in the first record that mactch the order number     

        • 1. Re: Linking Databases
          philmodjunk

          we need to know more about how you've set up your basic database.

          Most order/invoice systems have this structure:

          Invoices---<LineItems>----Products

          Is that what you had before you added this additional table where you have one record for each item?

          I think you can set up what you want if you base your layout on the new table so that you can list every item and their serial number, but then use the relationships you've set up so that you can add fields from the other tables to provide the additional information about your order.

          • 2. Re: Linking Databases
            RichardGrant

            We have two systems in house  BV and FileMaker 11 server . BV for Accounting and Inventory and FileMaker for Production Records.

            The BV record has a Confirmation number and line items of product (model #) , quantity  ( 1 to multi quantity )

            There is a database for each product maintained in production they contain:

            Model # , Serial # , Board Revision , Customer PO# , Customer Name , Shipping Date as well as calibration information etc..

            These databases generate the Serial Numbers for each unit ( 1 to multi quantity ) 

            I have added a new field in the production databases that has our BV Confirmation number ( document that tells production what to build and ship )

            I need to generate a document in FileMaker that can list all the serial number created for each confirmation ( this could be from multiple records from the same database as well as from other databases )

            Normal confirmation could have 1 controller , 10 transmitters , 2 power supplies

              

             

             

            • 3. Re: Linking Databases
              philmodjunk

              OK, I get why you have separate tables for each item as that is what makes it simple to produce indepente sequences of serial numbers of each one, but that really complicates your reporting as you would need to somehow merge all this data into a single report. As I understand it, using your example above, the report would list:

              Transmitter, Type ABC
                 123
                 124
                 125
                 ...
                 133

              Power Supply, Type xyz
                 001
                 002

              To do this in filemaker, you need to pull all the ordered item records from their separate tables into a single table so they all can be listed like this.

              One option is to use Import Records in a script so that you search each table for records with a given confirmation number and import them into the report table from each respective table if any are found. This would take the least design work for your existing system, but can be combersome and pulling up such a report could be pretty slow.

              Another option is to use your seperate tables solely for generating your serial numbers, but then use a combined table for listing all items for production. This creates the same unified table, but on an item by item basis when each order is completed, eliminating the need for a scripted import to set up each such list of items for production.

              • 4. Re: Linking Databases
                RichardGrant

                 The first option looks to be the best solution for my existing databases.

                To clarify I will link in the existing product databases into my new database

                I will then write a script that will bring each record that has the confirmation number I am looking for - with the SN of that record.