Lookup Column in Sharepoint Online showing unwanted columns from unwanted content types

Question

I am trying to implement a look up field which checks for a column 'CompanyName' which only exists in a single content type: 'Company Content Type'.

When creating a new document set, the field shows options from other document sets, in which the field I am looking for (and also showing) does not even exist.

Scenario:

• Client Content Type - Name, Address, City, Country, Tel No, Description

  • Company Content Type - Name, Address, City, Country, Tel No, Description, BusinessTelNo, CompanyName

  • Individual Content Type - Name, Address, City, Country, Tel No, Description, Surname, MobileNo, IndividualName, RelatedCompany

  • Other Content type: Name, Address, City, Country, Tel No, Description, NotImportant1, NotImportant2

Remarks:

1. Company, Individual and Other Content Types all inherit from Client Content type.

2. I created a calculated field for the content types, e.g. CompanyName and IndividualName (CompanyName takes a formola of =[Title], IndividualName takes a formola of =[Title]&[Surname]. This is so to be able to use these in the look up fields.

3. CompanyName only appears under Company content type.

4. Related Company in the 'Individual Content Type' is a look up field, which looks for 'CompanyName' and requires it to contain information.

5. The look up field, when creating a new 'Individual' document set, shows values from content type 'Company' but also from content type 'Other'.

It makes absolutely no sense.

Has anyone ever encountered this?

Answer 1

I found the root of the problem and have a solution for someone who might run into a similar situation.

Apparently when using the formula to set: CompanyName = [Title] (which takes the value of the default field Name and automatically populates it into CompanyName), CompanyName is being populated in every content type as every content type has the default column 'Name' (and thus 'Title') in the formula applies for every content type (irrespective to whether CompanyName is actually added as a column inside that content type). - I know this does not make a lot of sense but it was surely the problem!

Solution: we need to introduce a condition that only lets 'CompanyName' be populated from within 'Company' Content type.

This can be done by introducing a new field 'CompanyCheck' (Single Line of Text) which by default takes the value of "yes".

The formula of the look up field changes to:

=IF([CompanyCheck]="yes",[Title],"")

which in simple terms mean:

if (CompanyCheck contains "yes)
    then Autopopulate by value of Title 
    else Leave empty

This way if we're creating say an instance of 'Intermediary', the 'CompanyName' would not be automatically populated from 'Title' as 'CompanyCheck' does not exist, and 'CompanyName' will be left empty.

This worked for me.