Dropdown Using Database Selection

[emhill]

Quick question about using the database driven selection with a dropdown/select box. I would like to concatenate a substring of two fields to use as my options field. This is what I am doing now:

SUBSTR(SXCDE,1,1) + '-' + SUBSTR(SXDATA,1,40)

The result is I get no selection options at all. If I just do this:

SUBSTR(SXDATA,1,40)

All works fine except I don't have the code preceding the description. I would like to have the code in front of the actual description so the user that is just used to the codes will know what they are selecting.

Thanks!!!!

[rmullis]

Try using || instead of + to concatenate.

[kfritz]

Hi,

Try like this:

SUBSTR(SXCDE,1,1)||'-'||SUBSTR(SXDATA,1,40)

Hope this helps

[emhill]

Boom! That did it. Thanks for the prompt replies!!!!!!

[emhill]

Ok... One more additional question concerning this. I need to only select the active company for the user. The company code is stored in a field called UDCBD and that field is on the screen with an ID of UDCBD. The company code is the first character of UDCBD on the screen. The field name in my file is SXCOMP. I now have a criteria to only select where field SXNEM = 'CRCD' but I need to add an additional condition for company code. I know I need something like this in my selection criteria to add the second condition but this does not seem to work:

SXNEM='CRCD' AND "SXCOMP='" get('UDCBD').substr(0, 1) + "'"

I have tried it like the above and by placing the js: in front of the "SXCOMP='" portion to no avail.

Thanks in advance. I'm still larnin' all this!!! :-)

Thanks!

[kfritz]

What you can do is, build your selection string within the RPG code and bind the field to the selection criteria.

[emhill]

kfritz,

Thanks. Does that look like the right syntax? That is my problem. What you see is what will always need to be in the selection criteria I just need to know the correct syntax for adding the company selection.

[kfritz]

Hi,

I don't know which version of PUI you are running. I would recommend use the new SQL security. View this link: http://www.profoundlogic.com/docs/displ ... L+Security

In this case do:

choices selecetion criteria: js: "SXNEM='CRCD' and SXCOMP=?
choices parameter value: UDCBD1 (as a bound field)

Fill the UDCBD1 field within the RPG Code with your substring.

This is clear and safe.

Note: If you switch to the new SQL security, you have to adjust all your selection criteria!

without SQL security:
js: "SXNEM='CRCD' and SXCOMP="+get("UDCBD1");
(make a hidden outputfield with your substring)

[emhill]

Got it working. Thanks so much for your input. Not using the SQL security this is what I ended up doing:

js: "SXNEM='CRCD' and SXCOMP='"+get('UDCBD').substr(0,1)+"'"

Works like a charm when you get everything in the correct place. I'm going to have to check up on the SQL security. Thanks for pointing that out!!!!