8 Replies Latest reply on Dec 30, 2011 7:49 PM by LaRetta

    Help with Merging records - FMP 11

    newleaf67

      This is going to be insultingly easy for someone, but it's 3:00 AM here and I am on a deadline...brain like mush.

      I have about 600 records from my cilent database. They represent about 300 total clients, but the data set I have in imported from Quickbooks via Excel and I have a unique record for each JOB we did for that client. I have fields designated for each project, and now I want to combine all records for a given client into one.

       

      What I have:

      Record 1: Client A, Project A

      Record 2: Client A, Project B

      Record 3: Client A, Project C

      Record 4: Client B, Project A

      Record 4: Client B: Project C

      Record 5: Client C: Project B

      Record 6: Client C: Project C

       

      and so on....

       

      What I want:

      Record 1: Client A, Project A, Project B, Project C

      Record 2: Client B, Project A, Project C

      Record 3: Client C, Project B, Project C

       

      Thank you in advance. I've been using FMP for 20 years but I keep getting stupider....duhhhh

        • 1. Re: Help with Merging records - FMP 11
          RubenVanDenBoogaard

          Hi,

           

          If you have a clear id for each client you could make a self joining relation ClientID -> ClientID

          put the relation in a portal and use a looping script to add the project data to the first client record.

          Mark the other records from that client  'delete'  so you can delete them afterwards.

           

          Go to the next record which is not marked 'delete' etc.

           

           

          If you don't have a clear id for the client you have to make a clientID, a good starting point is to

          compare email adresses, zip codes+ home numbers etc.  This needs to be done carefully otherwise

          you are going to have double client, or worse missing client where a project is put at an other client.

           

          Hope this helps

           

          Best regards,

           

          Ruben van den Boogaard

          Infomatics Software

          ruben@infomatics.nl

           

           

          created by newleaf67 in General - View the full discussion

          This is going to be insultingly easy for someone, but it's 3:00 AM here and I am on a deadline...brain like mush.

           

          I have about 600 records from my cilent database.  They represent about 300 total clients, but the data set I have in imported from Quickbooks via Excel and I have a unique record for each JOB we did for that client.  I have fields designated for each project, and now I want to combine all records for a given client into one.

           

           

          What I have:

           

          Record 1:  Client A, Project A

           

          Record 2: Client A, Project B

           

          Record 3: Client A, Project C

           

          Record 4: Client B, Project A

           

          Record 4: Client B:  Project C

           

          Record 5: Client C: Project B

           

          Record 6: Client C:  Project C

           

           

          and so on....

           

           

          What I want:

           

          Record 1:  Client A, Project A, Project B, Project C

           

          Record 2: Client B, Project A, Project C

           

          Record 3:  Client C, Project B, Project C

           

           

          Thank you in advance.  I've been using FMP for 20 years but I keep getting stupider....duhhhh

           

          Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

          Start a new discussion in General by email or at FileMaker Technical Network

          Manage your email preferences.

           

          FileMaker Developer Conference 2012 • Miami, Florida • July 16-18 • www.filemaker.com/devcon

           

          • 2. Re: Help with Merging records - FMP 11
            newleaf67

            Thank you Ruben.  I was afraid I might be getting in over my head...I'm one of those self-taught users who has been getting away with things the wrong way for years.  Is "clear ID" just another way of saying "unique"?  I do think I have that...i.e. no clients with the same names, etc.  As for the portal and the script...well, I've worked with those a bit, but pretty weak at it.  Any "simpler" if not easier way?  Even if it involved exporting back to Excel or something?  I guess I should be considered an "enthusiast" at best.  Thank you!

            • 3. Re: Help with Merging records - FMP 11
              SEG-IT-Support

              Hi Newleaf,

               

              Can you tell me what your tables structure is? By reading your first post, it's a bit hard to understand if you actually have only 1 table with all the informations concerning the client and the projects or if you have 2 separate tables.

               

              Then when you say Client A, Project A, Client B, Project A, is this the same project that concerns more than 1 client?

               

              Best regards

               

              Thomas

              • 4. Re: Help with Merging records - FMP 11
                RubenVanDenBoogaard

                By unique id I mean 1 id for each client. so if John Johnson is 2 times in the list, both have the same ID.

                 

                The trick is to find which records are in fact the same client, is J. Johnson the same as John Johnson etc.

                If the names are the same and they are always unique  (there are no 2 John Johnsons) you could use the

                name field as a unique id, but you probably have to make the key more advanced by adding the postal code

                or email adress or something.

                 

                If you want some help with this, send me the file, or Excel backchannel and I'll give it a shot.

                 

                Best regards,

                 

                Ruben van den Boogaard

                Infomatics Software

                ruben@infomatics.nl

                 

                 

                 

                created by newleaf67 in General - View the full discussion

                Thank you Ruben.  I was afraid I might be getting in over my head...I'm one of those self-taught users who has been getting away with things the wrong way for years.  Is "clear ID" just another way of saying "unique"?  I do think I have that...i.e. no clients with the same names, etc.  As for the portal and the script...well, I've worked with those a bit, but pretty weak at it.  Any "simpler" if not easier way?  Even if it involved exporting back to Excel or something?  I guess I should be considered an "enthusiast" at best.  Thank you!

                 

                Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                Start a new discussion in General by email or at FileMaker Technical Network

                Manage your email preferences.

                 

                FileMaker Developer Conference 2012 • Miami, Florida • July 16-18 • www.filemaker.com/devcon

                 

                • 5. Re: Help with Merging records - FMP 11

                  Hi Newleaf, I will walk you through it ...

                   

                  1)  Backup first.  Create a Clients table.   Create an 'OldClientID' field, select Options and, at the validation tab at the top, validate data 'always', uncheck 'allow user override' and then specify 'unique'.

                   

                  2) Go to a layout based upon the Projects data and show all records. 

                   

                  3) Return to your Clients layout and perform an import (File > Import File > and find this same file.  Specify Projects in the source pop-up and it should show Clients in the target.  Map your fields and below specify Add records only.  Since you have validation set to unique on OldClientID, only one of each ClientID will be imported into your clients table. 

                   

                  4) You now have one client each in a table.  Now go to your graph (File > Manage > Database > and Relationships tab and join the tables as:


                  Clients::OldClientID = Projects::OldClientID.

                   

                  5) Place a portal from Projects onto your Clients layout to view multiple Projects per Client.

                   

                  6) Back up again. And now to address your IDs ... primary keys should be meaningless.  You currently are using Names or other meaningful data and that is not dependable because people change their names and more than one person can have the same name.  Once you have imported your Clients and have established a relationship based upon these incorrect IDs, you will want to switch to proper relational keys. 

                   

                  • In Projects, create a new foreign key, number, called ClientID
                  • In Clients, create a new primary key, number, called ClientID.  Go to Options > Auto-enter tab and specify Serial Number, On Creation, start at 1, increment by 1.
                  • Go to a layout based upon Clients and show all records
                  • Place your cursor in the new ClientID field and select Records > Replace Field Contents by serial, start at 1, increment by 1.  Be sure to also check 'update auto-enter'.
                  • Now go to your Projects table and show all records.  Place your cursor in the new ClientID field.  Select Replace Field Contents by calculated result. In the calculation dialog, select your Client table and the newly created ClientID.  Say OK.  Then Replace.
                  • Now change your graph to matching on this new ClientID.  You can keep the old ID if you wish for reference.

                   

                  If you get stuck on any step, stop and ask.  We are all here to assist.

                   

                  LaRetta

                   

                  Edited by LaRetta.  I wanted to also mention that, since we have validation set to unique on the Clients OldClientID, only ONE of each Client will import.

                  • 6. Re: Help with Merging records - FMP 11
                    newleaf67

                    Thank you Thomas.  I need to take a few hours off and I'll be back to this.  Between what you, Ruben and Laretta have offered, I hope I can figure this out...thank you all.

                    • 7. Re: Help with Merging records - FMP 11
                      newleaf67

                      OK, I ran through that and hit a wall in the import.  I think my data might be backwards from what you are envisioning.  See attached screenshot showing 4 records for the same client.  When Quickbooks pooped out to Excel, I ended up with Project totals in columns, not rows, so I imported to FMP with Clients driving name of each Record, and allocated fields for each of the columns - in this case, "LC 2010", "PHC 2010", "TC 2010", "LC 2011", "PHC 2011", and "TC 2011"...I'd like, of course, to have one record that shows the totals from each category all on the same line.

                       

                      Sorry if I am dense...feelin' stupid and very grateful for your help.  Where's the virtual tip jar?

                      Screen shot 2011-12-30 at 1.11.41 PM.png

                      • 8. Re: Help with Merging records - FMP 11

                        Hi Newleaf,

                         

                        I put together a sample of how the migration might take place according to the data you presented.  As I explained, your unique IDs should be FM serials.  You can import your excel spreadsheet to this FileMaker file (or one similar), specifying target  <New Table> (scroll to the bottom of the upper right target pop-up).

                         

                        Then join this new table occurrence to the other gray migration table occurrences just like temp is attached.  Once attached, create the new fields (two global and two calculation) as I have them in temp.  Then change the Migrate script to use your new table instead of temp.

                         

                        This one script will create the clients if need be and split the columns into Project records into a join table I named Activities. This is because a Project will have multiple years and I assumed you wanted to keep those totals separate so you could total just certain years.  It also switches you to unique keys which is important.  The only thing I did not do for you was create the Project records because I was unsure about them.  You will find I created the three you showed me. 

                         

                        http://directlinesolutions.com/downloads/migrate.zip

                         

                        I suppose I could have used filtered portal or even written to variables but I am on holiday so straining my brain this week is simply not going to happen.   I hope this file is helpful.

                         

                        LaRetta