13 Replies Latest reply on Dec 18, 2016 2:15 AM by alangodfrey

    Strange Export

    TKnTexas

      I have a FMP file that I do vendor research from.  I do my research and then I have a script to export most of the fields to Excel. Then I clean up the format and email to the vendor.  Today I got two blank rows where two invoices should be.  I have never had that. 

       

      Thinking there is something in my script, I did the Export manually, with the same results.

        • 1. Re: Strange Export
          TSPigeon

          TKnTexas:

           

          Thank you for your post!

           

          It looks like Purchase Order N... "062816-02-MA" and "RIV0000000007541" records are missing from the export to Excel. It looks like it knew the records existed, but the data wasn't exported. I wonder if this could be data corruption?

           

          -What version of FileMaker Pro are you working with? (e.g. 13v9, 14.0.6, 15.0.1)

          -Are you able to import the data into a New FileMaker File?

               -Does the issue occur when exporting from that file?

          -You might try running Recover on the File.

               -For more information see What to do when your file is corrupt.

           

          I am also going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive even more views and potentially more advice!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Strange Export
            TKnTexas

            My apologies for not responding sooner.  Things are very hectic in my department.  I treated the issue as a one time event.  But it happened again.  Two records in the found set, and NO lines on the Excel spreadsheet.

             

            -What version of FileMaker Pro are you working with? (e.g. 13v9, 14.0.6, 15.0.1)

            14.0.6 running on Win10 Professional

            -Are you able to import the data into a New FileMaker File?

            I created a clone of the file.  Then with the two records still as the found set, I tried to import them.  ZERO records imported.

            I tried to import the Recovered file's records into the CLONE.  ZERO records imported. 

             

                 -Does the issue occur when exporting from that file?

            -You might try running Recover on the File.

            I ran Recover.  It did not report any issues, repaired or otherwise. 

            • 3. Re: Strange Export
              TSPigeon

              TKnTexas:

               

              Two records in the found set, and NO lines on the Excel spreadsheet.

              I created a clone of the file.  Then with the two records still as the found set, I tried to import them.  ZERO records imported.

              I tried to import the Recovered file's records into the CLONE.  ZERO records imported.

              You are saying you are unable to export data into an excel or import into a clone of the file at all?

               

              Is this a standalone file or is it hosted?

              Is the issue occurring on all machines or just this one?

              Would it be possible for us to receive a copy of the file with steps to reproduce? (Sending a message to your inbox)

               

              TSPigeon

              FileMaker, Inc.

              • 4. Re: Strange Export
                philmodjunk

                Was there any kind of error message telling you that the records could not be imported during this import into clone test? What did that error message say? (speculating that the corrupted data might encounter a validation check that blocked the import.)

                • 5. Re: Strange Export
                  TKnTexas

                  "Was there any kind of error message telling you that the records could not be imported during this import into clone test? What did that error message say? (speculating that the corrupted data might encounter a validation check that blocked the import.)"

                   

                  Using the Recovered copy of the file as the source, which did not report any errors, during recovery; importing to the clone:

                   

                   

                  There were 155212 records in the found set in the source.

                  • 6. Re: Strange Export
                    TKnTexas

                    File is standalone, one my C:Drive.  It is linked to other files as well.  Using the original file, I was able to import updated information.  But I am concerned. 

                     

                    My machine is the only one currently accessing the data.  We are a small office. 

                     

                     

                    I am trying to make a case for the WONDERFULNESS of FileMaker Pro, and it is; but not being able to be assured that an export of data was correct will mar things. 

                     

                    I do not think there is a problem sharing the file, except for its size maybe.  This is only invoice data, at the top level, no details of what we bought or for what price.  I am going to take the files home with me to see if it is reproduce-able on a Mac that I use.

                    • 7. Re: Strange Export
                      TKnTexas

                      Update on this problem.  

                       

                      I created a new file.  I did not clone.  BUT, I did copy fields from one file to the other.  So many fields and calculations.  The relationships were simple to re-create.  My original had been loaded with data back to 2012.    It is very time-consuming to do this on my accounting software.  I have to pull the data then it pushes it to Excel.  Then I can import to FileMaker. 

                       

                      Some of the data is not from an export but from a report that is printed to a text file.  Then scripts have to loop through the massive number of lines to arrange the data before it can be imported/merged to the other data. 

                       

                      So, I did only for transactions in 2016.  I performed my FIND on the data for a vendor research... the result was two lines.  I clicked the script to export to an Excel worksheet, and ONLY column headers were created, no data.  Same results as my original. 

                       

                      *Sigh*

                      • 8. Re: Strange Export
                        philmodjunk

                        If there is file corruption, copy/pasting objects from the suspect file might also copy over some of the corruption.

                         

                        But TSPigeon has speculated that you might have corrupted data rather than a corrupted file. You might just delete those two records and manually re-enter the data for those two invoices.

                        • 9. Re: Strange Export
                          TKnTexas

                          My concern is the data as a whole.  The table is populated from two companies' accounting data (Great Plains).  Our catalog sales are separated from the main company. 

                           

                          For each, I just said I wanted all transactions entered in 2016.  It now makes all data suspect. 

                           

                          I will go back to the original file, print out the DDR and rekey a new one.  Then I will pull the data a third time.

                           

                          This is a critical tool for me.  I receive vendor statements that I look up to see what has been processed.  One statement I did today had 600+ invoices or credit memos.  50 were not processed.  If I had to look up each manually, I would never get it done.

                          • 10. Re: Strange Export
                            philmodjunk

                            There are some tools out there for working with text files. It might be useful to export the data as a plain text file and use such a utility to look for any unusual characters. If you find that your source for this data sometimes includes a random character, you can then set up any one of a number of methods to "clean" the data and filter out those problem characters.

                             

                            You might also use the Char function and some other text functions to check the data in each field of those problem two records. you might get lucky and find a nonvisible character in one or more of the fields that's at fault.

                            • 11. Re: Strange Export
                              TKnTexas

                              I read the whole report into a field for however many line.  No delimitation or separation.  I format that one field with a font such as New Courier.

                               

                              Using PatternCount I find the "throw away" rows and mark as "DEL".  Then I mark the CHECK row as such with the lines of remittance marked, usually "Data1" and "Data2".

                               

                              I get rid of the "DEL" rows.  Then I loop through the balance, through $Variable I store info I found in "Data1" and then write it into the related "Data2". 

                               

                              This has worked well.  The "working" table where I do this is updated to the downloaded data in my main table.

                               

                              We hope to upgrade to Great Plains 2015 from the current Great Plains 2010.  When we do, I will try to convince the CTO to let me connect with ODBC and eliminate downloads reading the SQL directly.

                              • 12. Re: Strange Export
                                philmodjunk

                                If you are having to do that much "preprocessing" then the possibility that you have some form of invisible "garbage" in your data seems pretty high. Messy data produces messy results.

                                 

                                In such cases the filter function can be invaluable since you do not have to specify what characters to remove, only what characters to keep. This can filter out characters whose presence you never imagined could be in the data to begin with.

                                 

                                That will leave legitimate non-printing characters that create problems due to being in the wrong place such as an extra tab or return that can still create issues for you. You can count the number of returns in your text with the ValueCount function and so that can tell you that there's an extra return that you didn't account for.

                                • 13. Re: Strange Export
                                  alangodfrey

                                  ..and when you get a blank row in a list like that never discount the idea that one of the fields in the previous row contained an unexpected 'Return'.