Conditional Summary Function

[Greg Hains]

Good morning.

 

I have found the following conditional summary function on Brian Dunning's site:

http://www.briandunning.com/cf/1367

 

For the most part it works OK, but I am having issues with it.  I have contacted the author (Koji Takeuchi) who has kindly offered to assist however is a busy man and I don't want to disturb him again.

 

Attached is a roster type solution, whereby a company has a number of engineers and a number of clients. Each engineer is assigned to a particular client by default, but each month any given engineer may attend a client site.  The idea of the Conditional Summary is to add up the total hours assigned to each engineer (in the top, client table), and have it summarised in the engineer table below.

 

The calculation works fine, except... only the top (x) clients are evaluated, which happens to be the same number of records in the engineers table. Its a complicated function and I cannot see where it falls over. There's no error, it just does not calculate more that (x) clients.

I have tried various things - such as certain characters in the client and engineer data, length of text in the fields, empty records etc, but cannot find the fault.

 

Would anybody like to have a crack at it? I'd be very appreciative if they can solve the problem as I think it's quite a powerful addition to any solution. Thank you in advance.

 

Cheers,

Greg 

 

Support Schedule.fmp12.zip

[eos]

I'm not sure if that is the cause of your issue, but the CF seems to have a flaw when it comes to summarizing related repeating fields. This expression

$$ConditionalSummary_counter =
  Case (
    IsEmpty ( $$ConditionalSummary_counter ) and to_context = to_target ; Get ( FoundCount ) ;
    IsEmpty ( $$ConditionalSummary_counter ) and to_context ≠ to_target ; ValueCount ( List ( SumField ) ) ;
    $$ConditionalSummary_counter - 1 
)

will – for a related repeating field – return the count of all values in all repetitions of that field – which e.g. in your sample returns 33 (all non-empty repetitions in “attendingEngineer”) instead of 21 (count of Clients records). 

 

You want 21 because you're testing in each client record's (from 1 to 21) the nth repetition [1-12] for the presence of the respective engineer's initials.

 

That being said: why aren't you using a join table with year, month, hours, companyID and engineerID (and – of course – IDs instead of initials)?

 

It's OK to use repeating fields to display summarized data in a cross-tab view, but you shouldn't store the real data in that way.

 

With the above join table you could create one repeating field [12] in the Engineers table and use e.g. SQL like this:

ExecuteSQL ( " 
  SELECT COUNT(*) 
  FROM EngineersClientMonth
  WHERE 
    engineerID_FK = ? AND 
    theYear = ? AND
    theMonth = ? 
  " ; "" ; "" ; engineerID_PK ; Engineers::gYearSelector ; Get ( CalculationRepetitionNumber ) 
)

where gYearSelector would be a global field that allows you to select the desired year for your Dashboard display.

[Greg Hains]

Hi Eos,

 

I want to thank you for taking the time to look at my issue and respond in detail to my distress call.

 

I am glad you can see where the calculation is going wrong. I started from the ground up and applied the function as I found it so I cannot truly claim to understand where it went wrong - not that I'm blaming the function's author either - I just used it off the shelf.  I thought that the repaying fields may be a way to manage the naming better, but I guess not...

 

You are clearly a more advanced developer than me, and I will pore over your explanation and try to make it work. I've honestly never used the SQL script step before, but looks like I'm about to learn.

 

Once again, thank you. 

 

Cheers,

Greg

[eos]

I've honestly never used the SQL script step before, but looks like I'm about to learn.

 

Hi Greg,

 

there is in fact a script step Execute SQL[] , but what I mean is the function ExecuteSQL().

 

Have a look at the attached example.

Support Schedule_eosMOD.fmp12.zip

[Greg Hains]

Good morning Eos.

 

Thank you very much for this file. I intend to study the modified function, see how it was applied on the layout and learn how you did this. I understand the individual steps in the function, but they can take on a new dimension when combined. Gives me goosebumps how clever it can be. 

 

I am looking for an emoticon where I bow to you on bended knees in appreciation - but cannot find one. lol

 

Cheers,

Greg

[Greg Hains]

Hi Eos,

 

Further to this morning's email, could I ask two questions of you please?

 

I'm trying to implement that same summary function to create another summary in the same manner and I get completely different outcome. I know it's the way I've employed it as opposed to your work. The second figure in the engineers monthly column is trying to add up the total hours allocated to that client - but yields only a horizontal row of the number 6. I cannot see where it's coming from, yet I think I followed the same method.

 

Secondly, I need to have conditional formatting in place to colour code engineers in the client list. I don't know which field (specifically) I am looking at to apply formatting. 

 

Could you please point me in the right direction? I'm sorry to be a pain...

 

Greg

[eos]

Hi Greg –

 

the setup script was just for the initial creation of the join table records; and since I was dealing with freshly attributed (and serialized) clientIDs, I used two generic loops for clientIDs and months. In a production environment you probably would read in a list of IDs for clients with an active support contract, or something similar, and create join table records based on that list.

 

I think you have two scenarios: create one set of join table records for a new year for all existing (active …) clients, or add one set for the current year for a new client.

 

Of course, you need to safeguard against creating duplicates; look at the sample file. 

 

Secondly, I need to have conditional formatting in place to colour code engineers in the client list. I don't know which field (specifically) I am looking at to apply formatting. 

 

Do you mean the “primary engineer” in the client list on the left, or the “attending engineer” in the matrix, i.e. the join table records? (btw., if you switch to engineerIDs, you need of course use them also for the primary engineer in the Clients table …) Depending on the answer, there are different approaches you may want to use. (CF, TextStyleAdd(), or even a small Web Viewer …)

 

As to the total hours per engineer per month – since your initial file didn't work properly, I wasn't sure how you need to calculate that; I added an amount field to the join table, but I think you use some allocation method … so: how do you calculate that? 

Support Schedule_eosMODv2.fmp12.zip

[Greg Hains]

Hi Eos,

 

It will be for the engineer in the client list, so that when a colour is set for an engineer (which I eventually want to make user-configurable), whenever an engineer (initials) is found in the roster, a dummy field behind the initials and second column will be coloured accordingly. I was going to be smart about it so I didn't have to apply CF to clients multiplied by 12 fields multiplied by engineers. A single smart CF applied to all fields would let me do that. I'll work that out.

 

The second column - you saw what I meant?

As an engineer is assigned got a client monthly, the default hours (hours per period) will add up so each engineer knows how many hours will be allocated him/her for that month. Its essentially the same as the client count, only this is an hour count. I applied the the same principal to the other(hours) field but to no avail. lol

[eos]

The second column - you saw what I meant?

 

Hi Greg -

 

yes, but not how to calculate it.

 

Have a look at the attached file. It uses an extra table to specify the contract conditions for a client per report period (presumably a calendar year); this allows to auto-enter the hours for a visit, then sum that up using a calculation similar to the client count.

 

If you go that route with the extra table, you need to adjust the setup script to also create those records (which I didn't for this example, just imported the records and appropriate fields from Clients).

Support Schedule_eosMODv3.fmp12.zip

[Greg Hains]

Hi Eos.

 

Once again thank you - you've helped me knock this problem on the head.

 

Cheers,

Greg