Substituting specific words in a message

Kat · 11-19-2020 11:38 PM

Good Evening,

I’m attempting to write a letter template where the user can type the name of the recipient into a form and it will automatically be updated in multiple letters. I’ve labeled the table where the recipient’s name is “Info” and the table with the long text “Messages.” The Column where the name is is “Student” and the column where the message is is called “Message.” There are two other substitutions I want to do in these letters as well. Their columns would be “Greeting” and “Parent.”

This is what I have created thus far. I’ve been plugging it into the formula in the Message column in the Messages table. It’s not working at all.

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
[Message],
“Student”,
“Info[Student]”
),
“Parent”,
“Info[Parent]”
),
“Greeting”,
“Info[Greeting]”
)

Thank you for any assistance you can give.

Kat

khuslid

If the letter is to come from a workflow template, you can (in word og google docs template) simply put the names of the colums that are to change. And even use expressions in the template such as lookup, select, etc.
Must be written as <<[Coumnname]>> in the text.

khuslid

Kat

I attempted to plug in the formula <<[Info!Student]>>, but it is not doing anything. As for the information on emails and workflow, I don’t think it is applicable to what I’m attempting to do because these letters aren’t emails and must be sent through the Outschool website. I am completely new to this so please let me know if I missed something when I read through the pages you sent me.

Here are screenshots of what I have so far. The third screenshot is the detail of the classroom message. I want to be able to replace the word student with the student’s name. Also, I will be adding classes and messages through the app on a regular basis so I need to have the substitution functioning with that ability.

Thank you and I hope this provides some clarity on what I’m attempting.

Classroom Message Tab:

List of Messages in each classroom:

Individual Messages:

Detail View where user can input student name and other information:

Thank you so much for any help you can give!

Kat

Marc_Dillon

Please provide screenshots of your table and column definitions within the editor.

Expressions of the form table[column] will return a List type value of ALL values in that column from the entire table.

Looks like you need some way to link the current user to the inputted record with their name, such as matching it to an email address, and using USEREMAIL(). Or you can utilize user-settings for this instead of a real table.

I agree that the workflow advice from @khuslid is not applicable for your situation here.

Kat

I’ve set the Info table to only allow edits and not additions or deletions. This way there is only one row for the Info[Student] to pull from. Below is a screen shot of both the info table in appsheet as well as the original google sheet. I’ve gone ahead and deleted my initial expression until we can get a better solution.

Next is my parent table that includes the class name as well as thumbnail:

And here is the final table of the actual messages.

Thank you so much for your help!

Kat

LeventK

@Kat
Your SUBSTITUTE expression is wrong in terms of syntax and reference. It shall be like this:

SUBSTITUTE(
	SUBSTITUTE(
		SUBSTITUTE(
			[Message],
			"Student",
			LOOKUP(2,"Info","_RowNumber","Student")
		),
		"Parent",
		LOOKUP(2,"Info","_RowNumber","Parent")
	),
	"Greeting",
	LOOKUP(2,"Info","_RowNumber","Greeting")
)

Kat

Thank you for the new expression.

I’m still not getting any results; the final long text is the same in the app. Am I plugging it into the right location by placing it in the Message column formula slot? I’m asking this because plugging in a regular substitution expression with plain text isn’t working either. Also, when I put in the substitution expression the user in the app loses the ability to edit the message or add new ones.

Thank you,

Kat

LeventK

@Kat
I understood that Info table has only 1 row with UPDATES_ONLY. And I also understood that Student, Parent and Greetings columns are set as QUICK_EDIT in the Detail View. Can you please explain what’s your app is doing or what you expect your app to do when those quick edit fields are changed and automatically saved (or the table row is updated)?

Kat

So the purpose of this app is to help me with the administrative part of teaching online. More specifically, the messages portion is supposed to help me eliminate any mistakes I have been making. For example, when I have to send 10+ messages out I have noticed that I am more likely to forget to change the “he” in a sentence to “she” or I send the wrong message for the wrong class. Even though I have slowed down and pay closer attention to my proof reading I’m still not catching all the mistakes. I’m hoping that having a clear form that I have to fill out every time will cut down on my mistakes. To this end I want to be able to go into the correct class, enter the student information into the form, and then copy/paste the resulting message into the Outschool website.

The Outschool site is setup with a messaging system that all communication needs to be done through so that Outschool support can see every interaction you have with the student and parent. That is why I can’t just set these up like emails.

If you have any ideas on how I can do this I would be very grateful for your help.

Thank you!!!

Kat

LeventK

Does this Outschool site offer or might offer a REST API or similar HTTP service so that you can push those messages to their system directly which will also free you from the copy&paste business?

Kat

Not that I am aware of. It’s just a messaging system within the site itself.

LeventK

Provided you can get details from an IT guy, I can help more.

Kat

What kind of details and how so?

LeventK

Provided we can POST data to that Outschool site in JSON format as an HTTP request, it’s possible to push your messages directly to that system via Webhook Workflow. If you can get the systems name or any similar information, may be it’s even possible to get some info from Google as well.

Kat

I’ve sent an email to Outschool inquiring if this is possible.

If it is not then what would you recommend?

LeventK

Is this site that you are working with?

If so; Mikhail Seregine is the founder guy and he is a former Google Sr. Engineer as well.
https://www.linkedin.com/in/seregine/
I queried the site a bit but couldn’t be able to catch a sign of REST API service. Let’s wait and see how they’ll reply your query.

Unfortunately your only option is that copy&paste thing provided we can’t find a solution with REST API.

Kat

That is the site. I’ve gotten the first response to my email saying they have forwarded the question to someone who can look at it in more detail and will get back to me in 24 to 48 hours.

I don’t mind the copy and paste thing as long as I can do something like that substitution.

Thank you!

LeventK

Can you please share the app with levent@able3ventures.com as a co-author and share the back-end gSheet with edit access so that I can check the issue from the source? Thnx.

Kat

I figured it out!!!

So to make the substitution apply I needed to add a virtual column and apply the expression you gave me to the virtual column. It started working perfectly once I did that one change.

Thank you so much for all of your help!!!

As for the REST API, I doubt they have one. I’m still waiting for a response, but I’m happy either way now that I got the expression working.

Once more, thank you so much for your help!!! I can’t tell you how much this will help me in keeping up with my classroom messages!

Thank you!

Kat