Calendar Reports

I know your path of least resistance, and perhaps the most obvious direction is to create a brand new table called sub jobs. And have one job have many sub jobs.

But in fact, you may be better off relating a job to itself. I know that seems a little incestuous, but Quickbase handles this very well.  An advantage to that design is that you don't need to create a new table and the forms and the fields. When you make the relationship to itself, you just need to be careful about the naming of the fields in the relationship. Quickbase knows what it's doing, but you will confuse yourself if you don't name them properly. So for example, in the relationship where one job has many jobs you need to go back into that relationship and edit the name of the field for related job to be something obvious like related main job and then the report link fields need to named as a sub jobs,  so careful with the name so as not to confuse yourself.

So give that a try because then you have one table a your calendar will have all the jobs and sub jobs. And of course, a sub job will know it's a sub job because it has a parent record and then you can colour your calendar different colours if you like, based on whether it is a main job or a sub job. Or maybe you want jobs and their child sub jobs all to have the same color.

An idea on colour is that you can have Colours automatically generate based on the record ID of the job. So for example, maybe 20 different colours randomly assigned according to the record ID of the job. And then you could have the Main Job and its child sub jobs all have the same colour.

I know I'm way off, tangent on the calendar colouring, but here are two formulas for colouring a calendar.  One has 20 colours and the other has 64.

var number LastTwo = ToNumber(Right(ToText(Rem([Record ID#],20)),2));

Case($LastTwo,
1, "e6194b", // red
2, "3cb44b", // green    
3, "ffe119", // yellow
4, "0082c8", //blue
5, "f58231", //Orange
6, "911eb4", //purple    
7, "46f0f0", // Cyan
8, "f032e6", // maganta    
9, "d2f53c", //lime    
10, "fabebe", //pink    
11, "008080", // teal    
12, "e6beff", // lavender    
13, "aa6e28", // brown    
14, "fffac8", // beige
15, "800000", // maroon
16, "aaffc3", // mint
17, "808000", //olive
18, "ffd8b1", //coral
19, "000080", // namy
20, "808080") //grey"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

var number LastTwo = ToNumber(Right(ToText(Rem([Record ID#],64)),2));

Case($LastTwo,

0, "#B4A8BD",
1,"#000000",
2, "#FFFF00", 
3,"#1CE6FF", 
4,"#FF34FF", 
5,"#FF4A46", 
6,"#008941", 
7,"#006FA6", 
8, "#A30059",
9,"#FFDBE5", 
10,"#7A4900", 
11,"#0000A6", 
12,"#63FFAC", 
13,"#B79762", 
14,"#004D43", 
15,"#8FB0FF", 
16,"#997D87",
17,"#5A0007",
18, "#809693", 
19,"#FEFFE6", 
20,"#1B4400", 
21,"#4FC601", 
22,"#3B5DFF", 
23,"#4A3B53", 
24,"#FF2F80",
25,"#61615A", 
26,"#BA0900", 
27,"#6B7900", 
28,"#00C2A0", 
29,"#FFAA92", 
30,"#FF90C9", 
31, "#B903AA", 
32,"#D16100",
33,"#DDEFFF", 
34,"#000035", 
35, "#7B4F4B", 
36,"#A1C299", 
37,"#300018",
38, "#0AA6D8", 
39,"#013349", 
40,"#00846F",
41, "#FFB500", 
42,"#C2FFED", 
43,"#A079BF", 
44,"#CC0744", 
45,"#C0B9B2", 
46,"#C2FF99", 
47,"#001E09",
48, "#00489C", 
49,"#6F0062", 
50,"#0CBD66", 
51,"#EEC3FF", 
52,"#456D75", 
53,"#B77B68", 
54,"#7A87A1", 
55,"#788D66",
56,"#885578", 
57,"#FAD09F", 
58, "#FF8A9A", 
59,"#D157A0", 
60,"#BEC459", 
61,"#456648", 
62,"#0086ED", 
63,"#886F4C",
64, "#34362D")

Does the Sub Job have any different fields/ information from a Job?   If it is 100% the same then I would relate the Jobs Table to the Jobs Table and make sure that the reference field is Related Customer Job  or something that makes it very clear a given Job is the Sub because it has a Parent.

If a Sub is different in any way, make a new table.  

I have a business case for a client where we have to take time cards from employees in the field and then break them into pieces for Payroll.   The big distinction is Regular and Overtime for components of a single time card.   Just like your case there are different Start and Stops but there is also Related Payroll Allocation Type which a Time Card does not have.

Hi Mickey,

If you aren't opposed to creating another table in your app, you could grab data from the jobs table to create Calendar Entries for them. This table could serve dual purpose of also storing your sub jobs data when you need to create sub-jobs.

In this way you can use the native parent-child relationship for your jobs and sub-jobs and have all of the data you need in one table to build the calendar and you can display the sub-jobs in an embedded report (perhaps a calendar?) on your job form.

I would then use Mark's suggestion to color code the "calendar entries" and "sub-jobs" to easily distinguish them on your calendar reports.

Also, keep in mind that you can use formula fields to generate custom strings to display on your calendars.

Jeff Richey | Solutions Consultant | Quandary Consulting Group
Website | LinkedIn | Knowledge Base