14 Replies Latest reply on Nov 12, 2010 2:28 PM by philmodjunk

    Combining 4 fields from one table into another single field of another table.

    BenMonroe

      Title

      Combining 4 fields from one table into another single field of another table.

      Post

      I want you to know up front that I am not a programmer or any other expert with databases. I have been trying to do this in Access for months and was able to do it once using 8 fields into one, but I don’t know how I got it to work. Once I learned I could do it I wanted to use the procedure for a different (Item Number) field so I started over with my database. I thought I had the correct SQL statement figured out, but it would not work when I tried it again.  File Maker was recommended to me as a better database program, so I downloaded the trial version to see if I can get it to work.

      The reason I want to do it this way is that I have several manufacturers with different part numbers that make the same part with the same description. By normalizing like this I have saved a lot of space and data entry. I had all this information on one spreadsheet when I started years ago and have imported it into a database.

       I have created a new database with 4 lookup fields that look up several dozen manufacturers, hundreds of part numbers, and 2 other fields that help describe an item (number). The 8 field table will be used to provide a (item) description once I get the Item (number) table working. Both of these will be used in another table that helps me keep track of other information about these items. I am trying to have drop down windows that allow me to choose one of these items (number) and one of the (item) descriptions from a list. The final table will be used to calculate the price and maintain information about when and where I purchased the item. Plus it should cross reference each of the items with other manufacturers and their part numbers.

        • 1. Re: Combining 4 fields from one table into another single field of another table.
          LaRetta_1

          Although I have worked with complex inventories, I still have no idea what you are actually asking but I think you want (from your subject) to concatenate.  You would create a calculation (result is text) something like this:

          Field1 & " " & Field2 & " " & Field3

          Replace Field# with your actual field names.

          However, it seems that you are suggesting that you are using the description as part of the unique PartID and that is concerning.  All it would take is one manufacturer to decide to change the wording of a part description (maybe their legal department decided the wording could be misinterpreted) and, if the description is an intregal part of your relationships, you will be in trouble. 

          You are (properly) going to a lot of trouble to split your information into individual fields.  This information can be DISPLAYED together using merge fields on your layout so unless this calculation is required for exporting, I do not see how it is helpful except for display as the second field in a value list (the first field being a unique ID). 

          I believe it would be wise at this juncture to hire a trusted Developer to review your structure and plans.  Only with some serious review and consideration by an experienced Developer can you structure this properly and that is not something which can be handled through a few paragraphs on a post.  This base structure is the most important piece.  If you get it right, the rest of your design will be easy; get it wrong and you will waste (hundreds of) hours and STILL have to undo some of your work to restructure it. 

          And no, I'm not saying this because I want the work ... I have too much on my plate now. I suggest it because I've designed over 20 such inventory/parts solutions (with multiple manufacturers and multiple price breaks and configurations) and I know what you are facing.

          • 2. Re: Combining 4 fields from one table into another single field of another table.
            BenMonroe

            I’m sorry you don’t understand what I am saying. And I don’t understand why someone hasn’t considered normalizing like this before. I thought that was one of the main functions of having a database. I have to guess that they just haven’t written the programs to work that way.

            I have tried what you are suggesting here. I get an error message saying I can’t do that.  Your suggestion is similar to what I used in the Access database I created, but I used the “Select” statement in SQL. I have tried so many things it is hard to remember. 

            I have looked at many Inventory databases over the years and none of them offer all the options I hope to get from what I am wanting.  

            • 3. Re: Combining 4 fields from one table into another single field of another table.
              LaRetta_1

              I doubt that the message you received when attempting to concatenate was "you can't do that." Wink

              Can you try it again and copy the calculation and paste it here and give me the specific message?  I assure you that concatenation works.  Aside from that, I still suspect you are approaching the issue from over-complicated perspective.

              "And I don’t understand why someone hasn’t considered normalizing like this before. I thought that was one of the main functions of having a database. I have to guess that they just haven’t written the programs to work that way."

              Now you've lost me.  Normalizing in WHAT way?  Have you studied relational theory and do you really know what normalizing means?  The main function of a database is to allow data to be broken in to its finite pieces.  It is the programmers' job to design the database so information is then available in flexible ways when overlaid with UI. 

              I don't know who you mean when you say "they just haven't written the programs to work that way."  Might this suggest something?  If you approach databases like spreadsheets, you will find they are quite different. You do not need many of the tricks you needed when using spreadsheets but you DO need to understand how to properly structure and link your tables and this is why I suggested getting assistance with the base structure.

              • 4. Re: Combining 4 fields from one table into another single field of another table.
                BenMonroe

                I mentioned up front that I was not an experienced database person. I am used to using Access and its Wizards to create what I want.  Once it created the SQL statements, I modified them to do what I wanted. I have done a lot of reading about databases and I feel I have a good understanding about normalization.  I will try to explain what I have done so you don’t keep thinking I’m making this more complex than what it has to be. At my end, I’m thinking it will make it much easier to edit my 5500 records once it’s finished.

                I started with a database (spreadsheet like) with about 3200 records. I used 16 fields in each record just to allow me to list all the manufacturers that make that one item. To correct this, I edited each one of these records so that there was only one manufacturer per record. That increased the number of records from 3200 to about 5500. As I did this, I imported the information into individual tables that allowed me to make drop down lists to chose from rather than having the manufacturer (as an example) typed out 5500 times. I have 250 manufacturers listed in the manufacturer table and the manufacturer part number table has 4300 unique part numbers. I created my main table with 8 look up fields which gave me all the information I originally started with.  As I started to edit these records in the new table, I realized that much of the description field was duplicated in other records because over 2000 were copied from the original records.

                That is when I started to work on creating tables for 8 ways to describe these records in a new description table.  All of that worked as it should. I was able to create a description table using these 8 other tables. I was able to delete hundreds of duplicated bits of information. I stopped editing the records to make sure I could continue with the main table.

                The problem came up with this new table as I tried to have it look up from the Description Table. I wish I would have copied (saved) the statement that actually worked for me. This one does not work for me.

                I modified the SQL statement that Access created from their Wizard and it was similar to this….

                SELECT [Description Table] [Description Table].[Manufacturer], & "  " &  [Description Table].[Part Number],  & "  " & [Description Table].[Size], & "  " & [Description Table].[Color], & "  " & [Description Table].[Type], & "  " & [Description Table].[Style], & "  " & [Description Table].[Ampere], & "  " & [Description Table].[Voltage], & "  " &  [Description Table].[Description] FROM [Description Table] ORDER BY [Manufacturer], [Part Number];

                To continue…Once I found out I could do this with 8 fields, I decided to start over and do the same thing with the item number. I realized that for many of these records I have created my own generic way of looking up the record and for ease I wanted a 4 part item number. Most would be 2 parts with the manufacturers name and their part number.  I was able to create all the tables to make this work also, but once I got to the point where I wanted to get the 4 field table into one field, it did not work.

                I’m not going to respond to all of your comments because we seem to be having a problem with what I say and what you think I am saying. If you do not have the patience to work with me on this then just say so and I’ll let it go.

                The one about programming means that they have not written either of these programs to allow me to make a drop down list from another table that shows me all four (or whatever) fields of the other table. Access allows me to choose up to 4 fields in their Wizard, but it will not show all four when you click on the record you want.  I had to modify it to work for me. File Maker only allows one field to drop down.  In each of these programs I can get the first field to drop down and I can choose the Manufacturer (in this case) I want to use but the other fields do display in either program. Thus I tried the calculation option. The formula I used is,

                 Item = Item::Manufacturer & Item::Part_Number_1 & Item::Part_Number_2 & Item::Part_Number_3

                 The error message I got was “This field comes from an unrelated table. Only global fields can be referenced in unrelated tables.” 

                I realize now that this was telling me I had to create a relationship here. I am used to using Access and it does that for me. So I screwed up.  It has accepted the formula now.  But it still doesn’t work. I've had enough for now.

                • 5. Re: Combining 4 fields from one table into another single field of another table.

                  Can you post the SQL that you managed to get working on Access for the 8 fields into 1?

                  This might help to understand what you got working before and then a FileMaker translation could be looked at?

                  • 6. Re: Combining 4 fields from one table into another single field of another table.
                    BenMonroe

                    I can look though my files again to see if I can find it again, but I'm thinking I deleted everything when I started over with the database. I wanted to use the same names and didn't want to get confused with which one I was using.

                    • 7. Re: Combining 4 fields from one table into another single field of another table.
                      philmodjunk

                      Ben,

                      I don't think the SQL example you posted in your last post would work in Access or any other SQL based system of which I'm aware. It appears to confuse a select statement for listing field names to be returned from a query and calculation expressions intended to merge data from multiple fields in the underlying tables into a single "field" in the record set returned by the query.

                      In FileMaker, you have to use a different approach and how you do this depends on the results you want. Do you want to see all of this data in a single row on your layout or will this be a column of data in a drop down list? (FileMaker can display 2 columns of data, not 1, BTW Wink)

                      • 8. Re: Combining 4 fields from one table into another single field of another table.
                        BenMonroe

                        That statement is the one that is in my present database and it brings up a drop down menu showing all 4 fields with a check option for which one I want to choose.  But only the first field is displayed after I click on it. I don’t know how the check box option got there. 

                        I would like to start typing the “item” and have it automatically fill in the rest of the item for me from a drop down list when I get to the one I want to choose.

                        Let’s use a person’s name as an example. I would like to type in their first name (all the people with that first name would come to the top of the list) then as I typed in the middle name only those with the same middle name would come up. As I continued typing the last name, only those people with the same last name would remain on the list. And the last thing could be anything else used to describe them.

                        • 9. Re: Combining 4 fields from one table into another single field of another table.
                          BenMonroe

                          I'm sorry that one does not work. It only shows me the 8 blank fields and does not give me an option to chose.

                          The statement that gives me the choice with a check is

                          SELECT [Item Query].[Manufacturer], [Item Query].[Part_Number_1], [Item Query].[Part_Number_2], [Item Query].[Part_Number_3] FROM [Item Query] ORDER BY [Manufacturer], [Part_Number_1], [Part_Number_2], [Part_Number_3];

                          • 10. Re: Combining 4 fields from one table into another single field of another table.
                            philmodjunk

                            I thought so. Wink

                            In Filemaker, you can define value lists that "auto-complete" and this can be used to set up what you want as far as typing in values and seeing the list shrink to shorter and shorter lists of selected values. You actually have two different issues here. One is to set up a value list that combines your multiple fields into 1 or two columns of values in your value list, the second is to get it to work with auto-complete--which can be a challenge in filemaker.

                            First to get multiple columns of data into a value list.

                            If you have a table named Items with four fields, Field 1, is the primary key you need to select in your value list and Fields 2, 3 and 4 are text fields that describe the data in ways we humans can understand. Then you can set up a 2 column value list like this:

                            1. Define a calculation field in the Items table with this expression: Field 2 & " " & Field 3 & " " & Field 4. Make sure that this is a stored indexed field that returns text.
                            2. In Manage Value lists, create a new value list and use the "Use values from field" option. You'll find you can specify Field 1 as the column 1 value and your new calculation field as the field 2 value.
                            3. If you assign this value list to a field as a drop down list, you can pull down the list and select the desired value and a value from Field 1 will be entered when you select a value.

                             

                            See if you can get that much working first, then we'll take a look at the auto-complete issue as well as some approaches that may work better for you in FileMaker.

                            • 11. Re: Combining 4 fields from one table into another single field of another table.
                              BenMonroe

                              Ok…I hope I am getting this. I added a primary key and calculation field to the Item table. (I was using the manufacturer as my primary key) As I choose a record from each of the 4 fields, it appears in the calculation field with a space between them.

                              From this I edited my Item Value List to use these two fields. (I tried several options with these two fields when I could not get any of them to work) The Item field in my main table allows me to view and choose any of these items, but when I click on my choice, it disappears.

                              • 12. Re: Combining 4 fields from one table into another single field of another table.
                                philmodjunk

                                Download this demo file and see 2 ways to get the basic value list working. There's no type ahead or auto-complete enabled for these value lists, but they do show how to get multiple columns of info into a 2 column value list.

                                http://www.4shared.com/file/uxb0Pzhu/MultiColumnValueList.html

                                • 13. Re: Combining 4 fields from one table into another single field of another table.
                                  BenMonroe

                                  The database example you wanted me to look at is set up so I cannot make changes, so I can’t look at the way it uses the settings. It may be that I just don’t know how to get around it.

                                  I found out why my tables were not working. I still had the relationships set up that confused the program, so I deleted them and it is now working. After I got that part to work, I was able to export parts of my Main database to an Excel spreadsheet so I can import them into my Quickbooks program. I’ll be able to use the coma separated format to do that too, but I use the information in my Excel estimating spreadsheet too.

                                  Now that I have got this working the way I want, would you tell me if I am doing something wrong before I continue.

                                  I did not use the Primary Key Field. I am using the Manufacturer Field, Part_Number_1 field, Part_Number_2 field, & Part_Number_3 field in a formula in my Calculated Field. So I am getting all four fields to list in the Calculated Field. I created a Value List that uses this Calculated Field to enter information into the Item Field in my Main Table. When I tried to use anything but the Calculated Field in my Value List, I would not get all four bits of information that I wanted.

                                  From here I want to go back to my Description Table and do the same thing with my description. That is chose 8 different ways of describing a record from drop down menus. Then this would be calculated into my Main Table in the Description Field.

                                  Thank you so much for working with me on this project. You would not believe the number of people I’ve talked to about doing project. I think most of them are using Access at the UW. File Maker was suggested to me by a employee that takes care of the computers in the Art Depatment.

                                  • 14. Re: Combining 4 fields from one table into another single field of another table.
                                    philmodjunk

                                    I'm not sure why you think the demo file is set up so you can't make any changes. The file is not locked in any way. You can enter layout mode to see how a layout is designed and use Manage | Database to see how fields and relationships are defined. Manage | Valuelists will show you how the value list was set up. Feel free to describe exactly what you did that led you to believe you can't make any changes.

                                    BTW, I've done a lot of development in Access also, so don't hesitate to describe things in VBA or SQL if that helps.

                                    There's a reason for using a primary key (a field that uniquely identifies each record in the table), as the column 1 field. By selecting and entering this one value, you have identified one specific record in the table and a relationship can either display data from the related record or auto-enter options can copy data from that related table.