The idea is to have a column in a table with radio buttons that allow only one row within the entire column to be checked. For example for our webshop maintenance we have a table with employment types for which one is checked as the default for a certain customer. Tasks use this info to pre-fill the employment type in their forms. Having radio buttons instead of check marks would make it impossible to accidentally check more than 1 row.
Of course the application needs to do this based on the rows in view and not entire table because in this example the table holds the employment types of all customers.
- Warrior
- 63 replies
Can't you add an employment type field to the customer then? Then make this field a lookup to the employment type view with combo as control type. This way you can select only one item from the possibilities. The way I understand your explanation that would perfectly fulfill your needs
- Hero
- 155 replies
Absolutely but that's already the case. The check mark we have in that table now is only to indicate the default employment type which gets prefilled into certain forms. The user can always change it in those forms via the lookup.
But now it's possible to select more than 1 default in the employment type list which is of course undesirable. I know this could be solved with a task to always select only 1 default and by making the check marks themselves readonly (but visible as confirmation for the user) but I think in this context having radio buttons in a table list instead of check marks could actually work really well.
But now it's possible to select more than 1 default in the employment type list which is of course undesirable. I know this could be solved with a task to always select only 1 default and by making the check marks themselves readonly (but visible as confirmation for the user) but I think in this context having radio buttons in a table list instead of check marks could actually work really well.
- Warrior
- 63 replies
Ah I understand.
I think somewhere in our software we made a similar solution where we use a trigger, which checks the inserted table for any enabled checks and then makes sure only the latest (newly) checked item stays checked.
But seeing as that could be considered a bit of a "duck-tape" solution, I now agree with your idea. Although I do wonder what would happen if you have a subject where you can see all employment types for all users... then how would you make sure all defaults keep being selected instead of being forced to just one
I think somewhere in our software we made a similar solution where we use a trigger, which checks the inserted table for any enabled checks and then makes sure only the latest (newly) checked item stays checked.
But seeing as that could be considered a bit of a "duck-tape" solution, I now agree with your idea. Although I do wonder what would happen if you have a subject where you can see all employment types for all users... then how would you make sure all defaults keep being selected instead of being forced to just one
- Hero
- 155 replies
That's a valid point of course. Perhaps the radio buttons could somehow be defined as a group just like you would with a trigger or a task. Such that when you click a radio button it only triggers it within its own group (within the current customer id in this example).
+5It might be a bit dangerous to have the UI be responsible for integrity rules such as having only one default employment type for a customer. If the employment types are inserted or updated in any other way in the future (a task, batch import, API), these UI rules will not have effect.
Having an additional (filtered) unique index in the back-end to help out could would. However, this would make it a bit tricky as well, as it would require the UI to have both rows to be updated at the same time to not break the constraint. It would require tasks and API callers to either do the same or first uncheck the previous default employment type, then mark or insert a new default employment type.
When it comes to enforcing data integrity rules and constraints, a trigger isn't a bad solution in my opinion.
Assuming the table looks something like this:
The logic for both insert and update would look something like this and would keep the data correct in all scenarios:
When it comes to maintenance of this code - If a structure like this is often present in your application, it could be interesting to try and generate this logic dynamically based on occurrences of this structure in the model, identifying tables that have an unique checkbox in a certain context.
A parameterized version of this template would look something like this:
Keep in mind that the template above would only work for single context columns and single primary key columns. It would need some tuning to also support other scenarios.
Having an additional (filtered) unique index in the back-end to help out could would. However, this would make it a bit tricky as well, as it would require the UI to have both rows to be updated at the same time to not break the constraint. It would require tasks and API callers to either do the same or first uncheck the previous default employment type, then mark or insert a new default employment type.
When it comes to enforcing data integrity rules and constraints, a trigger isn't a bad solution in my opinion.
Assuming the table looks something like this:
The logic for both insert and update would look something like this and would keep the data correct in all scenarios:
code:
-- Check if a multiple employment_types were inserted or updated to be default in a single statement.
-- If so, abort the action.
if exists(
select 1
from inserted i
where i.default_employment_type = 1
group by customer_id
having count(*) > 1)
begin
exec tsf_send_message 'only_one_default_employment_type_allowed', null, 1
rollback transaction
return
end
-- Check if there is an existing employment_type set to default for this customer
if exists(select 1
from inserted i
join customer_employment_type e
on e.customer_id = i.customer_id
where i.default_employment_type = 1
and e.default_employment_type = 1
and e.employment_type i.employment_type)
begin
-- If so, update the existing employment_type to be no longer default
update e
set e.default_employment_type = 0
from inserted i
join customer_employment_type e
on e.customer_id = i.customer_id
where i.default_employment_type = 1
and e.default_employment_type = 1
and e.employment_type i.employment_type
end
When it comes to maintenance of this code - If a structure like this is often present in your application, it could be interesting to try and generate this logic dynamically based on occurrences of this structure in the model, identifying tables that have an unique checkbox in a certain context.
A parameterized version of this template would look something like this:
code:
-- Check if multiple [TAB_ID] records were inserted or updated to be [CHECKBOX_COL_ID] in a single statement.
-- If so, abort the action.
if exists(
select 1
from inserted i
where i.[CHECKBOX_COL_ID] = 1
group by [CONTEXT_COL_ID]
having count(*) > 1)
begin
exec tsf_send_message 'only_one_[CHECKBOX_COL_ID]_allowed', null, 1
rollback transaction
return
end
-- Check if there is an existing [TAB_ID] set to [CHECKBOX_COL_ID]
if exists(select 1
from inserted i
join [TAB_ID] e
on e.[CONTEXT_COL_ID] = i.[CONTEXT_COL_ID]
where i.[CHECKBOX_COL_ID] = 1
and e.[CHECKBOX_COL_ID] = 1
and e.[PRIMARY_KEY] i.[PRIMARY_KEY])
begin
-- If so, update the existing [TAB_ID] to be no longer [CHECKBOX_COL_ID]
update e
set e.default_employment_type = 0
from inserted i
join [TAB_ID] e
on e.[CONTEXT_COL_ID] = i.[CONTEXT_COL_ID]
where i.[CHECKBOX_COL_ID] = 1
and e.[CHECKBOX_COL_ID] = 1
and e.[PRIMARY_KEY] i.[PRIMARY_KEY]
end
Keep in mind that the template above would only work for single context columns and single primary key columns. It would need some tuning to also support other scenarios.
- Hero
- 155 replies
Anne Buit wrote:
It might be a bit dangerous to have the UI be responsible for integrity rules such as having only one default employment type for a customer. If the employment types are inserted or updated in any other way in the future (a task, batch import, API), these UI rules will not have effect.
I understand the importance of data integrity as well as your concerns around ensuring it. In our case it would be ok because those employment types are maintained through the application only. In effect this could be compared with declaring a field mandatory through a procedure while it's still allowed to have a null value on the database. For example we apply this extensively in another part of the application where the sets of mandatory, optional and disallowed fields depend on the value inside a 'type' field in the same table. None of this is enforced by the data model itself because the specific screen is used internally in the organization only, and only through an official Thinkwise gui (Windows and web).
Having an additional (filtered) unique index in the back-end to help out could would. However, this would make it a bit tricky as well, as it would require the UI to have both rows to be updated at the same time to not break the constraint. It would require tasks and API callers to either do the same or first uncheck the previous default employment type, then mark or insert a new default employment type. When it comes to enforcing data integrity rules and constraints, a trigger isn't a bad solution in my opinion. (...)
That's real interesting info that I will definitely look into further. Thank you. 🙂
1 person likes this
+9Updated idea status Open → Closed
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.