7 Replies Latest reply on Jan 27, 2016 5:02 PM by alquimby

    Found Set is Wrong

    TKnTexas

      I have a solution that is giving me wrong answers.  This is the 3rd month I have used it.  It is only used once a month.  I aggregate transactions for the month, creating invoice data.  This is exported to our accounting software to bill.

       

      I am creating invoice 'headers' parent table, from the source of the child table.  I summarize the child records.  I have a layout that has the body and sub-summary.  I can review the data for accuracy.  Then I have a layout (created by duplicating the first) which is the sub-summary only.  I export this sub-summary to an Excel intermediary file, to import back to the parent-table.  Auto-enter fields added other data.  In a portal I can see the child records that made these up.  All worked well until this month.

       

      My BillPeriod field is 2016.01.  I do a find on that value that field.  My "annual" and "month" records have a January TranDate.  My "backup" records have a 12/31/15 TranDate.  My Export is done from the Sub-summary layout.  I can view these dates and the BillPeriod.  They are the correct records.

       

      When I view the Excel file, I have blank records (3-4) in a row.  I have TranDates of November.  This all worked last month.  The only change was to add an extra field to the export/import.  I have deleted the Excel field.  I have export via script and manually.  I get the same wrong data.  I am at a loss. 

        • 1. Re: Found Set is Wrong
          TKnTexas

          I am really struggling to get my January data out.  I have attached an image. The top screen is the Excel file received, showing November.  The bottom is the screen shot after (same as before export).  The CustID is the same first record, but it is clear the data is not the same. 

          DC_Filemaker.png

          • 2. Re: Found Set is Wrong
            siplus

            TKnTexas wrote:

             

            I export this sub-summary to an Excel intermediary file, to import back to the parent-table.  Auto-enter fields added other data.  In a portal I can see the child records that made these up.  All worked well until this month.

             

             

            Why do all this import / export, when you can stay in Filemaker all the time, create new records and set fields through ExecuteSQL statements (that do sums etc) ?

            • 3. Re: Found Set is Wrong
              TKnTexas

              That may be the case.  I have not yet mastered ExecuteSQL.  But that does not help with the original issue.  My exported Excel file is not the data selected in FileMaker.  Periodically I do analytical work in FileMaker that I send an Excel worksheet to someone. 

               

              This was one of the selling points for me, when I introduced the Accounting Manager to FileMaker Pro, reading and writing the Excel formats.

              • 4. Re: Found Set is Wrong
                siplus

                I never had problems exporting data to Excel, both via Export records (xlsx) and Save records as Excel, but always exported single records, not summarized data (which Excel excels at doing, once it's got the raw data, anyway).

                • 5. Re: Found Set is Wrong
                  alquimby

                  Any chance that you are also exporting records from related records? That could cause the empty rows.

                   

                  Al Quimby

                  • 6. Re: Found Set is Wrong
                    user19752

                    +1 or more, it looks like ALL fields are related since they have tablename:: in header line.

                    • 7. Re: Found Set is Wrong
                      alquimby

                      Well, if some of the related records don't have related data, then the exported rows will be empty.

                       

                      Al Quimby