7 Replies Latest reply on Dec 4, 2012 11:52 AM by philmodjunk

    Combining Fields in new Table

    MLind

      Title

      Combining Fields in new Table

      Post

           I have a master table of data which includes two separate fields that have data for the same product and want to know if there is a way to populate another table where it combines the two fields into a single list.  I know ... confusing. 

           To explain, I am creating paperwork for an electrical system where Component A can have two light bulbs connected (Bulb 1 and Bulb 2).  The Master Table is the list of Component A's (model, type...) which has Field columns for Bulb 1 (wattage, color) and Bulb 2 (wattage, color).  So, Master Table Fields are:

              _id_COMPA

              COMPA MODEL

              BULB 1 WATTAGE

              BULB 1 COLOR

              BULB 2 WATTAGE

              BULB 2 COLOR

           I want to then take the Bulb data for both BULB 1 and BULB 2 and combine it into a separate (or report) showing a single list of a BULB WATTAGE and BULB COLOR so I can create a puchase list of all of the light bulbs.   Please see attached sample tables.

           I hope that make enough sense.  Is there anyway in FMP to do that?

      SAMPLE_TABLES.jpg

        • 1. Re: Combining Fields in new Table
          philmodjunk

               Sounds like you are inputting data into table 1 that should never be entered into that table, but should be entered into table 2 from the beginning.

               Components-------<Bulbs

               Components::__pkComponentID = Bulbs::_fkComponentID

               See this thread for an explanation of my notation: Common Forum Relationship and Field Notations Explained

               With this setup, you can place a portal to Bulbs on your Components layout and use it to list any number of bulbs for a given component and you can then use a second layout, based on Bulbs to produce the report that you want.

               Ps. You might need yet another table between bulbs and components in this relationships if there is a lot of additional info that you need to record about each bulb type used.

          • 2. Re: Combining Fields in new Table
            MLind

                 Thank you for this feedback.  I agree that initially, it appears that it would have been better to have started with all of the info in Table 2, but the reality is that the specifics of each "Bulb" is being dictated by the "Component" that it is connected.  All Bulb numbering, types, sizes, ... are generated as a result of the component.  I know it's very confusing to describe. 

                 The quick way to make this work is to create 2 new layouts (1 for each component socket) and then just have them lookup the info from Table 1.  I was just hoping to have a single, consolidated table with all of the Bulb info. 

            • 3. Re: Combining Fields in new Table
              philmodjunk

                   What I am describing fits what you are saying. The specifics of each "bulb" are controlled by which component record is current. Record 1 can have 2 bulbs of type "A" and "B". Record 2 can be for a component needing 3 bulbs, 2 of type "B" and one of Type "C". A join table can be used if there is data specific to a particular matching of a bulb to a component And all of this data can be managed from a single layout.

              • 4. Re: Combining Fields in new Table
                MLind

                     Thank you very much for your help and I'll give it a try and let you know how it goes. 

                • 5. Re: Combining Fields in new Table
                  philmodjunk

                       BTW, this sounds like a BOM (Bill of Materials) such as is typical for documenting the assembly of products in a manufacturing plant.

                       Is it?

                  • 6. Re: Combining Fields in new Table
                    MLind

                         Basically, yes.  I am trying to extract/combine the data from the Master Table to create an equipment list for purhasing.  This all started as the Master Table was originally produced in Excel and then given to me to try and manipulate.  I would have preferred to start with FM.  Thanks.

                    • 7. Re: Combining Fields in new Table
                      philmodjunk

                           BOM, Purchase Orders and Invoices all have a very similar structure where you have a parent record that links to a detail record which in turn often links to a third table such as the table of products available in an invoicing or Purchase Order system. You may find reviewing the design of some of the starter solutions helpful in gleaning ideas that can apply to your database system.

                           I'd guess that this basic structure is the direction in which your design is heading:

                           Equipment----<component_equipment>--------Components

                           Equipment::__pkEquipmentID = Component_Equipment::_fkEquipmentID
                           Components::__pkComponentID = Component_Equipment::_fkComponentID

                           See this link if my notation is unfamiliar: Common Forum Relationship and Field Notations Explained

                           A component record in the Components table documents one specific type of component (such as a bulb) and is where you can record information such as the bulb's wattage, link in specifications documents, unit cost, etc.

                           A record in component_equipment identifies a specific component linked to a specific item of equipment. A quantity field in this table can record the number of components of this type that are needed.

                           component_equipment, BTW, is simply a BOM or LineItems table depending on whether you are purchasing this item or assembling it.