AnsweredAssumed Answered

Using a Case Statement to Populate a Field in Related Table

Question asked by PaulBurton on Mar 28, 2011
Latest reply on Mar 29, 2011 by philmodjunk


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.