average function in a relationship is not unique to found set.

[longjump]

Hope I'm in the right forum here.

How can I have a relationship where the master file can display a value that's computed by an average function of values in the related file, with the following conditions:

- This average must ignore omitted records in the related file, even if some of these omitted records have the same value in the match field that the found records do.

- Records in the master file that don't match any of the found records in the related file but do match omitted records in the related file, should not display the average of the found records in the related file.

- In the master file, e/ record has a unique value for the match field. In the related file, multiple records--including some omitted ones--share the same value in the match field.

- The found set being averaged is very fluid, constantly being rearranged and resorted manually in various ways by the user.

TIA -- longjump

[comment]

The only way to summarize a found set is by summary fields in the same file. Relationships will always ignore the found set in the related file, and consider only match/no match.

[longjump]

Dang, 'swhat I was afraid of. So how can I get around this? Tried making an average in the related file that's unique to records in the found set by making a calculation field like this:

AvgCalc = If(Status(CurrentRecordNumber) > 0, AverageField, "")

[where "AverageField" is a summary field]

This didn't work because when the master file looks at the related file, even the omitted records have current record numbers.

I can't use some kind of sub-average or something?

I guess I could make a script that assigns some arbitrary value to the found records in the related file, in order to refine the match. But that feels clunky to me.

Thanks again,

longjump

[comment]

It's difficult to advise without knowing at least some details and, most importantly, the purpose.

[longjump]

Yeah, sorry 'bout that. Was trying to spare you the long & boring, ended up too vague. Here's my too long & inelegant description:

Master file: "Ticket price calculator". Calculates market price for tickets to events. Includes a field "event date" that is unique to each record.

Related file: "Ticket price comparer". Contains prices that sellers are charging for various events. In this file the field "event date" is shared across multiple records.

Match field: "event date".

Relationship name: "event date". Matches the field "event date" in the two files.

I want Master file to be able to show the average price of tickets in the found set of the related file, and ignore all omitted records in the related file. The found set in the related file does not necessarily include every record that has that event date.

In the master file, I want this average price to be unique to the event date of the current record. In other words, if the avg price of tickets for January 1 in the found set in the related file is displayed when viewing the January 1 record in the master file, then when I switch to a different record in the master file I don't want to see the same avg price. Ideally the average price field should become empty because the current record no longer shares the same event date as any of the records in the found set of the related file.

Unfortunately the average function in use in the related file (a summary function in this case if I understand it right) seems to be global as far as the relationship is concerned. When the master file looks at the related file, all matching records in the related file have the same average price regardless of whether they're in the found set or not. I think this jibes w/ what you mentioned earlier, Comment.

Not only that, but all records in the master file that share an event date w/ any record in the related file show the same avg price.

I tried making an average price field in the related file that's unique to records in the found set by making a calculation field like this:

AvgPriceCalc = If(Status(CurrentRecordNumber) > 0, Average price, "")

This didn't work because when the master file looks at the related file, even the omitted records have current record numbers.

Is there some way to compute a sub-average in the related file that the master file can recognize?

BTW the found set in the related file can only be made manually; there's no way that I can think of to script it or to sort it from the master file, since the user is always playing around w/ different ways of sorting it and is always manually omitting and un-omitting records from it depending on his whim.

[comment]

I'm afraid I don't know of a good way to approach this.

As I said earlier, a relationship ignores the found set. When the master file looks at the related file, there's only one average, and that is the average of all related records - whether found or omitted.

More precisely, there's one average for each record in the master file, and that is the average of all records in the related file that are related to the master record (in your case, all records that have the same event date as the master record).

The workflow you describe is not altogether clear to me: surely, the user must be in the related file in order to "play around" with the found set. In that file, a summary field will always reflect the found set's average, so perhaps that might be enough?

Marking the found set is indeed clunky (and not easy to implement in a multi-user scenario, where each user has their own found set). It is possible to view the related thru a portal, and simulate "omitting" them by clicking (or two portals where you can shuffle records between "found" and "omitted"). But then sorting the portal by another criteria is very difficult, esp. in ver.5.

Another possibility, perhaps slightly less clunky: when done with the related file, set a field in master to the current average?

[longjump]

'Kay, thx again . Not being very adept yet w/ portals, I'll go clunky.

A parting rant: Seems to me fm should have a status function that returns whether or not a record is currently active (found). I was actually surprised not to find something like that. If there can be a "CurrentFoundCount" function, it seems like it'd be easy to have, say, a "CurrentFoundStatus" function. I can't imagine my scenario being unusual in being able to take advantage of such a thing.

Anyway, cheers!

-longjump