# Dynamic Reporting issue send mail based on day selection

**URL:** <https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064>\
**Category:** Ignition\
**Created:** [April 27, 2021, 11:15am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064 "2021-04-27T11:15:34Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 27, 2021, 11:15am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/1 "2021-04-27T11:15:34Z")

</div>

```auto
import system
from time import sleep
from time import localtime, strftime
curTime = strftime("%H:%M:%S", localtime())

if "02:06:00" < curTime < "02:07:00":

```

i am using dynamic reporting  
this is the script running in gateway timer

this script sending mail at particular time daily

what i am trying is ,when user select monday, tuesday or any one day in week. that day only i want to email report instead of sending report daily

can any one please help me out

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 27, 2021, 11:31am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/2 "2021-04-27T11:31:30Z")

</div>

Hi @prasath.t, can you use %A to get the weekday name and check for this in your first if statement?

You may also be interested in the future 8.1.6 release, as this contains [cron-based scripting event](http://forum.inductiveautomation.com/t/nightly-8-1-changelogs-2021/42410/69).

Also, slight note on using sleep in scripts. It can be considered bad practice in locking the thread with sleep (see [here](https://docs.inductiveautomation.com/display/DOC81/Adding+a+Delay+to+a+Script) for further information). Although, in your case it’s a Gateway scoped script, so might not be so much of an issue. Just a minor observation 🙂

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 27, 2021, 12:19pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/3 "2021-04-27T12:19:37Z")

</div>

Thanks for the reply  
in which statement i have to use and check?  
I never used %A is there any documentation available or please mention in my script how to use the %A

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 27, 2021, 12:27pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/5 "2021-04-27T12:27:14Z")

</div>

No worries @prasath.t. Could you try something like this?:

```auto
curTime = strftime("%A %H:%M:%S", localtime())

if "Monday 02:06:00" < curTime < "Monday 02:07:00":
...

```

Haven’t tried it personally, so I’m not sure if it will work with localtime(). [This](https://cpython-test-docs.readthedocs.io/en/latest/library/time.html) is the documentation I was using for reference.

If the above doesn’t work you may have better luck with [datetime](https://cpython-test-docs.readthedocs.io/en/latest/library/datetime.html#)

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 27, 2021, 12:28pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/6 "2021-04-27T12:28:38Z")

</div>

Thanks i will try this out

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 27, 2021, 12:53pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/7 "2021-04-27T12:53:12Z")

</div>

> [@matthew.ayre](#):
>
> an you use %A to get the weekday name and check for this in your first if statement?

Its working i checked thanks

but if user select multiple days , how to given in if condition  
is there any option available?

please if you find any option let me know

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 27, 2021, 1:17pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/8 "2021-04-27T13:17:12Z")

</div>

No worries @prasath.t. You could pass your selected days in a list format, for example:

`selectedDates = ['Tuesday, Sunday']`

Then separate out the day from the previous example:

```auto
curDay = strftime("%A", localtime())
curTime = strftime("%H:%M:%S", localtime())

```

Then check the day in your main script:

```auto
if curDay in selectedDates:
	if "02:06:00" < curTime < "02:07:00":
		# Do something

```

There is probably a better way of doing it, but that’s the best I can think of at the moment 🙂

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 27, 2021, 1:42pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/10 "2021-04-27T13:42:22Z")

</div>

That’s a good point @prasath.t! I completely forgot the main script was in a Gateway script.

You can’t directly pass the values to the Gateway script, but you could save the selected dates to a database or a memory tag for the script to query/read.

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 27, 2021, 1:44pm UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/11 "2021-04-27T13:44:55Z")

</div>

OK thanks will implement and check

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 7:29am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/12 "2021-04-28T07:29:43Z")

</div>

Hi, i have stored the days(example - monday, tuesday…) in database table as list

 ![database colmun](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/e/4ee8094bab3cf86f1b18c28058e7f4b0e481ca55.jpeg)  
there are 6 rows in tabel  
gateway script will excute 6 time because i have for loop for row  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/5/35580fa5975778ee2a9b4afa6225e48a11a445e0.png)  
i tired to run the query and printed its showing rows and column numbers only  
but i want selected column value to my list  
**selectedDates = [‘Tuesday, Sunday’]**  
this should change dynamically depends upon the value in select\_day column values in database

so when loop runs 6 times **selectedDates = [‘Tuesday, Sunday’]** this list should change dynamically based the row value in select\_day column in database

i tired but i couldn’t able to find solution

please help me out in this

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 28, 2021, 7:49am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/13 "2021-04-28T07:49:28Z")

</div>

Hi @prasath.t, you should be able to format the ‘select\_day’ as JSON assuming it is stored in the database as a string and it has the correct JSON structure (which it looks like it does).

This can be done by using [system.util.jsonDecode](https://docs.inductiveautomation.com/display/DOC81/system.util.jsonDecode).

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 7:52am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/14 "2021-04-28T07:52:09Z")

</div>

jsonString ` `=` `system.` `file` `.readFileAsString(` `“C:\tmp\json.txt”` `)

they mentioned the path. in my script how to call the database column like this ?  
please give example in my script  
i am trying change the list path dynamically based the row data

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 28, 2021, 8:00am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/15 "2021-04-28T08:00:22Z")

</div>

[system.db.runScalarQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runScalarQuery)

Something like (you will have to fix the syntax to match the type of DB you’re using, I’m only familiar with MSSQL 😅):

`rr = system.db.runScalarQuery("SELECT select_day FROM DatabaseName WHERE Id=SomeID")`

Note that runScalarQuery expects only 1 row and column to be returned. Alternatively, you can use runQuery (like in your example) and loop through the results to get the column value for each row.

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 8:05am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/16 "2021-04-28T08:05:29Z")

</div>

> [@matthew.ayre](#):
>
> rr = system.db.runScalarQuery("SELECT select\_day FROM DatabaseName WHERE Id=SomeID")

rr = system.db.runScalarQuery("SELECT select\_day FROM DatabaseName WHERE Id=SomeID")

yes its working in my database also

**loop through the results to get the column value for each row.** any example loop through to get the row dynmaically?

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 28, 2021, 8:15am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/18 "2021-04-28T08:15:35Z")

</div>

Just for my understanding, lets backup a step. Are you aiming to loop through and get all the dates in a list and then run the report once, or are you looking to loop through and run the report for each row returned?

Sorry, just want to make sure I understand the issue fully 🙂

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 8:19am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/19 "2021-04-28T08:19:10Z")

</div>

for each row in the table

```auto
**for row in table:**
 **	if curDay in selectedDates:**

```

each row has different day details  
one row has Monday and Wednesday  
second row has Friday and Sunday

based the on the list data loop runs if present day not in the list .it will stop and runs the second row and check its matching means it will mail the data

it will check each row

hope i explained you clearly?

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 28, 2021, 8:35am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/21 "2021-04-28T08:35:43Z")

</div>

Thank you 🙂 I thought that was the case, I just wanted to make sure.

Something like this could work:

```auto
rr = system.db.runQuery('SELECT select_day FROM TableName WHERE SomeCondition)
selectedDates = system.dataset.toPyDataSet(rr)

for row in selectedDates:
	selectedDays = system.util.jsonDecode(row["select_day"])

	if curDay in selectedDays:
		# Do Something
```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 9:00am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/23 "2021-04-28T09:00:38Z")

</div>

> [@matthew.ayre](#):
>
> `if curDay in selectedDays:`

**if curDay in selectedDays:** in this line getting error

Traceback (most recent call last):  
File "", line 7, in   
TypeError: 'NoneType' object is not iterable

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [April 28, 2021, 9:04am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/24 "2021-04-28T09:04:16Z")

</div>

@prasath.t your dataset has some null values in it. You will also have to check if row[“select\_day”] is not null.

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [April 28, 2021, 9:04am UTC](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064/25 "2021-04-28T09:04:43Z")

</div>

got it selectedDays = str(system.util.jsonDecode(row[“select\_day”]))

we have to use str

now its not showing error

[Next page](https://forum.inductiveautomation.com/t/dynamic-reporting-issue-send-mail-based-on-day-selection/46064.md?page=2)