How to do agregate calc on external field?

[Alan H]

I have a database with a number of relations. It is often convenient to see in view A the number of related records in table B (where A<->B is a many:many relation via an intermediate table). So I put a calcuation field in Table A

 Count (Table B::tUniqID) 

and this displays the number of related records in B.

The problem comes when A is a Table Occurrance that references an table in an different file. I can't create the field locally (where the relationship between A and B is defined). But if I open the file and try to create the field there, I don't have access to the same relationship.

The only solution I can think of is to create a new local table A' in which each record points to exactly one record in A, and then define the count field in A'. This makes for a maintenance nightmare as every time I add/delete records in A (which might happen when A' isn't even open in FM), I have to add/delete records in A' to make them consistent.

Am I missing something?

[RalphL]

I am sorry but I don't understand your problem. It sounds like you want to make a relationship between Table A in File 1 and Table B in File 2. Is this your problem?

[Alan H]

No, I have a relationship between Table A in an external file and Table B in a local file (through "Table A<->B" which is in the local file). I want to display for each record in A, the number of records in B that are related to the record in A.

It is easy to do the opposite (for each record in B count the number of related records in A), because B is local and I can define a field that counts the related records. However, I can't seem to do what I need here.

Thx

A

[ESpringer]

What do you mean by saying that you can't *create the field locally*? You should be able to define a field in table B which pulls data about related records in any A-table in its TOG (table occurrence graph), even if it's stored in another file. After all, you're not creating a field in table *A* (the "remote" one in the relationship); you're creating a field *in* B to register data *about* related data in table A. Right?

I take it you've already made the relation work. Perhaps what you're missing is how to reference the fields in the related table (it doesn't matter that it's in another file) while you're creating the calc field? (Yet your description of the one relational count you *did* set up successfully suggests that you do understand how to do this.)

Or is it that the data you want TableB (in FileB) to display from TableA (in FileA) is visible in FileATableA because of *further* relationships available in that file that are *not* included in the relationship group you've designed in FileB? In that case you'd need to create parallel relationship groups, using additional isomorphic TOs and paths for each file that helped make the data work in FileA...

So, like RalphL, I'm confused... What, exactly, is the nature of you'r "can't" here?

One more idea: perhaps you're confused about Table Occurences... Although you can't work on Table A's field definitions when you're not in its home file, any TO for A continues to reference A even as you make changes on A from A's home file. So the TO for A in FileB should work as a source for the data that you want to access *via* FileB's relationships *of* fields that you need to create in FileA.

What are *we* missing here?

[ESpringer]

One reason I'm confused is your earlier message says:

Count (Table B::tUniqID)

and this displays the number of related records in B.

and your later message says

Alan H said:

No, I have a relationship between Table A in an external file and Table B in a local file (through "Table A<->B" which is in the local file). I want to display for each record in A, the number of records in B that are related to the record in A.

It is easy to do the opposite (for each record in B count the number of related records in A), because B is local and I can define a field that counts the related records. However, I can't seem to do what I need here.

So you *have* been able to get a field that displays (presumably in A) the count of related records in B, according to the earlier message. But you're not sure how to do exactly that, according to the latter. Perhaps you haven't opened the A file and set up a relation with the same structure between A (there) and its TO of B?

[Alan H]

Or is it that the data you want TableB (in FileB) to display from TableA (in FileA) is visible in FileATableA because of *further* relationships available in that file that are *not* included in the relationship group you've designed in FileB? In that case you'd need to create parallel relationship groups, using additional isomorphic TOs and paths for each file that helped make the data work in FileA...

I am not quite sure what you are saying here... but you answered my question!

I just tried this in a simple test case and it worked.

File 1 has table A, file 2 has table B and table "A by B". If you make an external relationship in File 1 to File 2 and bring in a TO for both external tables, set up the relationships, etc, you can define a calculation field in File 1/Table A that is "Count (Table B::any field)" and this will show the number of related records in B to any record in A.

Now you go to File 2, import table A as an external table, set up the same (isomorphic, as you properly describe them) relationships and then reference the calculation field, and it just works!

I had a hard time believing that this would work, but now I see that it can, and does.

Thanks

[Alan H]

ESpringer said at the same time that I was responding to his earlier post:

So you *have* been able to get a field that displays (presumably in A) the count of related records in B, according to the earlier message. But you're not sure how to do exactly that, according to the latter. Perhaps you haven't opened the A file and set up a relation with the same structure between A (there) and its TO of B?

Exactly. I was hoping to keep File A agnostic of the existance of File B (for reasons of separation of distinct parts of a project, code independence, etc), but I see that that is impossible here.

Again, thanks

[ESpringer]

OK... I see your task now, and I'll assume you have good reasons for it. Well, whatever the criteria are that link B records to any given A-record, you could set up a self-join that counts the Bs with the same value:

For example, if your files were Members (A-table, remote) and Events (B-table, local) with a many-to-many join table, and if this JOIN table is IN the Events (local) file, you could set up a self-join relation FOR the join file (using another TO based on sharing the memberID field), and use a calc field to have each join-table record display the total number of join-table records FOR [this particular member].

You could (I think) even use another TO of the JOIN file to allow the A-table in File B to display this data about how many events are related to this member (this would work because all the join file records related to this member will have the SAME value in the count field, so you could simply plop the related field onto the layout for the A-table (in FileB).

Does that make sense? You could do this while leaving the other file "clean" of references to anything in this local file.

[Alan H]

I think what you described is absolutely friggin' brilliant--but I need to digest it (ie, try to implement it) to see if I can get it to work.

Cool

[Alan H]

I posted an example, in a new thread

[cellmaker]

Alan -

Where did you post your example?

Thx.