Using Dynamic View

Since I wrote the article titled "Running Dynamic SQL Object in PeopleCode" I got a hand full of feedback that the title is confusing. They always thought that it was about Dynamic View, well its probably a bad choice of title on my part. I will make it up to anyone who's looking for the Dynamic View code or how to use it.

Suppose the requirements is to have a different prompt table depending on the settings of other fields. To illustrate, if the user enter name on the other field, they want to see a prompt of personel number. If they enter product information, they want to see the inventory number, lot number, etc.. You could use %EDITTABLE to dynamically specify the prompt table. However
in this case there are too many possible values or involved combinations . These will require you to create too many views. It would be nice if you can create view on the fly depending on what the end-user wants. The answer is YES, we can do that, we can generate the desired SQL text in the PeopleCode based on what the user enters.

1. Get the field that has a promt using GetField function.
&fld_CarNbr = GetField(Field.CAR_NBR);
2. Get all the information user enters.
&data_Name = CAR_TBL.NAME;
&data_Model = CAR_TBL.MODEL;
3. Create the SQLTEXT.
&sqltext = "SELECT ID, NAME, MODEL, NBR FROM INV_TBL WHERE NAME = " &data_Name " AND MODEL = " &data_Model;
4. Override Dynamic View SQL
&fld_StdntCarNbr.SqlText = &sqltext;


Here another example. These replaces the data of the dynamic view.

&sqltext = CreateSQL("SELECT ID, NAME, MODEL, NBR FROM INV_TBL WHERE NAME = " &data_Name " AND MODEL = " &data_Model);

&RSTemp = GetRowset(Scroll.DYNAMIC_VW);
&RSTemp.Flush();
&Rec = CreateRecord(Record.DYNAMIC_VW);
While &Sql.Fetch(&Rec) &i = &i + 1;
&RSTemp.InsertRow(&i);
&Rec.CopyFieldsTo(&RSTemp.GetRow(&i).DYNAMIC_VW);
End-While;