7 Replies Latest reply on Apr 1, 2010 11:57 AM by Melinda

    Calculation Help -- need to eliminate blank fields from list.

    Melinda

      Title

      Calculation Help -- need to eliminate blank fields from list.

      Post

      Beginner

      Mac OS X

      FM Pro 9 on a shared server

       

      I have written a VERY simple calculation that finds exactly the information I need:

       

      If ( Days Billable  >  "0"  ; List ( Date ))

       

      HOWEVER, it presents blank fields in the list if the "Days Billable" field is equal to zero.  I want to eliminate those. 

       

      If it helps to know, the layout is in list view, and I search by employee between a specified date range.  All I need to know (for now) is the dates of all biilable work within the specified date range.

       

      As always, let me know if I need to clarify something, and thanks for your thoughts!

       

      Melinda

        • 1. Re: Calculation Help -- need to eliminate blank fields from list.
          philmodjunk

          I don't see the purpose of the calculation.

           

          Instead just enter your date range as you do now and then also enter the expression:

           

          > 0

           

          In the days billable field to filter out the records where days billable is zero.

           

          • 2. Re: Calculation Help -- need to eliminate blank fields from list.
            Melinda

            well, that worked!

             

            i was using global search fields for the instructor and dates, but changed them to "regular" fields and it did exactly what i needed...  THANKS!

            • 3. Re: Calculation Help -- need to eliminate blank fields from list.
              philmodjunk

              "i was using global search fields for the instructor and dates"

               

              If by that statement you mean that you entered criteria in global fields for a script to use to perform your find, you can still do this. You'd just modify your script to include a set field step that enters "< 0" into the field.

              • 4. Re: Calculation Help -- need to eliminate blank fields from list.
                Melinda

                That's exactly what I meant, and I have that working now, too... :)

                 

                • 5. Re: Calculation Help -- need to eliminate blank fields from list.
                  Melinda

                  Okay, everything was working just fine, but I as I added to the layout, I lost the ability to lose the blanks in the body if the condition of "billiable days > 0" is not met...  I've discovered that the search is finding the billable days, but it doesn't count the remaining data unless it is part completed on teh same day as any billable work.  I am collecting the number of billable days, as well as several other items (travel, event, personal leave, etc), but only the dates that are billable...  My layout is simple:

                   

                  Header -- global search fields for name and dates, with a button scripted to populate the summary fields for the dates searched (the summary fields are in the header, too)

                   

                  Body -- a list of billable dates and the course number they are associated with... so two fields,  Billable Dates and Billable Service Number

                   

                  Footer -- A cumulative total (year to date) summary for the same fields in the header.  This works perfectly...  :)

                   

                  The first script worked to find the number of billable dates as well as the actual dates... the fields that didn't meet the "0" criteria were ignored... and I was left with a nice and clean list in the body of my layout.... it was written:

                   

                  Enter Find Mode [ ]

                  Set Field [ADMIN DATA:: Employee Name; ADMIN DATA::gSearch Name]

                  Set Field [ADMIN DATA:: Date; ADMIN DATA::gSearch Start Date & "..." & ADMIN DATA::gSearch End Date ]

                  Set Field [ADMIN DATA:: Billable DATES; ">0"]

                  Perform Find

                   

                  I discovered, though, that any additional information was collected only if it was included during the dates that were billable...  So I wrote a second script...  it works just fine to collect the data I need in the header, but I get a messy list of fields in the body --  I am getting blank fields among the billable dates -- the correct dates are showing, I just need to get rid of the blank fields...  Here is the second script:

                   

                  Enter Find Mode [ ]

                  New Record/Request

                  Set Field [ADMIN DATA:: Employee Name; ADMIN DATA::gSearch Name]

                  Set Field [ADMIN DATA:: Date; ADMIN DATA::gSearch Start Date & "..." & ADMIN DATA::gSearch End Date ]

                  New Record/Request

                  Set Field [ADMIN DATA:: Billable DATES; ">0"]

                  Set Field [ADMIN DATA:: Employee Name; ADMIN DATA::gSearch Name]

                  Set Field [ADMIN DATA:: Date; ADMIN DATA::gSearch Start Date & "..." & ADMIN DATA::gSearch End Date ]

                  Perform Find [ ]

                  If [not Get (FoundCount)]

                  End if

                   

                  Any suggestions?  I have worked and reworked the scripts, and what I have written above is how I've left them for now...  I realize I only need one script, but I haven't been able to combine them so they work together to get what I need!  Each solves part of my problem, so I am trying to put them together to solve it altogether...

                   

                   

                   

                  • 6. Re: Calculation Help -- need to eliminate blank fields from list.
                    philmodjunk

                    You've put yourself in something of a dilemma here. Summary fields only compute values based on the current found set (or sub groups of the found set if placed in a sub-sumarry part). Thus, as you've discovered, if you exclude records from your found set, any data on the excluded records are no longer part of your summary field's summary calculation.

                     

                    I sounds like you'll have to replace your summary fields with calculation fields that use aggregate functions like Sum() to compute your values. You'll also need relationships that match to the correct set of records in order for them to correctly compute the totals you want.

                     

                    I'm guessing that you are specifying a specific date range and need to summarize data from all records that are included in that date range...

                     

                    If you add a new table occurrence  for the table you are referencing in this report to your relationship graph, you can link it like this:

                     

                    MainTable::gDate1 < MainTable 2:: Date AND

                    MainTable::gDate2 > MainTable 2:: Date

                     

                    Then a calculation field such as Sum (MainTable 2::NumberField) will give you the total of all records in the date range from gDate1 to gDate2 regardless of whether or not they are in the found set.

                    • 7. Re: Calculation Help -- need to eliminate blank fields from list.
                      Melinda

                      Thanks Phil!  It works perfectly now...  WHEW!  My co-workers are most impressed, but don't worry, I am quick to give credit where it is due!  :)  Thank you!  Thank you!  Thank you!