4 Replies Latest reply on Dec 16, 2012 5:21 PM by gnowc

    How does data ended up in appropriate buckets horizontally?


      I watched a presentation from Bob Bowers from the 2012 FM Conference on Advance Reporting. Here he showed how data between two years are layout out side by side. I couldn't figure out what triggered the data to drop into each appropriate bucket. I'm attaching two screen shots 'Order w/Bucket- one in a layout mode and the other in a browse mode from his presentation. On one of the layout (layout mode) there are several fields that have the letter T on them. Could someone kindly explained to me what the letter T means? I set up something similar but my data for both years all ended up in one bucket when displayed horizontally. Vertically the data did ended up in appropriate bucket. I thought I got them to display horizontally in their appropriate bucket but I lost it and not able to replicate it again. Something tells me it has to do with the sorting of the breakfields. What trigger the data to drop in the right bucket when displaying data horizontally? Hope I'm describing the situation accurately. Any help in this is much appreciated. Thank you.

        • 1. Re: How does data ended up in appropriate buckets horizontally?

          T = Tooltip on this object

          1 of 1 people found this helpful
          • 2. Re: How does data ended up in appropriate buckets horizontally?

            Not that the Tooltip is doing anything...

            It may have instructions though...


            There is also nothing particularly advance about what I have seen above... No doubt Bob's presentation had more to it.

            The order total appears to be previousyearamount + currentYearAmount... or perhaps they are calculated independently from what is entered in the order total based on the orderDate.

            The order has a relationship to the customer table via the _kf_customerID and is displaying the Company from that relationship. Everything else is native to this table.


            I think perhaps you need to look at the Field definitions and relationships. If you have FMP Advanced, run the script debugger to see what scripts may be triggered on the layout or on Field Exits or such.


            - Lyndsay

            1 of 1 people found this helpful
            • 3. Re: How does data ended up in appropriate buckets horizontally?

              Thank you Lyndsay for your Find 1st Fiscal Qtr.pngRelated Fields.pngBucket Layout Mode.pngBucket Browse Mode.pngexplanation. Please allow me to provide more objective data.  As I'm pretty new to FMP development.  Please bear with me.


              The order total (eventTotal in my case) is total  from each invoice.

              The SUM order total (SUMeventTotal) is generated from the total of all orders that falls between a defined date range.  The date range originated from a script using global variables and date calculations.


              I have scripts that define the date range, defining each fiscal quarters for both current and previous year  and same with a full fiscal year for current and previous year.  I run the script debugger and data viewer, each script pick up the right range as defined by the variables.   A screen shot of the scripts is attached.


              Then there are calculations that defines the Previous Fiscal Year amount and Current Fiscal Year Amount


              PrevFiscYearAmount = Case (EventYear =$$gprevYear; EventTotal)              {calculation}


              CurFiscYearAmount = Case (EventYear =$$gcurYear; EventTotal)                  {calculation}


              EventYear = Year (Year ( Date—Event Start )                        {calculation}


              EventTotal = Sum ( InvoiceLines Event# Match::${$PriceExtended})            {calculation}


              Sum_EventTotal = Totol of Event Total                                {Summary}


              Date—Event Start                                                                 {date & indexed}


              A screen shot of the fields is attached.


              After running the script "Pick up events for Qtr 1, it picks up all the events within the date range.  For eg. our 1st fiscal quarter from April1 - Jun 30, 2012 and April 1 - Jun 30, 2011.  However, the data is not segregating itself into the current and previous year bucket.  These two fields "PrevFiscYearAmount and CurFiscYearAmoung came out blank.  Hence not working in the summary reports as well.


              Thanks.  Any direction in how to proceed with this is greatly appreciated

              • 4. Re: How does data ended up in appropriate buckets horizontally?

                Your expertise in this is greatly appreciated.


                I'm wondering if the cuprit has to do with the EventYear since the global variables $$gprevYear and$$gcurYear are date ranges defined by script below (full script is attached againFind 1st Fiscal Qtr.png). 


                Set Variable [$$gprevYear; value:

                Let ( [

                today = Get ( CurrentDate);

                y = Year (today) - (Month (today)   <  4)


                Date (4; 1; y -1)  & "..." & Date (6; 30; y-1)



                And EventYear field is defined as Year = Year (Year ( Date—Event Start )  


                Therefore, somehow would not calculate the PrevFiscYearAmount & CurFiscYearAmount, in other words not allocate them in the appropriate bucket.


                PrevFiscYearAmount = Case (EventYear =$$gprevYear; EventTotal)          {calc