Skip to main content
Solved

Nested IF with OR

  • April 8, 2022
  • 3 replies
  • 39 views
S

Hi everyone, I’m trying to set a field value based on a formula :

IF(
	OR(
		{Commandes} = BLANK(), 
		{Date_heure_RDV} = BLANK()), 
		"", CONCATENATE({Commandes} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm')),
IF(
	OR(
		{SILGAN BOL} = BLANK(), 
		{Date_heure_RDV} = BLANK()), 
		"", CONCATENATE({SILGAN BOL} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm'))   
)   
)

But when saving the formula… it just keep the first part. Is it possible to do this kind of nesting?

Thanks for your help

Best answer by Ben_Young1

Hey @Sebastien_LEBLANC!

Here’s my version of your formula:

IF(
    AND(
        {Date_heure_RDV},
        OR(
            {Commandes},
            {SILGAN BOL}
        )
    ),
    IF(
        AND(
            {Commandes},
            {SILGAN BOL}
        ),
        {Commandes} & " - " &
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        )
        & '\n' &
        {SILGAN BOL} & " - " & 
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        ),
        IF(
            {Commandes},
            {Commandes} & " - " &
            DATETIME_FORMAT(
                {Date_heure_RDV},
                'M/DD/YYYY H:mm'
            ),
            IF(
                {SILGAN BOL},
                {SILGAN BOL} & " - " &
                DATETIME_FORMAT(
                    {Date_heure_RDV},
                    'M/DD/YYYY H:mm'
                )
            )
        )
    )
)

This formula produces the following results:

Let me know if I’m misunderstanding something about the formula, or if you want to tweak something about it’s behavior.

3 replies

Marielle_Gueis1
Forum|alt.badge.img+10
  • Marielle_Gueis1
  • Participating Frequently
  • April 8, 2022

Hi Sebastien,

Yes, it’s possible to nest, but a bit differently.
You can write the formula like this: IF(CONDITION1,OPTION1,IF(CONDITION2,OPTION2,OPTION3))

Maybe in javascript it makes more sense…
if (CONDITION1) { OPTION1 } else if (CONDITION2) { OPTION2 } else { OPTION 3 }
With this nested technique you only have 3 options. In your case you have twice “” (or Blank()) as option, so you could set “” as option 3 (in my shema), change your conditions a bit around and it should work.

Hope this helps!

S
Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • Answer
  • April 8, 2022

Hey @Sebastien_LEBLANC!

Here’s my version of your formula:

IF(
    AND(
        {Date_heure_RDV},
        OR(
            {Commandes},
            {SILGAN BOL}
        )
    ),
    IF(
        AND(
            {Commandes},
            {SILGAN BOL}
        ),
        {Commandes} & " - " &
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        )
        & '\n' &
        {SILGAN BOL} & " - " & 
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        ),
        IF(
            {Commandes},
            {Commandes} & " - " &
            DATETIME_FORMAT(
                {Date_heure_RDV},
                'M/DD/YYYY H:mm'
            ),
            IF(
                {SILGAN BOL},
                {SILGAN BOL} & " - " &
                DATETIME_FORMAT(
                    {Date_heure_RDV},
                    'M/DD/YYYY H:mm'
                )
            )
        )
    )
)

This formula produces the following results:

Let me know if I’m misunderstanding something about the formula, or if you want to tweak something about it’s behavior.

S

Hey @Sebastien_LEBLANC!

Here’s my version of your formula:

IF(
    AND(
        {Date_heure_RDV},
        OR(
            {Commandes},
            {SILGAN BOL}
        )
    ),
    IF(
        AND(
            {Commandes},
            {SILGAN BOL}
        ),
        {Commandes} & " - " &
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        )
        & '\n' &
        {SILGAN BOL} & " - " & 
        DATETIME_FORMAT(
            {Date_heure_RDV},
            'M/DD/YYYY H:mm'
        ),
        IF(
            {Commandes},
            {Commandes} & " - " &
            DATETIME_FORMAT(
                {Date_heure_RDV},
                'M/DD/YYYY H:mm'
            ),
            IF(
                {SILGAN BOL},
                {SILGAN BOL} & " - " &
                DATETIME_FORMAT(
                    {Date_heure_RDV},
                    'M/DD/YYYY H:mm'
                )
            )
        )
    )
)

This formula produces the following results:

Let me know if I’m misunderstanding something about the formula, or if you want to tweak something about it’s behavior.


@Ben.Young : You have indeed gave me the right solution… but I’m not sure I can grasp how you came to it. Anyway thanks a lot, I’ll look more into it so I could “reboot” my logic and old programmer mindset… I’ve done this for almost 20 years… but it’s been almost 10 years since I left the trade… I’m rust :thinking:

@Marielle_Gueissaz1 : Thanks for your input, I’ll “mix it” with Ben.Young solution and try to better understand why I couldn’t came with a working solution all by my self.

Marielle_Gueis1
Terms & ConditionsAccessibility statement