Help with my formula please [SOLVED]

Hi,

I think you need to relocate a few brackets. Now it is:

Try:
IF(AND({Mobile - B},{Mobile - CB})=BLANK(),{Mobile - B},IF(AND({Mobile - CB},{Mobile - B})=BLANK(),{Mobile - CB}))

Btw, both conditions are identical. I assume you have something else in mind. In your formula you switch B and CB around but in an “AND” formula that has no meaning.

Regards,
André

If I understand your intent correctly, you want get the number of the borrower / co-borrower that has left you a mobile number if only one of these two has done so, or nothing if none has? In that case, your formula ist basically correct, you are just missing the else condition in the inner IF and the closing parens for the two nested IF conditions. However, what your are doing is simpler to express using an XOR clause (exclusive OR, i.e. only true when only one of its conditions is true; see https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference#logical) and by leveraging the fact one your fields in that specific case is blank:

IF(XOR({Mobile - B}, {Mobile - CB}), CONCATENATE({Mobile - B}, {Mobile - CB}), BLANK())

will return the number either of the borrower or of the co-borrower if only one is set, or nothing if neither is.

Hi Andre

Thanks so much for your help! I love how helpful this community is.

Troy

IF(XOR({Mobile - B}, {Mobile - CB}), CONCATENATE({Mobile - B}, {Mobile - CB}), BLANK())

Hi Martin

Thank you for your help. Yes, you are correct that I want only the phone number of the borrower who provided it. Your suggestion worked great! If I could ask you for a bit more help, I’d greatly appreciate it. First is a question: What is the “else” condition or what does it look like? I couldn’t find it on the formula help page.

Secondly, after thinking it through…I’d like to have both phone numbers combined or concatenated if both were given. This would keep all options for my team in one column vs needing multiple. Can you help with what the formula would look like to do that? Assuming another IF statement but not sure if I can still use the XOR?

Thanks in advance!

Your are correct: if you want to concatenate the two numbers anyway, there is no need for XOR(). The formula to achieve what you are after is a simple concatenation of the two numbers and a conditional separator, i.e. one only inserted when both numbers are present:

CONCATENATE({Mobile - B}, IF(AND({Mobile - B}, {Mobile - CB}), " and ", BLANK()), {Mobile - CB})

Note that you can use any string instead of " and " as a separator, of course.

As to your question about the else clause: one common variant of conditional branching, as it is known in programming, is the if-then-else construct, i.e. if condition is true then do this else do that. Programming languages of the kind you type into a text editor (like, say, JavaScript) usually allow for ommission of the else clause, but AirTable’s formula language does not. You always have to specifiy all clauses, i.e. IF(condition is true , then do this , else do that), with the commas indicating the separation of the condition from the action branches .

Wow…thanks so much Marin! This worked perfect! Thanks again for helping someone with no programming experience. This forum is really cool and I’ve learned a lot by reading the questions of others and the subsequent answers from the community. And thank you for taking the extra time to provide the explanations.

Cheers!
Troy

CONCATENATE({Mobile - B}, IF(AND({Mobile - B}, {Mobile - CB}), " and ", BLANK()), {Mobile - CB})

Hi Martin

I messed up my formula because I honestly don’t understand enough to know exactly what your last formula is doing - if that makes sense:) The concept of it totally did what I want however (If borrower provided their cell then just their cell would be in the field, if the co-borrower the same and then if both, it would show them both as the result) but there at two details that I tried to add in which would make my final results looks like this:

If just borrower cell it would show 2122122121@textmagic.com

If just co-borrower cell it would be the same but with the co-borrower in front 2132132131@textmagic.com

If BOTH it would need show as mailto:2122122121@textmagic.com; 2132132131@textmagic.com

This would allow my staff to simply click on the field to open Outlook with whomever they should be sending the email/sms to.

This should be my last need for help!!

Troy

My last formula does a rather simple thing: it concatenates the two contact address fields. Because a blank field will not produce a string for concatenation, this effectively returns the non-blank field when only one is filled. To avoid just sticking both one behind the other when both fields are filled, there is a middle element in the concatenation, which is either “ and ” if both are filled, or blank if they are not. Effectively, this concatenates one of the following three combinations:

1. Borrower contact, blank, blank
2. Blank, blank, co-borrower contact
3. Borrower contact, “ and ”, co-borrower contact

By changing the separator string in the IF clause to "; ", you will be half way there. To get your mailto: prefix, you can add a copy of the conditional clause as a new first item in the CONCATENATE formula (which would now have four items), or you can just add "mailto:" unconditionally as the first item (as that will also work for single addresses).

I highly recommend spending some time reading up on the pertinent sections of the formula help.

Thanks again for your help and helpful explanations. I’m all set. Cheers, Troy