10 Replies Latest reply on Dec 4, 2008 6:38 PM by DrDave

    Append Problems

    DrDave

      Title

      Append Problems

      Post

      I have a simple script that deletes all records (in a data filed called AllRecords), then appends all records from four different external data files. In all, there should be 2,269 records.

       

      The problem is that I don't always end up with the expected number of records. The number that I'm short is not consistent.

       

      The only time that I can get consistent results is if I close FM, then open AllRecords and run the script then.

       

      I have tried things like flushing the cache before running the script, but that does not work.

       

      Any ideas/suggestions are appreciated. 

        • 1. Re: Append Problems
          davidhead
             No-one can really help you without seeing the script and/or the files. Without either we would be just guessing.
          • 2. Re: Append Problems
            DrDave
              

            Delete all records[]

             

            Import Records [NoDialogue;  "DataFile1.fp7" ; Add ; Windows ANSI]

            Import Records [NoDialogue;  "DataFile2.fp7" ; Add ; Windows ANSI]

            Import Records [NoDialogue;  "DataFile3.fp7" ; Add ; Windows ANSI] 

            Import Records [NoDialogue;  "DataFile4.fp7" ; Add ; Windows ANSI] 

             

             

            That's it. Thank you for helping.... 

            • 3. Re: Append Problems
              TSGal

              DrDave:

               

              Thank you for your post.

               

              If you have a found set in either DataFile1.fp7, DataFile2.fp7, DataFile3.fp7, or DataFile4.fp7, then only the found set of records are imported.  For example, suppose you have 100 records in each of the four files, but you have a found set of records in the last file of 50 records, then you would end up with 350 records instead of the expected 400 records.  Does this make sense?

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Append Problems
                DrDave
                  

                TGal, thank you. That makes sense and I did think of that. While trying to get this to work, I would have all data files open and then use Cmd-J to ensure that all records were showing...still inconsistent results. That's why I added a flush cache commend at one point. 

                 

                Let me clarify though. If I have a data file open, have a found set, then close the data file, with the found set still in play, would I only get the found set if I imported, even though the data file was closed?

                 

                DrDave 

                • 5. Re: Append Problems
                  TSGal

                  DrDave:

                   

                  If you have a found set and the data file is closed, the found set is still active.

                   

                  As part of your script, you may want to open each file and find all records.  Have a script to open the files, and within each file, have a script (ShowRecords) to Show All Records.  Then, after you open each file, run the external script to Show All Records.

                   

                  Your script may look like:

                   

                  Delete All Records []

                   

                  Open File ["DataFile1.fp7" ]

                  Perform Script ["ShowRecords" from file: "DataFile1.fp7" ]

                  Open File ["DataFile2.fp7" ]

                  Perform Script ["ShowRecords" from file: "DataFile2.fp7" ]

                  Open File ["DataFile3.fp7" ]

                  Perform Script ["ShowRecords" from file: "DataFile3.fp7" ]

                  Open File ["DataFile4.fp7" ]

                  Perform Script ["ShowRecords" from file: "DataFile4.fp7" ]

                   

                  Import Records [No Dialog; "DataFile1.fp7"; Add; Windows ANSI] 

                  Import Records [No Dialog; "DataFile2.fp7"; Add; Windows ANSI] 

                  Import Records [No Dialog; "DataFile3.fp7"; Add; Windows ANSI] 

                  Import Records [No Dialog; "DataFile4.fp7"; Add; Windows ANSI]

                   

                  -------

                   

                  Let me know how this works out.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Append Problems
                    DrDave
                      

                    Glad to know that the found set stays with the database, even when it's closed.

                     

                    I implemented the code you suggested and why won't it work! ...just kidding. :-) It works great and the results are consistent.

                     

                    Live and learn...that's what success look like.

                     

                    Thank you and take care.

                     

                    DrDave 

                    • 7. Re: Append Problems
                      davidhead
                        

                      TSGal wrote:

                       

                      If you have a found set and the data file is closed, the found set is still active.

                       

                      As part of your script, you may want to open each file and find all records.  Have a script to open the files, and within each file, have a script (ShowRecords) to Show All Records.  Then, after you open each file, run the external script to Show All Records. 


                      Actually that is incorrect (my bold added). This behaviour changed some time ago. If a file is closed, there will be no found set. If you import from a closed file, the entire record set in any table will be imported.
                      So the converse is true - if you DID want to import only a found set, the file would need to be open.
                      Just experimenting with the set of records that FileMaker Pro sees to import, I have found the following:
                      1. if the file is closed (with a found set) the entire record set is seen
                      2. if the file is opened by FileMaker Pro as the result of a file reference (file name is in parentheses under Window > Show Window), the entire record set is seen
                      3. if the file is open with a found set in current layout in the current window, the found set is seen
                      4. if the file is open with a found set but not for the current layout table, the found set is seen
                      5. if two windows are open for a file, one with a found set, the other with all records, FileMaker Pro sees the record set of the frontmost window
                      6. if you hide the window with a found set, FileMaker Pro still sees it 
                       
                      So the conclusion here is that if the files are open, you need to force a show all records. If the files are closed, the entire record sets will be imported. So in your script, it is probably easiest to make sure that the files are closed before import. 

                       

                      • 8. Re: Append Problems
                        DrDave
                          

                        David,

                         

                        This message scares me -- it seems now that there someone out there, very similar to me -- someone with a GREAT sense of detail! :-)

                         

                        Thank you so much for fleshing this out. For those new to FM, your message provides lots of information...information beyond the initial append issue.

                         

                        Thank you and take care.

                         

                        David 

                        • 9. Re: Append Problems
                          davidhead
                             Must be something to do with the name? Glad to help.
                          • 10. Re: Append Problems
                            DrDave
                               Speaking of names David, I once worked with a Wilson Head in Toronto, Canada. Great Guy!