Array Function in Beast Mode

rmbourne
rmbourne Member
in Beast Mode

Hello,

I created a card where users can filter multiple IDs at once. This is useful to quickly filter the card based on the account IDs that we got from another card. Users can simply highlight all the Account IDs, copy and paste them in the Input Text Box

image.png

The variable is called enter_account_ids. Is there a better approach than this formula?

image.png
image.png

I realized that IN() function only works if the items within it are individual strings so I just can't reference the variable enter_account_ids even if I fixed the format to be csv. With that I can't find anything else apart from SPLIT_PART. However, part of its syntax is the position of the string you wanted to extract so I had to make 700 lines of them to make sure all the account ids that will be entered in the variable will be included in filtering the card. I wonder if there's any function available in Beast Mode like the TEXTSPLIT() in excel or anything that would simply split all the strings through the specified delimiter without the need to specify any position. Any alternative will do as long as not this long.

Thank you.

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @rmbourne I would recommend using the INSTR function instead of having to check every possible split like this:

    case when INSTR(`enter_account_ids`,`account_id`) > 0 then `account_id` end
  • marcel_luthi
    marcel_luthi Coach
    edited February 2024 Answer ✓

    @MichelleH approach seems like the more reliable option, clearly you'll need to tweak it a bit, since if for example your user provides 1013 as the Id to look for, this would match accounts Ids 10, 101 and 13 as well, if you know that the list of IDs will always be separated by spaces, then you could do something like:

    CASE WHEN INSTR(`enter_account_ids`,CONCAT(' ',`account_id`,' ')) > 0 //Id is in the middle
|| `enter_accounts_ids` LIKE CONCAT('% ',`account_id`) //ends with the Id
    
|| `enter_accounts_ids` LIKE CONCAT(`account_id`,' %') //starts with the Id
    
|| `enter_accounts_ids` = `account_id` // Is the only Id
THEN 'true'
    
ELSE 'false' 
    
END

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @rmbourne I would recommend using the INSTR function instead of having to check every possible split like this:

    case when INSTR(`enter_account_ids`,`account_id`) > 0 then `account_id` end
  • marcel_luthi
    marcel_luthi Coach
    edited February 2024 Answer ✓

    @MichelleH approach seems like the more reliable option, clearly you'll need to tweak it a bit, since if for example your user provides 1013 as the Id to look for, this would match accounts Ids 10, 101 and 13 as well, if you know that the list of IDs will always be separated by spaces, then you could do something like:

    CASE WHEN INSTR(`enter_account_ids`,CONCAT(' ',`account_id`,' ')) > 0 //Id is in the middle
|| `enter_accounts_ids` LIKE CONCAT('% ',`account_id`) //ends with the Id
    
|| `enter_accounts_ids` LIKE CONCAT(`account_id`,' %') //starts with the Id
    
|| `enter_accounts_ids` = `account_id` // Is the only Id
THEN 'true'
    
ELSE 'false' 
    
END
  • rmbourne
    rmbourne Member

    Wow! I can't believe it's that simple. Thank you @MichelleH and @marcel_luthi, it works just the same. Plus, if ever we reach more than 700 accounts in the future, there's no need to add more lines in the formula. Thanks!

Categories