1 2 Previous Next 16 Replies Latest reply on Jan 15, 2010 4:40 PM by philmodjunk

    calculation that chooses the sort field

    imgaryshap

      Title

      calculation that chooses the sort field

      Post

      Hello -

       

      I'm using FMP 8 for a MAC.

       

      I have the following fields - Total number of Hours, Total Budget.

       

      I also have three date fields - Greenlight Date, Production Start Date, and Air Date

       

      I want to run three different reports using the date fields.

       

      I then want my Total number of Hours and Total Budget to appear in a particular Quarter column (i.e. 1Q, 2Q, 3Q, 4Q) depending on the sort.

       

      So essentially - a greenlight date sort could show my hours and budget date appearing in 1Q (Jan - March of the sort year), but the same yearly report by Air Date would have the data appear in the 4Q column.

       

      How do I do this?

       

      I'm assuming I create a Quarterly field that has a calculation whereby it imports the numbers and hours automatically based on the sort field (in this case the date).  

       

      So a 1Q Hourly Field might be: 

       

      When Sorting by __________ Field, then if dates of __________ field are between Jan and March of SORT YEAR; Total Number of Hours; 0

       

      any ideas?

       

      thanks!

       

      -G

        • 1. Re: calculation that chooses the sort field
          philmodjunk
            

          To get your columns of data, you'll need to investigate aggregate functions like Sum() to compute your totals and set up relationships so that either portals or calcluation fields put the correct data in the correct column. This can get quite complicated.

           

          a simpler format that takes much less effort to design would be:

           

          Q1 Totals

          Q2 Totals

          Q3 Totals

          Q4 Totals

           

          Then, a fairly stright forward summary report would likely work for you.

          • 2. Re: calculation that chooses the sort field
            imgaryshap
              

            that's what i have now - the simpler Sum versions - but that does not solve my problem. 

            if i set a quartly field - i essentially am locking it into that quarter.  no matter what date field I sort by, the number is locked to that quarter.

             

             

            I need - unfortunately - the complicated version like I described:

             

            1Q Total = When Sorting by __________ Field, if dates of __________ field are between Jan and March of SORT YEAR; Total Number of Hours; 0

             

             

             

             

            • 3. Re: calculation that chooses the sort field
              philmodjunk
                

              I understand but that doesn't have to be the case with some design work.

               

              You can use calculation fields to different text used to label your fields and use sorts and finds to pull up different groups of records in the order you desire. I'm not telling you not to proceed with what you want, just making sure you really want to take that one on when this other, more flexible approach might serve with less work to make it happen.

              • 4. Re: calculation that chooses the sort field
                imgaryshap
                  

                fair enough.  then I'm not understanding how to do your version of the simpler version.

                 

                I want those entering data to enter Total Number of Hours.

                 

                I don't want them to worry about where to place the Total Number of Hours - whether it should be put in a 1Q or 3Q column.   

                 

                What should I do in order to run the reports I want?

                 

                Do create unseen fields and separate layouts that handle this?

                 

                so for example - the user just enters Total Hours.  (Total Budget is generated automatically)

                 

                then - each record has 12 different unseen fields?  1Q "if dates of Air Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0" or "if dates of Greenlight Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0" or "if dates of Production Start Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0"   

                 

                for each quarter?

                 

                then, three separate layouts where report scripts will run to?

                 

                 

                 

                thus 

                 

                 

                • 5. Re: calculation that chooses the sort field
                  imgaryshap
                    

                  what's the calculation language to determine range of dates by quarter?

                  i.e. - "if dates of Air Date field are between Jan and March of SORT YEAR Field"

                  • 6. Re: calculation that chooses the sort field
                    philmodjunk
                      

                    It's difficult to give a more detailed answer without knowing the structure of your database. It's even possible that the structure of your database makes this process much more difficult than I am imagining.

                     

                    To pull up ranges of dates, a calclation field can take a date and return Year.Quarter as a number which is a useful key on which to sort as 2010.1 will sort before 2010.2.

                     

                    let ( M = Month( yourdatefield ) ; Year( Yourdatefield ) & "." & Case( M < 4; 1; M < 7; 2 ; M < 10 ; 3 ; 4 )

                     

                    When performing searches, you could enter find mode and type 1/1/2010...3/31/2010 into a date field to find all first quarter records for 2010 just to give a different option.

                     

                    If you want to pursue this further, please post a more detailed description of your existing tables and relationships, that'll help move us from the abstract to the concrete.

                    • 7. Re: calculation that chooses the sort field
                      imgaryshap
                        

                      sorry - i don't mean to be obtuse, but i have no idea what you're writing here, so I'll try to explain more clearly:

                       

                      Each record has the following fields:

                      Title (text)

                      Greenlight Date (Date field)

                      Production Date (Date Field)

                      Air Date (Date Field)

                      Cost Per Hour (number/currency)

                      Total Hours (number)

                      Total cost (calculation - Cost Per hour * Total hours)


                       

                      I need to run a report on a separate layout with columns like this would look something like this:

                      TITLE            1Q   Hours    Budget    2Q   Hours    Budget    3Q   Hours    Budget     4Q   Hours    Budget               Total  Hours      Budget

                       

                      Grand Total    1Q   Hours    Budget    2Q   Hours    Budget    3Q   Hours    Budget     4Q   Hours    Budget      GrandTotal  Hours      Budget

                       

                      I need to do this yearly report for each of the separate date fields, respectively.

                       

                      It is very common that  a greenlight date happens well before an air date.   When I run a Greenlight Date report for 2009, I want my Total Hours to automatically appear in the proper column.   (i.e. for the 1Q field - "if (Greenlight Date field => Jan 1, Sort Year) and (Greenlight Date field <= March 31, Sort Year);  Total Number of Hours; 0")

                       

                      But how do I do this if change the sort to Air Date or Production Date?

                       

                      is the answer twelve different fields with three different layouts? 

                       

                       

                       

                       

                       

                       

                      • 8. Re: calculation that chooses the sort field
                        imgaryshap
                          

                        that's why in my ideal world the simplest solution would be the following calculation:

                         

                        if (Field We're Seaching By => Jan 1, Sort Year) and (Field We're Searching By Date <= March 31, Sort Year);  Total Number of Hours; 0"

                         

                         

                        • 9. Re: calculation that chooses the sort field
                          philmodjunk
                            

                          I need to know more about how you are storing the data and what it would look like if I just pulled up a group of records in a table view and looked at them.

                           

                          Here's how I read your last two posts:

                           

                          You have exactly one table of data in your file with no relationships to other tables.

                          You have many records with the same name in the title field.

                           

                          Assuming the above is true, if I performed a find to find all records with the title "Green Acres"--just to select a title for our example, what would I see in your Date fields?

                           

                          Would all have the same Greenlight date? Production Date? Air Date?

                           

                           

                          • 10. Re: calculation that chooses the sort field
                            imgaryshap
                              

                            not sure why you would assume that the title is the same for all records.

                             

                            every record is unique.

                             

                            one table.

                             

                            if you pulled up "Green Acres" as a title.

                             

                            One record would show up with the data fields i decribed earlier.

                             

                            Title

                            Greenlight Date

                            Production Start Date

                            Air Date

                            Number of Hours Produced

                            Budget Per Hour

                            Total Budget (Budget Per Hour * Number of Hours Produced.

                             

                            That's it.

                             

                            I want to sort my titles by either Greenlight Date, Production Start Date, or Air Date.   And when I do, i want the Total Hours and Total Budget for the listed title to fall under a specific Quarter.

                             

                            I don't know how to explain it any simpler.

                             

                            Is there a way to do this: 

                            if (Field We're Seaching By => Jan 1, Sort Year) and (Field We're Searching By Date <= March 31, Sort Year);  Total Number of Hours; 0"

                             

                            if not - than it appears i need to do this for each quarter:

                            "if dates of Air Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0"

                            or

                            "if dates of Greenlight Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0"

                            or

                            "if dates of Production Start Date field are between Jan and March of SORT YEAR; Total Number of Hours; 0"  

                             

                            • 11. Re: calculation that chooses the sort field
                              philmodjunk
                                

                              I didn't say All records were the same title, just checked to see if more than one record might have the same title with the report computing sub-totals by program title, that would greatly change the design needed for your report.

                               

                              Please keep in mind that I can't see your database to understand its design I can only go by what you post. I apologise if I misunderstand.

                               

                              The list of fields suggested to me that you might be logging repeated airings of a TV series. In that case, the air dates might be different, but the title might be the same for the same series. You might even air the same show on different dates and want the report to sub total all numbers for all airings of a given show.

                               

                              OK, now to move forward...

                               

                              I keep looking at your earlier posts and frankly can't quite picture how you want this to look.

                               

                              Say I have three records, you find all records with a green light date for the desired year and sort by green light date. You'd then see three rows of numbers like this:

                               

                              TITLE            1Q   Hours    Budget    2Q   Hours    Budget    3Q   Hours    Budget     4Q   Hours    Budget               Total  Hours      Budget

                               

                              You've indicated that if you sorted on green light, your budget and total hours would appear in 1Q, but you don't explain what would appear in 2Q, 3Q, 4Q?????

                               

                              Suggestion, you might want to make up some pretend numbers and post them with explanations like "If I sorted on the green light field I want this", If I sort on Air Date, I see this...

                               

                              I'm thinking what you mean by "sort" is not what I'm understanding.

                               

                               

                               

                               

                               

                               

                               

                              • 12. Re: calculation that chooses the sort field
                                imgaryshap
                                  

                                let's call it a day.  i don't know how to articulate this situation any simpler.   i've given you all I know.  All the fields.  all the information.  even samples of what I would hope a calculation would look like.  You're either just not getting it, or I'm not relaying the info correctly.  

                                 

                                thanks for trying.

                                 

                                 

                                • 13. Re: calculation that chooses the sort field
                                  imgaryshap
                                    

                                  you keep coming up with outside the box scenarios and what-ifs (i.e. - "multiple air dates") that have nothing to do with the question I'm asking. I wish you would have just used the information I've given.

                                   

                                  Title - you enter a title

                                  Air Date - you enter a air date

                                  Production Date - you enter another date

                                  Greenlight Date - you enter another date

                                  Hours - you enter a number

                                  budget - you enter a budget

                                   

                                  you do this for every individual record.

                                   

                                  Then you find all the data in 2009 based on Air Date.

                                   

                                  The layout looks like the layout i gave you.

                                   

                                  Title -                1Q Hours  Budget   -             2Q Hours Budget - etc...

                                  Green Acres               8     2,000,000

                                  A-Team                                                                 9       4,000,000

                                   

                                  But this same report would look different if I ran it going by Green Light Date, if for example GREEN ACRES was greenlight in 2008

                                   

                                  Title -                1Q Hours  Budget   -             2Q Hours Budget - etc...

                                  A-Team                     9     4,000,000

                                   

                                   

                                  My question is - is there a calculation that can place the hours and budget in proper columns when sort/finding by a particular date field or do i have to make a different calculation per quarter per date field.   

                                   

                                  I've already begun working on the later and it works.

                                   

                                  I was hoping there was a simpler way, as i mentioned in previously in the thread.

                                   

                                   

                                  • 14. Re: calculation that chooses the sort field
                                    philmodjunk
                                      

                                    Maybe things will look better by Monday morning. Sorry things aren't working out.

                                     

                                    Well you haven't posted "All the information" in my view. I'm not trying to start an argument here, but I think there are parts of your design that are very obvious to you, but aren't to others because they have set up the database and don't know exactly what you need.

                                     

                                    You haven't posted the one thing that I think would clarify this: What your report looks like with actual numbers not just a row of column headings. I can picture multiple ways to interpret your posts and that type of example might just answer my questions and eliminate the alternatives that I keep trying to rule out.

                                     

                                    In my book, it seems strange that sorting your records on different fields would requite the same data to be located in a differnt column representing a different quarter of the year. I'm sure it makes perfect sense to you, but the reasoning just isn't making it down the wire from your brain to mine. :smileywink:

                                    1 2 Previous Next