PR Craft Template Overrides Lookup for Timecard Entry

If you use craft templates that have overrides applied to them, you can follow the steps below to configure your craft lookup to respect this logic.

This logic will only be applied if craft is a column shown on the timecard. This article will guide you through the process of creating a user defined lookup in Vista, applying the lookup to Vista's PR Timecard Entry, and configuring the Portal to inherit the lookup.

Note: The following lookup may not apply to your unique scenario. For assistance, please log a case with Professional Services on the Viewpoint Customer Portal.

Lookup

  • From
    • PRCM with (nolock)
  • Where

    • PRCM.PRCo = ? and PREH.PRCo = PRCM.PRCo

      and PREH.Employee = ?

      and 1 = CASE WHEN ? is null Then 1

      WHEN JCJM.Job is null Then 1

      When ? is not null and JCJM.Job = ? and PRCM.Craft is not null then 1

      Else 0 END

  • Join

    • LEFT OUTER JOIN JCCO on PRCM.PRCo = JCCO.PRCo

      LEFT OUTER JOIN JCJM on JCCO.JCCo = JCJM.JCCo

      LEFT OUTER JOIN PRCT on PRCM.PRCo = PRCT.PRCo and PRCM.Craft = PRCT.Craft and PRCT.RecipOpt = 'O' and JCJM.CraftTemplate = PRCT.Template

      LEFT OUTER JOIN PRCM JCraft on PRCT.PRCo = JCraft.PRCo and PRCT.JobCraft =JCraft.Craft

      OUTER APPLY PREH

  • Details
    • ISNULL(PRCT.JobCraft, PRCM.Craft)
    • CASE WHEN PRCT.JobCraft is not null then JCraft.[Description] Else PRCM.[Description] END
    • ROW_NUMBER() OVER (ORDER BY CAST(CASE WHEN PREH.Craft = PRCM.Craft Then 1 Else 0 END as varchar(1)) desc, RIGHT(' ' + ISNULL(PRCT.JobCraft, PRCM.Craft),10) asc)
  • Group By
    • ISNULL(PRCT.JobCraft, PRCM.Craft), CASE WHEN PRCT.JobCraft is not null then JCraft.[Description] Else PRCM.[Description] END
    • , RIGHT(' ' + ISNULL(PRCT.JobCraft, PRCM.Craft),10), CASE WHEN PREH.Craft = PRCM.Craft Then 1 Else 0 END

Applying the Lookup in Timecard Entry

  1. Open PR Timecard Entry
  2. Select the Craft Field and press F3
  3. Select the Lookups tab
  4. Enter in the name of the Lookup made in the above step
  5. Enter in the parameters -1,10,20,20,20 .
  6. Set the Load Sequence to 0
  7. Change any other load sequences on the form to a number greater than 0
  8. Click OK

Note: A warning message displays, and can be disregarded, when entering the lookup. "Table or Column does not exist. Record will be saved." The lookup will still save and properly pull data.

Portal Setup

  1. Navigate to Employee Tools > Timecard Settings.
  2. On the Craft Column select Blank for New Line behavior.

  3. In the Advanced settings, check Auto Select 1st. This auto-selection only applies if the lookup returns a single value.

    Run the attached following SQL script to update TimecardColumnStatic:

    Use [KDS-HRIM]

    GO

    Update TimecardColumnStatic

    Set DependentColumns = CASE WHEN DependentColumns is null then 'Craft'

    WHEN DependentColumns = '' Then 'Craft'

    Else DependentColumns + ',Craft' END

    Where ColumnName = 'Job' and DependentColumns not like '%Craft%'