This post started out as a further response to Joseph Temple's post on "find averages across multiple records?" but due to a glitch in the mail server I was unable to post it last night. Since this deals with an aspect of the new "x" relationship criteria I think it is worth starting a new thread. I am interested to know how people have used the x relationship since there seems to be a dearth of examples. I submit one below. Does anybody have any others?
This is a minimalist example using a self-join but I think it is not hard to see how it might be used in a more complex set-up. Create a new database with one table and one numeric field in the table. Create a second copy of the table in the relationship diagram and relate using the single field. Double click on the box in the centre of the relationship, select the x relationship and click Add, you now have two relations x and =, select the equals relationship and delete it. Now go back to define fields and add in a calculation field which is the average of the number field in the *related table*. (this was the specific case in Joseph's question, but any aggregate field will do here) Go to the standard layout and add the average field in so that we have two fields in the layout. If we now start adding new records and putting in numbers then the average field will instantly update working out the average value over all fields - it is easier to see this in the table view of the layout. If we change a number in a given record then it changes. If we omit a record <Ctrl-T> then it doesn't change so it really is calculating the average of the field over all the records. The aggregate field in this example has one value across all fields so it is natural to think of it as global, however, if you make it global it stops updating when you change the values in the other field. I think this is because *globality* destroys one end of the relationship and so the definition of the field no longer makes any sense. However, you do not need to store the values. I would love to see other examples. --- David -- To unsubscribe: send an email to <[EMAIL PROTECTED]> FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
