Implement a conditional lookup that processes variable fields

[Prawler]

I am tracking companies, investors, and founders. It just so happens that some founders are also investors.

For example:
Company X - Founder A - Investor M, Investor N (also Founder C), Investor O
Company Y - Founder B - Investor Q (also Founder A), Investor R, Investor C
Company Z - Founder C - Investor E, Investor F, Investor G (also Founder B)

I want to setup a view where people can see both (a) the Company that the Founder founded (straightforward lookup/linked record), (b) the Companies that the Founder has invested in (if any). How do I achieve this?

[Russell_Findlay]

Couple of thoughts here

1: create 2 separate linked record fields in your companies table both linking to people - title one founder and the other investor and link as relevant - then you can show these as and how you like. You are effectively creating relationship types

2: create a third table a junction table which links an individual to a company and has a field which desigNates the relationship - lookup and roll up fields will sort the rest for you.

[Prawler]

Hi Russel,

Thanks for the reply. I implemented #1 where Investors and Founders are now their own tables (both linked to the original Company table). In order to view the Companies a Founder has invested in (in the Founders table), I created a lookup field that searches Companies “that meet certain conditions.”

The condition that I want to set is “Companies where Investors contains [Insert Founder Name].” However, lookup fields are not compatible with formulas so I am stuck.

[Russell_Findlay]

Hi @Hardik_Mittal

Maybe my Option 1 wasn’t clear …

2 Tables - 1) People and 2) Companies

2 linked record links with different names (Founded - a link to companies that this person founded) and Invested in (a list of companies that this person invested in)

then a lookup field which looks up the linked person record under founder and reports back the companies that he / she is linked to as an investor

Hopefully the pics will give you clarity

Good luck

[Prawler]

Hi Russell,

Thanks for clarifying. If I am not mistaken, that would require me to manually maintain a list of ‘People.’

In my current workflow, I simply create “new linked tables” from the columns you have listed as “Founders” and “Investors” in the Company table. This allows founder and investor entries to automatically populate (in the primary columns of their respective tables) upon company insertion. If this is achievable with either of your design proposals, please let me know.

[Russell_Findlay · Accepted solution]

I think it is - if you write the name into the linked field - (and if it is already there simply select it) then that should work … it does require you not to duplicate which is good practice any way

If it is done by forms you may need to do something to ensure that the name field in people is unique.

I can imagine a periodic dedupe of data may be needed.

Ultimately it is a choice if you want to capture a field of people that can play different roles - so seeing people as an entity / object in the data base or seeing the separation of founder and investor where the data about a person may be duplicated and therefore inconsistent.

It depends on current and future use of the base.

Best of luck