Summary portal

[Angus McKinnon]

I've been working on a planner-style layout as part of a rental management package that I haphazardly develop. The idea behind the layout is to let users see, at a glance, how availability is looking for a particular item. There's a screenshot of it attached below. 

The items displayed are all records in my Order_Item table, they are essentially "line items" on a hire invoice. The planner itself is based in a separate table, with the items related using a Link-all arrangement. Then the portal is filtered to include only the relevant items. (The date range is constrained, and the item name is filtered via a PatternCount function)

The blocks of colour that you see are done with conditional formatting, each portal row is a long button bar with different formatting applied to each individual button. (Essentially adding a day to the portal start date for each column in the chart). I figured this was better than having 40+ calculation fields on each record in the Order_Item table. 

So far, it seems to be working quite well (if a little slowly, but that's hardly surprising given the amount of crunching going into the display). The next step is to hopefully display the total number of each item required, on each particular day. This has got me scratching my head. 

Each line item has a quantity field, as customers can choose to hire any number of an item. So someone could casually eyeball the chart and not realise that one line of blocks represents two, or more, items. I'd ideally like to have a row at the bottom showing a summary with the total required. I've read up on portal summaries and it would seem that the best solution is to create a separate portal, with the same filtering, that displays a summary field on only one row. That seems straightforward enough, but the calculations required to make this happen seem quite demanding. First I'll need to calculate whether each line item is reserved on that date, for every column in the planner. And then a summary field, for every column too. 

I could go ahead and code all this, but it's going to be quite a bit of repetitive work to produce it. And I suspect that the amount of calculation involved (on top of the conditional formatting already present) will make the layout horribly unresponsive. 

I can't help but feel there's a better way of doing this, but I'm stuck for ideas. Does anyone have any suggestions?

[comment]

I tend to use repeating calculation fields for this type of display. A repeating field can be summarized individually for each repetition, using a single summary field.

[mr_vodka]

I agree with exactly as Michael said.  I have created colored gantt charts using repeating calculation fields for this type of activity.

P.S.  BTW Hello Michael, it's been a long time my friend.

[Angus McKinnon]

This sounds very promising, thanks. (I've not really done much with repeating fields since early bodges in v6)

Am I right in thinking that I can define a repeating field with let's say 40 repetitions, one for each column in the planner. Each value is just a serial number counting up. So then I define a calculation using (Planner_start_date + repeating_field) in the formula. Will that then give me 40 repetitions of results, one for each day on the planner?

If so, that's going to save a lot of organisational overhead and manual definitions. Is it likely to be quicker for Filemaker to execute, too?

Edited March 16, 2019 by Angus McKinnon

[comment]

9 hours ago, Angus McKinnon said:

Am I right in thinking that I can define a repeating field with let's say 40 repetitions, one for each column in the planner. Each value is just a serial number counting up. So then I define a calculation using (Planner_start_date + repeating_field) in the formula.

It's actually simpler than that. You don''t need the first field. You can define a calculation field as =

Extend ( Planner_start_date ) + Get (CalculationRepetitionNumber) - 1

and set the result type to Date, and the number of repetitions to 40 (or perhaps a multiple of 7 ?). 

If you place an instance of this field on the layout and format it to display as custom date, showing only "Thu", you will have your header labels bottom row. Use more instances of the same field with different formatting to create the top row.

 

9 hours ago, mr_vodka said:

Hello Michael, it's been a long time my friend.

Hey John. And whose fault is that? 😊

[Angus McKinnon]

Thanks, that looks perfect. It'll also be a more elegant way of doing the date headers at the top of the layout too.  

[Angus McKinnon]

Partial success - I suspect my inexperience with Repeating Fields is the problem. 

Using Comment's code snippet above, I've defined a repeating field (in the Planner table) called Planner_Date_Array. This seems to do what I'd expect, if I display all the repetitions the dates are there as anticipated. 

Then, in the Order_Item, I've defined a calculation field (which I've called Quantity_by_date) which is based on an IF statement to see if each repetition of Planner_Date_Array overlaps with the hire period.    The syntax is simply 

If ( 
(Item_Planner::Planner_date_array ≥ Date Out
 and 
Item_Planner::Planner_date_array  ≤ Date In); Quantity; 0)

The Quantity field is just a number field that is set by the user when booking in the hire. So if we have a line item for two items, hopefully the Quantity_by_date field should return "2" on a repetition that corresponds with a date which overlaps the hire period. 

Then I've used a Summary field to return the total of Quantity_by_date and am using a duplicate portal to display it. 

 

The problem I'm having is that the first repetition of Quantity_by_date works, but all the subsequent repetitions just return a zero. Similarly, the Summary field returns the total that I'd expect to see in the first repetition only. 

I suspect I'm doing something wrong in the calculation but I'm a complete novice at handling repeating fields so may have overlooked something glaringly obvious. 

[comment]

3 minutes ago, Angus McKinnon said:

The problem I'm having is that the first repetition of Quantity_by_date works, but all the subsequent repetitions just return a zero.

That is the expected result when you neglect to use the Extend() function on non-repeating fields used in a repeating calculation field:

Quote

Without the Extend function, the value in non-repeatingField is used only with the first repetition in the repeating field.

 

[mr_vodka]

15 hours ago, comment said:

Hey John. And whose fault is that? 😊

 

Haha...  How about life, my wife, and the baby. 😵  Totally all those fault. 
😁

 

[Angus McKinnon]

Excellent, thanks Comment. 

 

The crucial part I'd overlooked is that every time a non-repeating field is used in a repeating calculation it needs to be wrapped in Extend. I hadn't realised that it applied to every mention of the field, not just for what I was thinking of as the definition of an array. 

So my original attempt was this:

If ( 
(Item_Planner::Planner_date_array ≥ Date Out
 and 
Item_Planner::Planner_date_array  ≤ Date In); Quantity; 0)

 

Whereas the correct syntax is this:

If ( 
(Item_Planner::Planner_date_array ≥ Extend(Date Out)
 and 
Item_Planner::Planner_date_array  ≤ Extend(Date In)); Extend(Quantity); 0)

 

(It took me a while to realise that Quantity needed to be extended as well, otherwise the calculation just returns a blank value. This is because, without the Extend function, Quantity has no values beyond the first repetition. The way I picture it in my head is of a grid, with repetitions increasing along the X and Y axis. The calculation can only proceed diagonally, and without Extend a non-repeating field is empty after the first row.)

 

 

[comment]

1 hour ago, Angus McKinnon said:

The calculation can only proceed diagonally

I see a repeating field as a one-dimensional array - so there can be no diagonal.

 

Edited March 18, 2019 by comment

[Angus McKinnon]

8 hours ago, comment said:

I see a repeating field as a one-dimensional array - so there can be no diagonal.

 

 

Agreed - it's more how I was visualising the calculation itself. Interestingly there don't seem to be many examples of this kind of calculation in all the various blog posts and forum threads that a google search turns up. 

It's all working fine now, just a few more tweaks to the layout before it's finished off. 

At the moment the date and day headings at the top of the layout are multiple individual fields. (Seven fields for the days, and five for the dates). With my new-found knowledge I'm tempted to re-do these as repeating fields. It would certainly make the table neater, but I've already done the work with the existing fields. Are there any performance advantages to having, say,  one repeating field with seven values, rather than seven individual fields as I have just now?

[comment]

3 hours ago, Angus McKinnon said:

At the moment the date and day headings at the top of the layout are multiple individual fields. (Seven fields for the days, and five for the dates).

As I said earlier, you can re-use the same repeating field for ALL the labels - see the attached example.

DateLabelsR.fmp12

 

3 hours ago, Angus McKinnon said:

Interestingly there don't seem to be many examples of this kind of calculation in all the various blog posts and forum threads that a google search turns up. 

Yes, unfortunately repeating fields have a bad reputation in the FM community. There are good reasons to avoid them when storing data, but they can be very useful for certain types of calculations - see for example:
https://fmforums.com/topic/29250-poor-mans-recursive-function/?tab=comments#comment-131639

 

 

 

Edited March 18, 2019 by comment

[Angus McKinnon]

17 hours ago, comment said:

As I said earlier, you can re-use the same repeating field for ALL the labels - see the attached example.

That's useful, and a very elegant way of doing it. 

 

Quote

Yes, unfortunately repeating fields have a bad reputation in the FM community.

I confess I'd got them categorised as a bodge from pre-relational times that is best avoided. But they undoubtedly have their place. 

I've managed to get the layout finished with much less overhead than I was fearing - thanks mainly to the advice here. 

[millmaine]

I have a similar problem. I have a layout summarising advertising activity and ticket sales for multiple events. 

I have tried a number of things.

First in order to show dates (over a defined date range) that activity and sales occur I use a calculation file called "zs_GanttChart_rt":

Let ([Current_Date = Extend ( zv_StartDate_gd ) + Get (CalculationRepetitionNumber)];

Case ( IsEmpty ( Refiner_1A ) and Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_Start) +2; Extend (Z_colour); Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_End) +2; Extend (Z_colour) ))

That works fine.

Then I try to put sales figures in. First I simply used a repeating Summary Field with:

List of zs_GanttChart_rt

That works to a point. I get all the sales figures listed in the appropriate repeating field. However I don't actually want a list of each sale that occurs on a particular day. I want the total sales on that day.

So I created a calculation field on the sales items: GetSummary (Sum_Quantity;Date_Start)

Then tried this:

Let ([Current_Date = Extend ( zv_StartDate_gd ) + Get (CalculationRepetitionNumber)];

Case ( Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_Start) +2; Extend (Summary_Sales); Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_End) +2; Extend (Summary_Sales) ))

Now here's where it gets interesting. If placed within sub summary part, it returns the correct value for the first relevant repetition only (and no other values). If placed in a grand summary part, it returns the correct value for the last relevant repetition only (and no other values).

I'm puzzled.

[mr_vodka]

No idea what your structure looks like...  You would have to have some sort of calculation to sum of each day.  Please post more info.