Checking more than one checkbox in a check field

[Robert_Huber]

Hi Ilyse, Jan

Yes, Ilyse is right, I would like to use a displayType TEXT_FIELD for the attributes attribute, because I would like to display that in a table view (or list view) in a single row with a standard hight of 19 pt. If I use it in a TEXT_AERA field, it would not horizontally display A H M (or better AHM), but vertically and I don’t want that. Hope it’s understandable what I mean…

Best regards, Robert

kazar:

Jan Blok:

Robert Huber:
A minor display problem for me to solve is eliminating the spaces between the characters (H M A should be AHM))

You can use:
dataprovider = Utils.stringReplace(dataprovider,‘\n’,‘’) to remove the newlines

Hi Jan – I think the problem Robert was referring to is not blank lines… but rather he was expecting to see a string because he was using a text-formatted field instead of textarea.

Robert Huber:
…not sorted…

In Servoy 3.0 we do sort the values, to overcome a search problem Enrico pointed out.

Yay! Does the query automatically add the necessary %'s so that users can do a simple form-based query on a form that includes checkboxes, or will we be writing methods for searching checkboxes?

kazar

[Robert_Huber]

Hello Ilyse

This is not boring at all (at least not for me), I was only away for two days so did not know what’s going on on this list .-)

You are absolutly right, but the problem is, in the join table are the actual assignments of (checkbox) selected values, like

Schmerlat - A (Airport)
Schmerlat - F (Finish)
Schmerlat - H (Home)

whereas the value list has more entries as the actual content of the join table (per waypoint). So, the value list is now on the attribute attributes on the join table, right?

Best regards, Robert

kazar:
I hope I’m not boring anyone, sitting here talking to myself about checkboxes. ;^}

But Robert’s question started me off on a chain of investigation/thinking/learning about checkboxes in a Servoy/SQL environment. Here are two more tidbits

Robert had asked:

A minor display problem for me to solve is eliminating the spaces between the characters (H M A should be AHM)

You must have the field on the right formatted with text display. Choices made in a checkbox field are entered into the data field (in what seems like arbitrary order) separated by line-return characters … i.e., a return-delimited list is what results. If you change the field on the right to Text Area display you’ll see what I mean.

if displayed in a text field (see Attributes text field on the right on the picture) and how to sort order that field alphabetically

You could probably handle the return-delimited list as an array and use a method attached to the checkbox field that uses a string function to sort the values… (I don’t know, just guessing) … HOWEVER, your desire to see the values sorted only points us back to your original observation that checkbox fields break the first normal form. If you want to work with, manipulate, report on or do much more than just look at the selections, use a fully relational approach instead.

For example, if you normalized the structure and stored each checkbox choice in its own row in a table you could then …
… create a list form in this valueChoices table and attach an onShow method that loads all records & then sorts them
… make the choiceDescription field on this form non-editable and attach a method that creates a related record if none exists for that choice, and that deletes a related record if it does exist (i.e., your method would act as a “toggle” to “turn the selection on and off”)
… define some kind of unstored calc to apply highlighting or even a checked box graphic to the rows on the list form, depending on each row’s “selected” or “non-selected” status
… put the valueChoices list form into an unrelated tab panel on the form the user will be working in

[[now I bet David W is about to post something that makes this approach look way overdone! (in fact, I hope he does)]]

No matter how much extra work this might look like up front compared to simply formatting a checkox field to display a value list, by sticking with normalized data you will make your life (and your users’ lives) SOOOO much easier down the road.

For example, check a couple of boxes in your checkbox field, go into Find mode, and try finding all records that have one of the choices checked. Only records that have ONLY that choice will show up, when the user would expect that ANY record that has that choice checked (plus perhaps others checked as well) would be returned. Using your screenshot as an example, if you entered Find mode and checked the “A Airport” box, you would expect that record to turn up, but it won’t. (See Finds on Checkboxes - How get Servoy to act like FMP? - Classic Servoy - Servoy Community) So to allow users to perform finds on checkbox fields, you will be writing & maintaining a rather complex onSearch method…

Or try generating a report that summarizes a count of each value-list choice. A snap with a related table that stores each selected value in a row; and significantly more complex to build the same report with return-delimited checkbox choices.

I’ve made a little start on a suggested UI for providing a checkbox-like UI within a normalized structure. If I get anywhere with it, I’ll post it as my first Servoy demo!

kazar

[Robert_Huber]

Hi Jan

So I request it. Yes, it should be available as you say via something like n:m relationship. It is always a major (unsolved) problem with tools like Serrvoy (database backend tools).

Thank you and best regards, Robert

Jan Blok:
We did think about making the normalized approach possible via something like an N-M relation, but so far no one reqested this.

[Jan_Blok]

Robert Huber:
Hello Jan

Ok, sorting comes in Version 3, that’s ok (for me).
A stupid question: What is dataprovider = Utils.stringReplace(dataprovider,‘\n’,‘’)? Is it a calculation field or a method (on this form) or ??? My attribute with that “H M A” content (for example) is named attributes (quite confusing, I know, but that’s how the originator of that instrument called it so I don’t change it).

In the editor there is a node utils, it contains functions to make your live easier, you pass a string, texttoreplace,replacetext and it return the corrected text

[Robert_Huber]

Hi Jan

Jan Blok:

Robert Huber:
Hello Jan

Ok, sorting comes in Version 3, that’s ok (for me).
A stupid question: What is dataprovider = Utils.stringReplace(dataprovider,‘\n’,‘’)? Is it a calculation field or a method (on this form) or ??? My attribute with that “H M A” content (for example) is named attributes (quite confusing, I know, but that’s how the originator of that instrument called it so I don’t change it).

In the editor there is a node utils, it contains functions to make your live easier, you pass a string, texttoreplace,replacetext and it return the corrected text

Ok, I got it working. The problem is, it doesn’t help, because if the the \n is replaced with utils.stringReplace, the previously set values via the checkbox field are also gone, not exactly what one wants .-) I assume because the \n is absolutly necessary for Servoy to recognice the various (0 … n) checkboxes set. So this way doesn’t work as intended.

Best regards, Robert

[kazar]

Robert Huber:
Ok, I got it working. The problem is, it doesn’t help, because if the the \n is replaced with utils.stringReplace, the previously set values via the checkbox field are also gone, not exactly what one wants .-)

Hi Robert – You had said in a previous post that you wished to use a TEXT_FIELD field (not TEXT_AREA) to display the checkbox-field selections, so that you could display it in table view at a standardized 19 pts. So let’s look at this as a display issue. IOW, leave the data-storage format as the line-return delimited format (the native checkbox-field storage format). This will not interfere with the boxes appearing to be checked when on a form view for that table. Now treat the display format requirement for your table view as a separate issue rather than trying to solve storage requirements and display requirements with the same answer.

For example, create a second field to store the results of Utils.stringReplace(dataprovider,‘\n’,‘’), or write an unstored calc to achieve the same visual result.

Would that work?

kazar

[Robert_Huber]

Hello Ilize

Yes, your suggestions help, in fact, under the given circumstances I think they are the best possible solution; but more and more I see, the only good solution is to implement this sort of values as we discussed it before, in a join table where on on end sit all the popup values (to access) and in the join table are the real pairs of selected values. Otherwise it’s always a work around in one way or the other. As Jan lok said no one requested that feature up to now I requested it but did not hear anything any more. Hopefully be some day we have it

Thanks a lot and best regards, Robert

kazar:

Robert Huber:
Ok, I got it working. The problem is, it doesn’t help, because if the the \n is replaced with utils.stringReplace, the previously set values via the checkbox field are also gone, not exactly what one wants .-)

Hi Robert – You had said in a previous post that you wished to use a TEXT_FIELD field (not TEXT_AREA) to display the checkbox-field selections, so that you could display it in table view at a standardized 19 pts. So let’s look at this as a display issue. IOW, leave the data-storage format as the line-return delimited format (the native checkbox-field storage format). This will not interfere with the boxes appearing to be checked when on a form view for that table. Now treat the display format requirement for your table view as a separate issue rather than trying to solve storage requirements and display requirements with the same answer.

For example, create a second field to store the results of Utils.stringReplace(dataprovider,‘\n’,‘’), or write an unstored calc to achieve the same visual result.

Would that work?

kazar

[john.allen]

Hi Robert,

Couldn’t you do this all of this already with a related table, including display as a text box, querying for ‘matches’, displaying only ‘checked boxes’? I’m probably missing something here but how about:

Main table ‘person’ say has primary key of person_id.
Secondary table ‘interests’ has primary key of interests_id and foreign key of person_id. Secondary table also has text column ‘interest’ and a integer column ‘check’.

Whenever a new record in the main table is created, automatically m number of child records are created in the interests table where m will equal however many different ‘interests’ (check box values) are available. All these new values will be linked to the persons table via person_id. What remains is to decide how to have the ‘check’ column be defined. Possibilities are to automatically fill them all on creation with zeros (i.e. the ‘box’ display is unchecked) and then when checked they become ones (checked). Alternatively they are all set to null at the start and then the check boxes (or radio buttons in this case) become ‘yes’ and ‘no’. The first way means that the display can be exactly as you first outlined it. The downside is that say one person has all zeros checked for their interests. Then you don’t know for sure whether they really don’t have any interests or whether someone just forgot to fill that out. Of course you have that same problem with a ‘regular’ check field too. In either case you then have all the possibilities of searching (how many have interest x? how many have exactly two interests? how many have interest x and interest y and do not have interest z?). All of these can be done in a very simple and straight forward manner. And at the same time their ‘choices’ can be displayed in any manner you wish (as original check boxes, as ‘horizontal’ concatenated text, etc.).

John

[Robert_Huber]

Hi John

Sorry for the delay of my answer. I have some questions to your proposed solution, see below:

john.allen:
Hi Robert,

Couldn’t you do this all of this already with a related table, including display as a text box, querying for ‘matches’, displaying only ‘checked boxes’? I’m probably missing something here but how about:

Main table ‘person’ say has primary key of person_id.
Secondary table ‘interests’ has primary key of interests_id and foreign key of person_id. Secondary table also has text column ‘interest’ and a integer column ‘check’.

I undestand your idea here, but I have a third table with all possible value list values in it, in this example all column interest values.

john.allen:
Whenever a new record in the main table is created, automatically m number of child records are created in the interests table where m will equal however many different ‘interests’ (check box values) are available. All these new values will be linked to the persons table via person_id.

I dont’ understand why you say the child records (records in table interests) would be created automatically. In my thinking, with three tables (in this example I would make PERSONS, PERSONS_INTERESTS, INTERESTS), I would assign the interests of a person in the link (join, i. e. PERSONS_INTERESTS) table by adding a new record for each interest of this person, i. e. combine person_id, interest_id for all interests of this specific person (e. g. person has 3 interests would give 3 records in join table.

john.allen:
What remains is to decide how to have the ‘check’ column be defined. Possibilities are to automatically fill them all on creation with zeros (i.e. the ‘box’ display is unchecked) and then when checked they become ones (checked). Alternatively they are all set to null at the start and then the check boxes (or radio buttons in this case) become ‘yes’ and ‘no’. The first way means that the display can be exactly as you first outlined it. The downside is that say one person has all zeros checked for their interests. Then you don’t know for sure whether they really don’t have any interests or whether someone just forgot to fill that out. Of course you have that same problem with a ‘regular’ check field too. In either case you then have all the possibilities of searching (how many have interest x? how many have exactly two interests? how many have interest x and interest y and do not have interest z?). All of these can be done in a very simple and straight forward manner. And at the same time their ‘choices’ can be displayed in any manner you wish (as original check boxes, as ‘horizontal’ concatenated text, etc.).

John

I would not need a check attribute in my thought solution, the join table PERSONS_INTERESTS would just give all the values. The problem is: How to display many records in one checkbox field. I think this is the underlying problem not possible in Servoy (up to now .-)

Best regards, Robert

[john.allen]

Hi Robert,

I get it. You really are after a check box on a join table with a many to many relationship. I was thinking of a simple one to many relationship where the ‘many’ are simply the person’s interests. And I think in some ways that still might be the best way to deal with it. Really it is not a true many to many situation. The relationship is really one person who can choose from a limited number of interests. The list of interests might change slightly over time but it is really only the ‘person’ table that is truly dynamic. And the fact that you are talking about a checkbox means that there can’t be very many interests to choose from or too much screen real estate would be needed to display it. But the relationship is really still one to many. The problem is more of displaying than of relationship. To me, making a many to many relationship and using a join table in between, needlessly complicates it.

Let’s say you have 8 interests to choose from. Everytime a new ‘person’ row is created, 8 new rows are created in the interests table with that personid. (When I say automatically, I mean that you set that up programmatically but it then happes automatically based on that set up). Those 8 records can be displayed in any way (horizontally or vertically) in the patient table and each one has a ‘check box’ next to it. Interests are checked. On leaving the form any unchecked interests are deleted.

Alternatively, create the new person. Then a series of global fields with check boxes and embedded methods create the ‘checked’ interests in the interests table with that personid. It could also be done in a similar manner with unstored calculations. In either case when returning to that form only the ‘checked’ interests would show as ‘checked’ boxes and only the checked interests would exist for that personid in the interests table.

Finally, you could just give one global text field set to display as a check box with a value list attached of the various interests. After checking the various interests, programmatically you would then use those values to populate the interests table with that personid and selected interests. For the most part check boxes are only used on data entry. After selection one isn’t interested in displaying the ‘non-selected’ choices as that takes up screeen real estate. If need be however you could ‘repopulate’ such a check box later if the interests change. At that point though I would recommend just using a portal or tabpanel to display the related interests for editing.

Does this make any sense? My main point here though is that there are many ways to display this as you would like if you view it as a one to many relationship. Many to many relationships in my mind are really when both sides are truly dynamic and continually growing say like in a teacher/student relationship where a third ‘join’ table is the class that they share.

[Robert_Huber]

john.allen:
Hi Robert,

I get it. You really are after a check box on a join table with a many to many relationship. I was thinking of a simple one to many relationship where the ‘many’ are simply the person’s interests. And I think in some ways that still might be the best way to deal with it. Really it is not a true many to many situation. The relationship is really one person who can choose from a limited number of interests. The list of interests might change slightly over time but it is really only the ‘person’ table that is truly dynamic. And the fact that you are talking about a checkbox means that there can’t be very many interests to choose from or too much screen real estate would be needed to display it. But the relationship is really still one to many. The problem is more of displaying than of relationship. To me, making a many to many relationship and using a join table in between, needlessly complicates it.

Yes, that’s right. At first, it may seem a bit overhead, because, as you say, there are usually only a few values to choose from. But, as Ilyse explained in this thread (some messages up), what if you are deleting one of the choices. If it’s a one to many to one (where the interests are) if you delete one interest or rename one, all others are deleted or renamed appropriatry. If there is only a one to many construct, it doesn’t work well (at all) if you rename or delete an interest entry. You have manually to delete/rename all entries on the many side. So the user never can change (delete, rename, add) an interest with this construct, and if something changes, the developer has to do it. So it’s a developer decision how to do, isn’t it?

Best regards, Robert

john.allen:
Let’s say you have 8 interests to choose from. Everytime a new ‘person’ row is created, 8 new rows are created in the interests table with that personid. (When I say automatically, I mean that you set that up programmatically but it then happes automatically based on that set up). Those 8 records can be displayed in any way (horizontally or vertically) in the patient table and each one has a ‘check box’ next to it. Interests are checked. On leaving the form any unchecked interests are deleted.

Alternatively, create the new person. Then a series of global fields with check boxes and embedded methods create the ‘checked’ interests in the interests table with that personid. It could also be done in a similar manner with unstored calculations. In either case when returning to that form only the ‘checked’ interests would show as ‘checked’ boxes and only the checked interests would exist for that personid in the interests table.

Finally, you could just give one global text field set to display as a check box with a value list attached of the various interests. After checking the various interests, programmatically you would then use those values to populate the interests table with that personid and selected interests. For the most part check boxes are only used on data entry. After selection one isn’t interested in displaying the ‘non-selected’ choices as that takes up screeen real estate. If need be however you could ‘repopulate’ such a check box later if the interests change. At that point though I would recommend just using a portal or tabpanel to display the related interests for editing.

Does this make any sense? My main point here though is that there are many ways to display this as you would like if you view it as a one to many relationship. Many to many relationships in my mind are really when both sides are truly dynamic and continually growing say like in a teacher/student relationship where a third ‘join’ table is the class that they share.

[john.allen]

Hi Robert,

But, as Ilyse explained in this thread (some messages up), what if you are deleting one of the choices. If it’s a one to many to one (where the interests are) if you delete one interest or rename one, all others are deleted or renamed appropriatry. If there is only a one to many construct, it doesn’t work well (at all) if you rename or delete an interest entry. You have manually to delete/rename all entries on the many side. So the user never can change (delete, rename, add) an interest with this construct, and if something changes, the developer has to do it. So it’s a developer decision how to do, isn’t it?

This comes up all the time in the work that we do. Over the last 15 years for example the ‘diagnosis’ codes for lymphoma have changed three times as diagnostic techniques have become more sophisticated. Sometimes we want to ‘delete’ a diagnosis code, sometimes we want to simply ‘deactivate’ a code and sometimes we want to change a code. Those in turn have to get reflected in the patient record. We also want the datamanagers to be able to change the ‘allowed’ values directly themselves. Sometimes we do this through a many-to-many relationship with a join table, sometimes we do it with a simple one-to-many relationship with another ‘allowed-value’ table that simply determines what the allowed values (a valuelist essentially) can be.

In the example Ilyse started, we can still have a one to many relationship between two tables (one person can have many interests: person.personid = interests.personid). We can then have a third table that simply stores the various allowed values (interests) - a relationship though isn’t really necessary. In that ‘interests-values’ table besides the interest column there can be an ‘active’ column which means whether that particular interest is active/current (and can also describe when it was active or the parameters involved if it is not currently active). The current/active interests make up our valuelist which I would tie to a global field with a check box display for choosing that person’s interests. After selection those chosen interests populate that person’s related values in the ‘person-interests’ table.
What happens when an interest gets deleted, ‘de-activated’ or changed? The way I like to handle that is to have two triggers on the ‘interests-value’ table for delete and update. I like backend database triggers more because that means that referential integrity is maintained no matter how changes are done (Servoy, direct queries, some other application, etc.) and you don’t have to recreate the wheel in each application. In our case if an interest gets deleted or changed then that in turn forces an update or delete in the interests table (where personid is the foreign key to the person table). I would also store in that person-interests table the ‘active’ status of the interest so that will get updated too whenever the ‘active’ status column gets updated in the ‘interests-allowed’ table via the trigger. Then you can have different relationships using the personid as well as the ‘active’ status to show a person’s interests.
This approach (whether using a true many-to-many relationship or the one described above) would allow you to have everything I think you have mentioned in this thread:

1. A checkbox selection process (valuelist is made up of the ‘active’ rows of the interests-allowed table whether that table is related or unrelated to the person-interest table. Valuelist is tied to a global field with an onDataChange method which parses the chosen values out and creates the relevant rows in the person-interest table)
2. Any type of display that you want for ‘chosen’ selections (Get the values out of the related person-interest table in any form you want either via relationship(s) or unstored calcs. Show this vertically or horizontally and in any order you establish in a record, table or list view).
3. Updating/changing/deleting of interests by the users (via triggers on the ‘interests-allowed’ table).

Wouldn’t that work for you in this case?

John

[Robert_Huber]

Hi John

Yes, your suggestions make a lot of sense and made me think about when do I need what of all this mentioned variants of the same problem.
I have to find out what situations I have and when has which solution which strengths and weaknesses. I will come back to this subject when I am a step further.

Thanks for your detailed describtion and all your very good ideas, i will try them!

Best regards, Robert

john.allen:
Hi Robert,

I get it. You really are after a check box on a join table with a many to many relationship. I was thinking of a simple one to many relationship where the ‘many’ are simply the person’s interests. And I think in some ways that still might be the best way to deal with it. Really it is not a true many to many situation. The relationship is really one person who can choose from a limited number of interests. The list of interests might change slightly over time but it is really only the ‘person’ table that is truly dynamic. And the fact that you are talking about a checkbox means that there can’t be very many interests to choose from or too much screen real estate would be needed to display it. But the relationship is really still one to many. The problem is more of displaying than of relationship. To me, making a many to many relationship and using a join table in between, needlessly complicates it.

Let’s say you have 8 interests to choose from. Everytime a new ‘person’ row is created, 8 new rows are created in the interests table with that personid. (When I say automatically, I mean that you set that up programmatically but it then happes automatically based on that set up). Those 8 records can be displayed in any way (horizontally or vertically) in the patient table and each one has a ‘check box’ next to it. Interests are checked. On leaving the form any unchecked interests are deleted.

Alternatively, create the new person. Then a series of global fields with check boxes and embedded methods create the ‘checked’ interests in the interests table with that personid. It could also be done in a similar manner with unstored calculations. In either case when returning to that form only the ‘checked’ interests would show as ‘checked’ boxes and only the checked interests would exist for that personid in the interests table.

Finally, you could just give one global text field set to display as a check box with a value list attached of the various interests. After checking the various interests, programmatically you would then use those values to populate the interests table with that personid and selected interests. For the most part check boxes are only used on data entry. After selection one isn’t interested in displaying the ‘non-selected’ choices as that takes up screeen real estate. If need be however you could ‘repopulate’ such a check box later if the interests change. At that point though I would recommend just using a portal or tabpanel to display the related interests for editing.

Does this make any sense? My main point here though is that there are many ways to display this as you would like if you view it as a one to many relationship. Many to many relationships in my mind are really when both sides are truly dynamic and continually growing say like in a teacher/student relationship where a third ‘join’ table is the class that they share.