We want to show Event, Task, and Leave Application in one single Calendar View. Can somebody point me to a way to achieve this?
2 Likes
Hi:
As far I know, the only way is using get_events method, calling it from xxx_calendar.js, and obtaining data from union sql query. See this:
In your case, you will need joining data from different origins … so, your query should be something like this: (syntax and table names probably are wrong …)
SELECT field1, field2 from 'tabEvent' UNION SELECT field1, field2 from 'tabTask' UNION SELECT field1, field2 from 'tabLeave Application'
Other approach is joining data using python code, generating a dict once data is “merged”.
Hope this helps.
Thank you avc!
I was looking into these two threads already but unfortunately, I’m too noob to tackle this based on the information I can find there.
In your query example, you are talking about UNION SELECT. That makes sense, I need to get the information from the 3 different DocTypes.
In what file do I add this query? This is completely new to me… Based on the given threads, I presume this will be in the frappe/ folder of our server Could it be one of these 3 files in apps/frappe/frappe/desk/doctype/calendar_view?
- calendar_view.js
- calendar_view.json
- calendar_view.py
or am I missing the ball completely?
I was also looking into another thread that pointed me in the direction of using the “get_events method” but I can’t find any further info about this method… does this ring a bell?
Hi:
Glad to being helpful.
JS code part can be added to .js file or to a client script. You should choose one of the doctypes, ie Task … Ok, create a client script, for list view … this way:
frappe.views.calendar["Task"] = {
field_map: {
"start": "exp_start_date",
"end": "exp_end_date",
"id": "name",
"title": "subject",
"allDay": "allDay",
"progress": "progress"
},
gantt: true,
get_events_method: "yourapp.yourmodule.events.get_events"
}
Create a .py file in yourapp/yourmodule directory called events.py . Inside:
@frappe.whitelist()
def get_events():
"""Returns events for Gantt / Calendar view rendering.
"""
data = frappe.db.sql("""
select
field1 as exp_start_date, field,2 as exp_end_date, field3 as name, field4 as subject, field5 as progress from tabEvents UNION select field1 as exp_start_date, field2 as exp_end_date, field3 as name, field4 as subject, field5 as progress FROM tabTask UNION SELECT field1 as exp_start_date, field2 as exp_end_date, field3 as name, field4 as subject, field5 as progress from `tabLeave Application`
"""., as_dict=True)
return data
So, client script defines fields mapping to calendar control, and call get_events method (defined in your .py file). This method launchs query that join the data an return it. Note that SQL UNION needs the same fields en each query. Your .py file can be stored in any directory … just use the right path at calling it. Remind @frappe.whitelist decorator too!
I am writing this without trying it, so there will be a lot of errors, but this is the way. Just play a little bit 
Hope this helps.
Wow, great! Thank you for starting me off! I have to do some homework now!
This is what I did step by step.
I’ve added a new Custom Script for the DocType: Task, Apply To: List View
this contains:
frappe.views.calendar["TFS Combi View"] = {
field_map: {
"start": "exp_start_date",
"end": "exp_end_date",
"id": "name",
"title": "subject",
"allDay": "allDay",
"progress": "progress"
},
gantt: true,
get_events_method: "the_factory_suite.events.get_events"
}
→ My custom app that has all the customizations to ERPNext is called the_factory_suite hence the location of the get_events method. (although I have an extra question regarding this location - see further)
then, I’ve created a get_events.py file containing the given code:
@frappe.whitelist()
def get_events():
"""Returns events for Gantt / Calendar view rendering.
"""
data = frappe.db.sql("""
select
field1 as exp_start_date, field,2 as exp_end_date, field3 as name, field4 as subject, field5 as progress from tabEvents UNION select field1 as exp_start_date, field2 as exp_end_date, field3 as name, field4 as subject, field5 as progress FROM tabTask UNION SELECT field1 as exp_start_date, field2 as exp_end_date, field3 as name, field4 as subject, field5 as progress from `tabLeave Application`
"""., as_dict=True)
return data
nothing less, nothing more…
In a way I’m not able to view the freshly made view TFS Combi View in the dropdown menu of the calendar, so I think I’ve missed something in the custom script. I’ve tried to take out the get events method line just to check if the menu item would appear and I even restarted the Frappe Bench, but I can’t get the new item in the menu.
Besides that I’m in doubt of the exact location of the get_events.py file. When I look at the tree structure of Frappe my get_events.py file is located at /frappe-bench/apps/the_factory_suite/events/get_events.py but I’m confused as there is another subfolder under /apps/the_factory_suite that is also called the_factory_suite and under that one another that is called like that. Anyway, my main concern is that I’m calling the right location for the .py file in the custom script like this.
Hi Pedro:
Once thing at a time
1. Sql query.
“field1”, “field2” … are suggested symbolic names … you have to use real field names. Take a look to your doctypes, and locate the tables and the field names desired. Just a sample:
Event doctype: The table is named tabEvent (always tab+doctype name) … Event doctype has some good candidates: starts_on, ends_on, subject … Go to console (awesome bar and search: system console) and execute this SQL query:
SELECT * FROM tabEvent;
This query return the table content, and you can see what data you want to use for calendar view … let’s say that the fields mentioned above. Ok, our sql query should be
SELECT subject, starts_on, ends_on FROM tabEvent;
Now … Task Doctype, same pathway. I think that subject, exp_start_date and exp_end_date fields contains desired data … so SELECT subject, exp_start_date, exp_end_date FROM tabTask;
Finally, Leave application … Probably I’d choose employee_name, from_date and to_date … query should be: SELECT employee_name, from_date, to_date FROM tabLeave Application`
Result UNION query:
SELECT CONCAT('Event: ', subject) as subject, ‘#FF5733’ as color, starts_on as exp_start_date, ends_on as exp_end_date FROM tabEvent UNION SELECT CONCAT('Task: ', subject) as subject, ‘#86F95C’ as color, exp_start_date as exp_start_date, exp_end_date as exp_end_date FROM tabTask UNION SELECT CONCAT('Leave application: ', employee_name) as subject, ‘#5CA8F9’ as color, from_date as exp_start_date, to_date as exp_end_date FROM tabLeave Application
This query returns all data merged, with a column showing text “Event”, “Task” or “Leave application” followed by the event/task name or employee name to show in calendar.
3. get_events method
Try get_events_method: "the_factory_suite.events.get_events.get_events" . First get_events is the .py file … second one is the method inside the file.
3. Mapped fields in client script
id, allday and progress are not included in the data returned by get_events method (probably it not make sense), so map only returned data. Note that fields exp_start_date, exp_end_date and subject are exactly the names used in sql query behind “as”.
Client script:
frappe.views.calendar["TFS Combi View"] = {
field_map: {
"start": "exp_start_date",
"end": "exp_end_date",
"title": "subject",
"eventColor": "color"
},
gantt: true,
get_events_method: "the_factory_suite.events.get_events.get_events"
}
get_events.py:
import frappe
@frappe.whitelist()
def get_events():
"""Returns events for Gantt / Calendar view rendering.
"""
data = frappe.db.sql("""
SELECT CONCAT('Event: ', subject) as subject, '#FF5733' as color, starts_on as exp_start_date, ends_on as exp_end_date FROM `tabEvent` UNION SELECT CONCAT('Task: ', subject) as subject, '#86F95C' as color, exp_start_date as exp_start_date, exp_end_date as exp_end_date FROM `tabTask` UNION SELECT CONCAT('Leave application: ', employee_name) as subject, '#5CA8F9' as color, from_date as exp_start_date, to_date as exp_end_date FROM `tabLeave Application`
""", as_dict=True)
return data
As @buildwithhussain says … cooool! 
Play and try to custom it as your requirements: color, texts, etc …
Hope this helps.
9 Likes
Thank you avc for taking the time to explain the workflow in such detail. You help a newbie with rapid steps forward!
As for the sql query, of course, the exact field names must be specified. Sad that I didn’t realize that myself. So I modified it based on your example.
I first copied the custom script exactly from your example:
frappe.views.calendar["TFS Combi View"] = {
field_map: {
"start": "exp_start_date",
"end": "exp_end_date",
"title": "subject",
"eventColor": "color"
},
gantt: true,
get_events_method: "the_factory_suite.events.get_events.get_events"
}
I then also modified the .py file, initially exactly as you specified it:
import frappe
@frappe.whitelist()
def get_events():
"""Returns events for Gantt / Calendar view rendering.
"""
data = frappe.db.sql("""
SELECT CONCAT('Event: ', subject) as subject, '#FF5733' as color, starts_on as exp_start_date, ends_on as exp_end_date FROM `tabEvent` UNION SELECT CONCAT('Task: ', subject) as subject, '#86F95C' as color, exp_start_date as exp_start_date, exp_end_date as exp_end_date FROM `tabTask` UNION SELECT CONCAT('Leave application: ', employee_name) as subject, '#5CA8F9' as color, from_date as exp_start_date, to_date as exp_end_date FROM `tabLeave Application`
""", as_dict=True)
return data
i then ran a bench clear cache and a bench clear website cache
But strangely enough, I can’t see the newly created “TFS Combi View” calendar anywhere. So I can’t check if I’m getting anywhere or not.
So I have some extra questions raising:
I have the Custom Script residing under Task-List. Am I wrong to expect to see an extra line in the Calendar Menu called TFS Combi View? Like here somewhere:
That would be the idea, and in an ideal world, we would have a custom shortcut button on the Home Page that brings us directly to this combined view.
I’m definitely missing something to have this new view available in Calendar. Still, crawling the web for extra info I don’t manage to find some explanation or tutorial to figure out this primary step.
As you have a screenshot of a calendar exactly working as it is supposed to be in the combined view, (including very personalized events!!!) maybe you can point out to me how you did that…
I am already very grateful for all the effort you have made to help me!
Little note on the side, the console did not work on our self-hosted system - it always returned a syntax error. I went to look in the respective Event, Task and Leave Application details for the relevant field names.
Hi:
The doctype you client script is based on should match with the frappe.views.calendar doctype …
frappe.views.calendar["TFS Combi View"] = {
Haven’t you a doctype called “TFS Combi View”? I’ve assumed this … once created this doctype calendar view is avaliable … The doctype will not be used to store data, just for “host” calendar view. Is a “dirty” solution, I know 
Task, Event, Leave Application has their own calendar view, from .js file (task_calendar.js, event_calendar.js …). Tried, but seems that client script can’t override this … mmm
The list of calendars of your screenshot is provided by hooks.py file
https://frappeframework.com/docs/v14/user/en/python-api/hooks#calendars
From home page you can use a button or link to the calendar view url … is easely doable.
Hope this helps.
1 Like
I didn’t have the “TFS Combi View”. I just made one, inside my Custom App.
Details:
- Name: TFS Combi View
- Module: The Factory Suite
- Is Single: ON
- Custom: ON
and I had to add a field, so I did with a simple data field called Note.
When visiting this DocType, I can’t select any calendar view yet… I tried also with Is Single not checked but still no calendar view.
You are referring to the documentation for hooks.py to version 14, but we are on version 13… that is also probably why the console does not work as I don’t see a field to enter Type. Is there a significant reason between the two versions why I don’t achieve what we are trying and what you made look rather easy?
Unmark “Is single” … In view list you will see the button to switch view mode.
Yes, SQL queries in console was introduced on v14.
v14 have a lot of improvements and rich features. I’d strong recommend consider migration.
To fast, I do see the new DocType “TFS Combi View” now (without the is single marked) … and I have te opportunity to select a Calendar view of it. But when opening, there is a brief pop up that I’m trying to copy… the calendar for TFS Combo View shows empty for the moment…
BTW I’m using ERPNext as an Administrator user
The Message that pops up is this:
Message
Failed to get method for command the_factory_suite.events.get_events.get_events with No module named ‘the_factory_suite.events’
in the Gantt-view, I get this error:
exception": "pymysql.err.OperationalError: (1054, "Unknown column ‘tabTFS Combi View.exp_start_date’ in 'order clause
1 Like
Said above that file is /frappe-bench/apps/the_factory_suite/events/get_events.py … so move it to /frappe-bench/apps/the_factory_suite/the_factory_suite/get_events.py and change client script to get_events_method: "the_factory_suite.get_events.get_events"
2 Likes
YES!!! It is working… Exactly the same way as you showed me in your mock-up, including the colors… You can’t imagine how you helped me out here! I don’t know how to thank you… I’ve learned so much… especially on the locations of files and scripts on the server…
I can start tweaking around but this is just great… I’ll mark the tread as solved!
1 Like
I am currently slowly but surely getting everyone here familiar with ERPNext because we come from a completely different ERP package. As you can see I’m trying hard to get somewhere in customizing ERPNext and sometimes I manage on my own, and sometimes I have to rely on people with much more knowledge through this forum. I must admit that I am currently very scared to upgrade to V14 as we are now 100% dependent on ERPNext for our day-to-day operations, and I am not sure whether or not I will succeed in making the upgrade without issues popping up… Substance for a later thread I suspect… after some extra study work. In any case, I am an absolute fan and advocate.
4 Likes
Really cool! The best thing you can do is sharing your knowledge and experience helping other people. I am not an experienced user, but this forum was absolutely essential to me.
We all have a lot of things to learn from others.
Keep on rockin.
1 Like
This should be standard or custom app.
1 Like
What do you mean?
The new DocType for this view is in a custom app over here.
The only drawback of this solution (and that is probably the “dirty” solution where avc was talking about) is that the view works exactly as expected but when clicking or dragging an item in the calendar it does not link to the actual task or event. The solution gets the information but if you want to change something you’ll have to select the right calendar view (Event, Task etc…) do the changes. But I wanted a solution that gave me an overview, and that is exactly what it does.