toggle between two check boxes

[merkaba22]

Hey guys -- here's another newbie question -- I almost think its been asked a thousand times before but here goes:

I have a series of associated fields:

ProductID1; Checkbox1-1, checkbox2-1

ProductID2; Checkbox1-2, checkbox2-2

ProductID3; Checkbox1-3, checkbox2-3.

I need:

checkbox1-x and checkbox2-x to be deselected if ProductIDx is empty.

Checkbox1-x to be selected if ProductIDx is not empty; and

Checkbox1-x deselected when Checkbox2-x is selected and ProductID is not empty.

Ideally, I would like to be able to use these "status" choices for sorting and/or reports ...

Thanks again for all your help!

[Vaughan]

You want to have these values set, or you want to ensure (validate) that the user sets these values?

[merkaba22]

Yes, I think:)

I want the user to only have to set Checkbox2-x ... without conditions. If that's what you mean.

[Vaughan]

One way might be to allow users to make entries into checkbox fields, but create calculated fields that apply your logic to filter out those values that don't necessarily make sense.

I'll call the fields ProductID, Check1Entry, Check2Entry, Check1, Check2

where Check1 and Check2 are the calcultion fields -- these are the fields that you refer to for all results, not the "entry" fields.

The calculation for Check1 would be something like:

Case (

IsEmpty( ProductID ), "",

IsEmpty( Check2Entry ), "",

Check1Entry

)

The calculation for Check2 would be something like:

Case (

IsEmpty( ProductID ), "",

Check2Entry

)

For your rules, I'd be asking one more question: if there is a product id and check1 has an entry, what do you want to display in Check2? The calculation I've made for Check2 ignors the state of Check1, because your rules above don't specify anything.

[Vaughan]

I forgot to add: you have ProductID1, ProductID2, etc: when fields get duplicated like this in a database it usually means that a relational design would be more appropriate.

In this case, a Products table which has the fields ProductID, Check1Entry, Check1, Check2Entry and Check2 (as a minimum from what you have so far mentioned).

[merkaba22]

This is what I ended up creating for Check1:

Case(IsEmpty(Product ID 1),"",IsEmpty(PO Item Close 1),"",PO Item Open 1)

And I was looking to see a check mark in "PO Item Open 1" (and no check mark in PO Item Close 1) because "Product ID 1" had a value but it didn't work; ie. no auto check mark in "PO Item Open 1" ...

Did I miss something or was I unclear?

[Vaughan]

The check marks do not appear in the original entry field -- it will appear in the calculation field that you just created.

[merkaba22]

I am very very grateful for your input here. But I really must being doing something wrong.

I just drew a field on the layout for "check1" and still I see no check mark -- I even tried to format this calc field as a check box but still it doesn't show anything. I am sorry, I must not be clear ...

Since the layout is extensive and I don't want to re-write it if I don't have too, is there no way to have "PO Item Open 1" (which is a checkbox with a value list of " ") be selected only when there is a value in "Product ID 1" and no check mark in "PO Item Close 1" (which is a checkbox with a value list of " ") as a default; and then offer a user choice to add a check mark to "PO Item Cose 1" which automatically deselects "PO Item Open 1" accordingly?