Checkbox with Multiple Values

[Pizzadude]

I am looking for the best way to store multiple values from a checkbox, so I can use them later to filter access to records.

I have a parent UX called CompanyInfo, bound to a MySql table of the same name.

I have a child UX named Users, bound to a table of the same name.

In the parent UX, I have a repeating section, bound to a table called Groups.

So in the parent UX I can add as many Groups as I wish, and name them whatever I choose.

So far, so good. Think of the Company as a Realtor, and the Users as agents. The Groups are all the different offices, Dallas, Denver and New York.

In the Users UX, I want to be able to assign a User to one OR MORE offices.

In the Users UX, I need to store the primary key - Group_PK(s), and display the Group_Name(s), in case a group should be renamed.

If I only needed to assign a user to one office, I can use a radio button.

I tried a checkbox in the Users UX, bound to a column in the users table. It allows multiple selections, but only stores them in a character based, comma-separated string, such as 3,8,10. Not a good solution.

Obviously I am doing something wrong, or there is a better way to accomplish this.

Any ideas?

[GGandhi]

Re: Checkbox with Multiple Values

tried a checkbox in the Users UX, bound to a column in the users table. It allows multiple selections, but only stores them in a character based, comma-separated string, such as 3,8,10. Not a good solution.

what would a better solution?
what type of field is user_group in user table?
when you select the check box what are you returning? for example
new York
dallas
or
new York|1
dallas|2

[Pizzadude]

Re: Checkbox with Multiple Values

The user_group in the user table s varChar. It is returning a comma-separated string, such as 3,8,10.

[GGandhi]

Re: Checkbox with Multiple Values

in MySQL if you need to store multiple values, I think it should be a set.
and what is your checkbox definition as I have indicated above?

[Pizzadude]

Re: Checkbox with Multiple Values

The user_group checkbox is being populated dynamically with a sql statement:

SELECT DISTINCT Group_Name, Group_Pk
FROM groups
WHERE CompInfo_FK = session.Company_IDFK
ORDER BY Group_Name, Group_Pk

This returns GroupPK, such as 3,8,10, but displays Group_Name.

I am think that I need another table that store the User_PK and the Group_PK, and bind the user_group checkbox to the primary key of that table. In that way a user can belong to multiple groups.

[GGandhi]

Re: Checkbox with Multiple Values

SELECT DISTINCT Group_Name, Group_Pk

as you can see this statement will return the id not the name, that is why you are getting the numbers as opposed to the names

This returns GroupPK, such as 3,8,10, but displays Group_Name.

I am think that I need another table that store the User_PK and the Group_PK, and bind the user_group checkbox to the primary key of that table. In that way a user can belong to multiple groups

probably the best way to do. so, it is easy to maintain adding to a new office and removing from an office

[Pizzadude]

Re: Checkbox with Multiple Values

I added groupmembers table.

It had 3 fields:
GroupMember_PK
User_FK
Group_FK

I am not clear on how to set this up in Alpha. When the value in the checkbox in the Users UX is changed, it must update or insert a record in the groupmembers table.

This would ideally execute in the afterDialogValidate event.

How do I make that happen?