More on Self Joins

[Julian_Kirkness]

I build HR Solutions and am trying to work out how to create reporting relationships in Airtable.

My usual approach is to have a Employee table and a Jobs table…

Employee links to Jobs via the Job field on Employee

The Jobs table has a self Join via field called Reports to the Manager’s Job

This finally links back to the Employee table again to access the Manager’s information

This then typically allows me to use the macro/calculated field/query features to access an employee’s manager’s details for things like routing approval emails for leave requests.

This seems to fall at the first hurdle in Airtable because Lookup fields don’t seem to know anything about self joins. Is there any way round this because it\s pretty fundamental to most things I do with databases?

[Julian_Kirkness]

… thoughts…

I think it may be possible to calculate some of these values with a Multi Step Zap - however my initial attempt to use Find in Zapier - I was trying to Find the parent employee record of a leave and the ‘Employee’ field contained a record ID rather than the visible value of the key field - I couldn’t work out a way to search on this - is there one?

My alternative is going to involve setting up auto number fields and Lookups and use these to search but it seems a bit clunky to say the least.

[Tim_Wilson]

I may be misunderstanding you here, but I’m using a self join in a table that includes supervisory relationships. If you create a link field, you can link to the current table.

[Julian_Kirkness]

Hi Tim

Thanks for your comment - I have set up a Link field called Manager (a Self Join) which works fine - my issue is how to lookup other fields from the linked record. I need to be able to get hold of the email address field from the Manager’s record.

If I create a new Lookup field it doesn’t give me the option of choosing a field from the Manager link.

[Julian_Kirkness]

Well - I have created a “Manager Text” field as a formula to hold the text value of the Manager link field and Zapier was able to search using this. As I said - clunky.

I assume this is because the API returns the ID for Link fields?

I can see that this could give rise to quite a few unwanted formula fields and is pretty unintuitive - is their either a better way to do this type of thing or plan to solve the problem?

[Matthew_Billiod]

Hey @Julian_Kirkness,
Thank you for the post!

I think your lookup issue could be fixed temporarily by having “full text” search capability on the lookup popup. (See Find a Record to Link - Search Expansion).

A better long term solution is what you were talking about initially: having a query for the lookup field. That was proposed in this post: Filter on linked fields.

Please check both out; provide your input and use case; and please like the post… doing so informs the airtable team of how important the feature is to the user base.

[Julian_Kirkness]

HI @Matthew_Billiodeaux

The issue isn’t how to select a related record - it’s how to use a Lookup (not Link) field to pull information from a linked record - and specifically when the Linked record is through a Self join. Under these circumstances, trying to create the Lookup field doesn’t give you the option to select the Self Join as a source of the field (i.e. where you choose the column linking to the other table).

As I have said, I have found a way for Zapier to find information from a self joined table using Find but the resulting solution if a bit too clunky for my liking.

Any more ideas?

[Scott_Bleackley]

Here is an example self join linked field ( works fine ) then a field to do lookup on that join field. Note that the invoice table is not one of the optional selections. In this case I want to find the difference of the sum of all of the payments and invoices for a particular client, to arrive at an outstanding amount.

The best way I have found, so far, to solve this problem is to create a lookup field in the “income table” on the “invoice amount”, then create a lookup field in the “invoice table” on the Invoice lookup, in the “Income table”. Not great, or very elegant, but it works,

Any thoughts or suggestions are most welcome.

[Katherine_Duh]

Hi everyone! Lookup, rollup, and count fields now support looking up, rolling up, and counting from a linked record field that has been linked to its own table. This will make it easier to model the relationships between records that exist in the same table. Going to go ahead and set a timer for this thread to close—thank you all for your patience, and happy linking!