Selection dependent Checkbox group

Hi there, I wonder if anyone can offer some advice?

I'm working on a CMS for a client who sell accessories for various audio/visual products. My intial struggle is trying to work out the best way for constructing the insert/update pages for these accessories as they're a little more complex than I've created before. Essentially their products fall into one of a number of Categories (Mobile, Audio, Gaming, etc), and then into any number of Devices in that Category. For example, Product A falls into Category 'Mobile' and available for Devices iPhone 5, Samsung Galaxy S3 and Samsung Galaxy S2 while Product B falls into Category 'Gaming' and then Devices 'Nintendo DS', 'Nintendo 3DS' and 'PSVita'.

I'll create a table in the database named 'Category' which will simply have a 'category_id' and 'category_name' and a second named 'Device' which will have 'device_id', 'device_name' and 'category_id'. I have no issues creating the Insert/Update pages to administer these two tables. There will then be a table named 'Product', but in the Insert/Update forms for administering this table, as well as providing a name, code, image upload among other elements they will need to select an appropriate Category and then select any number of Devices related to that Category. Ideally what I would like to happen is when they select a Category from a list on the Insert page it will then present all the appropriate Devices for just that Category as a series of checkboxes from which they can select any number of them. Then when they go to the Update page for a product it is already displaying the correct Category and correct series of Device checkboxes for that Category. A concern is that the list of Devices for each Category is going to be dynamic as the client will be able to add and delete Devices from the list via another form.

I would be very grateful for any advice on how I may best achieve this? Plus in terms of recording the appropriate Devices for each product, should this be achieved through Manage Relational Tables?

Many thanks in advance
Paul

your table structure should be:

devices:
deviceID - primary key
deviceName

categories:
categoryID - Primary Key
categoryName

devicecategories:
devicecategoryID - Priamry Key
devicecategoryDeviceID - relates to devices.deviceID
devicecategoryCategoryID - relates to categories.categoryID


the devicecategories table is a linking (or relational) table used to create a many to many relationship of devices to categories.

you will need to use the manage relational table behavior to assign the categories to the device.

see the "Managing Relational Tables in Data Bridge" tutorial on the data bridge support page:
data-bridge/

Hi Jason

Thank you very much for your advice. I'll will look into it and see how I get on.
Kind regards

Paul

Hi Jason, hope you're well.

I'm just picking up on the project I spoke of before, it has developed further than expected and not quite sure how to structure it to work with Relational Tables. I wonder if you can offer any advice.

Essentially I'm building Insert and Update pages for products. A product can be assigned to a number of different Devices, and some of these Devices can be within different Categories. For example..
PRODUCT - Blue case - is compatible with DEVICE iPhone 5 and DEVICE iPhone 4 which are both within CATEGORY Mobile.
PRODUCT - Red case - is compatible with DEVICE iPhone 5 in CATEGORY Mobile and with DEVICE iPod Mini in CATEGORY Audio

I've planned four tables...
CATEGORY containing CATEGORY_ID and CATEGORY_NAME
DEVICE containing DEVICE_ID, DEVICE_NAME and CATEGORY_ID
PRODUCT containing PRODUCT_ID, PRODUCT_NAME and countless other details
ProductDevice containing ProductDeviceID, ProductDeviceProductID and ProductDeviceDeviceID

On my insert page, for the user to be able to select any DEVICE in any CATEGORY I created a number of RecordSets that are all populated by the DEVICE table, but each one filtered by a different CATEGORY ID. I then followed the online 'Blue Sky Music' tutorial by having checkboxes within a Repeating Selection populated by the first device RecordSet, and then duplicated for all the other device Recordsets. I've attached a screen grab so you can see how each one is housed in a tabbed panel.

The problem I've found is that because I've had to split the DEVICES across a number of Repeat Selections, when I reach the stage of inserting the MRT into the Insert page and specifically the Options Table Join panel it lists all the Repeat Selections separately when ideally I need to be able to select them all seeing as they are all populated by the same database table.
Do I need to structure the database differently or maybe the page to be able to work with the MRT? I had originally hoped that I could set up several MRTs on one page, but noticed in another forum post that this isn't possible.

I would be very grateful for any advice you can offer.
Many thanks in advance and kind regards
Paul

you will need a separate MRT behavior for each category

Hi Jason, thank you for getting back to me. I thought I'd read on other posts that you couldn't have more than 1 MRT on the same page... has this now changed? If so, do I insert the same redirect path into the all of them?

Many thanks
Paul

in data Bridge, you can use more than 1 MRT behavior on the page.

only the last one should have a redirect set.

That's great news, thank you very much Jason.

Kind regards
Paul