How to get the Range of a set through relationship

[keshalyi]

Hey y'all. Another unsolicited tip, that probably noone is interested in, but made my day today when I figured it out.

The problem: There is no direct way to obtain the range of a set of data by relationship. This is particularly irritating in the very common situation of accessing data in an intermediary table meant to resolve a many-to-many relationship. For instance, I have a database that stores scheduling for classified ads. It formerly had a table 'Publications' and a table 'Pub Dates' and you selected what pubs you wanted the ad in and what dates. Now, this made an implicit many to many relationship between those two tables, such that every date with a certain ad corresponds to every pub with a certain ad and vice-versa. AS such, I found it simplified my structure to just make a table of ad instances, where the entries were each combination of ad, pub and pub date that needed to be printed. The problem I am solving here came when I tried to display for the user a list on their screen that they could add to and subtract from, of every pub that they had selected for this ad. And another one for dates. If I made a relationship directly to this table, they got a long, akward list of every instance. This could get hairy, and irritating, if for instance they have 3 pubs and 10 dates - thats 30 instances! What I needed was some way to make a relationship that displayed the range of each of these fields.

Bad Solutions: My first brave attempt worked off the following observation - if I derive a value list from a field, it displays the range of the field. That is, if I make a value list of field A, which has the following ten values in its ten records: {A, A, C, H, Brown, H, A, C, H, A}, the value list reads {A, C, H, Brown}. Exactly what I need, right?

Well, unfortunately, value lists only display into reation to a field or button of some sort being pressed, cause they're contextual value lists (yeah, akes sense, doesn't it?). What I noticed though, was that I could make a calc field using the ValueListItems function. This made my field equal the values of the Value list delimited by carriage returns. Perfect, right?

Yeah, well, unfortunately, this has one major drawback - it doesn't work! I suppose it may be possible I just didn't know how to make it work, but it acted very erratically. The field wouldn't update, even when the value list updated, whetehr it was indexed or not. Sometimes, the field would flush and display nothing. And it would randomly (or at least semi-randomly) update, so values woudl appear as they should but only at weird intervals.

So I scrapped it.

Good solution: So I thought hard about it and here's what I came up with.

What I need is a relationsihp that displays one record for each value of a certain field in that record. So, what I did is horrendously simple: I made a relationship in the instances file, linked a multikey of the ad key and Pub to itself, in a slef join. This gave me a relationship that showed all values of a certain value for a certain ad. Then, I did a sort on this relationship by Instance key. Then, I made a field, that is true if the first value of instance key in the self join is equal to the value of instance key in the current record. Then, I made a relationship from the ads table to the instance table using a multikey of Ad key and the test of first instance. That way, it takes the first record, for each value, and flags it to be related. (the only irritation is that, of course, I can't make that relationship directly, per se, because the calculation is based on a relationship nd can't be indexed. Bah! Luckily, the data in the calculation should be moderately static, so I just did a lookup.) Anyway, that's my way! Anybody else have any solutions to this hairy problem?

Liek I said, its really not that com