Using an Expression, how do I add, but not replace, text in an field?

[cjphoto]

Hey there. I need help with an expression please. I'm creating a bot. When the [CONDITION] is set as VACANT,  [NOTES] is set to "[NAME 1] - Does Not Live here". However, sometimes there are notes already in [NOTES]. I don't want to replace those notes, just add to them.

So I tried this expression:

TRIM(IF(ISNOTBLANK([NAME 1]),
[NOTES]&"
"&[NAME 1]&" "& "- Does not live here", [NOTES]))

But, in my data source, if [NOTES] is blank, it gives me a line break and then the text even with TRIM:

So I tried this expression instead:

IFS(

ISNOTBLANK([NAME 1]),
[NAME 1]&" "& "- Does not live here",

AND(
ISNOTBLANK([NAME 1]),
ISNOTBLANK([NOTES])
),
[NOTES]&"
"&[NAME 1]&" "& "- Does not live here",

TRUE, [NOTES])

This is giving me problems because the bot obviously ends up just using the first IF expression because [NAME 1] is blank. Would appreciate any help on an expression that could give me the results that I'm looking for, if possible. Thanks much!

[koma25]

Look in the documentation of the function [_thisrowbefore]

https://support.google.com/appsheet/answer/11547057?hl=en

[Izzat_Safrah]

Try this.

IFS( 

ISNOTBLANK([NOTES]),

[NOTES]&"
"&[NAME 1]&" "& "- Does not live here",

ISNOTBLANK([NAME 1]),

[NAME 1]&" "& "- Does not live here",

TRUE," "

)

[cjphoto · Accepted solution]

Thank you so much, Izzat! This worked very well! However, when the [NOTES] AND [NAME 1] were blank, it was inputing " - Does Not Live Here". So I added an ISBLANK expression to [NAME 1] to solve that problem.

TRIM(
IFS(

ISBLANK([NAME 1]), [TERRITORY SERVANT NOTES],

ISNOTBLANK([TERRITORY SERVANT NOTES]),

[TERRITORY SERVANT NOTES]&"
"&[NAME 1]&" "& "- Does not live here",

ISNOTBLANK([NAME 1]),

[NAME 1]&" "& "- Does not live here",

TRUE, " "

)
)

Thanks again for your help!