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)]
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
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.