Counts

Follow

James Lawler

• November 13, 2014 15:21

Hi Guys this may seem a little complex so I will keep it as simple as possible.

App 1 = Staff Members
App 2 = Absences

Every time a staff member is absent we record one absence and 'Number of Days' they were absent within App 2.

The title of the 'Absence' card is the staff members name, labelled "Who" - referenced from Staff Members app (App 1).

So in App 2 we end up with...

Who: James L
Number of Days: 2

...What we want to do is record someones bradford score against the 'Staff Members' app. The Bradford score is as follows:

'number of absences (occurrences) x number of absences (occurrences) x total number of days absent = Bradford Score (SxSxD=B)'

We obviously have the 'Number of Days' in the Absences app, but wondered whether or how we can use calculations to figure out the number of occurrences within the app.

I do not know javascript or PHP but imagine if I was going to calculate the bradford score, from within the Staff Member app, and as a layman it would look something like:

COUNT number of 'Who' in 'Absences' WHERE 'Who' is equal to 'Staff Members name' (in this app - 'Staff Members') = number of occurences
*
COUNT number of 'Who' in 'Absences' WHERE 'Who' is equal to 'Staff Members name' (in this app - 'Staff Members') = number of occurences
*
SUM of 'Number of Days' (within absences app)
For the period of 01/01/2014 to 31/12/2014.

e.g. 3 occurrences of 'James L' within App 2 * 3 occurrences of 'James L' within App 2 * Sum of Number of Days for James L

I hope that makes some kind of sense and someone can help?

0

• 

  Rainer Grabowski (FVM)

  • November 13, 2014 15:50

  Hi James,

  add a calculation field in App 2, call it e.g. COUNT with code:
  //for every absence item with a name in @who count
1

  Then a calculation field in App 1:
  sum of @count x sum of @count x sum of @number of days

  Hope it helps.

  0

  Comment actions Permalink
• 

  James Lawler

  • November 13, 2014 16:00

  Rainer that is awesome and I am definitely closer.

  I now have:

  'Sum of COUNT * Sum of COUNT * Sum of Number of Days'

  What I need is:

  Sum of COUNT (Where 'Who' in App2 is same as 'Name' in App1) * Sum of COUNT (Where 'Who' in App2 is same as 'Name' in App1) * Sum of Number of Days

  Is that possible?

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • November 13, 2014 16:16

  When I got you right, the field "Who" in App 2 is referenced from App 1.
  So when someone creates his absence card, he clicks into the field "Who" and selects his name. So his absence card is connected to his Staff Member entry in App 1.

  The calculation field in App 1 (Staff members) with the code

  Sum of COUNT * Sum of COUNT * Sum of Number of Days

  sums up the occurances of "1" and the sum of "Number of days" ONLY from the connected absences cards (App2).

  The staff item of "James L." counts only the absence items of "James L.", cause only those are connected to the staff item of James L. via the Who-field in App 2.

  0

  Comment actions Permalink

Please sign in to leave a comment.