The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 05 Oct 06 5:52 PM
|
fiogf49gjkf0d We store the SHORTTEXT for a PICKLIST named HOMEDISTRICT. I've been trying to use #SQL to retrieve the TEXT value from the PICKLIST. When I hard-code the SHORTTEXT into the #SQL statement, it works fine, but I can't seem to replace the hard-coded value with a variable. My event viewer says 'Failed to Parse SQL'.
The hard-coded statement pulls back the correct value of 'NACN - California, Nevada':
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext='nacn'" format=0>
This does not work (Fails to Parse):
<input type="hidden" name="homedistrict" value="<#F name=lcam_bio_homedistrict>">
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext='"+homedistrict+"'" format=0>&
Any ideas how to replace the hard-coded value with a variable?
Thanks,
Dave
|
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 05 Oct 06 6:53 PM
|
fiogf49gjkf0d You are trying to have one server side tag feed another server side tag via client side scripting. This is fundamentally not going to work.
What about adding an alias that handles the join for you? Assuming your ShortText values are unique you could create an alias that joins to the picklist table and returns the text value.
And more along the lines of what you are trying, try feeding the #Field tag to the #SQL tag directly:
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext='<#F name=lcam_bio_homedistrict>'" format=0>
I suspect it will not work but occasionally you can get lucky with the order the server side tags are processed. I guess I would not be too suprised if the #Field tags were processed first.
Timmus |
|
|
| |
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 06 Oct 06 2:51 AM
|
fiogf49gjkf0d David,
I'm not 100% sure how you are using this, if you just want to use show a field based on the value of another field, then as Timmus suggested just use an alias that joined to your primary value.
However if you need to change this value on the fly inside the page, then you may what to bring in two picklist, one which shows the text and another that shows the shorttext, then linked them up using javascript as the selectedIndex of the primary picklist changes, it also changes the value of the secondary picklist
--
Duncan |
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 06 Oct 06 8:15 AM
|
fiogf49gjkf0d Timmus,
Thanks for your reply. I tried it the way you suggested. I'm not receiving an error, but not getting my value either. I had tried the #Field tag in the statement before, but with '+' to concatenate it, and received the error.
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext='<#F name=lcam_bio_homedistrict>'" format=0>
This seems like maybe it's closer. Is there any way to see how it's being interpreted?
Dave
|
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 06 Oct 06 8:26 AM
|
fiogf49gjkf0d Duncan,
I only need to display the data. It should all be read-only. I'm not sure if I know how to use an alias to join to my primary value as Timmus suggested, but I'll be taking a look to see if I can figure it out. Are there any examples in the base product? I should probably point out that I don't know Java Script (can probably read it) and I'm very new to HTML.
Dave |
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 06 Oct 06 10:56 AM
|
fiogf49gjkf0d Dave,
You can try this:
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext=:lcam_bio_homedistrict" format=0>
Don't include the single quotes around the name of the alias and include the colon. I don't know if this will work - it might if you're on a detail page (as opposed to a query page). You can use certain bind variables in #SQL statements, such as:
<#SQL code="select firstname from userinfo where userid = :theuserid" format=0>
Which will stuff in the current userid. What I'm suggesting might work since you're trying to plug in an alias. I haven't tried it, but it's worth a shot.
Jeff
|
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 06 Oct 06 11:00 AM
|
fiogf49gjkf0d Dave,
One other thing, if my previous suggestion doesn't work. If you don't need to dynamically change things based on what's selected in the page, you could use a <#INCLUDE> tag that will output the HTML for your <select> control.
Jeff
|
|
|
| |
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 09 Oct 06 4:15 AM
|
fiogf49gjkf0d Hi Dave
Jeff's first suggestion will only work if you are passing this value "lcam_bio_homedistrict" in the url string to the page.
<#SQL code="select text from picklist where picklistid='kLCMSA0000IE' and shorttext=:lcam_bio_homedistrict" format=0>
For example ..slxweb.dll/view?name=mypage&id=123&lcam_bio_homedistrict=myvalue
--
Duncan |
|
|
|
Re: Retrieve TEXT from PICKLIST when SHORTTEXT is the stored value
Posted: 26 Dec 06 5:07 PM
|
fiogf49gjkf0d This is the solution I finally came up with:
Create an ACTION (lcms_c_am_homedistrict_long):
sub main()
dim dbh as long
dim homedist as string
dim SQLstr as string
dim ContactID as string
ContactID = RequestGetValue("conid")
'homedist = isalias("lcam_bio_homedistrict") & aliastotable("lcam_bio_homedistrict")
'sessionsetvalue homedist,RequestGetValue("bio_homedistrict")
'dbh=requestgetname(0)
SQLstr = "SELECT TEXT FROM PICKLIST WHERE PICKLISTID = 'kLCMSA0000IE' AND SHORTTEXT IN (SELECT HOMEDISTRICT FROM LCMS_C_AM_BIODATA WHERE CONTACTID ='" + ContactID + "')"
'dbh = DoSQL(SQLstr, "1", "")
homedist = DoSQL(SQLstr, 0, "")
setactionresult homedist
end sub
Use the ACTION in a TEMPLATE like this:
<td style="border: 1px solid silver" align=left><#INCLUDE name=lcms_c_am_homedistrict_long type=action> </td>
|
|
|
|