13 Replies Latest reply on Mar 28, 2010 9:15 PM by JeffHughe

    Combining fields from 4 different files

    DaleFoster

      Title

      Combining fields from 4 different files

      Post

      I am using FileMakerPro 10.0v3 for MAC. I have created 4 different fp7 files, each containing unique fields with data about a unique account # (field 1 in each file). So, if I find records in one file, say field 2 is > 100,000, I need to get all data from the other three files that match the account #, field 1. 

       

      I am a newbie to databases and would appreciate any help you can be.

        • 1. Re: Combining fields from 4 different files
          ninja

          Dale Foster wrote:

           So, if ...field2 is > 100,000, I need to get all data from the other three files that match the account #, field 1. 


          Howdy Dale,

           

          What will you then do with said data?  What you'll then want to do with it could bear on the best way to go get it.

           

          Any way you look at it, you'll want to set up file references to your other databases(File>Manage>File References) and relate them on your relationship graph (File>Manage>Database>relationships tab).  What you do next depends on what you're trying to accomplish.


          • 2. Re: Combining fields from 4 different files
            MikeyG79

            First I'd combine all the separate files into 1 with multiple databases. Then create some relations between them.

            • 3. Re: Combining fields from 4 different files
              ninja

              That may be worth looking at.

               

              Combining the 4 files into One database with multiple tables may indeed be a good choice, but it is not required in order to form the relationships.

               

              Note: no word games intended, just wording it for clarity for new folks.

              • 4. Re: Combining fields from 4 different files
                DaleFoster

                What I am wanting to do is to take multiple fields from each file and create one consolidated data base.

                • 5. Re: Combining fields from 4 different files
                  DaleFoster

                  Thanks.

                   

                  As I said, I am really a newbie. That is exactly what I want to do, but I couldn't figure out exactly how.

                  • 6. Re: Combining fields from 4 different files
                    ninja

                    Dale Foster wrote:

                     

                    As I said, I am really a newbie. That is exactly what I want to do, but I couldn't figure out exactly how.


                    Howdy Dale,

                     

                    Sometimes the hardest part about answering a question is understanding what the question is...

                    What is exactly what you're trying to do?

                     

                    Combine four databases into one database with four tables, or link to four records if and only if one of them meets certain criteria as your original post describes?

                     

                    If you describe what you're trying to accomplish with the real world example of why, it will be much easier to get you there.

                     

                    Thanks!

                    • 7. Re: Combining fields from 4 different files
                      DaleFoster

                      There are unique fields in each of the four files (1 table each) that I need to strip out and consolidate into one file (table). Each of the 4 files has one field, "account number", that allows you to associate each record from each file via the account number. 

                      • 8. Re: Combining fields from 4 different files
                        JoshO.

                        If all you need to do is move the records...you should be able to import the table.  Although, you may need FM Advanced for that.

                        • 9. Re: Combining fields from 4 different files
                          ninja

                          Got it Dale, Thanks.

                           

                          I would first link one dbase {Dbase1} to the other three using File References and relationships via "account number"

                           

                          Then make the additional fields in your table on Dbase1 to get the data from the other three files.

                           

                          Then run a looping script in the basic form of:

                           

                          Show all records

                          GoToRecord [First]

                          Loop

                          SetField [ Dbase1::firstfield ; Dbase2::firstfield]

                          Setfield [ Dbase1::secondfield ; Dbase3::secondfield ]

                          etc...

                          GoToRecord [next;exit after last]

                          End Loop

                           

                          This of course assumes a manageable number of fields to copy over.

                           

                          jmormond's method would also work well, but would result in four tables, or one table with four copies of each account number.

                           

                          So the bottom line is: How many fields are we talking about in each of the four existing Dbases?

                           

                          No matter what, make appropriate backups before you start mass-migrating data.

                           

                           

                          • 10. Re: Combining fields from 4 different files
                            DaleFoster

                            Ninja-

                             

                            Each property in Dallas County, Texas has a unique Account #  that the Dallas Central Appraisal District creates and assigns it. The appraisal district allows people like me to download property information on every property in the county. The problem is, the data is only available in CSV flat files (tables) and is contained, not in one file, but in 4 separate files (obviously each file has the Account # as its first field). So, what I have done is to create 4 separate databases, each database representing one of the  Appraisal District tables. Now what I would like to do is to combine them in to one database, one table.  

                             

                            The idea is to create one table, with all property data about each Account # in one record.

                             

                            Hope this answers your question.

                            • 11. Re: Combining fields from 4 different files
                              JoshO.

                              Once you have all the fields created in one table...just do 4 imports (which you can script).

                               

                              Match the Field Names so the CSV file populates the correct fields it contains...BAM.  Done.  :smileywink:

                              • 12. Re: Combining fields from 4 different files
                                ninja

                                I would so have to go with Josh 100% on that one.

                                 

                                Being that its hard to hit a moving target....multiple databases with one table each, conditional views in your OP, viewing data from other dbases, now .csv files...

                                 

                                If you create all of the fields you need in a single table in a single database and import #1 with its fields, then update matching records on import for #2, #3, #4 you should get this done in an hour or so.

                                 

                                If the target moves again, it may take a little longer...

                                 

                                It certainly can be scripted, just be careful when the time comes for adding fields or changes in .csv data order...FMP can be dangerous for scripted imports based on what I've read on this forum.  I don't have the need to script imports myself (yet) so I haven't gotten bit by that particular gremlin.

                                • 13. Re: Combining fields from 4 different files
                                  JeffHughe

                                  It's called a view, Foster.