6 Replies Latest reply on Sep 11, 2014 1:52 PM by philmodjunk

    merging internal and external fields



      merging internal and external fields


      I have a simple DB for which I'd like to merge two fields.  One accesses an external file of names and over the years I'd choose the name from the drop down list.  The other field was filled in manually when the external file did not include a name I needed.  Is there a way to merge these two fields so that I can get rid of the external file but maintain the names in each record that was previously populating that field?

        • 1. Re: merging internal and external fields

          Sounds like you should be able to set up some kind of import records operation to pull this data into a table in your current file, but since you do not describe the design of any of the tables in either file, it is impossible to be sure nor to be able to go into detail. You'll need to describe both the external table and the table where you are adding names that don't appear in the first table.

          • 2. Re: merging internal and external fields

            Phil, I'm not as familiar with the terminology as I should but I'll try to describe better.  One field is a drop down that accesses a small external filemaker DB.  That external file only has about 15 names in it.  Somehow, the main DB was set up to access those names when I click on the drop down menu.  The drop down menu shows the names, I choose one, and that field displays the name I chose.  The other field is just a simple text field into which I can manually type a name when it is a new name that wasn't included in the external DB of names.  I hope this helps.  In a second layout, I both the field that accesses the external DB and the text field where I just enter names are displayed but in that layout, there is no drop down and I cannot access the external DB.  But that might be irrelevant.  My goal is to merge these two fields to eliminate the drop down.  Each record in the main DB has only one of these field populated because the name only comes from the external DB file or I enter it manually.  No overlap.

            • 3. Re: merging internal and external fields

              Phil, maybe the external DB would be referred to as a table?

              Actually, it's called a Value List.  I just looked it up.  While I was doing that, I found nothing in Google on how to merge fields.


              • 4. Re: merging internal and external fields

                First a history lesson. Back when this file was created, you could only define one table to each file. If you wanted a separate list of data stored for use in your database, you created a new FileMaker file, defined fields and layouts and entered your data. To link this data to the rest of your database, you linked that one table in that one file to your existing file in a relationship. And you could define a Value list to list the value from the table in that file. So you are using a Value list that displays values that are probably entered in separate records of that other file's table. I could be wrong, you'd have to open up that file and check.

                Starting with Filemaker 7, we gained the ability to set up multiple tables all in the same file. We no longer had to link individual files together to get a working database system, but FileMaker maintained that capability and it has its uses even today.

                So we need to look at exactly how your data is set up. Is this a "use values from field" value list? If so, the names are stored in that other file's single table. You can use Import Records with the New Table option specified for the target table to create a new table in your main file that contains the same list of names as the original. And you can modify the design of your database so that you can add new names to this table so that they will then appear in your value list the next time you use it.

                It's a pity that you are using FileMaker 8.5. I've released two database files that serve as tutorial files on value lists and more sophisticated data selection methods called "Adventures In FileMaking #1, and #2" While much of the information would be useful even in an older version like 8.5, you'd need at least FileMaker 12 to open these files. If you want, you could download the 30 day free trial copy of FileMaker 13 and use it to open these files.

                If you do, you can find the download links for these free to download files on my FaceBook page.

                But you are also welcome to continue this thread after checking the options specified for your value list and reporting back here what you have found.

                Caulkins Consulting, Home of Adventures In FileMaking

                • 5. Re: merging internal and external fields

                  Phil, yes, it's a shame but since I only use Filemaker for this one application, it doesn't make sense to spend $329 for the upgrade.

                  I don't need the list of names to choose from each time I create a record.  Back when I had a small list of clients, it made sense but now, I just type my client's name into the field that is NOT linked to the external file/value list.  So rather than importing that list and having an internal table, I really just want to have each record have a basic text field that contains the name from either of the current places I can add the name.  No record has both in it.  So on any record, field 1 will have a name and field 2 will be blank or the other way around.  It's just that field one has that drop down menu linked to the external value list.  Interestingly, the second layout in my file has a field that is not seemingly linked to that external value list but is just a mirror of what name I select if I use that field in layout 1.

                  From the screenshot, I suppose I'm using what you describe as "use values from field" but maybe 8.5 uses "display data from" synonymously.  Again, I don't care about the names in that external file/value field, I only want to have one name in each record that is a merge of the two fields that have names in them (but only one is populated for each record, as I've menioned)

                  I hope this helps.

                  It sounds like you've put a lot of effort into helping the FM community with your tutorials.  Nice.

                  • 6. Re: merging internal and external fields

                    "display data from" is not the same thing. "use values from field" is an option that appears when you either create or modify a value list. "display data from" shows you what table (occurrence) is the source of the field you are looking at. Your two fields may or may not be from the same record in the same table. It depends on how this was designed originally.

                    You need to open up your data base and look at what tables, fields are defined in it. Replace Field Contents can copy data from one field into another so it sounds like you can use that tool to copy some data over and then discard the field that is formatted with the drop down list, but it depends on how your fields, tables and layouts are set up. In current versions, this is called Manage | Database, but I think 8.5 named the menu option something different. You may have something like "define fields" or such in the File menu.

                    If you are not already doing so, I'd save a copy of the file and then try making these changes in the copy so that you can revert back to the original if things get screwed up beyond recovery.