codefluent

Need to create a unique constrain on 2 collection fields

Scenario: A Time Tracking/Punch Card system is being created that will be used by 40+ schools. Due to how payroll is processed by accounting, employees must estimate the number of hours they will work towards the end of the pay period. In addition every pay period each school must enter the date in which their employees must estimate the hours for remainder of the pay period.

Theses are the entities related to my question:

What I am trying to achieve:

  1. Most importantly, I need to ensure that for the PayPeriodEstimateHoursSchedule a school cannot enter 2 records for the same pay period.

  2. If possible, I would like to create a method called LoadBySchoolAndPayPeriod that can be called from the single entity instead of the collection entity.

PayPeriodEstimateHoursSchedule.LoadBySchoolAndPayPeriod(bmiSchool, currentPayPeriod)

Instead of

PayPeriodEstimateHoursScheduleCollection.LoadBySchoolAndPayPeriod(bmiSchool, currentPayPeriod)

.

  <cf:entity name="School" namespace="DemoCompositeKey">
    <cf:property name="Id" key="true" />
    <cf:property name="Name" />
  </cf:entity>
  <cf:entity name="PayPeriod" namespace="DemoCompositeKey">
    <cf:property name="Id" key="true" />
    <cf:property name="StartDate" typeName="date" />
    <cf:property name="EndDate" typeName="date" />
  </cf:entity>
  <cf:entity name="PayPeriodEstimateHoursSchedule" namespace="DemoCompositeKey">
    <cf:property name="Id" key="true" />
    <cf:property name="Date" typeName="date" />
    <cf:property name="PayPeriod" typeName="{0}.PayPeriod" />
    <cf:property name="School" typeName="{0}.School" />
  </cf:entity>
Solution

  • The first solution is to use the uniqueConstraintNames attribute:

    <cf:entity name="PayPeriodEstimateHoursSchedule" namespace="DemoCompositeKey">
  <cf:property name="Id" key="true" />
  <cf:property name="Date" typeName="date" />
  <cf:property name="PayPeriod" typeName="{0}.PayPeriod" uniqueConstraintNames="UC1" />
  <cf:property name="School" typeName="{0}.School" uniqueConstraintNames="UC1" />
</cf:entity>

    Or define a composite key:

    <cf:entity name="PayPeriodEstimateHoursSchedule" namespace="DemoCompositeKey">
  <cf:property name="Date" typeName="date" />
  <cf:property name="PayPeriod" typeName="{0}.PayPeriod" key="true" />
  <cf:property name="School" typeName="{0}.School" key="true" />
</cf:entity>

    Concerning the method, you can create a CFQL method:

    LOADONE(School, PayPeriod) WHERE School = @School AND PayPeriod = @PayPeriod