Great way to make [List of Notices] for a record

[MultiTech]

Often I find myself needing to find a way to communicate several things to a user for a record. Things like:

• You forgot to create child records that are required for correct operation
• Something needs to be assigned to something
• You still have X number of things to do
• We need a signature
• The order hasn’t been paid, it’s missing something else, and another thing

Whenever I have a list of things I need to communicate to users, I find myself creating something like what you see above. The list is dynamic, meaning there may be 15 items in the list and it will expand/contract based on how many items are in the list

Here’s the formula used

substitute(
list(
	if(IsBlank([Related InstPhase_Sections]), 
		" --> Missing Sections",
	"nope"),
  
	if(IsBlank([Installation_Phase_Required_Images]), 
		" --> Missing Required Items",
	"nope"),
  
	if(and(
		IsBlank([Related InstPhase_Sections]), 
		IsNotBlank([Installation_Phase_Required_Images])
	),
		" --> Need to assign required items to a section",
	"nope"),
)
- list("nope")
, " , ",
"
")

• The column type is a LongText - this way you get multi-line functionality
• It starts with a SUBSTITUTE(), but if you set that aside for a moment you’ll notice the next bit is a LIST() - that’s the meat-and-potatoes of it all.
• The central idea here is we’re literally creating a list of things to display to the user
• The key in getting things to “go away” when we want them to is to include a “fallback” option…
  • then remove that option from the list. (^_^)
• We then take that list and substitute out the separator used, and replace it with a line-break
  • this puts each item on it’s own line
• When you need to add something else into the list, meaning you’ve got another reminder you need to show people, you just add in another option branch

Formula Breakdown

• Lines 1-2: basic openers
• Line 3: starts an option branch for (if there’s NO sections)
• Line 4: the display entry for when there’s no sections
• Line 5: the fallback option
• Line 6: spacer
• Lines 7-9: option branch for (if there’s NO required images)
• Lines 10-16: option branch for (if there ARE required images, but NO sections)
• Line 17: closing of the list started on line 2
• Line 18: removes the fallback option from the list
  • At this point, the list would contain a bunch of fallback options if any of the option branches defaulted to the fallback
  • So the list would look something like this:
    nope , nope , -->blah blah , etc…
  • So by removing all the fallbacks, we’re left with a list of only the options we need to display
• Line 19: this line holds all the instructions for the SUBSTITUTE() started in line 1
  • The first comma separates out the first part of the SUBSTITUTE() formula (the list)
  • The first set of double-quotes holds the default separator used by SUBSTITUTE() - “space comma space” - and is what the SUBSTITUTE() formula is looking for
• Lines 20-21: The set of double-quotes holds a line break
  • This is then switched out for every instance of the comma separator - which has the effect of putting each item on it’s own line

---

There’s many different ways you can format your list, the bullet point you use could be an actual bullet point font-image, but I figured I would share this quick method for generating a list of reminders - since I use it ALL the time!

[SkrOYC]

Good one, I like it.
I noticed the fact that it’s not a string, instead a list wich makes sense. I don’t imagine making all of this out of IFS().

The usage of SUBSTITUTE to change from a " , " to a line break is great, very ingenious.

I like Show columns instead of normal ones for “messages” to the user but I don’t know if we can apply a format rule to Show columns. If we do, I’d preffer that route, although I wouldn’t be able to reference to this “status” column if I need it somewhere

[MultiTech]

Bonus if you’ve got the “preview new options” turned on for your app

Turn the LongText type into “HTML” and use the following formula instead:

concatenate(
"<ul>
  <li>", 
  substitute(
  	[YOUR_LIST_HERE], 
    " , ", 
    "</li>
  <li>"
  ), 
  "</li>
</ul>"
)

Formula Breakdown

• Line 1: open a CONCATENATE()
• Line 2: starts a string - with an Unordered List HTML opening tag
• Line 3: closes the string - with a List Item HTML opening tag
• Line 4: starts a substitute (similar idea to what we’re doing in the first post)
• Line 5: this is where you list goes, in whatever form it takes (as long as it’s a list!)
• Line 6: This is the default separator used by lists, and the thing we’re feeding to the second part of the SUBSTITUTE()
• Line 7: starts a string - with a list item HTML closing tag
• Line 8: closed the string - with a list item HTML opening tag
  • Both lines 7 & 8 contain the “replacement” part we’re feeding to the SUBSTITUTE()
  • So each default separator is instead going to be replaced with the line item tags
• Line 9: closes the SUBSTITUTE()
• Line 10: starts a string - with a list item HTML closing tag
• Line 11: closes the string - with an Unordered List HTML closing tag
• Line 12: closes the starting CONCATENATE()

[SkrOYC]

BTW, you can forget about the “nope” ones if you use IFS() instead of IF() since the last one needs a third argument and the first one doesn’t afaik.

Something like this:

SUBSTITUTE(
  LIST(
    IFS(
      IsBlank([Related InstPhase_Sections]),
      " --> Missing Sections"
    ),
    IFS(
      IsBlank([Installation_Phase_Required_Images]),
      " --> Missing Required Items"
    ),
    IFS(
      AND(
        IsBlank([Related InstPhase_Sections]), 
        IsNotBlank([Installation_Phase_Required_Images])
      ),
      " --> Need to assign required items to a section"
  ),
  " , ",
  "
"
)

Or is it that IFS() that are False produce blank instead of nothing and you would need to remove the blanks anyway*?*

[MultiTech]

Sure, but I prefer to easily see things - especially when I’m sharing a formula for others with less understanding of the ins-and-outs. It’s clear a dry what’s happening with the fallbacks and removing them from the list; if I used blanks it’s not as clear.

Just remember to remove the blank option from the list!

[SkrOYC]

Yeah, I noticed that when I was writing it! Thanks

[MultiTech]

Oh and here’s another way (if you don’t want to include the bullet inside the list item itself)

concatenate(
  "  -->", 
  substitute(
    [Tmsht_Remaining_Users_Need_Timesheets][Name], 
    " , ", 
    "
  -->"
  )
)

Helpful for creating a nice derivative list for display

[MultiTech]

@SkrOYC

I like Show columns instead of normal ones for “messages” to the user, but I don’t know if we can apply a format rule to Show columns. If we do, I’d prefer that route, although I wouldn’t be able to reference to this “status” column if I need it somewhere

Formatting rules on Show columns = true (^_^)

But yeah, then you can’t reference back to the notice column for things.

I like to use the presence of anything inside that notice column as an easy way to tell if something should be in a certain status: IsNotBlank([Warning/Notice_Column])

[Marc_Dillon]

Why are you doing it that way, instead of just:

IFS( x , "text
")
&
IFS( y , "other text
")
&
....

?

[MultiTech]

As I said before…

(^_^)

[Marc_Dillon]

Yah but there must be some reason why you chose a more complicated approach. Does it offer some benefit?

[SkrOYC]

Well, from my POV, you can manipulate list on ways that text can’t, you would need to change it to a list before hand. You know this but as examples you can count, index, intersect, sort, etc

[MultiTech]

Being able to count the reminders can be helpful, but honestly @Marc_Dillon I’ve done it your way as well.

• I gave it a think, and I honestly can’t come up with a good reason why I did it this way

• Simply a matter of that’s what my mind settled on when I needed to make a list of reminders

“¯_(ツ)_/¯“

[Marc_Dillon]

Fair enough

[Fabian_Weller]

I would prefer show type instead of longtext. Show type get’s calculated when you open the view. It is not calculated on sync. So you can save sync time.

[MultiTech]

I remember why I use the long text column over the show column…

With the long text, there’s actual values inside the column - which means I can base functionality off of that fact alone.

IsNotBlank([Reminders])

IsNotBlank([_This])

[Rifad]

This is amazing! How do we add multiple columns of related list. ex: [related comments][comments] just returns list of all related comments. What if I need multiple columns of related column ? Name and comments of same row ? How can we do it ?

[Rifad]

I meant Something like this.

[dbaum]

@Rifad

How do we add multiple columns of related list.

Your question sounds similar to the following recent post: Re: iterating over child rows to create a string - Google Cloud Community . Maybe the technique mentioned there addresses your need.