Mesage Template

[Petar_Blazevic]

Hello, 

im trying to make action that will send pre-defined message to customer over WhatsApp. 

Problem im facing is when i try to substitute template message with customer specific information. I know that i need multiple substitute expressions. Problem for me is that SUBSTITUTE breaks formatting. 

does anyone have some ideas how to circle around this problem? 

[Steve]

I don't see why you'd need to use SUBSTITUTE().

How does SUBSTITUTE() break formatting?

Please post a screenshot of the entire expression you're using.

[Petar_Blazevic]

I have predefined message saved in LongText type field in Google sheet, this message is something like:

Hello <<Guest Name>>, 

in few days you will arrive at our <<Property>> for your vacation. Here is some final information before your arrival on <<Arrival>>.

so i need to substitute information in <<data>> with actual values i want to use. Because i might change some data later and dont want to have it hardcoded

[Steve]

Text formatting within a spreadsheet cell is entirely ignored by AppSheet. So, SUBSTITUTE() or not, the formatting will be lost.

[Petar_Blazevic]

Text formatting is not ignored as when i read text without SUBSTITUTE() i have predefined text and format in WhatApp

Is there option to Unicode text before passing it to SUBSTITUTE? 

[SkrOYC]

---

@Petar_Blazevic wrote:

I know that i need multiple subs...

---

I'm not so sure about that

[dbaum]

What formatting do you have in the text in your app that you're referring to? WhatsApp doesn't support formatted text anyway, although it does provide basic markup-based formatting.

[Petar_Blazevic]

Im talking about only basic next/new row formating

[Petar_Blazevic]

SOLUTION:

1. use: ENCODEURL()

2. then SUBSTITUTE() with url encoded values that will be substituded.

my example:

CONCATENATE("https://api.whatsapp.com/send?phone=", [Contact uuid].[Phone No.],"&text=", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ENCODEURL(SELECT(Messages[English], ([Name] = "CheckIn"))), "%3C%3CArrival%3E%3E", TEXT([Arrival],"DD.MM.YYYY") ), "%3C%3CCheckInTime%3E%3E", TEXT([App Name].[Check In Time],"HH:MM") ), "%3C%3CSecurity%20Deposit%3E%3E", [App Name].[Security Deposit] ), "%3C%3CGoogle%20Map%20Link%3E%3E", [App Name].[Google Map Link] ), "%3C%3CGuest%20Name%3E%3E", [Contact uuid].[Guest Name] ), "%3C%3CApp%20Name%3E%3E", [App Name].[App Name] ))

[SkrOYC]

So, apparently, you have a "messages" table, that's why you are using your own workaround (with SUBSTITUTE()) to make it work.

CONCATENATE(
  "https://api.whatsapp.com/send?phone=",
  [Contactuuid].[PhoneNo.],
  "&text=",
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              ENCODEURL(
                SELECT(
                  Messages[English],
                  ([Name]="CheckIn")
                )
              ),
              "<<Arrival>>",TEXT([Arrival],"DD.MM.YYYY")
            ),
            "<<CheckInTime>>",TEXT([AppName].[CheckInTime],"HH:MM")
          ),
          "<<Security Deposit>>",[AppName].[SecurityDeposit]
        ),
        "<<Google Map Link>>",[AppName].[GoogleMapLink]
      ),
      "<<Guest Name>>",[Contactuuid].[GuestName]
    ),
    "<<App Name>>",[AppName].[AppName]
  )
)

 Next time would be great to know this kind of details.

PS: It's not a bad idea though, just somewhat cumbersome

[Petar_Blazevic]

"<<Arrival>>" should be "%3C%3CArrival%3E%3E" due to ENCODEURL

What now i would love it that

Messages[English] could be dinamic depending on contact language. Example Messages[English] or Messages[German]