Checkbox value storage (multiple) and retrieval in read-only

Question

It seems this question keeps poping up. But I ll keep it simple: 
 1. What is the best way to store checkbox values, when multiple choices are selected?

2. Is it possible to show such record on a read only checkbox? 
 I also checked Checkbox Component - Appian 21.2 , but I could not find the information I am looking for. 
 I also tried changing the DB and CDT from integer to varchar/text.

Additional topics I looked at: 
 Appian Community 
 Appian Community 
 Appian Community 
 Appian Community 
 Kind regards,

Mike Schmitt · Accepted Answer

I see you're converting the values to/from a single String. I'd strongly suggest, instead of using a bespoke format (joining by an arbitrary character '#' then splitting by the same), you simply make use of the built-in JSON functions and store the resulting string as JSON, since JSON has built-in routines to cleanly handle this in varieties of cases. 
 e.g. a!localVariables(
 local!selectedOptions: a!toJson({}),
 {
 a!checkboxField(
 label: "Checkboxes",
 labelPosition: "ABOVE",
 choiceLabels: {"Option 1", "Option 2", "Option 3", "Option 4"},
 choiceValues: {"Option 1", "Option 2", "Option 3", "Option 4"},
 value: a!fromJson(local!selectedOptions),
 saveInto: {
 a!save(
 local!selectedOptions,
 a!toJson(a!defaultValue(save!value, {}))
 )
 },
 validations: {}
 ),
 
 a!paragraphField(
 label: "DEBUG",
 value: local!selectedOptions,
 disabled: true()
 )
 }
)

ajhick · Answer

Mike's use of JSON is great here as it does get around a lot of problems such as your delimiter (in this example it was "#") being one of the values you want to capture and I'd certainly suggest his approach over mine. One thing to keep in mind though is that JSON will add a lot of extra characters so your validations need to account for this. For example, if your field in the database can only take 255 characters even though you may only be saving 200 characters that will be actually displayed to your end user this will certainly exceed the 255 limit. 
 Personally I've found that in the long run it's just easier to reflect the relationship that exists in the database. It does take a bit to get your head around but once you are across relational databases, entity relationship diagrams and normalisation then you save a lot of potential future headaches with negligible upfront cost. There are always exceptions but as a rule this is the approach I take. 
 One common example is customers and contact details. Let's say you have a `Customer` table and in it you want to capture Home Phone, Mobile, Business Phone and Email Address. This is simple enough right? Just have a column for each of these. Done! 
 
 But what if you want to mark which one is the primary contact? Ok, add a primary contact column and specify the primary there. What about all the NULLs? You've set up a table and made columns that you know is going to have A LOT of NULLs, this isn't great for storage or performance (at scale). What if someone has multiple email addresses? As time goes on, what if someone has none of these and is only contactable via Instagram? Are you going to add an Instagram column? What about knowing the history of contact details for a customer? This would of course not be needed for every use case but it is for some. What you end up with is a `Customer` table with a significant number of columns dedicated to contact information (I haven't even touched mailing and physical addresses). For sure, contact details are important to a customer but it isn't the customer themselves. This information would be better capture in a `Contacts` table with a one to many relationship. One customer has many contacts. 
 
 This is significantly more flexible and means your application can adjust to future requirements much more easily. 
 To be honest, I've yet to dive into Appian's new-ish low code data capabilities but this doesn't change (as far as I know!) the way we should think about data and how one entity relates to another (Eg. One customer has many contacts). Many things can change about an application but generally the fundamental relationships don't. If you get these right, you're setting yourself up for long term success.

ajhick · Answer

Try converting this to your needs 
 a!localVariables(
 local!selectedOptions,
 {
 a!checkboxField(
 label: "Checkboxes",
 labelPosition: "ABOVE",
 choiceLabels: {"Option 1", "Option 2", "Option 3", "Option 4"},
 choiceValues: {"Option 1", "Option 2", "Option 3", "Option 4"},
 value: if(
 a!isNullOrEmpty(local!selectedOptions),
 {},
 split(local!selectedOptions, "#")
 ),
 saveInto: {
 a!save(
 local!selectedOptions,
 joinarray(save!value,"#")
 )
 },
 validations: {}
 ),
 a!paragraphField(
 value: local!selectedOptions,
 )
 }
)

ajhick · Answer

I wouldn't say this has just one answer. You can save it in many different ways depending on your exact use case but what I would say is I've seen many times that people box themselves into needing to save multiple values into the one database field which would then require the use of joinarray() to condense the values into one string and split() to turn it into an array again (Basically what's in here ). 
 I'd say this isn't ideal but obviously depends on your data design on how practical it is to create a many to one table. You'd also have to consider any reporting or views you'd need from this data in the database (if any) as although it's possible it can be problematic to split multiple values from one field. 
 I'm interested in others views but the "best" way would probably be to have the options driving the checkbox selections in a table in the database and then have a many to many relationship set up to join your record to the reference table. 
 For question 2, there's always disabled but if you play around with a!richTextDisplayField() you could create a cleaner version.

Mike Schmitt · Answer

sebastians0002 said: Can a single checkbox be split onto 4 collums in a database? In essence if you had 4 x checkbox, with a single checkbox and you would save them each of them in a single collumn? 
 This depends on how flexible you need your data structure to be. 
 If you're 100% sure that you will always have these exact 4 "additional request" types, with no (or very, very few) additions or subtractions, then each of these could be represented in their own column of your DB table (or maybe a helper table set up specifically to handle "additional request" info). To handle this in your CheckboxField on-form, your SaveInto could do most or all of the work by manually iterating over each CDT field, checking whether the save!value array contains the corresponding item, then saving a TRUE or FALSE value into that field. The definitive drawback to this plan is that any future changes in this structure would require manual changes to the form's SaveInto, which of course also requires someone to remember that such a change is needed in that particular spot (which is never as easy as it sounds). 
 So on the other hand, if you would potentially need more flexibility than that, like different "additional request" types to be shown or not shown under different circumstances, and/or some slow but steady turnover of "types" where new ones are added every so often and older ones go away sometimes, then you would likely want to set up some sort of link table where you add a small row to that table linking to a "additional request type ID" (where the ID is the primary key ID of the lookup table containing your Additional Request Types), as well as a "user ID" (in other words the primary identifier of whatever person/user/entity the requested type belongs to). On your form you'd have the checkbox save the IDs into an integer array, then in your process you'd assemble those selections into a CDT array corresponding to the link table, then write those entries to the DB.

GopalK · Answer

Hi, 
 For question 1, when you select multiple values then it will be saved as colon separated values in db. if you query this value from database , then you would need to again convert into list of values . 
 for Question 2. You can use Disabled parameter.

Checkbox value storage (multiple) and retrieval in read-only

Top Replies