Filter value list for checkbox

[jjb]

I have one table which has:

TableA:

classID

classSubTypeID

Another (related table):

TableB:

classTypeID

classSubTypeID

I am creating a layout based on tableA. On that layout I'd like to place 3 separate checkboxes populated by a value list of classSubTypeID, where each set of checkboxes is for a different classTypeID. What is the simplest way to do this?

thanks for any help.

[Søren Dyhr]

Relate classSubTypeID to the same in the other, define the valuelist over this relation.

--sd

[jjb]

Thanks for the reply. Let me clarify, because this doesn't seem to be quite enough for what I'd like to do.

2 tables:

Class

- classID

- classTypeID

Types

- TypeID

- category

I'd like the layout for the class table to be like this:

CategoryA CategoryB CategoryC

- Type1 - Type4 - Type6

- Type2 - Type5 - Type7

- Type3 - Type10 - Type8

So, the same layout should contain 3 (or more) different checkbox groups. Each checkbox has a value list which is a filtered set of values from the same table. So, how do I make the value list filter from the table in 3 different ways on the same layout?

Basically in SQL the values should be the result of "select subtypeID from subtypeTable where typeID = '%var%'"

thanks again.

[Søren Dyhr]

What bothers me is the use of checkboxes, since they compared with radiobuttons or popups allows several values to be chosen at the same time. Each checbox group of say 3 choices makes 7(8) combinations.

I have not even the faintest idea, what you're asking about?? Unless it's what I made a template to show???

--sd

FillViaChecks.zip

[jjb]

Thanks again for all your help. I've been trying to implement your solution in my app, but it's not quite working. I keep getting a 'No Values Defined' message in my checkbox list.

I'm attaching a more basic version of what I'm trying to do. The checkbox group is on the 'student' form. I'd like the checkboxes to just list the ones which are listed as category ID 1, and then I'd also add similar lists for the other categories.

tst.zip

[Søren Dyhr]

I keep getting a 'No Values Defined' message in my checkbox list.

Isn't it due to the table where the choises are kept, being empty?? Enter a few records and the behavoiur changes.

--sd

[jjb]

I don't think that's the problem. The choices I want to display are in the classTypes table. All of the values show-up in the checkbox as it is in the current file I uploaded, but I'd like to divide these up into separate checkbox groupings dependent upon the value of the categoryID.

When I try and check 'show only related values' (classCategory) then I get the message. This is in the student layout.

[-Queue-]

Soren is correct. Your checkbox is a related field from an empty table. Create a record in your studentClassTypeXref layout with a StudentID of 1 and you'll be able to select the ClassType from the student layout whose ID is 1.

[jjb]

Yes, but I'd like the layout to behave differently if it's at all possible. I'd like to create the entries in the studentClassTypeXref table from the student layout.

Do I need to change the relationship graph somehow?

The student layout should end up looking something like this:

studentID: <autonumber>

Category 1 Category 2 Category 3

_A1 _ A2 _ A3

_B1 _ B2 _ B3

_C1 _ C2 _ C3

Is it possible to do this without first needing the Xref entry?

[-Queue-]

Then you need to set your relationship to 'Allow creation of related records' from student to studentClassTypeXref.

It sounds as if you'll need multiple value lists to divide the categories as you desire.

[jjb]

I've done that and still get the same results. As soon as I clear all of the checkboxes, the checkbox group gives a "No Values Defined" message.

[-Queue-]

Um, are you referring to the relationship or the value list? One should have nothing to do with the other.

The only reason I can think of that you would see 'No Values Defined' is if all the records in classTypes were deleted.

[jjb]

Um, are you referring to the relationship or the value list? One should have nothing to do with the other.

I don't understand your question.

I think what I'm trying to do is pretty simple. I just want to show a checkbox with a set of values from a different table filtered on a specific key. What is the general way to do this?

[-Queue-]

I've done that and still get the same results. As soon as I clear all of the checkboxes, the checkbox group gives a "No Values Defined" message.

This is what my question was questioning. You weren't specific as to whether you were talking about the relationship or value list response from my previous post.

To filter a value list, your relationship will need to include the key, and the value list will need to be defined to 'Include only related values starting from' {the TO containing the filter }. This will, of course, only allow you to have one checkbox per record, based on the filter. To show multiple ones on the same record will require additional fields to hold each key and value lists to hold the filtered results.

[jjb]

I've set up the relationship to allow multiple posts.

I plan to have more than one value list, but I don't see why that should be necessary to get the first one to work.

[jjb]

Another attempt.

Here's a pre-filtering attempt version of the db. What would I need to do if I wanted the checkbox group on the student layout to only show the types which are in category 1 (a1, b1, c1).

tstCopy.zip

[-Queue-]

As I said before, you need to set your relationship to 'Allow creation of related records' from student to studentClassTypeXref. In the sample you posted earlier, this option is not selected.

Still, this shouldn't have an effect on your value list showing 'No Values Defined'. The value list is only dependent on there being records in the classTypes table.

[-Queue-]

Create a calculation or global field equal to 1 in your student table. Create a calculation of GetAsNumber(typeDesc) in classTypes. Create a new relationship from student to classTypes, matching the calc of 1 to the GetAsNumber calc. Then change your value list to use the typeID and typeDesc from the new classTypes relationship, and 'Include only related values starting from' student.

[jjb]

Thanks so much that seems to work. I was hoping to come up with a solution which would not involve the student table, so that the value list could be reused for other tables which relate to the classTypes table, but it seems like that's not really possible with FM.

[-Queue-]

Not without an additional value list or possibly the use of an extra TO to hold the 1, 2, 3, etc. calcs. You would have to create new relationships from the necessary TO's, but it might work.

[jjb]

In the end, I implemented this in a different way. Basically I created additional calculation fields in the table I wanted to create the value list from. The calculation was of the form:

if(category = "1"; classType; "")

etc.

I then used the calculated field to create the value list. This was much simpler and more 'portable' for use in more than one layout.

Thanks to all for their help.