3 Replies Latest reply on Mar 17, 2015 11:30 AM by philmodjunk

    Create report that eliminates empty fields

    RobynKimpton_1

      Title

      Create report that eliminates empty fields

      Post

      I want to create a report that eliminates all empty fields, then slides up.  I'm familiar with sliding; I've done it tons of times, but not eliminating empty fields.  This is entirely within one record; it does not report on data from more than one record.  I thought about doing an If, then, else statement with an embedded is empty, but I don't know what to put for the true.  The false, in this case, would be whatever is in the field.  Each field is from the Chart of Accounts.  Because different companies have differences in their Chart of Accounts (some things are consistent like Advertising, for example.  Everyone does advertising.  Other items are unique to the business type.  This is the reason I want to exclude empty fields from the report.  I then want to perform calculations on the fields that are not empty.  Thank you!

        • 1. Re: Create report that eliminates empty fields
          philmodjunk

          Can you explain what you are trying to do with your calculations and how this is not working for you?

          • 2. Re: Create report that eliminates empty fields
            RobynKimpton_1

            The calculations are pretty simple.  Sometimes it's a total of all the fields on a report; other times, it's (Fixed Expenses + Discretionary Expenses) / Gross Profit Percentage.  (Still pretty simple; the calculations themselves are not the problem.)  The problem is that I'm using this for a variety of different types of companies.  So their Charts of Accounts differ.  I want to create the database with all of the accounts that I could see, and I want the reports to filter out those that are empty then show and calculate only on the fields that have data.  That way "Cost of Food", for example, will show up on the report that I do for a restaurant, but will not show up or calculate on the same report that I do for a business that doesn't do anything with food.  Does this make sense?

            Thank you for your help!  It's so kind of you!

            • 3. Re: Create report that eliminates empty fields
              philmodjunk

              This might be a case where you need related records in place of your fields. In such case, a list view report based on the related table automatically omits the records that are not needed for the current company record.

              But what is not clear, is what you mean by this reference to calculations which does not appear to have any connection to the calculations you just described in your last post:

              I thought about doing an If, then, else statement with an embedded is empty, but I don't know what to put for the true.  The false, in this case, would be whatever is in the field. 

              Empty fields set to slide up, resize enclosing part will simply not be visible on your layout when in Preview mode or when printing/saving as PDF. The label text that identifieds the field, however would still be visible and would not slide up. In older versions, we might replace the label text with a calculation field that is empty when the field it labels is also empty so that it can be set to slide up also, but in FileMaker 13, we can use "Hide Object When" to hide the label text and set it to slide up/resize just like the field it labels.

              Key facts about sliding layout objects:

                     
              1. It's only visible in preview mode and when you print/save as PDF...
              2.      
              3. Sliding fields will shrink but not expand.
              4.      
              5. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
              6.      
              7. Objects in headers and footers will not slide.
              8.      
              9. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
              10.      
              11. Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
              12.      
              13. Consistent side borders are difficult to achieve with sliding fields.
              14.      
              15. In FMP13, hidden objects (”Hide object when”) will slide/resize.