cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jgh40
Helper I
Helper I

avoiding duplicate calendar entries, limit historical query

I have a working flow that is exactly based on solution noted in this thread.

The screenshot of the flow is listed in this thread for the solution.

 

The automation works great, however when performing a query before posting a new event, it fails correctly due to past entries being present in calendar.

 

Could anyone help me with this edge case? 'GetEvents' is picking up past events matching same subject. For context, this is an event that is reoccurring monthly, but dates are always different however subject is always exactly same content. The body has the date of event (e.g. 06/04/2020 08:00 AM)

 

Is there a way to look for events only in the current month?

 

Thanks, again to this very helpful community!

 

Tagging users: @v-litu-msft @ScottShearer 

1 ACCEPTED SOLUTION

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @jgh40,

 

Sorry, the expression should be:

formatDateTime(item()?['createdDateTime'],'yyyy-MM')

 

The following is solution with correct expression:

 

You could use the Filter query action to filter out items which month is equals to current month.

For example, I want to get the month of create date time whether it equals to the current month, use the formateDateTime() function to convert the create date time into 'yyyy-MM', then convert current date into it, too.

formatDateTime(item()?['createdDateTime'],'yyyy-MM')
formatDateTime(utcNow(),'yyyy-MM')

 

 

Annotation 2020-06-08 134719.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
anupam8555
Continued Contributor
Continued Contributor

Hi @jgh40  ,

 

You can apply following Filter Query to fetch calendars of current month .

 

 

start/dateTime gt 'Function 1' and end/dateTime lt 'Function 2'

Function1 - string(startOfMonth(utcNow())) - used to get start of month

Function2 -string(subtractFromTime(startOfMonth(addToTime(utcNow(),1,'Month')),1,'Day')) - used to get end of month

 

image.png

 

It worked for me, you can try as well. 

 

If this helps you , please mark this as resolved so it can be helpful to others.

v-litu-msft
Community Support
Community Support

Hi @jgh40,

 

You could use the Filter query action to filter out items which month is equals to current month.

For example, I want to get the month of create date time whether it equals to the current month, use the formateDateTime() function to convert the create date time into 'yyyy-MM', then convert current date into it, too.

formatDateTime(item()?['Created'],'yyyy-MM')
formatDateTime(utcNow(),'yyyy-MM')

Annotation 2020-06-08 134719.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I tried editing my current flow, but it had an error:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(item()?['Created'], 'yyyy-MM'), formatDateTime(utcNow(), 'yyyy-MM'))' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

 

image (2).png

I tried to get this working, but was unsuccessful. My current flow denotes that if query comes back with '0' entries, to create a calendar entry, otherwise terminate with 'Cancelled.'

 

It kept resulting in '0' entries, but going into a 'Cancelled' state. The flow that I am working off of is linked in my original post.

 

image (3).png

anupam8555
Continued Contributor
Continued Contributor

Hi @jgh40 ,

 

Can you try removing subject in query or can you try putting subject in query with static Email title ? 

E.g.

subject eq 'demo'

 

Let me know.

I had this before, and it was working, but I am trying to filter out event not created in current month.

anupam8555
Continued Contributor
Continued Contributor

Hi @jgh40 ,

 

If you want to filter out events not created this month than you need to use createdOn field rather than start/dateTime or end/dateTime.

 

Thanks

v-litu-msft
Community Support
Community Support

Hi @jgh40,

 

Sorry, the expression should be:

formatDateTime(item()?['createdDateTime'],'yyyy-MM')

 

The following is solution with correct expression:

 

You could use the Filter query action to filter out items which month is equals to current month.

For example, I want to get the month of create date time whether it equals to the current month, use the formateDateTime() function to convert the create date time into 'yyyy-MM', then convert current date into it, too.

formatDateTime(item()?['createdDateTime'],'yyyy-MM')
formatDateTime(utcNow(),'yyyy-MM')

 

 

Annotation 2020-06-08 134719.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Updating expression yielded similar results. It found past events in previous calendar months, and shifted to 'Cancelled.'

 

After examining output from run, it was finding this from previous events:

"createdDateTime""2020-06-04T17:43:31.4715746+00:00"
 
After deleting past events, it worked as designed. I created past events to test, however past events were created this month!
 
Note to the solution I marked in this post: The original flow screenshot is found in message 8 , however expression was updated in 'Accepted Solution'.
 
Thank you!
 
 

 

 

 

I don't get it, but automation failed today as it found events from previous months. I did test this and found it to be working, but am confused how it didn't in this case. There are past events from May and June, and others, however nothing matches this current month of July. With filter array expression, I was expecting for these events to be created but the flow did not do this.

 

I've double-checked automation, and it is as it was when solution was verified. Any help is appreciated!

It doesn't seem to be matching an empty array, even though the array is empty. The output body from Filter Array is: [ ]

@v-litu-msft 

 

Thanks 🙂

v-litu-msft
Community Support
Community Support

Hi @jgh40,

 

How many events were created in July?

Could you please share the output of Get events? (please shield the personal information) 

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. No events were created. Filter array was empty. Here is output from GetEvents @v-litu-msft 

 

[
  {
    "subject": "Subject",
    "start": "2020-06-18T00:00:00.0000000",
    "end": "2020-06-19T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cSBAAA=",
    "createdDateTime": "2020-06-04T22:19:21.2424832+00:00",
    "lastModifiedDateTime": "2020-06-05T03:34:51.9267358+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcSBAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "low",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 15,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2020-05-21T00:00:00.0000000",
    "end": "2020-05-22T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cLWAAA=",
    "createdDateTime": "2020-06-04T22:14:51.1347001+00:00",
    "lastModifiedDateTime": "2020-06-04T22:14:51.8862704+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcLWAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "low",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 15,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2020-04-16T00:00:00.0000000",
    "end": "2020-04-17T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cMRAAA=",
    "createdDateTime": "2020-06-04T22:15:02.7430599+00:00",
    "lastModifiedDateTime": "2020-06-04T22:15:04.6269821+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcMRAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "normal",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 420,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2020-03-19T00:00:00.0000000",
    "end": "2020-03-20T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cNGAAA=",
    "createdDateTime": "2020-06-04T22:15:11.8908269+00:00",
    "lastModifiedDateTime": "2020-06-04T22:15:13.3829731+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcNGAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "normal",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 420,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2020-02-13T00:00:00.0000000",
    "end": "2020-02-14T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cNpAAA=",
    "createdDateTime": "2020-06-04T22:15:17.6445354+00:00",
    "lastModifiedDateTime": "2020-06-04T22:15:19.0047566+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcNpAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "normal",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 420,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2020-01-16T00:00:00.0000000",
    "end": "2020-01-17T00:00:00.0000000",
    "body": "",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cOVAAA=",
    "createdDateTime": "2020-06-04T22:15:25.7648886+00:00",
    "lastModifiedDateTime": "2020-06-04T22:15:29.4897582+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcOVAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "normal",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 420,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  },
  {
    "subject": "Subject",
    "start": "2019-11-14T00:00:00.0000000",
    "end": "2019-11-15T00:00:00.0000000",
    "body": "<html><head><meta name=\"Generator\" content=\"Microsoft Exchange Server\">\r\n<!-- converted from text -->\r\n<style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style></head>\r\n<body>\r\n<font size=\"2\"><span style=\"font-size:11pt;\"><div class=\"PlainText\">&nbsp;</div></span></font>\r\n</body>\r\n</html>\r\n",
    "isHtml": true,
    "responseType": "organizer",
    "responseTime": "0001-01-01T00:00:00+00:00",
    "id": "AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP-XvgotUBwCSNbVFqzo_R70rAImyovEjAASm-breAACSNbVFqzo_R70rAImyovEjAASm-cPUAAA=",
    "createdDateTime": "2020-06-04T22:15:38.7154791+00:00",
    "lastModifiedDateTime": "2020-06-04T22:15:41.0041706+00:00",
    "organizer": "mail@example.com",
    "timeZone": "UTC",
    "seriesMasterId": null,
    "categories": [],
    "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADVjZmRkMmZiLWMxODYtNGVmYi04NWZhLTU0MmM5ZjFjZTU4MgBGAAAAAAAq98IoomflQoRiP%2FXvgotUBwCSNbVFqzo%2BR70rAImyovEjAASm%2FbreAACSNbVFqzo%2BR70rAImyovEjAASm%2FcPUAAA%3D&exvsurl=1&path=/calendar/item",
    "requiredAttendees": "",
    "optionalAttendees": "",
    "resourceAttendees": "",
    "location": "",
    "importance": "normal",
    "isAllDay": true,
    "recurrence": "none",
    "recurrenceEnd": null,
    "numberOfOccurences": null,
    "reminderMinutesBeforeStart": 420,
    "isReminderOn": false,
    "showAs": "free",
    "responseRequested": true
  }
]

 

jgh40
Helper I
Helper I

Hi. Does anyone have any ideas how to not count an an empty array as a positive integer? It seems that an empty array is being treated as having content, so no events are created. Thanks in advance!

Tagging @v-litu-msft as this user was very helpful with the initial solution, but I would appreciate help from anyone in community.

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,668)