Skip to main content

If() with and() or()

S

Hello community!

I’d love some troubleshooting help. What I really want is already detailed in a feature request here, where I can compare two arrays and remove the values that are in both. In the meantime, I’m trying to create a workaround and running into a few snags.

Here is a mockup of the base I’m working in: Link

I’m working with a gear reservation and rental system for a camping program. There is only one program at a time, and participants reserve rental gear ahead of time (through an online form, email, or a phone call). Requests come in and are put into airtable by whoever answers the phone.

So in the participant table, there is a column for requests, and a column for promises. I’d like to (for each participant), automatically calculate a third column for “request denials”. It may be easiest to think about it this way: “gear requests” MINUS “gear promises” EQUALS “gear denials”. If participant1 requests a raingear top and receives a raingear top, then request denial is blank. If participant2 requests a raingear top and a sleeping bag, and receives only a sleeping bag, then request denial for that participant is “raingear top.”

My hope is to create a nested if statement. I’m not yet to the nested part, I’m just trying to establish proof of concept that it works for one item.

Here is the function I’ve tried, that doesn’t work, to return nothing if “Rain jacket: W XS” is in both columns, or if it’s in neither column, and returns “Rain jacket: W XS” if it’s if only one column.

IF(
  OR(
     AND(
         FIND(
             "Rain jacket: W XS", {Gear requested}
              ),
         FIND(
             "Rain jacket: W XS", {Gear promised}
              ),
      ),
     AND(
         NOT(
            FIND(
                "Rain jacket: W XS", {Gear requested}
                ),
            ),
         NOT(
            FIND(
                "Rain jacket: W XS", {Gear promised}
            ),
         ),
      ),
  "", 
  "Rain jacket: W XS"
   ),
)

Is this how IF works with OR and AND?

If this works, then I’ll work on nesting the IF statement for all of the items.

I’d appreciate any thoughts, guidance, or other ways to make this work.

Thanks!

Sean

    3 replies

    T
    • Tyler_Kurlas
    • Participating Frequently
    • 26 replies
    • July 11, 2018

    Hey Sean,

    This is just to flag your list… it requires the extra step of opening the linked record making sure your promised fields are in the same order as your requested. Hope this helps until arrays are better supported…

    IF({Gear requested}={Gear promised},‘OK’,‘See List/Verify Order’)


    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • July 11, 2018

    To address your original request — namely, to compare two arrays and remove all elements found in both — it is likely doable, albeit not very pretty. One limiting factor is Airtable’s current inability to loop through processes. Instead, one has to create an explicit loop for up through the expected maximum number of iterations. The second limiting factor is the potential ungainliness required to address variable-length array items. (For an example of how challenging this can be, take a look at my reply to a user who wished to extract hashtags from text.)

    [Brief break to go read your feature request.]

    Oh, right — I remember that! Not sure why I didn’t respond; possibly because @Jeremy_Oglesby beat me to it. I also hadn’t wrapped my head around that hashtag request noted earlier, so I hadn’t dealt with the issue of how to address the nth anything from free-form text.

    That said, I’m not going to address that, here — check the earlier link to see why. Instead, I’ll offer some pseudocode for returning an ArrayC containing only those items in ArrayB that aren’t present in ArrayA, assuming array items of a standard, fixed length.

    By that last bit I mean each item in the array is the same number of characters long. Depending on the nature of the items, this could be accomplished either by modifying the items as exposed to the user — for instance, by changing 'Rain jacket: W XS' to 'RJkt:W:SS' — or behind the scenes, performing a substitution before and after XORing the array. For sake of your UX, I’ll assume the latter.

    Assumptions: I’m not sure how you’re creating your arrays. (If you are rolling up linked records and having trouble getting ARRAYUNIQUE() to work as expected, see this reply.) I assume, though, your arrays look something like this:

    Rain jacket: W XS, Rain jacket: M XS, Sleeping bag: Single
    1. Normalize both {Gear Requested} and {Gear Promised} to fixed-length fields, This is done with nested SUBSTITUTE() functions, one depth per possible item:
    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Gear Requested,
                'Rain jacket: W XS',
                'RJWXS'
                ),
            'Rain jacket: M S',
            'RJMS '
            ),
        'Sleeping bag: Single',
        'SBS  '
        ),
    'Sleeping bag: Double',
    'SBD  '
    )&','

    Do the same for {Gear Promised}. This should result in encoded strings in this format (with a trailing comma):

    RJWXS, RJMS , SBS  ,

    Note that codes with fewer than five digits are right-padded with spaces. Also note that, for deeply nested SUBSTITUTE()s, I often find it easier not to indent as I would an IF() statement but instead concatenate the function calls, as so:

    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    {Gear Requested,`
    'Rain jacket: W XS',
    'RJWXS'
    ),
    'Rain jacket: M S',
    'RJMS '
    ),
    'Sleeping bag: Single',
    'SBS  '
    ),
    'Sleeping bag: Double',
    'SBD  '
    )
    1. Figure out how many elements are in {Gear Promised encoded}. Keep in mind this is not the number of elements you can subtract from {Gear Requested encoded}; that number will be determined by a hard maximum built into the formula. Instead, this will be a number less than or equal that maximum indicating how many iterations of the subtraction process to apply. Calculating this upfront greatly simplifies the resulting formula, as it no longer has to determine whether each iteration is valid. I’m going to call the field {NumberOfItems}; it’s a formula field with the following formula:
    LEN(
    {Gear Promised encoded}
    )-LEN(
        SUBSTITUTE(
            {Gear Promised encoded},
            ',',
            ''
            )

    You may want to throw an alert if {NumberOfItems} is greater than {MaxItems}.

    1. For up through {MaxItems}, eliminate items from {Gear Requested encoded} that match an item in {Gear Promised encoded}. The following assumes {MaxItems} of 5; it can easily be expanded.
    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    {Gear Requested encoded},
    IF(
        {NumberOfItems}>=1,
        MID({Gear Promised encoded},1,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=2,
        MID({Gear Promised encoded},8,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=3,
        MID({Gear Promised encoded},15,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=4,
        MID({Gear Promised encoded},22,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=5,
        MID({Gear Promised encoded},29,5),
        ''
        ),
    ''
    )

    1. This leaves you with {Gear Denials encoded}. Reverse the process of Step 1 to return the array to user-friendly phrasing. Note: The resulting array will have a dangling comma; if that bothers you the way it would me, wrap {Gear Denials encoded} with

      LEFT({Gear Denials encoded},LEN({Gear Denials encoded})-1)

      before decoding to {Gear Denials}.

    If you want an array containing all items unique to {Gear Requested} or {Gear Promised}, process once for {Gear Requested} - {Gear Promised} and once for {Gear Promised} - {Gear Requested} and then combine the arrays.

    S
    W_Vann_Hall wrote:

    To address your original request — namely, to compare two arrays and remove all elements found in both — it is likely doable, albeit not very pretty. One limiting factor is Airtable’s current inability to loop through processes. Instead, one has to create an explicit loop for up through the expected maximum number of iterations. The second limiting factor is the potential ungainliness required to address variable-length array items. (For an example of how challenging this can be, take a look at my reply to a user who wished to extract hashtags from text.)

    [Brief break to go read your feature request.]

    Oh, right — I remember that! Not sure why I didn’t respond; possibly because @Jeremy_Oglesby beat me to it. I also hadn’t wrapped my head around that hashtag request noted earlier, so I hadn’t dealt with the issue of how to address the nth anything from free-form text.

    That said, I’m not going to address that, here — check the earlier link to see why. Instead, I’ll offer some pseudocode for returning an ArrayC containing only those items in ArrayB that aren’t present in ArrayA, assuming array items of a standard, fixed length.

    By that last bit I mean each item in the array is the same number of characters long. Depending on the nature of the items, this could be accomplished either by modifying the items as exposed to the user — for instance, by changing 'Rain jacket: W XS' to 'RJkt:W:SS' — or behind the scenes, performing a substitution before and after XORing the array. For sake of your UX, I’ll assume the latter.

    Assumptions: I’m not sure how you’re creating your arrays. (If you are rolling up linked records and having trouble getting ARRAYUNIQUE() to work as expected, see this reply.) I assume, though, your arrays look something like this:

    Rain jacket: W XS, Rain jacket: M XS, Sleeping bag: Single
    1. Normalize both {Gear Requested} and {Gear Promised} to fixed-length fields, This is done with nested SUBSTITUTE() functions, one depth per possible item:
    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Gear Requested,
                'Rain jacket: W XS',
                'RJWXS'
                ),
            'Rain jacket: M S',
            'RJMS '
            ),
        'Sleeping bag: Single',
        'SBS  '
        ),
    'Sleeping bag: Double',
    'SBD  '
    )&','

    Do the same for {Gear Promised}. This should result in encoded strings in this format (with a trailing comma):

    RJWXS, RJMS , SBS  ,

    Note that codes with fewer than five digits are right-padded with spaces. Also note that, for deeply nested SUBSTITUTE()s, I often find it easier not to indent as I would an IF() statement but instead concatenate the function calls, as so:

    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    {Gear Requested,`
    'Rain jacket: W XS',
    'RJWXS'
    ),
    'Rain jacket: M S',
    'RJMS '
    ),
    'Sleeping bag: Single',
    'SBS  '
    ),
    'Sleeping bag: Double',
    'SBD  '
    )
    1. Figure out how many elements are in {Gear Promised encoded}. Keep in mind this is not the number of elements you can subtract from {Gear Requested encoded}; that number will be determined by a hard maximum built into the formula. Instead, this will be a number less than or equal that maximum indicating how many iterations of the subtraction process to apply. Calculating this upfront greatly simplifies the resulting formula, as it no longer has to determine whether each iteration is valid. I’m going to call the field {NumberOfItems}; it’s a formula field with the following formula:
    LEN(
    {Gear Promised encoded}
    )-LEN(
        SUBSTITUTE(
            {Gear Promised encoded},
            ',',
            ''
            )

    You may want to throw an alert if {NumberOfItems} is greater than {MaxItems}.

    1. For up through {MaxItems}, eliminate items from {Gear Requested encoded} that match an item in {Gear Promised encoded}. The following assumes {MaxItems} of 5; it can easily be expanded.
    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    {Gear Requested encoded},
    IF(
        {NumberOfItems}>=1,
        MID({Gear Promised encoded},1,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=2,
        MID({Gear Promised encoded},8,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=3,
        MID({Gear Promised encoded},15,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=4,
        MID({Gear Promised encoded},22,5),
        ''
        ),
    ''
    ),
    IF(
        {NumberOfItems}>=5,
        MID({Gear Promised encoded},29,5),
        ''
        ),
    ''
    )

    1. This leaves you with {Gear Denials encoded}. Reverse the process of Step 1 to return the array to user-friendly phrasing. Note: The resulting array will have a dangling comma; if that bothers you the way it would me, wrap {Gear Denials encoded} with

      LEFT({Gear Denials encoded},LEN({Gear Denials encoded})-1)

      before decoding to {Gear Denials}.

    If you want an array containing all items unique to {Gear Requested} or {Gear Promised}, process once for {Gear Requested} - {Gear Promised} and once for {Gear Promised} - {Gear Requested} and then combine the arrays.


    Wow! @Tyler_Kurlas & @W_Vann_Hall thank you. I’m still interested in doing this, also since I am getting ready for a similar problem when participants check out gear and return it (I’ll want a third column for ‘gear not returned’). This will take me some time to work out, I hope to be back in a few weeks with an update. Thank you so much!

    Reply


    Most helpful members this week

    Terms & Conditions