Track recurring values?

[Saubs]

Hello all,

I'm trying to build a calc formula that is aware of the number of times a particular value appears in a portal. I've attached a sample file which demonstrates what I mean.

Basically I need a formula in a calc field in the child table that says something like, "if this is the first or second time this employee's name appears in the portal, then . Else, if this is the third or fourth instance of this employee, then . I will gladly elaborate on if necessary, but the main issue is how to make my formula aware of how many times the employee's name appears in the portal. I'm thinking it may involve the List or PatternCount function, but I've been unable to nail it down.

Thanks very much for your time and assistance.

Example.fp7.zip

[comment]

It is not quite clear from where would the particular value come from. Anyway, you could use something like:

ValueCount ( FilterValues ( List ( Employees::Value ) ; MyValue ) )

[Saubs]

Thank you for your reply, comment. I apologize, I haven't explained this very well. I want to add a new field called 'cOccurence' to the child table. This field will track each occurence of each employee's name in the portal (see new attachment, it should illustrate this better). I will then incorporate the value returned by 'cOccurence' into the cPension formula.

Thank you for your time and patience.

[comment]

I think it would help if you explained the background to this. The child table in your example is clearly NOT the Employees table, as it contains duplicate employee records - it seems this is a Tasks table? And I assume you want to calculate the occurrence number of an employee's task within the SAME parent record?

[Saubs]

You are correct, comment -- it's not an employees table, it's actually a line items table. Each record in the parent table is an employment contract. Employee names go in the line items table. An employee name can appear one time or several times on a given contract. And yes, I want to calculate the number of occurrences of an employee within the same parent record.

Thank you

[comment]

Try it this way.

Note that the employee's name has no place in the join table.

CountOccurence.fp7.zip

[Saubs]

comment,

You NAILED it. Very nicely done-- thank you so much.

It looks like the join table is the important piece of the puzzle that I overlooked, huh. And then the self-join is what really makes it work.

Thank you for your time!

[comment]

It must be done from the join table, because that's the only place where BOTH EmployeeID and ProjectID are.