I'm burning my brains on something that has to be as easy as 123. But for some reason it doesn't work.

I'm working on a timesheet and want filemaker to mark if a person runs a second shift (on the same day). I have a calculation field which generates a unique id by combining the date and employer id. This number generated is unique, except when a double shift is run (date and employer id are the same, so same unique id is generated).

I then tried a self-joining relation, where this generated id is the relation. I figured a count or sum calculation would do the trick, no luck...

Please comment.

Why dont you add the shift number as part of the concatenation?

Shouldn't it be employee id? Anyway, a count of related records, e.g. Count ( selfjoin::EmployeeID ) should produce a correct result - after the current record is committed.

BTW, you do not need the calculation field: you can base the relationship directly on both fields. You can even eliminate self from the count, by defining the relationship as:

Timesheets::EmployeeID = Timesheets 2::EmployeeID

AND

Timesheets::Date = Timesheets 2::Date

AND

Timesheets::TimesheetID ≠ Timesheets 2::TimesheetID

I got something else... I think it works.

Created a self-joined relationship (named: duplicate shift check) with employer id and date as matches. Sorted by employer id (very important).

Then the following calculation field:

If ( duplicate shift check::double shift checksum = GetNthRecord(duplicate shift check::double shift checksum;Get(RecordNumber)- 1); 1; 0)

It does mark the second shift as '1', but I actually wanted both shifts to mark. So I will look into the submitted suggestions now.

Negative, it does mark 1, but on the wrong records...

This did the trick...

Count(duplicate shift check::)-1

Simple as 123 ;-)

Then with conditional formatting I marked yellow those > 0.