Flatten same lookup entries

[Jerry_Chiang]

I have a table called Contacts where I list the people’s names, company, title, phone, email, etc.
I also have a table called Meeting History where I list the people, venue, date, and notes associated with my meetings.
In that same table, I have a field called Company, which I look up based on the people there. Pretty much all the meetings are with only one company, and usually there is only one person in the meeting, but, when if I record meetings with multiple people from the same company, can is there a way I can just show the single Company once in that field?

For example:
I meet with John and Judy from company ABC.
I add their names to the Contacts table and ABC as their company.
In the Meetings table, I put both their names under People; however, the Company field looks up both of their names and returns “ABC, ABC” as the Company.
Can it just show ABC once? Is there a way to get it to list just the unique companies in the meeting?

[Andre_Zijlstra]

I would find something like this:
First I create a field COMPANY_COUNT. The type of field would be “COUNT”. It will count the amount of times a person is registered in this field.

Then in another field (COMPANY_FLAT) I would use a formula like this:

IF(COMPANY_COUNT<=1,COMPANY,LEFT(ARRAYJOIN(COMPANY),FIND(",",ARRAYJOIN(COMPANY))-1))

ARRAYJOIN: this will get all data from the field, and divides it with a comma.
FIND will locate the position of the comma
LEFT will select the first positions of the field COMPANY and the length is set by the position of the comma minus 1.

[Jerry_Chiang]

Thanks! I think that worked. I find will find the first comma if there is more than one? In that case, it will just return the name of the first company, correct? Not a problem if that’s the case since I believe I can reorder the names in cell.

[W_Vann_Hall]

Also, instead of a lookup field you could use a rollup field with an aggregation function of ARRAYUNIQUE(values). This should return only a single instance of the duplicated company.

Note that if you ever have an instance of there being, say, three individuals from two companies, I’m not sure you can apply ARRAYUNIQUE() and afterwards know which two individuals to associate with a single company.

[Andre_Zijlstra]

@Jerry_Chang @W_Vann_Hall I think the ARRAYUNIQUE works fine. I tested a Base with 3 tables: PERSONEL, COMPANY, MEETING.

• PERSONEL is linked to COMPANY
• MEETING has a field Month and I added PERSONEL from the same COMPANY and in some cases only one COMPANY was selected.
• In the table MEETING a Rollup field looked at the field PERSONAL (in the table MEETING) and checked the field COMPANY with ARRAYUNIQUE, and for sure, every company was mentioned only once. Nice!

PERSONEL

COMPANY

MEETING

ARRAYUNIQUE > ROLLUP