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

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.

Thanks.

I get what you wrote as I pasted:

however I am getting this error.

Can you post your code?

a!checkboxField(
  label: "I need:",
  labelPosition: "ABOVE",
  choiceLabels: {"Travel tickets", "Hotel reservation", "Event fee", "Advance"},
  choiceValues: {"Travel tickets", "Hotel reservation", "Event fee", "Advance"},
  value: ri!record.additionalrequests,
  saveInto: ri!record.additionalrequests,
  validations: {}
)

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,
    )
  }
)

Thank you Sir, that worked. I am correctly displaying in on the next read only interface.

There was an error, if I checked some options and then unchecked everything, so I had to slightly fix it.

I used the following code:

a!checkboxField(
  label: "I need:",
  labelPosition: "ABOVE",
  choiceLabels: {"Travel tickets", "Hotel reservation", "Event fee", "Advance"},
  choiceValues: {"Travel tickets", "Hotel reservation", "Event fee", "Advance"},
  value: if(
    a!isNullOrEmpty(ri!record.additionalrequests),
    {},
    split(ri!record.additionalrequests, "#")
  ),
  saveInto: a!save(ri!record.additionalrequests,if(isnull(save!value), save!value, joinarray(save!value,"#"))),
  validations: {}
)

How would I go about the other way, saving it in tables of a database?

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?

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()
    )
  }
)

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.

Thanks for the overview of the design of such solution. This will help in trying it out for myself in practice.

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.

Thank you ajhick for the extensive answer!

Thank you ajhick for the extensive answer!