How to show relationships between records of the SAME TYPE in the SAME TABLE?

[Atiq_Zabinski]

Base lists Members of a Group organized on Facebook, which Member admitted them to it, and where applicable, which Member recommended them for admission.

a Single Line Text column has each Member’s Facebook name, and
a Link to Another Record column that references the Facebook Name column specifies which Member admitted that Member to the Group.

I want a third column that will auto-populate with Members added by that Member, based on the info in the second column.

For the life of me, I can’t figure out how to do this. Everything I’ve read here has been about establishing relationships between records of different types, whereas this is about establishing relationships between records of the SAME TYPE in the SAME TABLE.

If I can get past this point, I may or not need help with the next step. I imagine it will be easier to solve, but whatever solution is offered should take into consideration what I want to do with that third column.

First, I will want to duplicate the above with columns for Members recommended by other Members for admission, and conversely whatever Members may have recommended them. (Members without Moderator or Admin status can not admit Members, but can recommend Members for Mods or Admins to admit. Thus a Member could either have one relationship: to the Member who admitted them, or two: to the Member who admitted them, AND the Member who recommended them.)

Secondly, we have other columns that rate that Member’s performance in the Group, towards the end of considering Members for promotion to Moderator and Admin levels in the Group. We also want to encourage everyone to recommend and admit good members, so we want everyone’s performance scores to be automatically adjusted so they garner a percentage of the scores of the people they have admitted OR recommended. So, I will need an “adjusted score” column with a Formula (Rollup?) that will aggregate the unadjusted scores of Members admitted or recommended by each Member.

Thanks so much for any help!

[Atiq_Zabinski]

Followup: I have devised a temporary workaround, but it makes for a bad workflow (duplicate entry).

I created a second table which I named Tribes. I copied and pasted the Facebook Name column from the first table “Members”. Then I created a second column, a Link to Another Record column which I linked back to the Members table, and copied and pasted in the contents of the Added By column in the Members table. Then in the Members table, I made a Link to Another Record column and linked to the Tribe table.

This succeeded in auto-populating with the members each member had added. I was then able to add a Lookup column referencing the performance scores of those members, and a Rollup column that summed up those scores.

Unfortunately, this requires that every new member be entered in both tables. Duplicate entry always makes for bad workflow: it makes for extra work on the user end, introduces new room for data entry error, and may preclude the possibility of data entry by submission of a single form.

I guess what I’m looking for is a way to auto-populate the second table every time a new entry is added to the first. Either that, or a better approach to the whole thing.

Thanks again for any help.

[Atiq_Zabinski]

12 days and still awaiting reply. Should I assume there is no solution, and I’ve already hit the limit of what AirTable can do?

[W_Vann_Hall]

I’m not sure I follow your description entirely, but take a look at this demo base and see if it approaches what you’re looking for:

There are two tables, Group and Members - but data entry is performed entirely from the Group table.

• To enter a new member, select the plus sign ("+") in a new row under the ‘Member’ column. This will pop open a window to the Members table; select ‘Add new record’ at the window bottom. In turn, this opens a data entry window for a new Members record; enter the member name under ‘Name’ and close the window.

• There are three other entry fields of interest in the Group table: ‘Add by’, ‘Rec by’, and ‘Rating’.

• For both ‘Add by’ and ‘Rec by’, select the plus sign ("+") in the appropriate row and column. This will pop open a window to the Members table. Select the Member who added and/or recommended the member in question.

• The ‘Added’ and ‘Recommended’ fields are automatically updated to reflect members added or recommended by the current member.

• When a member is assigned a ‘Rating’, the cumulative added and recommended scores for the member(s) who added or recommended him are automatically updated.

• ‘AddNbr’ and ‘RecNbr’ are also automatically calculated, in case you want to weight or average the raw scores.

This should give you single-entry record-keeping from a single screen. It should also be open enough to allow you to build whatever additional member tracking and incentives you wish.

As the link is read-only, I think it won’t allow you to examine the field definitions. Accordingly, I’ve embedded field configuration in the field descriptions. (Of course, should this meet your needs, you can copy the base and freely edit it.)

If I’m too far off somewhere, let me know and I’ll see if I can whack it back into shape.

Vann

[Atiq_Zabinski]

Thanks, that’s exactly what I was looking for! This gives me new hope that we might be able to use AirTable for our project.

[W_Vann_Hall]

Great! I’m glad my near-obsessive compulsion to spend time working on everything except what I’m supposed to be proved helpful to someone, at least, this time around! :winking_face: