Driper
new to the springs
Posts:2
 |
| 10-16-2009 02:16 PM |
|
I'm a long term basic user of Dynamic Forms. I now have a need for something more adavance which I think can be done but I can't work it out. I need to have a form that only displays a question IF a value is True from an SQL query. Specifically I have a list of clients and a list of products. Clients are authorised to order certain products. If they are not authorised I don't want the question (do you want this item) to be visible. This will be a mass order form showing all items that they could purchase (1 question per product). There are only about 40 items so its not as backwards as it sounds.
My question is how do I get Dynamic Forms to only show a question if an SQL query returns a value? I had tried adding a hidden field and comparing a lookup with the hidden field (which contained the expected product description). If the two fields matched then show the question. If they dont match (becuase SQL returned no rows) then don't show. Logic is fine but doesnt work (probably becuase I don't know what I am doing).
Any help greatly appreciated. |
|
|
|
|
Driper
new to the springs
Posts:2
|
| 10-19-2009 04:31 AM |
|
OK I needed to get this working so I tried troubleshooting by moving forward in little steps. I took example 7 with the show/hide. I imported the iPortable module and then added the following code into the checkbox question's client side field (assuming that becuase it already did this in other areas it would work easily). Well it doesnt seem to work. What else needs to happen for this to work?
if ( $(chkToggleHelp))
showhtml('FirstName')
else
hidehtml('FirstName') |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-19-2009 06:37 AM |
|
To show only a question if SQL query returns only a value, you can use question events. Here'a a quick tutorial:
1) create a hidden field and use SQL default option to populate the value
2) create your question make under Advance Field Options set it to "hide until forced visible by question event"
2) create a question event to "display hidden question based on another questions response"
-- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-22-2009 04:54 PM |
|
David,
I have the same issue and based on your instructions, these are the steps I took.
1) Created a question with following options:
Question = "test"
question type = "text box"
Under the advance field option I selected the option "Hide until forced visible by question event".
2) I then created a question event with the following options:
event type = "Display hidden question based on another questions response"
Initiation / Postback Question = ??
Affected Question = 'test'
The problem I encounter is in step number two, as you will notice the option Initiation / Postback Question is black, the reason it's blank is because the question I was looking for was not listed in the combo box and the question does exist. The question that I was looking for question type is a text box and it has valuation configuration setting under module configuration. Upon submission the valuation checks the text entered into the text box and confirm the entry do not exist in the database. If the entry do not exist, I would like in my example the question 'test' to be revealed. If does exist, I don't the question 'test' to be revealed.
What can I do to address this problem? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-23-2009 06:53 AM |
|
HI, here's what you need to do. Since a textbox cannot trigger a question response (no postback), you need to do this:
1) create a hidden field with shortname like HiddenTest
2) under client side event for your textbox field: $(HiddenTest) = $(Test)
3) in your question response, now you can choose the HiddenTest for your Initiation / Postback Question
-- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-23-2009 07:13 AM |
|
If I enter a value in the question TEST, the statement $(HiddenTest) = $(Test), will always read true and therefore will display the hidden question. I only want to display the hidden question if the value entered do not exist in the database. How do I accomplish this? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-23-2009 12:38 PM |
|
HI, this is very tricky because you have a textbox that someone enters a number and you want to check the SQL table to see if it exists or not. Try this method:
1) create two hidden fields, with shortname "ValueEntered" and "ValueExists"
2) in your client side event for the "Test" textbox field, type in: $(ValueEntered) = $(Test)
3) create a question event, choose "Set question default based on another questions response"
Initiation / Postback question: ValueEntered
Execute for any response
Affected Question: ValueExists
Use SQL driven and type in your SQL statement (something like):
if exists (select columname from tablename where columnname = $(ValueEntered) then select "True" as defaultvalue
else select "False" as defaultvalue
Then create another question event to unhide fields if ValueExists is "False"
See if this will work out for you. If not, we may have to split this into two forms going forward if you want to get this to work. -- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-24-2009 09:35 AM |
|
David,
I have attempted your suggestion and I have tried several variations, but none worked. It appears the main problem is the ValueExists question is not getting updated with the SQL statement, which will trigger the hidden question to be display. I have executed the query and I know it works, but this technique just don't want to update the default value for the ValueExists question.
Below is the query used.
IF EXISTS (SELECT columname FROM tablename WHERE columnname = $(ValueEntered))
SELECT 'True' AS DefaultValue
ELSE
SELECT 'False' AS DefaultValue
After hours of trying, I decided to use two forms.
Although this didn't work, this is a very good feature I think would be very valuable for Dynamic Forms, please consider adding this feature into one of your next releases. |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-26-2009 06:42 AM |
|
HI, several questions:
1) can you enable debug mode when filling out the form and submitting to copy the exact SQL statement that's being passed? Can this same SQL statement be executed under Host / SQL?
2) Is that your SQL query above? If so, it's incorrect. You should replace the "columname" and "tablename" with whatever your columname and tablename is. What I gave you is just an example but it requires some tweaking because I don't know your SQL table structure. -- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-26-2009 06:58 AM |
|
1) I have already created the two forms, but I will redo my steps, enable debug and submit my results.
2) The sql statement above is just an example, I did use my actual table name and column name. I have also test the query and it work, so I know everything in the query was correct. |
|
|
|
|