Can tokens like $(UserID) be used in the SQL Lookup query for a suggested text field?

When I specify:
 Where PlayerUD = 2
it works.

However,
where PlayerID=$(UserID) 
fails.

Hi there.. Yes, you should be able to do this. When you said it 'fails' does it fail on the user-facing page or just on the admin area? Tokens don't always work in the admin 'Test SQL' area but you should be able to use tokens on the user-facing page directly.

-Chad

Not sure what you mean by "admin 'Test SQL' area" vs the "user-facing page ".

I am placing SQL code in the "Lookup SQl" field" of a DNN Text Suggest Field.

When I am entering data on the form, I get the expected results when I hardcode the UserID; when I use the token, the list is empty. The 2 example queries are below.


select rank() OVER (ORDER BY a.MyTeam) as ID, a.MyTeam as Name
from MyTeams a
where PlayerID= 2
order by Name


select rank() OVER (ORDER BY a.MyTeam) as ID, a.MyTeam as Name
from MyTeams a
where PlayerID= $(UserID)
order by Name

Is my case correct on the token?

Also, can I use other tokens (i.e. field shortnames)?

HI, try this:

select rank() OVER (ORDER BY a.MyTeam) as ID, a.MyTeam as Name
from MyTeams a
where PlayerID= '$(UserID)'
order by Name

I believe only system tokens (ie. UserID, PortalID, etc) will work and not shortname tokens you create on the form. -- David

No go. This only works when hardcode the UserID.

Tried it with the quotes days ago and again today with no luck.

Is there another way to do this?

I also want to do things like displaying a pick list based on other data entered like Region and City. Any ideas or suggestions?

Anyone have an idea about how to get this done???

It appears that tokens like $(UserID) cannot be used in a query for a DNN Suggested tesxt query. Is this a bug or by design?

How can a list be built based on other values on the form?

One idea I have is to create a temp table after the related fielxt ds are complete using a field completion eent. The issue is how to access the temp table in the sugg. text query.

Hi.... Can you setup some sort of example of this on Beta Springs? Beta Springs is running 3.4 beta but I just checked the code and the code is the same for both 3.3 and upcoming 3.4 release. Also, the code is the exact same for the Text field and DNN Text Suggest field within the code so I am not really sure why it wouldn't be working.

Does it work if its a standard text field? Do you happen to have a field called UserID as the short field name that could be interfering?

Thanks,

Chad

Chad wrote:
Beta Springs is running 3.4 beta but I just checked the code and the code is the same for both 3.3 and upcoming 3.4 release.

I am running DF v 3.30.50


Chad wrote:
Also, the code is the exact same for the Text field and DNN Text Suggest field within the code so I am not really sure why it wouldn't be working. Does it work if its a standard text field?

I am not sure how I would setup lookup SQL for a standard text field. I thought that the “Lookup SQL” was only available for the DNN Text Suggest. I am not sure how I would test this for a standard text field.


Chad wrote:
Do you happen to have a field called UserID as the short field name that could be interfering?

No.

$(UserID) works everywhere else that I use it on this form. For example, this code works for a Combo Box:

Select '< Enter or Select Team>' as QuestionOption, '0' as QuestionOptionValue
Union All
select a.MyTeam as QuestionOption, a.MyTeam as QuestionOptionValue
from MyTeams a
where PlayerID='$(UserID)'
order by QuestionOption

I am also using $(UserID) in a form completion event that inserts a record into a table and it works properly.

I am also using$(userID) in the Initial SQL Bind and it works properly there, (i.e. Select … where … and PlayerID='$(UserID)' ). I do not know why this would affect the Lookup SQL in any way.


Here is a simple test that I used to demo the bug. It is easy to duplicate.

1. Add a DF module to the page
2. Create a DNN Text Suggest field
3. This code in ‘Lookup SQL’ works OK. On the user facing page, when you begin typing the usernane, suggested text pops up correctly.

select userid as ID, Username as Name
from users
where UserID='2
order by Name

This code does NOT work!

select userid as ID, Username as Name
from users
where UserID='$(UserID)'
order by Name

So, I guess this means that the token is not being replaced in the 'Lookup SQL'.

Give it a try, you should get the samer esults.

Hi... Ok, sorry I was confused about this. I kept seeing 'SQL Lookup Query' and I kept thinking you were referring to either SQL Options for a field or for text fields the 'SQL Default' property. I just checked and the look up query doesn't currently include the feature to parse for any tokens... We have added this to the upcoming 3.4 release.

Thanks, and it sounds like you can look forward to this in the 3.4 release.

-Chad

Yhanks Chad. I look forward to 3.4.

BTW, it would be great if the tokens on the form were available. This way the query for the suggest text field could list data based on prior fields entered. For example, I could query venues for a partucular city/state to reduce the list size and hone in on what the user maybe looking for!

Hello,

Yes this sounds like a great enhancement, I can refer this to our development team to overlook this feature for future enhancements.

Thanks for the great idea,

Ryan

Chad wrote:

Hi... Ok, sorry I was confused about this. I kept seeing 'SQL Lookup Query' and I kept thinking you were referring to either SQL Options for a field or for text fields the 'SQL Default' property. I just checked and the look up query doesn't currently include the feature to parse for any tokens... We have added this to the upcoming 3.4 release.

Thanks, and it sounds like you can look forward to this in the 3.4 release.

Did this make the 3.4 release??

Hello,

Yes this did make it to the Dynamic Forms 3.4 release, and it's currently available, if you would like to see the baseline new enhancements or purchase the module, please refer to this link:

http://www.datasprings.com/Dynamic-Forms

If you have any questions please feel free to ask.

Thanks,

Ryan