I

It seems to me that your Tests table is missing a field for the vitamins indicated for each test. Once you add that, the relationships are simply connecting the VitaminID fields in the three tables: from Tests to Vitamins, and from Vitamins to Products. Format the fields in Tests and Products as checkboxes, to allow multiple indicated/contained vitamins.

Hi comment-

Thanks for the speedy reply. I'm working with your suggestion right now - a couple of questions about it:

1) My Vitamins Table could have over a hundred items on it - I know that wasn't obvious from my description - I tried to simplify my example.

Is there a way to have a check boxes field scroll - or do all fields have to be visable? I know that it's kind of counter to the logic of a check box to not see all of them in front of you at once - but so many fields will take up quite a bit of real estate. Also users will be able to add fields to the application - and I'd have to leave "blank" space for them - how much - I'm not sure.

2) I'm having trouble being able to click in the Check Boxes - they seem to "blink" when I click on them - but don't seem to hold the values.

3) Maybe I should have mentioned this first - the boxes themselves aren't visable - I'm just clicking where they should be. I see them very briefly when I click. Is this because the default color for the box is white, and I have a default white background?

Thanks so much to comment, and anyone else who would have some input here.

-Mark

1. You could use an alternative method to assign vitamins to a Test or a Product. For example, a script could open a new window with a list of all vitamins, and the user could add/remove the assignments by clicking. I can't go into a detailed description of how to set this up right now, but the important thing is to end up with a text field where each assigned VitaminID resides on its own line (which is exactly what a checkbox-formatted field does).

Another option is to have a special layout just for making the assignments - there you can stretch the checkboxes to accomodate the full screen.

2. Allowing users to add fields doesn't sound like a very good idea to me.

3. Indeed, white foreground is not visible on a white background.

Update to previous posting:

- By changing the field fill to grey, I now see the check boxes are indeed white, and they are indeed being checked/unchecked - so that solves a major problem.

- So what can I do to get the check boxes to default to black - so they can be seen on an ordinary white background?

Thanks,

Mark

comment - again, thanks for the speedy reply.

1 - Actually, I think I understand what you mean about the script. I could open a new window - display the one Vitamin field with all it's check boxes - and have the user select them there (then have button for user to close window-) and then display the same Vitamin field - but just as an Edit Box - which would, post-selection would be no more than 15 fields - and could have a scroll bar if necessary. Hey, that might just work!

2 - I agree that users adding fields isn't great - but it's a primary feature of this app - users can add their own Vitamins and Products into the database. I'll have to write out actual limits on how many additional records can be added.

3 - Ah, so if I change my foregound to black - the it will be visible, I presume. Thanks again.

Well comment, thanks for getting me on track!

If anyone else would like to add their two cents - I'd appreciate it. In the meantime, I'm off to code - thanks to comment

-Mark

comment-

So in testing out your method - all seems good - with one minor exception. I can't get more than one Product to show up on the Test layout. I can if I use a portal, but not a field (that is sized large enough to hold multiple values.) Any guess as to why?

***SCRATCH THAT ABOVE *** There are multiple records - so that's why I'm only seeing one at a time - the portal, I believe is the only option to display the multiple records - correct?

Much Thanks in Advance,

Mark

A portal is the only way to display multiple RELATED records.

Thanks again for getting me over the hump on this one - it's made a world of difference.

-Mark

Just so you're totally aware, Mark ...

I agree that users adding fields isn't great - but it's a primary feature of this app - users can add their own Vitamins and Products into the database. I

Users should add records not fields. If your Users need to add fields to add new products then you are missing the point of relational. Products should be records in a table (and I assume they are by your discussions above) and not multiple fields.

I just wanted to give you a second HEADS UP on this issue (as Comment has already mentioned).

Big bad sticky problems happen if Users get in your Field Definitions.

LaRetta

LaRetta

Whew!!

Just felt I had to be straight on it for your sake!

L

Thanks for looking out for me as usual -)

Here's a demo of an alternative method.

Vitamins.fp7.zip

comment-

Wow - that's nice! Now you have me rethinking what I've done - although the key idea - or what I consider the key idea remains the same - using the checkbox values to easily store/link related records.

Awesome!

-Mark

Way two scripts one is enough ?

Vitamins.zip