Send Email From OE Lead Table Namelist

desmond_lee

Hi Experts

I don't know how to workaround on this. See Below table.

In the OE Lead Table, I want to send 2 emails to <PII removed by staff> & <PII removed by staff> on the condition that when another table i.e. Survey Matrix table (Col H until O that any row show 0 value) After 7 days from the date of [SHARING DATE]. I have add a Col [TRACKER] (IF(OR([CCNX]=0,[CSGK]=0,[CTWN]=0,[CCNZX2]=0,[CCNZX1]=0,[CCNKB]=0,[CCNZB]=0,[CMYP]=0),WORKDAY(DATE([SHARING DATE]), 7),"")) that shows the 7th day date and I got stuck here.

Condtions Example :

1. OE Lead table Col [SITE NAME] has the value "CMYP" = Headeer Col O [CMYP]

2. Survey Matric table Col O [CMYP] = 0

3. Filter OE Laad Table row that has CMYP value in Col A (4, 12) then send email.

desmond_lee

HI Suvrutt Gurjar

Any idea how to do this?

Suvrutt_Gurjar

I am sorry that your requirement is not clear from the description. Could you restate.

In general if one does not receive response from the community in reasonable time, it could mean description is not clear, the question is too long or open ended or the question is app design or approach related which could have many possibilities and generally outside of community help area.

desmond_lee

Some portion is remove because there is an email there. That's ok I already explain to you. You there is still clarification I can explain again

Suvrutt_Gurjar

Yes, I see that the entire thread has got deleted. In future, please ensure to post screenshots with pseudo data and by hiding any confidential data such as emails.

I am sorry that since the thread is deleted including my suggested solution. I obviously do not remember your column names etc. You may need to repost the table column screenshots again with necessary explanation.

desmond_lee

It's Ok let's do it again. I want to send email at 12:01am base on the following conditions :

Table Survey Matrix ISNOTBLANK([TRAKVER]), Ref - Column [CCNX], [CSGK], [CTWN], [CCNZX2], [CCNZX1], [CCNKB], [CCNZB], [CMYP] Table OE Lead Col [SITE NAME]. If The value is 0 ( in table Survey Matrix)the filter the row in OE Lead table then schedule BOT sendemail.

There is no relationship between tables Survey Matrix and OE Lead. The column [Project List] in OE Lead just a dereference dropdown name in the Project List Table.

Suvrutt_Gurjar

Please post column details of the "Project List" table also.

Will also request you to structure your posts better. For example for a community reader it may not be very clear which is the screenshot of which table. You may want to give suitable title above the screenshot. Something like below.'

Survey Matrix table Screenshot (Below):

desmond_lee

This is the Project List

Survey Matrix

Suvrutt_Gurjar

You may also need to state the reference relationships explicitely. For example, please clearly mention "Project List" table references the "OE Lead Table" through reference column [Site Developer]

Could you please confirm above understanding is correct?

It is very difficult to study and guess and ask for confirmation.

desmond_lee

Yes this is correct. This is OE Lead Table [Site Developer] which is also a LABEL. The key in the OE Lead table is [DEVELOPER EMAIL]

This is the Project List Table. It a reference dropdown

Suvrutt_Gurjar

Thank you.

Please try below

1. Please add a column called say [DEVELOPER EMAIL] in the "Project List" Table with an expression something like [SITE DEVELOPER].[DEVELOPRE EMAIL]

2. Please add a column called say [SITE NAME] in the "Project List" Table with an expression something like [SITE DEVELOPER].[SITE NAME]

3. Please add a column called say [DEVELOPER EMAIL] in the "Survey Matrix" Table with an expression something like [PROJECT ID].[DEVELOPRE EMAIL]

4. Please add a column called say [SITE NAME] in the "Survey Matrix" Table with an expression something like [PROJECT ID].[SITE NAME]

5. Please create a slice called say "Email_Records" on the "Survey Matrix" table with a slice filter expression something like ISNOTBLANK([TRACKER])

6. You can create a daily scheduled bot on the "Email_Records" slice with a filter expression something like

OR (
AND([SITE NAME]= "CCNX" , [CCNX]=0),
AND([SITE NAME]= "CSGK" , [CSGK]=0),
AND([SITE NAME]= "CTWN" , [CTWN]=0),
AND([SITE NAME]= "CCNZX2" , [CCNZX2]=0),
AND([SITE NAME]= "CCNZX1" , [CCNZX1]=0),
AND([SITE NAME]= "CCNKB" , [CCNKB]=0),
AND([SITE NAME]= "CCNZB" , [CCNZB]=0),
AND([SITE NAME]= "CMYP" , [CMYP]=0)
)

Please ensure the following setting is on for the bot.

7. In the "To" setting of the emailing step of the bot please have the expression of [DEVELOPER EMAIL]

desmond_lee

Quote " 1. Please add a column called say [DEVELOPER EMAIL] in the "Project List" Table with an expression something like [SITE DEVELOPER].[DEVELOPRE EMAIL]"

[DEVELOPER EMAIL] itself in Project List Table is a dropdown (FORM IN PROJECT LIST) reference to OE Lead Table col [DEVELPER EMAIL]

Are we going to put [DEVELOPER EMAIL].[DEVELOPER EMAIL] app formula on top of this reference ? Note : I change SITE DEVELOPER to DEVELOPER EMAIL in the Project List Table becuse they are the same thing. I rename the [SITE NAME] in OE Lead Table as [PROJECT SITE NAME] beacuse they also the same thing. Rename all these to make me less confusing.

New Table Added Columns. Is this correct ?

Suvrutt_Gurjar

if you already have the columns [DEVELOPER EMAIL] or any column that extracts the email from "OE Lead" table , then you need not add those again. In the earlier shared image of "Project List" table you did not show this column. The idea is to extract the email and site name in the "Survey" table for use in bot.

desmond_lee

"7. In the "To" setting of the emailing step of the bot please have the expression of [DEVELOPER EMAIL]"

Can you elaborate this a litle bit more cause I don't really understand this part. Below is the setting for the event portion

Filter Condition

Suvrutt_Gurjar

@desmond_lee wrote:

7. In the "To" setting of the emailing step of the bot please have the expression of [DEVELOPER EMAIL]"

Both are clearly mentioned.

My request to you will be to familiarize yourself with all the automation feature components by going through various help articles on automation.

AppSheet automation: The Components - AppSheet Help

desmond_lee

I understand these and in fact there is other apps I use this direct table. But in this case they are different. Its getting text info from survey matrix table and send emails (a few email) from another table OE Lead. Still readling how to do this. The contact email (DEVELOPER EMAIL) at the survey matrix table is not the intend email to send. The email for send is at those sites that has 0 value at any row and pull this email at the OE Lead table (email can be >1 in one site). This is the challenge.

Suvrutt_Gurjar

@desmond_lee wrote:

ext info from survey matrix table and send emails (a few email) from another table OE Lead

For this reason only there was suggestion to pull the developer email data from OE Lead table ( using the Project List table ) to Survey Matrix table.

@Suvrutt_Gurjar wrote:

3. Please add a column called say [DEVELOPER EMAIL] in the "Survey Matrix" Table with an expression something like [PROJECT ID].[DEVELOPRE EMAIL]

desmond_lee

Done

Suvrutt_Gurjar

Then please use it in the "To" field of the bot step.

desmond_lee

At the fileld of the email as below

And at the Email Preview it send to the DEVELOPER EMAIL. This is not the intended email I want to send. I want to send to CMYP developer email

Suvrutt_Gurjar

If referencing is proper, then it should pick up a proper email.