I often have to build demos that include calendars. However, the dates usually are in the past by the time I get to the demo. The following SQL statement solves that issue. I have a dynamic data table (data can be in SQL or anywhere else) with the event data. You can see a JSON view of one of the records in DD.
When I render the calendar, I use a data source that selects the list of events. However, the start and end dates are dynamically calculated using the SQLite date() and strftime() functions. There is what looks like a double json_extract, but that is because the JSON in DD is a string when entered through Jigx management.
The SQL select statement that returns startDT and endDT takes today’s date, add the day offset as specified in the start field in DD, and appends the time in the start field in DD. I then build up the complete start dates and times by concatenating the two values. I do the same for the end date but add an hour to the SQL statement.
When using something like exchange and ms graph as a datasource you can do the same thing and replace the date part of the datetime with the same formula while using the time part of the graph calendar item.
Anyway, the result is a DD table for calendar entries and a SQL statement that always returns the entries for your calendar from tomorrow onwards. It makes for a nice demo scenario.
Here is the SQL select statement, followed by a JSON representation of the DD table. You will soon be able to import a JSON array into DD in jigx management.
datasource:
type: datasource.sqlite
options:
provider: DATA_PROVIDER_DYNAMIC
entities:
- default/calendar
jsonProperties:
# column names of the result set specified under jsonProperties will retain their json object format
- attendees
- tags
- start
# PLEASE NOTE: the query below contains 2 complex statements to calculate the start and end date of the meeting. this is only for demo purposes
# to ensure that entries in the calendar always starts tomorrow when you view the sample code.
# Under normal circumstances the start and end dates are ISO-8601 format (YYYY-MM-DDTHH:MM).
query: |
SELECT
id,
'$.subject',
'$.location',
'$.body',
'$.bodyPreview',
date('now', json_extract(json_extract(data, '$.start'), '$.dayOffset'),'localtime') || 'T' || json_extract(json_extract(data, '$.start'), '$.time') as startDT,
strftime('%Y-%m-%dT%H:%M:%S', datetime(date('now', json_extract(json_extract(data, '$.start'), '$.dayOffset'),'localtime') || 'T' || json_extract(json_extract(data, '$.start'), '$.time'), '+1 hours')) as endDT,
'$.isAllDay',
'$.attendees',
'$.tags',
'$.isOnline',
'$.onlineUrl'
FROM [default/calendar]
The calendar jig using the data source:
title: Calendar
type: jig.calendar
icon: calendar
header:
type: component.jig-header
options:
height: small
children:
type: component.image
options:
source:
uri: https://cdnsm5-ss13.sharpschool.com/UserFiles/Servers/Server_181580/Image/calendar.jpg
data: =@ctx.datasources.calendar-data-dynamic
item:
type: component.event
options:
from: =@ctx.current.item.startDT
to: =@ctx.current.item.endDT
title: =@ctx.current.item.subject
location: =@ctx.current.item.location
people: =@ctx.current.item.attendees.emailAddress
description: =@ctx.current.item.bodyPreview
tags: =@ctx.current.item.tags
onButtonPress:
when: =@ctx.current.item.isOnline="true"
type: action.open-url
options:
title: Join Meeting
url: =@ctx.current.item.onlineUrl
isTrackingTransparencyRequired: false
isHidden: =@ctx.current.item.isOnline="true"?false:true
the json for the 9 records:
[
{
"attendees": "[{\"emailAddress\":{\"email\":\"mary.johnson@example.com\",\"fullName\":\"Mary Johnson\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"james.brown@example.com\",\"fullName\":\"James Brown\",\"avatarUrl\":\"https://images.unsplash.com/photo-1591084728795-1149f32d9866?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=928&q=80\"}}]",
"body": "<p><a href=\"https://teams.microsoft.com/l/meetup-join/xx123xx\">Click here to join the meeting on Microsoft Teams</a></p><p>Learn how to analyze marketing data and gain insights to improve campaign performance. We\"ll cover Google Analytics, social media analytics, and email marketing reports.</p>",
"bodyPreview": "Learn how to analyze marketing data and gain insights to improve campaign performance. We\"ll cover Google Analytics, social media analytics, and email marketing reports.",
"end": "1",
"id": "32e37b87-8f64-4731-99ee-8a133ffbaed2",
"isAllDay": "FALSE",
"isOnline": "true",
"location": "Microsoft Teams",
"onlineUrl": "https://teams.microsoft.com/l/meetup-join/xx123xx",
"start": "{\"dayOffset\":\"+2 days\",\"time\":\"14:00\"}",
"subject": "Marketing Analytics Workshop",
"tags": "[{\"title\":\"Online\",\"color\":\"color10\"},{\"title\":\"Data\",\"color\":\"color11\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"mary.johnson@example.com\",\"fullName\":\"Mary Johnson\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"james.brown@example.com\",\"fullName\":\"James Brown\",\"avatarUrl\":\"https://images.unsplash.com/photo-1591084728795-1149f32d9866?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=928&q=80\"}}]",
"body": "<p>Join us for a workshop to refine our brand messaging. We\"ll work together to create a clear and consistent message that resonates with our target audience.</p>",
"bodyPreview": "Join us for a workshop to refine our brand messaging. We\"ll work together to create a clear and consistent message that resonates with our target audience.",
"end": "1",
"id": "5845076a-e51e-4b75-bc75-bfa31e254e9f",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room B",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+3 days\",\"time\":\"14:00\"}",
"subject": "Brand Messaging Workshop",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Brand\",\"color\":\"color9\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}}]",
"body": "<p>Join us to discuss and plan our content marketing strategy for the next quarter. We\"ll cover blog posts, whitepapers, and infographics.</p>",
"bodyPreview": "Join us to discuss and plan our content marketing strategy for the next quarter. We\"ll cover blog posts, whitepapers, and infographics.",
"end": "1",
"id": "628a2f49-6331-4382-8928-52d6ec0a423f",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room A",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+2 days\",\"time\":\"10:00\"}",
"subject": "Content Marketing Planning",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Strategic\",\"color\":\"color12\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}}]",
"body": "<p>Join us to brainstorm ideas for our upcoming video marketing campaigns. We\"ll discuss topics, formats, and distribution channels.</p>",
"bodyPreview": "Join us to brainstorm ideas for our upcoming video marketing campaigns. We\"ll discuss topics, formats, and distribution channels.",
"end": "1",
"id": "7d6e2952-a593-49df-b580-d37f7f7bd5dd",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room A",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+3 days\",\"time\":\"10:00\"}",
"subject": "Video Marketing Brainstorming",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Strategic\",\"color\":\"color12\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\"}},{\"emailAddress\":{\"email\":\"mary.johnson@example.com\",\"fullName\":\"Mary Johnson\"}}]",
"body": "<p>Bring your creative ideas to our brainstorming session, where we\"ll explore potential themes and tactics for our upcoming social media campaigns.</p>",
"bodyPreview": "Bring your creative ideas to our brainstorming session, where we\"ll explore potential themes and tactics for our upcoming social media campaigns.",
"end": "1",
"id": "8d4652b1-1688-4e1b-810a-ed00f47c1b7e",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room B",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+1 days\",\"time\":\"13:00\"}",
"subject": "Social Media Brainstorming Session",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Strategic\",\"color\":\"color12\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}}]",
"body": "<p><a href=\"https://teams.microsoft.com/l/meetup-join/xx123xx\">Click here to join the meeting on Microsoft Teams</a></p><p>Discuss strategies for improving conversion rates on our website and landing pages, including A/B testing, user experience, and persuasive copywriting.</p>",
"bodyPreview": "Discuss strategies for improving conversion rates on our website and landing pages, including A/B testing, user experience, and persuasive copywriting.",
"end": "1",
"id": "b24b1ab5-27e4-4c85-8b6e-b97eb397d171",
"isAllDay": "FALSE",
"isOnline": "true",
"location": "Microsoft Teams",
"onlineUrl": "https://teams.microsoft.com/l/meetup-join/xx123xx",
"start": "{\"dayOffset\":\"+3 days\",\"time\":\"09:30\"}",
"subject": "Conversion Rate Optimization",
"tags": "[{\"title\":\"Online\",\"color\":\"color10\"},{\"title\":\"Conversion\",\"color\":\"color7\"},{\"title\":\"Strategic\",\"color\":\"color12\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}},{\"emailAddress\":{\"email\":\"james.brown@example.com\",\"fullName\":\"James Brown\",\"avatarUrl\":\"https://images.unsplash.com/photo-1591084728795-1149f32d9866?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=928&q=80\"}}]",
"body": "<p><a href=\"https://teams.microsoft.com/l/meetup-join/xx123xx\">Click here to join the meeting on Microsoft Teams</a></p><p>Learn and share tips for optimizing our website and content for search engines, with a focus on keyword research, on-page optimization, and link building.</p>",
"bodyPreview": "Learn and share tips for optimizing our website and content for search engines, with a focus on keyword research, on-page optimization, and link building.",
"end": "1",
"id": "c7dc63c8-7bf9-4836-b78b-600636d1f27c",
"isAllDay": "FALSE",
"isOnline": "true",
"location": "Microsoft Teams",
"onlineUrl": "https://teams.microsoft.com/l/meetup-join/xx123xx",
"start": "{\"dayOffset\":\"+1 days\",\"time\":\"15:00\"}",
"subject": "SEO Best Practices",
"tags": "[{\"title\":\"Online\",\"color\":\"color10\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}}]",
"body": "<p>Join us to discuss updates and improvements to our marketing strategy. We\"ll cover new campaigns, content ideas, and performance metrics.</p>",
"bodyPreview": "Join us to discuss updates and improvements to our marketing strategy. We\"ll cover new campaigns, content ideas, and performance metrics.",
"end": "1",
"id": "ebdb7ec3-0211-4adf-8f2e-7f96d82bffda",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room A",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+1 days\",\"time\":\"10:00\"}",
"subject": "Marketing Strategy Update",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Strategic\",\"color\":\"color12\"}]"
},
{
"attendees": "[{\"emailAddress\":{\"email\":\"jane.doe@example.com\",\"fullName\":\"Jane Doe\",\"avatarUrl\":\"https://images.unsplash.com/photo-1546961329-78bef0414d7c?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=774&q=80\"}},{\"emailAddress\":{\"email\":\"john.smith@example.com\",\"fullName\":\"John Smith\",\"avatarUrl\":\"https://images.unsplash.com/photo-1624224971170-2f84fed5eb5e?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1598&q=80\"}}]",
"body": "<p>Join us for a review of our recent competitor analysis. We\"ll discuss their marketing strategies, strengths, and weaknesses, and identify opportunities for our own campaigns.</p>",
"bodyPreview": "Join us for a review of our recent competitor analysis. We\"ll discuss their marketing strategies, strengths, and weaknesses, and identify opportunities for our own campaigns.",
"end": "1",
"id": "ec917f5c-ef0f-485f-bb5a-98d8e8051853",
"isAllDay": "FALSE",
"isOnline": "false",
"location": "Conference Room B",
"onlineUrl": null,
"start": "{\"dayOffset\":\"+3 days\",\"time\":\"09:30\"}",
"subject": "Competitor Analysis Review",
"tags": "[{\"title\":\"In Person\",\"color\":\"color8\"},{\"title\":\"Data\",\"color\":\"color11\"}]"
}
]