Table linked to itself

[Bob_Blightman]

I have a table linked to itself

A Lookup field type still works fine

But a Count field type always returns the value 1 when in some cases it should be more than 1

[TheTimeSavingCo]

Hm, could you provide a screenshot of that?  I wasn't able to replicate it when I attempted to I'm afraid!

[Bob_Blightman]

Adam

Thanks for the quick reply

Hopefully you can see the above screenshot. So there are 6 records linked to the ID 66, and when you look up the first name of the "master" record, it returns it OK. But the Count field only shows 1 when it should show 6

[AlliAlosa]

The count is correct based on how you have it set up - it counts how many linked records are in a particular field, and you have one linked record in each field.

Linking records to the same table does not work the same way linking records on separate tables does. When you link records between separate tables, a reciprocal field is created on the table you’re linking to, and the relationship is bidirectional. When you link to the same table, there is no reciprocal - the field link only goes in one direction.

One way to get the result you’re looking for is to create a second table, called something like "Families". You can do this most easily by editing your existing linked record field and pointing it at a new table. This will take all the existing values in that field and add them to a new table as a unique list. On this new table, a count field should function as your expecting.

[Bob_Blightman]

Yes, I used to have a Families table, but I felt that made the whole thing clumsy and it also meant that you couldn't just have a single form.

So I'll live with it as it is at this stage

[seferiannie]

i'm confused... what are we counting here? and why would you mirror to yourself?

[Bob_Blightman]

Seferianne

Thanks for your reply. So I have a "Members" table where we can have several members in the same family. I have identified a "Lead Member" in the family and have linked the other members to the ID of the "Lead Member" (so the linkage is from the "Members" table to the "Members" table). I then wanted a Count field to count the number of members in each family, but in every case this returns the number 1. 

My information from someone else in the Community is that when you have a linkage within the same table then it's only a one way linkage (so that for instance I can look up the Lead Member of the family in each record) it's not two way, so I can't use the Count function.

I could obviously get round this by having a separate table, but that makes the whole thing unnecessarily cumbersome and I can get round it just by specifying explicitly the number in each family rather than calculating it

Bob