1 Reply Latest reply on Mar 29, 2011 9:21 AM by philmodjunk

    Using a Case Statement to Populate a Field in Related Table



      Using a Case Statement to Populate a Field in Related Table


      I have two tables:  1) Revenue, 2) Data.  Revenue is related to Data by a field ""Key," which is auto populated in Data at load time and in Revenue with a script.

      In Data, there is a column called "Revenue" which has a numeric value, and a value for "Year."  This means that the value in the Revenue column is for a particular calendar year.  So the data is denormalized.

      I want use a script, grabbing the Revenue value from Data and sticking it into the Revenue table into 1 of 4 columns - "R2007," "R2008," "R2009," or "R2010," depending on the value of "Year" it corresponds to in "Data."  For example, a value for revenue from a record in Data having a Year value of 2007 should be placed in the column R2007 in Revenue.

      Here is the script that I wrote, which does not work:

      Got Layout [(Data)]

      Goto Record/Request/Page[First]

      Set Variable[$vRevenue; Value: Data::Revenue]

      Goto Related Record[From Table: "Revenue"; Using Layout: "Revenue" (Revenue)]

      If[Case (Revenue:: Year = 2007; Revenue::R2007 = $vRevenue . . . etc

      End If

      I use the goto related record command to make sure the revenue is plugged into the right record in the table Revenue.

      This does not work, not sure why.  


        • 1. Re: Using a Case Statement to Populate a Field in Related Table

          Go To Related Record can fail spectacularly if there is no related record to go to and you do not check for this possibility.  The Complete Go To Related Record

          That's not the issue here, but please be careful when using this script step.

          IF steps cannot function as a case statement so your caset statement can only be evaluated inside it as True or False. You'd need to modify that part of your script like this:

          If [Revenue::year = 2007]
             Set field [Revenue::R2007...
          Else IF [Revenue::year = 2008
             Set Field [Revenue::R008...
          //and so forth

          You may not need a script at all for this. You could define 4 calculation fields to use in place of this script.

          cR2007 could be defined as If ( Year = 2007 ; Data::Revenue ; "" )

          Similar if functions can give you revenue for subsequent years.

          An even more flexible approach is to not use any fields in Revenue at all for this. You can use a filtered horizontal portal technique to display the revenue figures from Data in different columns for each year. You can control the range of years that appear in the portals (a "horizontal portal" is really a group of one row portals) so that you do not have to keep redefining your calculations to add data for the next year.