Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 04, 2022 05:47 AM
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.
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).
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
Solved! Go to Solution.
Nov 11, 2022 12:50 PM
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…
So now Helper Table will look something like this:
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!
Nov 04, 2022 11:51 AM
Hm-
For the first item:
Set up table B as follows:
"Special-Booking-" & {Booking Number}
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”:
IF({#Bookings (from Notification Number)}>1,"duplicate notification")
So now Table A looks like this:
And the Notifications table looks like this:
Nov 05, 2022 09:35 AM
The irony of two p’s in the title does not escape me. :winking_face:
Nov 10, 2022 08:25 AM
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:
Nov 11, 2022 12:50 PM
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…
So now Helper Table will look something like this:
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!