Create a value list based on the choices in a checkbox set

[mark s reynard]

I would like to know if its possible to create a value list based on the selections made in a check box set, basically it would be a filtered value list

eg

Field Colour, as checkbox, from valuelist showing 4 colors (red,blue,green,Black)

Red x

Blue

Green x

Black

I have ticked Red and Green, so for this record I would like a value list just showing red and green

My thinking is this should be easy, but i think i may be losing it.

[Lee Smith]

Hi Mark, and Welcome to the Forum

what is the purpose of this list?

In other words, why do you need to find only these two colors?

[Vaughan]

Yes it can be done but it's quite an advanced technique.

Create a self-join relationship based on the record ID. Then create a value list based on the field that contains the checkboxes, displaying only the related values from the self-join.

[Lee Smith]

Yes vaughan, and there are examples showing how to do this. However, I don't think we have all of the information of what the need is. That is why I'm wondering what the purpose of such a list really is.

Lee

[bcooney]

I agree with Lee. I don't think that we have the all the facts. I'm thinking that he wants a new field in the same table that shows just the checked values in the field that is assigned the Colors value list.

So, OP, if you put the same field on the layout and format it as an Edit box, you'll see that checkbox selections are stored as return-separated values. Maybe that's all you need--a different display of the same field.

[mark s reynard]

Ok I used a none real example, to try and simplify my query.

Ok I have 3 tables, imagine a beauty salon.

Staff

Treatments

Bookings

In Staff I have a field 'CanDo', which is shown on the layout as a checkbox set showing all the values from the Treatments Table.

Eg

Nails

Hair

Acrylic Nails

Electrolysis

Make-up

Pedicures

Massage

For each of the staff records, I will tick which treatments they can do.

So in my bookings table.

New Booking Layout

I firstly choose which member of staff I would like to use

I then have to select what treatment I would like.

So I would like to show 2 fields in DropDown Lists.

StaffChoice ok a simple valuelist from the staff table. ok here

TreatmentChoice, I would only like to see only the treatments that the Staff selected can do.

I hope this makes sense.

Many thanks for all your help, much appreciated. I just cant seem to get my head around it.

[bcooney]

You should change to a join table btw Staff and Treatments, "Skills". The Skills table would store the foreign StaffID and TreatmentID.

On the Staff form, you would have a portal to Skills with create child record on in the relationship and the Skills::_kF_TreatmentID. Format the TreatmentID to be a value list of the Treatments (ID and Name, show only the second value).

Does a Booking record have Booking Line Items? Can each line item belong to a different StaffID?

If so, the Booking Line Item would have a StaffID and a TreatmentID. This TreatmentID would use a related value list based on the StaffID selected, going out to the Skills table, and bringing back available TreatmentIDs for that StaffID.

[This makes more sense to me, as a booking record has a customerID. That customer may be booking more than one treatment--the line items--each performed by a different staffID]

If I have time tonight, I'll clobber together a demo.

Edited October 21, 2009 by Guest

[mark s reynard]

that is most kind, thanks

[mark s reynard]

Vaughan your version works a treat,

Very useful technique and it looks professional too, and much easier than using a portal and a link table.

Many Many Thanks

[bcooney]

Well, here's another approach using a join table and a related value list. Perhaps it allows for more straightforward reporting.

Bookings.fp7.zip

[Vaughan]

I agree with Barbara: if any sort of reporting is required (like a list of who has done what, which is highly probable) then I'd recommend a portal with related records.