Calendar in "bar graph" form

[madman411]

Hey guys. I was browsing the interwebs for a calendar solution to show the duration of booked assets. I found a simple one on here but can't seem to relocate it.

What I'm looking to do is have all my assets listed down the left of the chart, and have the days of the month listed across the top. It would be in a monthly view. The chart would have the booking date ranges in a "bar graph" spanning that date range for the respective asset.

Is this feisable? I'm going to keep looking for the original post I found, but this is what I'm going for.

[comment]

A repeating field with 31 repetitions could do it.

[madman411]

I thought about using a repeating field for displaying the days of the month across the top.

I would assume that the "bar graph" repeating field would do something referencing the date range supplied, with conditional formatting applied.

Is this type of calendar best made in a List View?

[comment]

List view, portal - depends on what you want to do with it.

[madman411]

I've found this site which is exactly what I was thinking:

 

http://coresolutions.ca/update-gantt-charts-in-filemaker

 

however it seems they're no longer offering support on their model. Has anyone managed to successfully create a Gantt Chart in FMP without needing a plugin?

[madman411]

Okay, here's what I have so far.

 

Their calculation which sets the repeating fields that fall under the specified date range is:

Let([
current_date = Extend(zv__startDate__gd) + Get ( CalculationRepetitionNumber )
];
If(current_date > Extend(startDate) and current_date < Extend(endDate) + 2; Extend(colour))
)

I have modified it to the following, which fills in the repeating fields that fall under the date range of the FIRST related booking record:

Let([
current_date = Extend(start_date_gd) + Get ( CalculationRepetitionNumber )
];
If(current_date > Extend(_bookings::start_date) and current_date < Extend(_bookings::end_date) + 2; Extend(_bookings 3::color))
)

I'm looking to have the date ranges of all the related records be filled in, regardless if they conflict. I have three TO's of "_bookings" (_bookings, _bookings 2, and _bookings 3) for another layout which comment helped me figure out. It browses all related records and alerts the user if a booked date range conflicts with any related record, and then another occurrence highlights the related records themselves so I can see which records conflict with each other. 

 

Comments original solution used the IsEmpty function for the conditional formatting calculation to display available records in that range. I added a "not" to make the unavailable records turn bold. Works like a charm. I figured a similar method would fill in the repeating fields respectively, but I seem to be mistaken. I think I'm along the right track with the modified formula I had above, but perhaps my TO references are wrong. 

 

I have come up with the following, but in the process I've confused myself (go figure).

Let([
current_date = Extend(start_date_gd) + Get ( CalculationRepetitionNumber )
];
If(_bookings::asset_id = _bookings 3::asset_id and current_date > Extend(_bookings::start_date)  ≤  Extend(_bookings 3::end_date) and Extend(_bookings::end_date)  ≥  Extend(_bookings 3::start_date) and _bookings::tracking_id  ≠  _bookings 3::tracking_id and current_date < Extend(_bookings::end_date) + 2; Extend(_bookings 3::color))
)

This just clears the Gantt Chart completely. I seem to have fallen in to the "one step forward, two steps back" mess.

 

I should probably note that the table the layout is primarily referencing is the Asset table.

 

Can anyone see where I'm going wrong? I made it partially work, which tells me I'm going in the right direction.

[comment]

I'm looking to have the date ranges of all the related records be filled in,

 

Do you mean you're charting this at the Asset level? If so, start by defining a repeating calculation field cChartR in the Bookings table (result is Number)  =

Let (
d = Extend ( zv__startDate__gd ) + Get ( CalculationRepetitionNumber ) - 1
;
Extend ( startDate ) ≤ d and d ≤ Extend ( endDate )
)

EDIT:

Then define a summary field (still in the Bookings table) as Total of [cChartR].

 

Place this summary field on the layout of Assets and format it conditionally when > 0. Alternatively, you can format it as Boolean to show a mark when the corresponding day is booked.

 

 

 

 

 

---

We are assuming here that zv__startDate__gd contains the first day of the currently viewed month.

Edited November 4, 2013 by comment

[madman411]

Hi Comment

 

You are correct, I'm charting at the asset level and zv__startDate__gd is the demo files start date for each month. Once this is set, the following 40 days are displayed, as per my repeating value.

 

With CChartR used on the chart layout I'm getting the same result as I was using before though. The chart isn't displaying multiple booked date ranges. Do I need to utilize one of my other TO of Bookings?

[comment]

With CChartR used on the chart layout I'm getting the same result as I was using before though.

 

 

 

DELETED - see below

Edited November 4, 2013 by comment

[madman411]

Thanks for your patience with me Comment, but now all the assets with related booking records are just filling in all 40 of the repeating fields.

 

Here's my calculation for cChartR in my bookings table: (perhaps the global start date field should be in the booking table..?)

Let (
d = Extend ( _system_inventory_assets::start_date_gd ) + Get ( CalculationRepetitionNumber ) - 1
;
Extend (start_date)   ≤   d and d   ≤   Extend (end_date)
)

Calculation Result: Number, Repeating 40, Do not evaluate.

 

Here is my calculation for the repeating "color blocks" field, used on the chart layout within the Asset table:

Case(Sum(_bookings::cChartR) ; Extend (_bookings::color))

Calculation Result: Text (the text returned is the color the blocks become, based on the booking status -- this all works fine), Repeating 40, Do not evaluate.

 

The asset table and booking table are merely joined by the Asset ID. Asset table is joined to bookings 2 like this, respectively:

 

asset_number = asset_id

and g_bookingstart =< end_date

and g_bookingend >= start_date  

 

(this relationship was used so the user could enter a date range on a previous layout and the asset would display in red if there was an existing booking within that range).

 

booking 2 and booking 3 are joined to each other this way, respectively:

 

asset_id = asset_id

and start_date =< end_date

and end_date >= start_date

and booking_id not= booking_id

 

I'm going to keep playing with how I have this set up, but perhaps someone's trained eye might see where I've messed up.

[comment]

I am sorry - apparently I was mistaken regarding the calculation field. I will edit my posts above - meanwhile, here's a demo file that shows how it should work.

 

 

ChartBookingsR.fp7.zip

[madman411]

I am sorry - apparently I was mistaken regarding the calculation field. I will edit my posts above - meanwhile, here's a demo file that shows how it should work.

 

You're fantastic! Thanks for the help.

[madman411]

One last question;

 

Say if each booking record was assigned it's own status, say "pending" [blue] or "out" [green] and i wanted to condition the date range in the gantt chart to reflect those status', is there a way to do that with this solution? I noticed that if I reference the booking ID within the conditional formatting, one booking ID is returned for all the bookings related to that particular asset. 

 

Needless to say, you've put me on the right track. Thank you again.

[comment]

Wow, that's some question.  Ok, let's see:

Suppose there are three types of bookings: Red (default), Green and Blue. And suppose it's safe to assume that there will be no more than 9 overlapping bookings of each type per asset per day in the charted period. Then we could change the formula of cChartR to =

Let ( [
d = Extend ( Assets::gChartStartDate ) + Get ( CalculationRepetitionNumber ) - 1 ;
n = Extend ( StartDate ) ≤ d and d ≤ Extend ( EndDate )
] ;
n * Case ( Extend ( Type ) = "Blue" ; 100 ; Extend ( Type ) = "Green" ; 10 ; 1 )
)

This would enable you to conditionally format the summary field according to its magnitude (i.e. red if ≥ 1, green if ≥ 10, blue if ≥ 100). You could also extract each digit individually, in case you prefer non-mutually-exclusive formatting.

[madman411]

Many thanks, comment.

[madman411]

Alright Comment. I'm requesting your assistance in a last addition to this chart.

 

When a booking is made, the user can enter an Order number as a reference. This number is only four digits, so not long.

 

I have worked with your code and managed to get the newest booking entry to display the order id in all of the associated repeating fields, over the colored conditional formatting fields that are sChartR, using a repeating field called cChartID's (a mouthful!):

Let ( [
d = Extend (start_date_gd ) + Get ( CalculationRepetitionNumber ) - 1 ;
n = Extend (start_date) ≤ d and d ≤ Extend (end_date)
] ;
n * (Extend(order number))
)

Since there's no way to summarize text, such as with your status color calculation, I'm wondering if there's a similar approach to display the related record's order number over the respective "bar"? Having only one instance at the beginning of the order period would be ideal, but I'm quite content with each field displaying the order number (I can fit a four digit number quite comfortably in each repeating field).

 

Is some sort of summary field required, or do I need to create an additional field with an additional calculation?

[comment]

Try =

Let ( [
d = Extend (start_date_gd) + Get (CalculationRepetitionNumber) - 1 ;
n = Extend (start_date) ≤ d and d ≤ Extend (end_date)
] ;
Case ( n and ( d = Extend (start_date_gd) or d = Extend (start_date) ) ; Extend (order number) )
)

This will display the order number on the first day of the booking - or on the first day of the chart, if the booking is carried over.

Since more than one booking may meet the criteria, you can use a summary field defined as Minimum or Maximum to pick the earliest or the latest order. Note that this is still a numeric operation, and an order number such as "0123" will be reduced to 123 in the process.
 

[madman411]

Thanks comment. One attempt I made at a calculation was so close - didn't include the "ext end" function!