Hi,

I am having an issue trying to get my head around checkxboxs in DF.

I have a form that has the following fields (from a Product table):

Product Name, Ingredients
Apple Sauce    1,4

I need the Ingredients field to hold IDs pointing to ingredients which are held in another table. So for example my ingredients table might look like this:

ID, Name
1,Sugar
2,Salt
3,Pepper
4,Apple
5,Orange

In my form I query the Product table and read back the Ingredients column so get back (in the above example) 1,4. What I now need to do is create a checkbox list for all rows in the Ingredients table but with row 1 (Sugar) and 4 (Apple) being marked as selected.

Obviously I then need to be able to store any changes when the form gets saved back to the database so in effect if I ticked 5 (Orange) as well then the saved form would write back 1,4,5 to the Ingredients field in the Product table.

My guess this has something to do with Checkbox Groups but having looked at the docs and the forum I just cannot see where to begin.

Can anyone give me a clue or point me in the right direction.

Thanks.

Nathan

I have just had a thought. If it would make it easier i could create a ProductIngredient link table as follows:

ID, ProductID, IngredientID

Would this be better or make it easier to get DF to do what I want?

I have thought that this may be an enhancement requirement as follows:

When Checkbox Group is selected and Question Options - SQL Driven is selected, you current have two columns that need to be returned which are QuestionOptionValue and QuestionOption.

What I would love to see is the addition of QuestionOptionSelected.

If the value returned (for example from a count(*)) is 0 then the checkbox would NOT be selected but if the value is greater than 0 then the value would be selected.

I guess their may already be a way of doing this but if not would you think about adding what I have said above?

Thanks.

Nathan,

You are abosluetly right, the solution is indeed the checkbox group. And yes this is possible, as just this morning, I accomplished the exact same thing, but instead of ingredient ID and Name, my checkbox list was DateID and Dates. Same difference, so what I did would most likely work for you. 

I posted a follow up on the thread I started for this same topic: http://www.datasprings.com/dnn-modu...box-group/

I know that this can be frustrating, but keep at it, and I will be glad to help further if needed.

Steve

I have it 99% working but I have the group box loading the items into the checkbox ground and when I save the results I have them being added back to the database. I cannot find any way to get the checkbox group to select checkbox items if they exist in the database table. This is why I recommended that an enhancement be made to the product to have a "QuestionOptionSelected" column as I could then do an SQL sub select clause in the query when producing the checkbox group. For example

select ID as QuestionOptionValue, Name as QuestionOption, (select count(*) from CheckboxSelectedTable where CheckboxID=$(DSParam1)) as QuestionOptionSelected from myTable

In effect the above would return something like:

QuestionOptionValue, QuestionOption, QuestionOptionSelected

1,Sugar,1
4,Apple,1

This would make it really easy to then be able to select checkboxs via the query to populate the checkbox group.

If anyone can tell me another way of doing this with DF that would be great, if not please please please can this be added to DF otherwise it makes it impossible to populate a checkbox group.

Thanks.

Hi Nathan,

Thanks for your information and so forth... We will add this enhancement to the list of future releases, I see what you are referring to and I do think it would be a nice enhancement (maybe a future patch or maybe 4.1/4.2). If you need something immediate we do offer Premium Support to escalate enhancements but for now, I will add it to the list to review for the future!

Thanks,

Chad

Hi Chad, this is a bit of a show stopper for the project I am working on as we have lots of instances where we would need to use checkboxes for cross linking different parts of the site based on input on forms. How do I find out more about Premium Support and what it is likely to cost. Thanks.

Hi Nathan,

Here's more information about Premium Support.
http://www.datasprings.com/support/...rt-options

Please use the Contact form and include the URL to this forum thread as a reference so we can prepare an estimate for you.

http://www.datasprings.com/contact-us


Thanks,
Candace

Thanks for the info. I am struggling a bit because the project that needs it has a fixed budget and looking at your premium support I would need to buy 6 hours and $90 an hour which is fair but a bit beyond the scope of this project. Can you confirm for me that what I am seeing is correct regarding the checkbox issue. For clarity I will explain simply: A combo group can be created in a form but their is no way to define those that are already selected in a database which would make it unusable for any type of edit form.

Thanks

Nathan

Hi Nathan,

Well there are a few features of the SQL Binding features that do not currently support a few field types (we are hoping they will for 4.1). These include File Upload Fields, Image Fields, Multi Selection Fields, etc... I know we had a client that did end up getting this to work for them but they had to end up using a client side event that would fire upon initial JavaScript. I think the JavaScript that we wrote for them would loop through a hidden field and then loop through another function call to populate the checkboxlist field.

Depending on your budget (we always require a minimum mostly because everything takes longer then a lot of people expect) , but if you can figure ouhttp://www.datasprings.com/desktopmodules/activeforums/themes/grey/save32.pngt a way to fit into the minimum we should be able to have the enhancement added within 4-7 business days from picking up the hours.

Thanks,

Chad

Thanks for the clarification. I will contact you regarding the premium support as I will need to get it done as the client is expecting it.

Nathan

Hi Steve,

I think he is needing a way to determine that specific items should be checked or not checked (upon loading via SQL Options). For example, right now you can load under SQL Options and choose a "Default" but depending on how custom the query is I don't believe there is a method/way to then choose the default (for example if your SQL Options used $(Tokens) there is no way to generate the list and find a default). Also, it might need to be more "Dynamic" as the default values might change etc...

Anyway... Nathan was able help push this along the enhancement line much faster as he picked up some Premium Support and I have spent the last couple of days adding the enhancement in... So thank you Nathan!

The feature will be in the next patch that I will be sending you shortly Nathan and the column name is called QuestionOptionSelected. Whenever you have this column and its set to -1 or "True" then the item (either checkboxlist, listbox, and/or radio button or combo box as well) will be selected upon runtime.

Thanks!

-Chad

For people who want to be able to set the selected items when using multiple checkboxes, it is now possible in the latest version of DF and I will explain how to use it.

Lets say that you have a Recipe table with the following columns: RecipeID, RecipeName, RecipeDescription but you want to store ingredient from a list of lots of ingredients. So you would have another table called Ingredient with the columns: IngredientID, IngredientName.

So for the purpose of example we would have the following records in the two tables:

RecipeID, RecipeName, RecipeDescription
1, Toffee Pie, Yummy rich toffee pie!

IngredientID, IngredientName
1, Orange
2, Sugar
3, Pasta
4, Toffee
5, Pepper

Ok now I am no chef but lets say that in a the toffee pie we are going to say that the ingredient should be Sugar and Toffee so we would have another table to store the link between the Recipe and the Ingredient tables so lets say we create a table called RecipeIngredientLink with the following columns: LinkID, LinkRecipeID, LinkIngredientID

So in the RecipeIngredientLink table we would have the following records:

LinkID, LinkRecipeID, LinkIngredientID
1, 1, 2
1,1,4

Now in DF we would create a form and on it have a Group Combobox and we would use the SQL option to populate the defaults and to flag the selected items when they are listed in the form.

So we are going to need to query to read all the ingredients (in case we want to add or remove an ingredient from the recipe) and we also need to query the link table to mark the selected items. For the sake of this example I am going to assume that you have a field called HiddenRecipeID that you have passed into the form. So the SQL you would need would be something like the following:

select IngredientID, IngredientID as QuestionOptionValue, IngredientName as QuestionOption,
(select case when count(*)=0 then 0 else -1 end from RecipeIngredientLink where LinkRecipeID=$(HiddenRecipeID) and LinkIngredientID=IngredientID) as QuestionOptionSelected
from Ingredient
order by IngredientName

So what is basically happening here is that we are reading in all of the ingredient and putting them in the group checkbox form element but we are then doing a sub query to read the link table to see if the number of records in that table for the recipe and the current ingredient is greater than 0 and if it is then the case statement returns -1 (true) otherwise it returns 0 (false) because the QuestionOptionSelected requires either a true or a false to say if the item is selected or not.

I have only tested this with the checkbox group but I understand that it is also available in the listbox as well and should work the same way.

Good luck!!

Nathan

Nathan,

I applaud your detailed explanation on how you got this to work with the changes in the latest release of DF. I know you must be feeling pretty good now that you have this working for you. Great Job!

My comment is more for Chad and is thus: I must be dense as I don’t understand why the need for the new implementation of QuestionOptionSelected. As I mentioned above, I was able to do this in the previous version of DF with only two queries.

One is a SQL statement that loads all of the options for the checkbox group, and is placed in the SQL Driven Options area for that checkbox group control. The second is a SQL statement that queries a view, which in Nathan’s case would filtered records by the $(RecipeID) parameter for a given recipe, and would return the recipe and its ingredients, with ingredients concatenated back into a string as such 1,3,4,7. This query is placed in the Initial SQL Query box for the form’s Initial SQL Rendering/Bind area of the module configuration.

As long as all of your field names in this second query use your form controls’ short names i.e. ingredientID, as soon as you load your form for a specific recipe, the appropriate ingredient check boxes (i.e. 1,3,4,7) are selected, and the rest are not. And all of this without the need for a case statement, albeit concatenating the ingredients values from multiple records back into a string for the SQL novice is no cake walk.

Don’t get me wrong, I think Chad and the rest of the gang at DataSprings do a fabulous job, which is remarkable considering the number of high quality modules they have developed and maintained. I could also see where this new implementation could come in handy for me in other scenarios and look forward to giving it a try. I just do not see why it was needed for Nathan’s recipe form.

Steve

Hi Steve,

Thanks for your comments and feedback. Yes I agree that you can use the feature already with a separated list for a SQL data bind however in many cases there really is a difference between a SQL bind and a default value etc.. There are situations such as question events to unhide those fields and you would like them defaulted etc...

So in some instances you can really implement a SQL Data Bind specific to those options but in other cases the requested feature to have a column which represents the values checked offers those implementations more control (such as maybe on page load when data binding is not enabled and you want to have SQL Driven options as well as a default value). You could have always specified a default value in the past as well but you can't always know what that default is because it could be data drive or because you can't test with $(Tokens) int he SQL Options area.

So... I understand what you are saying now but these are really separate features that can both be utilized at different times I suppose.

Thanks,


-Chad

Chad,

Thanks for the clarification.  I do agree, the new feature does add more functionality to the form and the ways in which they can be designed. As I mention above, I cannot wait to give the new feature a try.

I hope everyone at Data Springs has a great Holiday Season, and I look forward to what you and your crew develop in 2012.  Keep up the great work!

Best regards,

Steve