Creating 52 week calendar

[laguna92651]

Looking for some direction on how to go about setting up this requirement.

I have a set of accounts, each account has an activities plan with 12 or so line items, activities, with dates associated with each activity.

Account line items for account Tech Coast.com

I would like to be able to consolidate multiple account lines activities into a single 52 week calendar grid (or longer).

Calendar Grid

If the account line item date falls within a given week date range, the line item "No", a 1, 2, 3 etc., would be placed in appropriate weeks field, with a gray background, to show that it is scheduled. If the line item has been marked as "done" the grid entry on the calendar would show the number with with a blue background. If a line item does not have a number associated with its "No" field a "/" would show in the calendar grid and would show as a "/" with a blue background if marked as done.

Each account would would have its own line in the 52 week calendar. And I would want to be able to change the start date of the calendar and recompute the week labels, the label would use the date of that Sunday for the week as the label. I would still want to retain a record of those steps that are no longer showing.

It would be nice to be able to click on a marked grid cell and cycle through gray background and blue background.

[comment]

Can an account have more than one activity in any given week? Your display doesn't leave any room for it.

 

[laguna92651]

I have never had that happen, but I guess it would be possible. If that possibility causes undo complexity, I could live without dealing with multiple activities in a week.

[comment]

51 minutes ago, laguna92651 said:

If that possibility causes undo complexity,

The main complexity is lack of room. Anyway, I suggest you do it this way:

First, add a global date field (gViewDate) to the Accounts table. Next, create a list view of the Accounts table, and place a one-row portal to Activities in the body part. Filter the portal by the following expression =
 

Let ( [

week = 1 ;

sun0 = Accounts::gViewDate - DayOfWeek ( Accounts::gViewDate ) - 6 ;
sun = sun0 + 7 * week ;
sat = sun + 6
] ;
sun ≤ Activities::Date and Activities::Date ≤ sat
)

Place the field you want to show inside the portal and format it conditionally to display the status. If you want, you can make it a button to toggle the status.

Duplicate the portal (and its contents) as many times as the number of columns you want to display, and arrange the duplicates side-by-side. For the portal in column 2, change the filtering expression to week = 2, and so on.

For the header, define a calculation field (result is Date) =

Let ( [
d =  Extend ( gViewDate )  ;
sun0 = d - DayOfWeek ( d ) - 6
] ;
sun0 + 7 * Get ( CalculationRepetitionNumber )
)

and let it have the same number of repetitions as the number of columns you wish to display.

Note that the portals need to be refreshed when you change the value of gViewDate.

[laguna92651]

Thanks for the help, this is the right idea, I haven't quite got it working yet. Here is the grid I'm currently generating, the dates are not  slotted correctly yet.

Here is the test data I'm using. I used the account activity date in the portal to more easily track what is happening, it would normally be the stage no. The second group of data shows what the data slotting should look like. I assumed the gViewDate is the start date of the calendar? I'm generating a set of intervals that starts a week before the gViewDate.

How would I go about refreshing all of the portals?

Edited June 27, 2016 by laguna92651

[comment]

At a glance, your 3rd, 4th and 5th portals show the same thing as your 1st portal - so I would guess you haven't adjusted their filtering expression to week=3, week=4 and week=5.

 

1 hour ago, laguna92651 said:

How would I go about refreshing all of the portals?

In older versions you'd use Refresh Window [Flush cached join result]. Beginning with v.14, you have the Refresh Portal step - but you'd have to give  a name to all your portals and refresh each one individually - so you might want to stick to the old method.

[laguna92651]

I check the portal a dozen time just hoping that was the problem. What might the problem be in the weekly labels starting a week before the gViewDate? Also the entries for the first two accounts should be blank?

[comment]

11 minutes ago, laguna92651 said:

the weekly labels starting a week before the gViewDate?

The labels start on the Sunday of the week of gViewDate (which can be any date). If gViewDate is Friday, Jun 24, 2016, then the first label will show the Sunday of that week that is June 19, 2016.

In any case these labels are just that - labels. They do not play any part in what is being shown in the portals.

[laguna92651]

Let ( [
week = 3 ;
sun0 = T31_OPPORTUNITIES::gViewDate - DayOfWeek ( T31_OPPORTUNITIES::gViewDate ) - 6 ;
sun = sun0 + 7 * week ;
sat = sun + 6
] ;
sun  ≤  T31j_opportunity_IMPLEMENTATON PLAN_||id_opportunity|::Date  ≤ sat
 )

This is what I have in portal 3, OPPORTUNITIES = Accounts table and IMPLEMENTATION PLAN = activities line items table stated in previous post. Your script is very small so not much can go wrong, but someway I managed to botch it up.

sun  ≤  T31j_opportunity_IMPLEMENTATON PLAN_||id_opportunity|::Date  ≤ sat

Okay I see the problem there should be an And here, I had fixed in the earlier portal but didn't update the other portals, I will give it a go.

[comment]

5 minutes ago, laguna92651 said:

Your script is very small so not much can go wrong, but someway I managed to botch it up.

It's a calculation, not a script. But you are right, you have it wrong. Instead of:

sun  ≤  T31j_opportunity_IMPLEMENTATON PLAN_||id_opportunity|::Date  ≤ sat

you need to have:

sun  ≤  T31j_opportunity_IMPLEMENTATON PLAN_||id_opportunity|::Date and T31j_opportunity_IMPLEMENTATON PLAN_||id_opportunity|::Date ≤ sat

 

[laguna92651]

Thank you very much. This was such a very elegant solution to something that I thought was going to be very complex, works great!

[laguna92651]

Is it possible to count the number of "activity items" in a given weekly slot? Or even better the number of "activity item" types in a given weekly slot? If a type is a 1, 2, or 3 etc., how many 1's, 2's or 3's etc.?

[comment]

Do you mean per account, or in total?

[laguna92651]

Count in total by week interval.

[comment]

The nice thing about filtered portals is that they require very little to none added resources (such as fields and relationships). The downside is that they work only at the layout level and there's very little you can do with the filtered results other than display them. For example, summing individual filtered results for the same week would be very difficult.

I would suggest you add another relationship between Accounts and (another occurrence of) Activities, using the X relational operator. Then place filtered portals to this occurrence in the grand summary part of your layout, and place a summary field defined in the Activities table as Count of [ ActivityID ] in the portals.

 

5 hours ago, laguna92651 said:

Or even better the number of "activity item" types in a given weekly slot? If a type is a 1, 2, or 3 etc., how many 1's, 2's or 3's etc.?

Properly, this would be produced from a layout of Types where you would have a row of filtered portals for each record - same as you have now for each account. To show the same thing on the Accounts layout, you would have to use a dedicated set of filtered portals for each type, and filter it by type as well as by the week. This requires a fixed and known number of types.

 

[laguna92651]

I will give that a try, thanks for the guidance.