Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 01, 2022 08:22 AM
Hi all- I have a table sourced by a CSV upload with properties and owner names (and lots of other fields) on single rows, 1 row per property. Some owners own multiple properties. I’m using Airtable to clean this up and separate the single row source data into two different views- one of ‘properties’ to upload properties as ‘deals’ into a CRM, and another view of ‘people’ upload as ‘contacts’ in the CRM.
If there are 50 properties, there may be 35 owners, ergo, 15 owners own more than one property, but i only want to upload 35 owners, and those who own multiple will be linked to multiple. I don’t want to upload duplicate instances of the same contact just because one person owns two properties. I just want one instance of that person, and in the CRM would link them to both properties.
I’ve used this solution from @W_Vann_Hall to identify when an owner owns multiple properties, and have a count of properties owned, but what i am trying to do now is create condition in the records that i can use to get a view showing only one instance of the person who may own multiple properties.
Referring to the link and sample base above, in terms of that sample data, I want a view in the ‘job names’ sheet that would show all records where there is a value in unique jobs, AND would show just one record with “Training Coordinator” and one instance of “Dermatology Nurse” As it’s set up now, those two job names appear twice in the job name col, but not at all in the unique name field, so the formula is isolating and effectively removing them… I want to find a way to show one instance of each of those two jobs, in addition to all the other unique jobs. Effectively, filter OUT the 2’nd instance of training coordinator and dermatology nurse… or 3’rd 4th etc etc
A way to number the records would work, but i can’t figure out how to do that- eg, identify that 'property 1 is 1’st instance of this owner, property 2 is the second instance, and so on, but i can’t figure out how to make that happen.
I appreciate any thoughts and ideas,
Jan 01, 2022 10:57 AM
Can you create a new Owners table that is linked to the properties table? You can do this by converting an owner field into a linked record field. Then you can use a count or rollup field to determine the number of properties per owner.
Even if the CSV import is part of an ongoing workflow, you might still be able to leverage a two table system.
Overall I no longer recommend the method of linking all records in one control record unless absolutely necessary. The resulting giant values in the linked record field of the control record, and the giant rollup fields do not scale well as your dataset grows, and they can slow down your base.
Jan 01, 2022 11:53 AM
I have a count of the properties per owner using the ‘giant rollup method’ and i must confess, I do not understand the formula being used but it works… However i don’t know how i’d create a separate table of owners from the single flat file source data that has multiple instances of owner data when they have multiple properties … i’ll explore that and see if i can make it work