New Emails In A List Highlighted

[Harrison_Jones]

Hello Everybody,

Firstly thank you so much for any help in advance.

I am trying to mimic a formula that we have in G Sheets where we highlight any new instances of an email address (customer) in a list (but only the first & not any repeating instances) so we can see if the customer is a new customer or if the customer is a repeat customer.

I have hit a wall with trying to figure it out myself. With my current formula, a “new” customer record will switch to an “existing” customer row when the customer buys from us again (as it is no longer unique & has more than one instance in the column)

My current AT formula (totally incorrect I know)

IF(ARRAYUNIQUE({Renter Email (ROOT - LINK)}), “NEW”, “Existing”)

My GSheets formula that I am trying to replicate in AT.

=if(isna(match(E3787,$E$1:$E3786,0)),“New”,“Existing”)

Thank you so much for any help again.

Cheers
Harry

[JonathanBowen]

Hi @Harrison_Jones - from your description it sounds like you have the renter email on each record in the table and in GSheets your formula is able to iterate through all of the records and find a match or not. Airtable can’t do that kind of iteration over records in the same way, so the best way to achieve is this to separate your renters and your rentals into two tables - something like this:

Renters:

Rentals:

So the “renter” field on the rentals record is a link to the renters table.

Now, back on the Renters table you can count the number of rentals and then have a formula where if the count is greater than 1 they are existing, otherwise new:

IF({Count of Rentals} > 1, 'Existing', 'New')

This new/existing value can be pulled back to the rentals table too if you want using a lookup:

JB

[Harrison_Jones]

Hi JB,

Thanks so much for the response.

I have separated the rentals & the users into separate tables & followed your suggestion.

One issue I can see is that when a user makes a 2nd booking, the original rental will not stay as New & will switch to Existing as number of rentals is >1.

Is there a way around that you can think of?

Thanks again for your help.

Cheers,
Harry

[Harrison_Jones]

Hi JB,

Figured it out.

I had 2 columns with a “new bookings ref” & a “master booking ref” for each deal record. The master stays with the rental each time it renews.

If it is a new booking then the master booking ref & the booking ref are the same, so a simple IF they match formula has solved the problem.

Thanks for your help, you are an AT Rockstar.

Harry