Difficulty sending personalized email with information split in two tables that do not have all common points

[prettycold]

Hello fellow Gliders,

I need some help with an approach as whatever solution I come up with, there seems to be a catch.

Imagine a Personal Shopping assistant application where there are tables for :

1. Personal Assistants
2. Customers
3. Customer Groups (Imagine a local community they belong to)
4. Services a Customer needs (can be of different categories)
5. Preferred sellers for each Service Category

Granularity of Service table is per Customer per Category Eg., Jane needs Face Makeup related service.
Granularity of Preferred Sellers is per Seller per Service Category Eg., Seller A for Face Makeup, Seller B for Clothes, Seller C for Footwear and so on. There can be multiple Preferred sellers for each service category.

Once a Customer requests for a particular service, the Personal Assistant who is associated with this particular Customer needs to a send a personalized email to all Preferred Sellers that are associated with this particular service category requesting for proposals. I’m trying to automate this part.

Now the challenge occurs in:

1. Grouping information from Service Category and Preferred Seller table as the email needs to have information from both. The only common pieces of information between these two are Customer Groups and Service category. 1 row in Service Category can be linked to multiple rows in Preferred Sellers. Customer details are obviously not part of Preferred Sellers table.
2. I need to send a personalized email to each Seller with their company name, the other information like Customer name and details may be the same.
3. So there is a need to iterate through each seller and send them the appropriate email content.

The approach I’m trying to implement:

1. I defined a table called Proposals where I capture the Seller contact name, Seller email, Seller company, Customer name, Customer address details, Email Subject, Email body, Email sent on, Email status, Personal Assistant, Personal assistant’s email as it’s columns

• The idea is to first populate this table and then iterate through required rows and send the email

Populating this table seems to be a challenge. All information is present in a Resident page including the Services they had opted for. Once the personal assistant clicks on a Service Category, I take them to a page that filters out the list of Preferred Sellers based on the Customer group and category. Now I have the subset of sellers who should receive a bid request. I defined a list collection to display the sellers, on item click I added an action to populate this Proposals table with Seller name, email, company name. I’m unable to access Service category and Customer information from this screen (Service Category table).
This is Challenge number 1.

Assuming I populate this table with required information completely, iterating through the list and sending multiple emails is the second. For this I assume I need to check Zap loops (I use Zapier for other automation) and send email using Outlook integration (as the client use Microsoft outlook)

In short, since the granularity of these 2 tables are different and do not have all common pieces to connect, I’m looking for some workaround, but nothing seems to work.

I know it sounds quite confusing , I can probably share a Loom recording if that’ll help…

Thanks a lot for reading till the end

P.S: I’m open to moving the Send Email button to a better screen, or define a new screen if it’ll help in any ways. This app is using Glide pages.

[Darren_Murphy]

@prettycold

I know it sounds quite confusing , I can probably share a Loom recording if that’ll help…

I think it would. I’ve read through, but there is a lot to digest there and it’s difficult to visualise just from a text description. I feel there is probably a fairly straight forward solution, for example rather than attempting to set up a loop, you might be better off preparing a JSON collection with all the email content for each recipient, and then send that through a webhook. But it would be easier to describe how that might be set up if I could see and understand the tables/flow involved a bit better.

[prettycold]

Thanks a lot for the immediate reply @Darren_Murphy. Let me simulate this app quickly and then explain the problem through a screen recording. Will post back an update in sometime.

[prettycold]

Hi @Darren_Murphy

Here’s a simple app that I simulated for the purpose of explaining with some hypothetical data and I walk through the app here in detail and where I’m getting caught.

I just published the app too, ideally it is user based. For the sake of this post, I made it public. It’s in here!

Hope it’s a bit clear now. The problem is with fetching all the information that I need because of the way in which the relationships are.

Thanks in advance!

[Darren_Murphy]

I just had a quick look at your video. I don’t have a lot of time right now to explain too much, but essentially what you should be able to is make use of the User Profile row for the purpose of temporarily storing whatever values you need to create the extra relations. The way to do this is to modify the actions that take you to the various details screens, so that they first write the RowID of the selected item into a column in the User Profile row. Then because it’s in the user profile row, you can then access that value from anywhere.

Hopefully that helps a bit.

Also, take a look at the below video - it demonstrates a similar technique (but in a cleverer way):

Understanding (and hacking) Form Screens

Hey fellow Gliders! Form screens (or form containers) in Glide are critical when creating apps that allow users to add new data (projects, tasks, posts, etc.) to your database. Navigating form screens requires some understanding of screen and table relationships and involves some mental juggling when configuring visibility conditions within the form. In this video, I peel back the layers of a form screen to show you how they work and how to work around a couple of the commonly frustratin…

[prettycold]

Thanks much @Darren_Murphy . I did try this route, probably I overlooked some aspect. Let me watch the video you shared and then try it again patiently

[Darren_Murphy]

You may need to use a combination of both.

[prettycold]

Alright, I watched the video now @Darren_Murphy . Understood what mistake I did, let me now try applying what @Robert_Petitto explains and update this thread soon. Thank you so much for sharing this.

[prettycold]

@Darren_Murphy ,

I added a lot of columns to Users table (i.e. all the columns that provide information to the email body including the email subject line and email body) using the approach explained, I now think the new table “Bid Proposals” is unnecessary as it is only complicating the flow. I add email sending functionality as part of the action itself.

It does work Let me implement the same in my actual app and see how it goes

My only doubt is is, is it a good practice to add these many columns to the Users table. Will it affect the performance or consume updates due to this?

[prettycold]

@Darren_Murphy ,

I added a lot of columns to Users table (i.e. all the columns that provide information to the email body including the email subject line and email body) using the approach explained, I now think the new table “Bid Proposals” is unnecessary as it is only complicating the flow. I add email sending functionality as part of the action itself.

It does work Let me implement the same in my actual app and see how it goes

My only doubt is is, is it a good practice to add these many columns to the Users table. Will it affect performance or consume updates due to this?

[Darren_Murphy]

@prettycold

My only doubt is is, is it a good practice to add these many columns to the Users table. Will it affect the performance or consume updates due to this?

How many columns do you have?
It’s not uncommon to have several hundred computed columns in the Users table.
Most of the Apps I build will have at least 100.

[prettycold]

Wow, definitely not 100. At the most I may have 20, but email body may be a big template column. I guess it should be okay.

The original requirement was to select multiple sellers and send a mail at one shot. I shall come to that once I’m done with this.

[Darren_Murphy]

@prettycold

Wow, definitely not 100. At the most I may have 20, but email body may be a big template column. I guess it should be okay.

A “big” template is about 300,000 characters. If yours is less than that, you should be fine

[prettycold]

That’s good news! Thanks a lot for all the help

[prettycold]

@Darren_Murphy ,

Now that sending emails by going into a Seller’s detail page works, I’m now required to just select all sellers and then have one button that should send a personalized email to each. I now have the content as rows in my Bid Proposals table. I also remember you mentioning to configure as a JSON object. So searched for some references and came across this link.

Just wanted to make sure this approach will work in Zapier too as I do not use Make. I’m just starting to configure the JSON object now.

[ThinhDinh]

As long as Zapier can parse the JSON and iterate through it to send emails, that would work. I don’t have much experience with Zapier, but that’s what I would do in Make.

I don’t know if they would parse the JSON automatically from a webhook payload, only way to know is your test .

Then it seems you have to do a loop.

Loop your Zap actions

[prettycold]

Sure @ThinhDinh , let me try this and give an update soon. Thanks for the link!

[prettycold]

I made good progress, thanks to @Darren_Murphy and @ThinhDinh for your suggestions. I built my JSON Paylod that has the right set of information to be passed to Zapier. I’m stuck at the step where I’m unable to set the value in my User profile row. For some reason the relation does not show. I read quite a few threads where many faced this same issue, and in most threads it was a problem of the relation being multiple, or the relation not having any values in it.

In my case, it is a single relation, and I can see the matching values in the table. Still when I set the column, the relation does not come as part of my source table’s other columns.

Did a quick loom recording thinking it will be of help. Any pointers as to what I may be missing?

P.N.: Sorry about the background noise as I work from a coworking space

[Darren_Murphy]

Not quite sure, but I think what might be happening there is that you get a context switch after the Add Row action. And so the Set Column values is trying to address columns in the Bid Proposals Helper table. What happens if you reverse the order of the actions?

[prettycold]

Well, I add/delete a row in Bid Proposals table when the user clicks this “Select” button.

Clicking on Verify & Send email, I show this:

Clicking on Send email is when I populate the Bid Proposals helper table as I need all the required JSONs ready. I then try to populate the JSON Paylod to my User profile row.