Dupplications / look-up

[CE_Management_T]

Hey guys,

I’ve been really struggling with this for quite a long, hope you can give me a hand. I’m aware, that those questions have been asked already several times here, but still I couldn’t apply any of proposed solutions to my case.

1. I have table A where I’m adding booking numbers. There are also several “special” booking numbers in table B. Whenever in table A there is a booking number, that already exists in table B, I want to have it tagged/highlighted in the next column (in table A).

2. In the same table A, I’m adding a notification numbers. Whenever I add the notification number that already exists in the same table A, I want to have it highlighted in the next colum.

Thanks a lot

[Nathaniel_Grano]

Hm-
For the first item:
Set up table B as follows:

• Make the second column your “Booking Number” field as a Link to another Record field that links to table A
• Make the first column a formula column, something like "Special-Booking-" & {Booking Number}
• When you set up your link field in table B, a reciprocal field is automatically created in table A. Rename that field to “Special Booking”

So now, anytime you add a record to table B, you’ll be specifying its booking number and then that link will show up in the second column in table B.

Table A

Table B

For your second item, I’d recommend creating a third table called “Notifications”:

• The first field of your Notifications table will be the Notification number.
• Your notification field in Table A should now become a “Link to another record” field pointing to this third table.
• The reciprocal field in the Notifications table can be labeled “Bookings”
• Add a Count field in the Notifications table named #Bookings that counts the number of linked Bookings
• Bring the #Bookings field into Table A as a lookup
• If necessary, add a formula column to Table A with something like
  IF({#Bookings (from Notification Number)}>1,"duplicate notification")

So now Table A looks like this:

And the Notifications table looks like this:

[Bill_French]

CE_Management_Team:

… Dupplications / look-up

The irony of two p’s in the title does not escape me. :winking_face:

[CE_Management_T]

Hey guys, thanks for help

@Nathaniel_Granor
So the first item I managed to solve.

I’m still struggeling with the second one - I can’t follow your suggestion, becuase the notification number is a main column introduced from the form, so it can’t be a link to another record.
I’ve been trying to solve it with zzbot, dedupe, but the problem is that it must be automatic, without pressing any button, or waiting 10 min for refresh.

Can we modify somehow the inicial idea? Or do you have any other suggestions?

Thanks a lot,

@Bill.French I’m glad it didn’t :stuck_out_tongue_closed_eyes:

[Nathaniel_Grano · Accepted solution]

Whew. OK, I think I have a solution and it’s a bit… weird.
In order to do any formulas with arrays in your table you have to have a rollup of some kind to make the array, and in order to do that, there has to be a linked record involved. So…

Forgetting my earlier suggestion…

• Your Notification ID remains just a text or numeric column
• Create a new table, let’s call it “Helper Table”
• Create a link to Helper Table from table A
• Create a single record in Helper Table. We can name it “Helper Record”. Every record in table A must link to this same single record.
  • You can create a link to your form that includes a prefill value for that field so that every entry includes it: How to Pre-fill a Form - Overview | Airtable Support
• In Helper Table add a lookup field named “Notification Rollup” that is looking up the Notification ID column from Table A

So now Helper Table will look something like this:

• Back in Table A, add the “Notification Rollup” field from Helper Table as a lookup. I’ve just named this new lookup field “Rollup” for short. This will result in every Booking record having the same rolled up list of all the notification IDs.
• Now add a new formula field with this convoluted formula:

IF(
  FIND({Nonlink Notification},Rollup,1)>0,
  IF(
    FIND({Nonlink Notification},Rollup,FIND({Nonlink Notification},Rollup,1)+1),
    "Duplicate Notification Number"
  )
)

Your Table A now looks like this:

You can of course hide the “Helper Table” column and the “Rollup” column from your view.

Remember: Every Table A record must link to the same Helper Record for this method to work.
What is the formula doing, you ask?
It is using the FIND function to search for the 2nd occurrence of the Notification Number in the rolled-up list of notification numbers. If there is a 2nd occurrence that means we have a non-unique notification number. If there is only 1 occurrence then Notification Number is unique.

I’m pretty proud of finding this crazy workaround by the way. I hope it works for you, but even if it doesn’t this was a fun puzzle!