9 Replies Latest reply on Feb 18, 2010 12:58 PM by AndyCohen

    Copy data between tables

    BigRickNYC

      Title

      Copy data between tables

      Post

      Hello All,

       

      I am new to FileMaker so I'm still trying to feel my way around. I'm familiar with databases in general and have a long history with MS Access.

       

      I am converting an Access database into FileMaker and have figured out the Import function as best I can. I have a Contacts table that I have imported into the Contact Management database template that comes with Filemaker. I am trying to separate my Notes field and move it to the Notes table. Currently, there are 60k records in the Contact Management table and 60k blank records in the notes table. The ID fields were not populated during the import and FM does not give me the option to import directly into Notes.

       

      How can move my notes data from Contact Management table to the Notes table?

       

      I am using FM Pro Advanced 10.0v3.

       

      Thank you in advance, any help will be appreciated.

       

      Regards,

      Rick 

        • 1. Re: Copy data between tables
          comment_1
            

          BigRickNYC wrote:

           

          I am trying to separate my Notes field and move it to the Notes table.


          How many notes per contact - and how can they be separated?


          • 2. Re: Copy data between tables
            BigRickNYC
              

            Currently, only one note per record. Most records do not have any notes. After the process, I will be able to create multiple notes for each Contact record.

             

            I was able to copy a single note and ID fields for one record. I am now editing the following script to repeat for the entire table. This script will only be used once.

             

            Go to Record/Request/Page[ First ]

            Loop

            Set Variable [ $ID_Contact; Value:Contact Management::k_ID_Contact ]

            Set Variable [ $Notes_Contact; Value:Contact Management::Notes ]

             

            If [ IsEmpty ( $Notes_Contact ) ]  <---------is it possible to use something like If NotEmpty do these steps Else go to the next record/repeate the loop?

            End If

             

            Freeze Window Go to Layout [ “Notes_Utility” (Notes) ]

            Set Field [ Notes::kf_ID_Contact; $ID_Contact ]

            Set Field [ Notes::Text; $Notes_Contact ]

            Commit Records/Requests[ Skip data entry validation; No dialog ]

            Go to Layout [ original layout ]

            Go to Record/Request/Page[ Next; Exit after last ]

            End Loop 

             

             

             I'm working on the above script - it is presently not functional.

             

            Thanks for you help. 

            • 3. Re: Copy data between tables
              comment_1
                

              Why don't you simply:

               

              1. Delete all records in the Notes table;

               

              2. Go to Contacts layout and find contacts with notes;

               

              3. Go to the Notes table and import records from the Contacts table (mapping ContactID to ContactID and Notes to Note).

              • 4. Re: Copy data between tables
                BigRickNYC
                  

                Yes, I thought that would be a simple work around also. I think that due to relationships in the database the following results occur:

                1. When I delete the blank records in the exsiting Notes table, it also deletes all of the records in the Contacts Table

                2. I am not able to import directly into Notes as it appears in a Related Tables section of the Import dialog box.

                 

                I am using the Contacts Management template database in FM 10 if that gives you any insight. Also, the Notes field in Contacts is a custom field that I created.

                 

                 

                • 5. Re: Copy data between tables
                  comment_1
                    

                  BigRickNYC wrote:
                  When I delete the blank records in the exsiting Notes table, it also deletes all of the records in the Contacts Table

                  You can disable that in the relationship's definition - at least for the duration of the deletion.

                  Or replace the foreign key to Contacts with nothing, so that relationship is broken - then you can delete them without affecting Contacts.

                   

                   


                  BigRickNYC wrote:

                   

                  I am not able to import directly into Notes


                  I don't see why not: start by going to a layout of the Notes table and select File > Import Records. The Notes table will be automatically selected as the target table. Select the same file as the source, and Contacts as the source table.

                   


                  • 6. Re: Copy data between tables
                    BigRickNYC
                      

                    I deleted the relationship and deleted the records in the Notes table. FM also deleted all of the Contacts.

                     

                    I can only import into Contacts Management - the Notes table is grayed out. 

                    • 7. Re: Copy data between tables
                      BigRickNYC
                        

                      i ended up creating a new Contacts Management table from scratch and running the import processes again for Contacts, Notes, and two other tables. I don't understand what the problem was but I spent hours of trying to find a work around.

                       

                      My initial impressions of FM are not all that positive. This process would have taken a short amount of time using Update Queries in Access, in addition, I could have imported all of my tables into the database in one process. I'm trying to understand if FM is designed with an end user in mind, vs an advanced user or programmer, and is limiting typical components that you would find in a relational database application such as MS Access/SQL, MySQL, etc... The way that FM handles CheckBoxes is foreign. If a field is a boolean datatype i.e. yes/no, why would a check box allow for more than two values - this violates all rules for Data Normalization allowing more than one value to be stored in a field. I can see the simplicity in giving the user a Checkbox object that can be placed on a "Layout - is it a form or is it a report?" connect it to the boolean field and the task is complete. Instead the checkbox series is placed on a layout and then properties are set including creating a Boolean List of 1. The '1' is then hidden by dragging the size of the object width to hide it instead of actually being useful as a label or having the option of deleting it all together.

                       

                      I know no one likes to hear negative feedback but may these suggestions will be useful in subsequent versions of the software. Converting this custom database is the biggest reason that I delayed in moving to a Mac. FM does not seem like a try relational database but instead a hybrid of sorts.

                       

                      Thank you again for you time and efforts.

                       

                      Rick 

                      • 8. Re: Copy data between tables
                        mrvodka
                          

                        BigRickNYC wrote:

                        If a field is a boolean datatype i.e. yes/no, why would a check box allow for more than two values



                         

                        There is no boolean type field. FileMaker has very loose data types and this can be both beneficial and cumbersome to deal with.

                         

                        FileMaker is different and coming from other RDBMs will have a learning curve. However once you understand FileMaker in depth, you will find that it has its strengths.

                         

                         



                        • 9. Re: Copy data between tables
                          AndyCohen
                            

                          Have you looked into the Import capabilities in FMP yet?  I would use Import to do what BigRickNYC described above

                           

                          I too had problems with FMP.  I also came from an MIS/dB, SQL and an Access background. 

                          Yes... there's a learning curve even for those (like me) who should already know it all!

                          Yes... there's an interesting question about complying with the Normal Forms, but not with regard to the mulitple values capability with checkboxes.  Unless of course you make the mistake of using Checkboxes as a key and not control the number of values... and there's the rub...

                           

                          Unlike all dB Mgt systems I've ever learned, FMP will go ahead and let you violate the Normal Forms.  Now if you know better and understand how to get relational systems to work correctly then you can easily ensure your application meets the criteria.

                          Another MIS-world criticism is FMP's single file approach which tightly couples the data and the schema to the application.  There too, if you understand and need data independence then FMP easily allows you to develop in a manner which completely separates the data from the functionality as in Transactional and MIS systems...  Bottomline is if you understand these concepts FMP allows you to use them, if you do not then....

                          you build what I usually refer to as a plate of spaghetti... (or a "build-to-spec"... ;-)). This is what most end-users who develop their simple apps in FMP usually come up with.  Which is not necessarily bad because they usually work fine!  Since version 7 of FMP this 5th gen toolkit has become so complex and Pro-centric that FMI had to develop Bento to make it easier for end-users to build their own.

                           

                          After building more than a dozen apps with FMP I do not see these criticisms as disadvantages... at least to me... 

                          FMP offers alot more interactive functionality which in Access requires quite a bit of programming in Visual Studio using VB and lower level DDL calls. With FMP I can build an interactive and visually appealing application which an equivalent in Access would take months more in development time and QA.  and then there's the cross platform capability which, well... you simply cannot do in Access.

                          Also, once you fully GROK how FMP uses context(TOs and TOGs), then you can accelerate your development to warp speed and the real value of FMP becomes hard to beat.

                          Hope you stick with it BigRICK!