Dynamic Validation Example

Dynamic validation compares checklist answers to a value stored in the database for a unit. That means that the option value for this question must be stored in the database. Usually, those codes are received from an external system and stored in the unit_lookup_codes table.

To simulate that, the following code can be added to the bottom of the create_new_unit stored procedure of a database. All units will have the ‘STANDARD’ option stored.

   -- Insert Unit Lookup Code

   insert into unit_lookup_codes

      (unit_id, lookup_code)

   values

      (@unitID, 'STANDARD')

• This screen requires the stored procedure Create_New_Unit. If you do not have this stored procedure you will need to add it to your database. This can be done using this code in SQL or Oracle (seek technical assistance if required). This will create a stored procedure named Custom_Create_New_Unit. This name should then be used instead of Create_New_Unit.

  create or replace procedure custom_create_new_unit(productCode in nVARCHAR2, unitId out number)

  as

    plantId number;

    productId number;

    serial nvarchar2(20);

    buildGroup number;

    productDate date;

    unitIdentifier nvarchar2(20);

    identity2 nvarchar2(20);

  begin

    -- get latest unit ID

    select max(unit_id) + 1 into unitId from master_unit_info;

    

    -- if there are no units

    if (unitId is null) then

      unitId := 1;

    end if;

      

    -- get default build group

      select build_group_id into buildGroup from build_groups

      where is_default = 1;

      

     -- Get date

     productDate := sysdate;

     

     -- Get default plant

     select plant_id into plantID from plants

      where is_default = 1;

      

     -- Get product id

     select product_id into productID from products

      where code = productCode;

      

     -- Build rotation #

     unitIdentifier := '0000' || to_char(unitID);

     

     -- Create new serial

     serial := '0000' || to_char(unitID);

     

     if (length(unitIdentifier) < 6)

        then identity2 := unitIdentifier;

        else identity2 := substr(unitIdentifier, -6, 6);

    end if;

       

     --Insert Master Unit Info

     insert into master_unit_info

     (unit_id, product_id, plant_id, model_year, serial, identity_1, identity_2, identity_3,

      build_group_id, prod_date, data_source_id)

     values

     (unitId, productID, plantID, extract(YEAR from productDate), serial, substr(unitIdentifier, -4, 4), identity2, to_char(unitID),

      buildGroup, productDate, 5);  

  declare v_nextSeq int;

  select nvl(max(sequence) + 1, 1) into v_nextSeq

    from chk_generation_queue;

  end;

  CREATE      procedure [dbo].[custom_create_new_unit]

     @productCode nvarchar(20),

  --   @productDate datetime,

     @unitID      int output

  as

  begin

     declare @plantID    int,

             @productID  int,

             @serial     nvarchar(20),

             @buildGroup int,

             @productDate datetime,

             @identifier nvarchar(20)

     -- Get default build group

     select @buildGroup = build_group_id

       from build_groups

      where is_default = 1

     -- Get date

     select @productDate = getdate()

     -- Get default plant

     select @plantID = plant_id

       from plants

      where is_default = 1

     -- Get product id

     select @productID = product_id

       from products

      where code = @productCode

     -- Build rotation #

     --select @identifier = '0000' + convert(nvarchar(5), @unitID)

     -- Create new serial

     --exec create_serial @productDate, @unitID, @serial output

  --   select @serial = '0000' + convert(nvarchar(5), @unitID)

     --Insert Master Unit Info

     insert into master_unit_info

        (product_id, plant_id, model_year, serial,

         identity_1, identity_2, identity_3, build_group_id, prod_date, data_source_id)

     values

        (@productID, @plantID, convert(smallint, datepart(yyyy, @productDate)), @serial,

         right(@identifier, 4), right(@identifier, 6), @unitID, @buildGroup, @productDate, 5)

    select @unitID = SCOPE_IDENTITY()

     -- Insert into Checklist Generation Queue

     declare @nextSeq int

   update master_unit_info set serial = '0000' + convert(nvarchar(5), @unitID), identity_1 = '0000' + convert(nvarchar(5), @unitID)

   where unit_id = @unitID

     select @nextSeq = isnull(max(sequence) + 1, 1)

     from chk_generation_queue

     insert into chk_generation_queue

        (unit_id, is_force_create, sequence)

     values

        (@unitID, '1', @nextSeq)

  end

A database lookup has to be created to look for the value:

1.     Select Products > Checklist > Questions > Lookup Configurations.

2.     Click Add.

3.     Select Database Lookup as the Type and Validation as the Purpose, as shown below.

4.     Click OK.

5.     In the Tables List area, scroll to find the UNIT_LOOKUP_CODES table.

6.     Drag the UNIT_LOOKUP table onto the TABLES item located in the Query Tree pane. This will display the list of available fields.

7.     Select the LOOKUP_CODE field and click OK. You are returned to the SQL Builder dialog.

The FILTERS clause is used to determine which unit you want to check – in this example, it is the unit you have on screen and is identified by the [current_unit_id] run-time value.

8.     Right-click on FILTERS and select Add.  

9.     Set the following values, as shown in the image below.

o       Table: UNIT_LOOKUP_CODES

o       Column: UNIT_ID

o       Operator: EQUAL

o       Filter: DYNAMIC, with current_unit_id selected from the list.

10.  Click OK.

11.  In the SQL Builder screen, enter a description for the database lookup and click OK to save.

12.  Click Products > Checklist > Questions > Questions.

13.  Highlight the checklist question and click the Edit button.

14.  Select the Answers tab. Ensure that the static answers match the information you are checking for in the Validation tab.

15.  Select the Validation tab.

16.  Click Add to enter a new validation test.

17.  Select Database Lookup and click OK.

18.  In the Validation tab, select the recently created database lookup from the list and click OK.

19.  You can now run Data Collect to answer the checklist question when it is presented and see how it validates the value entered in the unit lookup table.

20.  The following image shows the example air filter checklist question with the correct option selected:

The following image shows the example air filter checklist question with the incorrect option selected: