Restrict value list according to field contents

[MikeKD]

Hi, first I'm a serious newbie - please treat me like an idiot!

A doing a db to write school reports with.

I have a table of report comments (fields include gender and rating) and a table of students (fields also include gender and rating).

I can enter into each kids report by selecting comments from value lists using fields from comments table, but there are loads to choose from!

Can I filter just the relevant ones (e.g. only male comments if gender field is male)?

How?

Many thanks in advance!!

[ESpringer]

Presumably each report corresponds to a student who has a gender... but it's not that the *report* literally has its own gender, right? Have you already established a relation between the students table and the reports table? I'm assuming that much has gone well...

First, notice you *could* simply work from one field for the student's name (if you're sure these are unique and won't change) or for the student ID (a safer way to individuate student records). Then, you could have the student's gender show up directly on the report layout as a field *from* the students table (ask if you're not sure how to get a field from a related table to dislay on the report layout)...

OR you can have a calculation field in the report layout display whatever the value is for this student's gender (define a calc field in the reports table and just specify the gender field from the related students table as the content of the calculation... OR you can have a field that looks up the student's gender and then hard-enters it into your report's gender field.

One reason that you might want to do the last thing is that you can then set up a "self-join" relationship (between two different table-occurences of the reports table) keyed to match the gender field -- call the duplicate table "SameGenderReports". Now, you can use this relationship to make a value list of all of the comments entered for ay other reports *with the same gender tag*.

Now, I'm not sure why you're trying to avoid selecting comments from one long list... you know you can "type into" the value list with the keyboard rather than scrolling down... If your comments begin with useful key words it would be very easy to manage data entry with one long list... how long is the value list?

Here's a more all-purpose idea, by the way: in your comments table, you can set up a key field for "category", and I believe this is one of the few situations where you might actually want to use a repeating field if each comment might fall into one or just a couple categories. You could then categorize comments in various ways and use other fields on the report to serve as "hints" for the category you want. Does the "rating" category correlate with comments, for example? In FM7, you can set up pretty complex relations, like: relate this report to any comments where (Report::Gender = Comment:category -OR- Report::Rating = Comment::Rating). Perhaps a bit more reflection will reveal the most helpful way to make the most relevant comments come up most readily, without excluding other possible ones.

By the way, how are you entering the comments? That is, is it just one comment per report, or are there multiple comments in a repeating field, or...?

(Of course I am also musing what kind of school it is that has comments that usefully differ *that* much by gender.. but that's quite beside the point!)

[MikeKD]

Many thanks for help so far - I'm just off to try things out!

The reason my report comments had different gender was to get te pronouns right, they're identical otherwise!!!

There are multiple comments for each report, e.g. understanding, performing, composing, listening and attitude. These fields at the moment are in the pupils table, but I'm about to create a reports table.

[MikeKD]

Mini eureka!

I've been playing unsuccesfully trying to select appropriate comments according to gender and then had a brain wave.-

If I just have one unisex set of comments and useunisex pronouns e.g. SHEHE, I could then do a conditional search and replace.

e.g If gender = "male" Substitute(text: [sHEHE: he] ; [HERHIM; her] else Substitute(text: [sHEHE: she] ; [HERHIM; him]

But....

1. how do I get that formula to work (I'm a music teacher!)

2. how do I make the appropriate field so that I only edit that one kids report comments, not the originals? (At the moment I'm compiling 5 catagory comment fields into one calculation field, but it's not editable.

I would still like to restrict available comments on the value list according to pupil table fields, and really don't understand how that's possible. e.g. I wouldn't be able to put "poor" comments in a "good" students report, or "Year 7" comments in a "Year 9" students report. Ultimately I should have hundreds of comments to work from, and unfortunately the keywords are often halfway through!

Many thanks again.

[MikeKD]

Another mini eureka!

I've got the formula above to work.

My next step is to sort or filter the value lists in the kids reports produced by the comments table.

At the moment they're sorted alphabetically - they would be far more useful sorted according to their rating field (i.e. excellent, good, poor etc).

Unfortunately I have no idea how to do that!!

But at least I can actually write reports now!

cheers

[MikeKD]

Right, I'm now trying to set up a self join that will only show up related results, so that an "Excellent" in the pupil table will produce a value list that only shows comments from the comment table in the "Excellent" catagory.

Unfortunately, I can't get it to work, and can't work out why.

I don't suppose you could give some real "idiots guide" instructions!

Many thanks for help so far.

cheers,

[dbruggmann]

You don't need a self join, you need a relationship between the field in the pupil table and the field in the comments table that contain

[MikeKD]

Bingo!

I've finally got it!

(Glad I said I as an idiot to start with!).

Many thanks.

I think my problem was that I set up too many key fields.

My next task is to try to sort my value lists, but I can't see any way at all to do this.

Should this move to value list thread now?

cheers,

Mike

[ESpringer]

Notice that you can have a value list include a second field, and once you do that, you can sort by that field. Note that the "also display values from..." option doesn't actually mean the second field will be inserted -- just displayed in the data entry process and used to sort.

So you could define a CommentOrder field that uses 01a, 01b, 01c, 01d, 02a, 02b, 02c (etc.) to custom-sort your evaluative comments for the purpose of your data-entry value list.

[MikeKD]

Brilliant, just had a chance to play with that and it all works fine - many thanks!

I've spotted a new problem though, and tht is that not all the records from the comments table are being displayed in the value list,

e.g I have 47 behaviour comments, all relevant records should be displayed, sorted by 2nd rating field.

They are sorted correctly, but only 10 are displayed.

Any idea what's I might be doing wrong here?!

By the way, many many thanks for all your help - FM should pay you! (or I should, but the FM option is better for me!)

cheers

[MikeKD]

Ah, got more clues on what's going on:

The value list is only displaying the first behaviour comment record for each rating. If I take away the 2nd (rating) field from the value list then all records are displayed (though obviously not sorted).

Is this supposed to happen - I thought that I'd have all the "excellent" comments listed, then all the "good" ones etc.

cheers!