Jump to content

How to do agregate calc on external field?


Alan H
 Share

This topic is 6436 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6436 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.