Tuesday, February 20, 2007

Use Dynamic SQL for Prompts - SqlText

Normally you can not use dynamically generated SQL at runtime as your prompt record. You can use %EDITTABLE to specify the prompt table you want to use, but it has to be predefined. There is this little known property SqlText for Field Class, which allows you to do exactly that.

Here is the description and sample code taken from peoplebooks.

This property is valid only for fields that have a dynamic view as their prompt record. If you set SqlText to a non-null value, that text is used instead of the dynamic view's normal text used for prompting.

Suppose you wanted to have a different prompt table depending on the settings of other fields in the row. Normally you could use %EDITTABLE to dynamically specify the prompt table you want. However in this case there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in PeopleCode based on what the user enters.

If you use a dynamic view as the prompt table, and have the dynamic view contain a SQL object that is updated from PeopleCode, you could achieve this functionality. However, a SQL object is a shared object, so if multiple users used the same page, they overwrite each other's settings and the SQL object contains the SQL for the most recent user. Similarly if a single user had multiple rows on a page, the SQL object is valid only for the most recent row. This means if the user went to another row and did a prompt, they would get the wrong values again.

The purpose of this property is to enable you to specify the generated SQL text independently for each occurrence in each transaction. It enables you to override the text of a dynamic view being used as a prompt table on a field by field basis.

It is up to the developer to verify that the text specified for this property is valid, that is, that it selects the correct number of fields for the record definition, and so on.

This property is read-write.

Local string &SQLSTRING;

Function set_jrnl_id_prompt();
&SQLSTRING = "SELECT DISTINCT JOURNAL_ID, BUSINESS_UNIT_IU, JOURNAL_DATE, LEDGER_GROUP, SOURCE, SYSTEM_SOURCE, PROC_PART_ID, JRNL_HDR_STATUS, DESCR FROM PS_JRNL_HEADER WHERE JRNL_HDR_STATUS IN ('N','E','V')"
If All(JRNL_EDIT_REQ.BUSINESS_UNIT) Then
&SQLSTRING = &SQLSTRING | " AND BUSINESS_UNIT_IU='" | JRNL_EDIT_REQ.BUSINESS_UNIT | "'"
End-If;
If All(JRNL_EDIT_REQ.LEDGER_GROUP) Then
&SQLSTRING = &SQLSTRING | " AND LEDGER_GROUP='" | JRNL_EDIT_REQ.LEDGER_GROUP | "'"
End-If;
If All(JRNL_EDIT_REQ.SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SOURCE='" | JRNL_EDIT_REQ.SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.SYSTEM_SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SYSTEM_SOURCE='" | JRNL_EDIT_REQ.SYSTEM_SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.PROC_PART_ID) Then
&SQLSTRING = &SQLSTRING | " AND PROC_PART_ID='" | JRNL_EDIT_REQ.PROC_PART_ID | "'"
End-If;
GetRecord().GetField(Field.JOURNAL_ID_FROM).SqlText = &SQLSTRING;
GetRecord().GetField(Field.JOURNAL_ID_TO).SqlText = &SQLSTRING;
End-Function;

7 comments:

Bahar said...

We are on tools version 8.20.07. When I try to use this method, I get this error:

"SQLText is not a property of class Field"

Is there any way to make this work for tools 8.20 ?

Ketan Kothari said...

Check your peoplebooks. I have not used this in Tools 8.20

Bahar said...

Hi Ketan,
Thanks for your response. I checked PeopleBooks and the property 'SQLText' is not available for the 'Field' class. do you have any suggestions as to how I can achieve the same functionality without using the 'sqltext' property ? Any help would be greatly appreciated.

Thanks, Bahar

Ketan Kothari said...

No. You may try to use the %EDITTABLE
as the prompt table and change the view name at run time.

Mandy said...

AddDropDownItemSyntax

AddDropDownItem(CodeString, DescriptionString)

Description

The AddDropDownItem method adds an item to the dropdown list in the control for the field. The first time this method is called, it overrides the prompt table or translate table used to populate the list. Those items no longer appear in the list. Only the items added using this method display.

Subsequent calls to this method adds additional items to the dropdown list. The items added with the first call to the method also display.

If there is an existing value and the dropdown list is changed with these functions, the selection shows as (Invalid value) unless the new list contains an entry with the same code as the existing value.

Considerations Using AddDropDownItem

If the data for the dropdown is language sensitive, the values for the dropdown should come from the message catalog or from a database field that has a related language record, and should not be hard-coded.

A good place for your PeopleCode program to populate a dropdown list is in the RowInit event. This event executes before the page is shown for the first time, so it prevents unnecessary SQL.

Parameters

CodeString
Specify the value used to set the field value if this item is selected. Codes longer than the size of the field are truncated.

DescriptionString
Specify the value the end-user sees in the dropdown list.


Returns

None.

Example

Using a hardcoded list is not appropriate for this function because translations do not work. The data must come from the Translate Table (or other record) directly so that the data is translated correctly.

Local Rowset &Xlat;

&FLD = GetRecord(Record.JOB).GetField(Field.ACTION);
&FLD.ClearDropDownList();

Evaluate %Component
When Component.JOB_DATA_CONCUR
&Xlat = CreateRowset(Record.PSXLATITEM);
&Xlat.Fill("WHERE FILL.FIELDNAME = 'ACTION' AND Fill.FIELDVALUE in ('ADL','HIR') and EFFDT = (select max(EFFDT) from PSXLATITEM
B where B.FIELDNAME = 'ACTION' and B.FIELDVALUE in ('ADL','HIR') and EFFDT <= JOB.EFFDT)");

&Xlat_cnt = &Xlat.ActiveRowCount;
For &I = 1 To &Xlat_cnt
&CodeIn = &Xlat.GetRow(&I).GetRecord(1).FIELDVALUE.Value;
&DescIn = &Xlat.GetRow(&I).GetRecord(1).XLATLONGNAME.Value;

&FLD.AddDropDownItem(&CodeIn, &DescIn);
End-For;

Break;
When-Other
End-Evaluate;

See Also

Field class: ClearDropDownList method.

mrpogs said...

Hello,

Hopefully this post will tweak someones memory.

How does the SQL in the SQLText get into the dynamic view?

Thanks,
Mike

Ketan Kothari said...

At runtime using sqltext property of field object or by specifying it in the view definition.