11 Replies Latest reply on May 7, 2013 9:53 AM by KurtSnow

    Sorting a report by the results of an "If" function

    KurtSnow

      Title

      Sorting a report by the results of an "If" function

      Post

           I am trying to sort a report by the results of an "If" function.

           "If" field is called mediaSort = If ( BuyNumbers::media="Radio"; RadioMetros::metro;  DMA::DMA)

           Result field is called mediaSortResult

           I tried two methods:

           1) Created a Set Field mediaSortResult; MediaSort]

           2) Set mediaSortResults as a calculation = Unstored, =mediaSort

           Then I sorted the report on the mediaSortResutls field

           Both methods fail. Any clues why?

        • 1. Re: Sorting a report by the results of an "If" function
          philmodjunk

               1) why di you not use:

               Set Field [ mediaSortResult; If ( BuyNumbers::media="Radio"; RadioMetros::metro;  DMA::DMA) ]

               and for 2) why not:

               If ( BuyNumbers::media="Radio"; RadioMetros::metro;  DMA::DMA)

               ??

               from the names, RadioMetros::metro and DMA::DMA

                reference data in different table occurrences. Thus there must be a relationship involvled. What relationship do you have?

               

                    Both methods fail

               Exactly HOW do they fail? What results to you get?

               Do you see correct or incorrect values in MediaSortResult? If they are incorrect, what incorrect results?

          • 2. Re: Sorting a report by the results of an "If" function
            KurtSnow

                 Radio Metros are a geographic area found within DMAs. Two tables that are both linked to the Orders database.

                 The correct result appears on the "Media Orders" form, but not on the "route" report (see attached). I need the report to list all radio station beneath the Radio Metro, and the TV station under the DMA.

            • 3. Re: Sorting a report by the results of an "If" function
              KurtSnow

                   Route report

              • 4. Re: Sorting a report by the results of an "If" function
                philmodjunk

                     I don't see any "attached". Was your "attached" a jpeg gif or png file? As stated in the controls below, those are the only file types that can be uploaded with the Upload an image controls.

                     I need more detail about the actual relationships.

                     Your original post refers to three different table occurrences:

                     BuyNumbers, RadioMetros and  DMA

                     Your most recent post refers to an "orders database"

                     I'd need to know precisely how these are linked in relationships (match fields, operators...) and the table occurrence context (A layout based on what table is current?) at the time this script step executes.

                • 5. Re: Sorting a report by the results of an "If" function
                  KurtSnow

                       It was a PDF. Attached are .jpgs.

                       I have more than a dozen tables.

                       Orders (the main table)  is connected to Buy Numbers

                       Orders <connected to> DMA <connected to <Radio Metro>

                       Note: Everything is joined properly. All the date shows up just fine, it is only a problem when I sort the route report on the If function field called Media Sort.

                  • 6. Re: Sorting a report by the results of an "If" function
                    KurtSnow
                    /files/3f07b8ffb0/Route.jpg 1280x689
                    • 7. Re: Sorting a report by the results of an "If" function
                      philmodjunk

                           I wasn't suggesting that your relationships were wrong, just that I can't suggest a solution without a much more complete picture of the particular tables and relstionships involved.

                           From what you have posted, I have a bit more info

                           BuyNumbers?----?Orders?----?DMA?----?RadioMetrics

                           But that's still a very incomplete picture. I asked for the match fields as a way to tell which relationshihps are many to many, One to many, etc as that can be very key detail, but that info is still missing.

                           On what table is your route report based?

                           How is the value returned by this calculation supposed to affect what is seen on the Route Report?

                      • 8. Re: Sorting a report by the results of an "If" function
                        KurtSnow

                             Table: Orders

                             Match Field: __pkOrdersID

                             Type: Many

                             Orders linked to 13 other tables. The report in question is linked to "Clients", Buy Numbers", "Media Companies," "DMA," and Radio Metros"

                             Clients / __pkClientsID  / One -- Orders linked to "Clients" by foreign key _kpClientID

                             Buy Numbers / __pkBuyBumberID  / One -- Orders linked to Buy Numbers by foreign key _fkBuyNumberID

                             Media Company / __pkMediCompanyID / One -- Orders linked to Media Companies by foreign key _fkMediaCompanyID

                             Media Company is linked to DMA  and Metro by respective foreign keys. Both DMA and Radio Metros are a "many" relationship.

                              

                             Table is based on orders.

                              

                             On the Media Orders form above you can see that the MediaSortResult field is correct in that it is displaying the DMA name as this station is a TV station, and not a radio station. Consequently, the DMA name appears, not the Radio Metro name. On the Radio Buy Route Sheet is supposed to look like this:

                              

                             Market: Eugene (DMA)  listing station KVAL

                             (Ignore Sacramento -- it was in there by mistake)

                             Market: San Diego (DMA) listing KGTV

                              

                             Note: If I sort the report by DMA, rather than the IF function, the report works fine.

                        • 9. Re: Sorting a report by the results of an "If" function
                          philmodjunk
                               

                                    The report in question is linked to "Clients", Buy Numbers", "Media Companies," "DMA," and Radio Metros"

                               But only one of those may be named in Layout Setup | Show Records From. Which one is listed there? From your statement "Table is based on Orders", I take it that Orders is listed in that drop down.

                               I can now deduce these relationships:

                               RadioMetrics>-------MediaCompany----<DMA
                                                              |
                                                              ^
                               BuyNumbers----<Orders
                                                              v
                                                              |
                                                          Clients

                               On what table is the first layout, the one where you have a correct result, based? (What table occurrence name appears in Layout Setup | Show Records from?)

                               And I assume that the field that is empty on the Route Report the calculation field where you select between DMA and Radio Metro tables for the name?

                               Evaluation Context would appear to be the first key thing to check If my assumptions and deductions are accurate. When you defined your calculation field, I assume that it was defined in Orders and Orders was selected in the Context drop down in Specify Calculation? Or did you define it in MediaCompany with MediaCompany as the context table occurrence?

                               Other factors that could produce empty fields when DMA::DMA is supposed to supply the name on the report:

                               1) missing or incorrectly linked Media company record.

                               2) missing or incorrectly linked DMA record.

                          • 10. Re: Sorting a report by the results of an "If" function
                            philmodjunk

                                 Another possiblity is shown in the one to many relationships between media company and both DMA and Radio Metro. That implies that Media Company links to many records in each of those tables, but your calculations can only reference the "first" such realted record in each of those tables. If the DMA or Metros field is empty in that first related record, you'll get an empty result.

                            • 11. Re: Sorting a report by the results of an "If" function
                              KurtSnow

                                   I figured it out. I had an incorrect field in my sort order. Sorry I troubled you with all this. Thank you for all your input.