Create Unit

Description

To use this form the station property On commit must be set to Prompt. For further information see here.

The Create Unit form allows you to add a unit to the system based on information that you enter or select. This could include details such as the serial number, color or build group.

The form can include checkboxes, combo boxes, list boxes that are populated with data from the database. You can then make a selection from that list.

Alternatively, or in addition, it can contain a text box where you can enter a customized value.

For a complete tutorial on how to populate this control see here.

Example

How to Set Up the Form

1. Create Stored Procedure for Unit Creation

In order to insert a new unit in the database you will need to use a stored procedure. As with all customised stored procedures it must start with the prefix _custom. For example custom_create_new_unit.

The stored procedure will be different for every plant but you may be able to save time by starting with the example provided here.

Please ensure the correct spelling and case is used when specifying the @retMsg and @unitID parameters.

2. Create the Create Unit Form

The Create Unit form sends a value to the custom_create_new_unit stored procedure which then creates a new unit in the database. It also sets the new unit as the current unit so that it can be viewed in the Main screen.

3. Add Generic Controls to Form

In order to know what type of unit needs to be created at least one value must be entered or selected in the form. These values are entered, manually or automatically, using Generic controls.

If you want the operator to select from a list populated from existing database values you must use the Checkbox, Combobox or Listbox control. If you want the operator to enter a value manually you must use the TextEdit control.

The output of these controls will be passed to the Create Unit form which will use them to create a unit using the stored procedure.

For more information on populating generic controls see here.

4. Link Form to Stored Procedure

You now have everything you need to enable the stored procedure to create a new unit in the database.

Select the form.

In the Properties pane click the edit button () next to OnAcceptStoredProcedure.

Select the required stored procedure from the drop-down list.

The Parameters pane lists the parameters that are required by and returned by the stored procedure. If Is Output is enabled for a parameter then that parameter will be returned to the Main form. In this instance this will usually be the Unit ID. This means that the newly created unit will be displayed to the operator.

Select the first parameter that has Is Output disabled. The Properties tab updates.

Select the Parameter Type. This can be one of the following:

Value: A static value that will always be used by the stored procedure. This requires a number type and the value that will be used.

Component: Uses the output (Value Member) from one of the controls in the form. This is selected from the Component drop-down list which gives the names of the available controls.

Variable: Contains global variables from the database. This includes any variable relating to the unit, the station or the user. This requires the Variable Path to be specified.

Enter the details required for the parameter type.

Repeat for all of the required input parameters.

Select each of the input parameters and ensure that the Type property (e.g. System.Int32) is correctly set to match the Type return from the stored procedure parameters:

@retmsg should be set to Parameter Type Value and Type to System.String

@unitID should be set to Parameter Type Value and Type to System.Int32

Click OK.

The form is now ready to use.

Using the Form

The Create Unit form is accessed from the Main screen by clicking on the Commit button on the Go to unit control.

The form will open and you can enter the required details.

When you click OK the unit will be created and selected at the station.

To use this form the station property On commit must be set to Prompt. For further information see here.

Available Controls

Form Specific

Control	Used to perform this action
Generic Checkbox	Enable or disable option.
Generic Combobox	Choose option from a drop-down list.
Generic Listbox	Choose from list of options.
Generic TextEdit	Manually enter value. Can also be populated automatically.e.g. using a barcode scanner.
OK Button	Use selected root cause.
Cancel Button	Cancel root cause selection.

 

Stored Procedure Example - Unit Creation

create procedure [dbo].[custom_create_new_unit_example]

   @serial      nvarchar(20),         

   @productCode nvarchar(20), 

   @retMsg      nvarchar(1000) output, -- Pay attention to the exact spelling and casing!

   @unitID      int output -- Pay attention to the exact spelling and casing!

as

 

begin

   set nocount on

 

   declare

      @plantID    int,

      @productID  int,

      @buildGroup int,

      @modelyear  smallint,

      @prodDate   date

  

   set @unitID = -1

 

   if (@productCode is null)

   begin

      return

   end

 

   if (@serial is null)

   begin

      return

   end

 

   -- Get current date and use this for production date

   select @prodDate = getdate()

 

   -- Get default build group

   select @buildGroup = build_group_id

   from build_groups

   where is_default = 1

 

   if (@@rowcount =0)

   begin

     set @retMsg ='No default build group exists!'

 

        return

   end

 

   -- Get default plant

   select @plantID = plant_id

   from plants

   where is_default = 1

 

   if (@@rowcount =0)

   begin

     set @retMsg ='No default plant exists!'

 

     return

   end

 

   -- Get current model year

   select @modelyear = convert(smallint, datepart(yyyy, @prodDate))

 

   -- Get product_id

   select @productID = product_id

   from products

   where code = @productCode

 

   if (@@rowcount =0)

   begin

      set @retMsg ='Product could not be found, check that it exists in Cockpit!'

 

      return

   end

 

   -- Try to find unit

   select top 1 @unitID =unit_id

   from units

   where serial = @serial

   and model_year =@modelyear

   and product_id =@productID

   order by unit_id

 

   -- Quit if unit already exists

   if (@@rowcount = 1)

   begin

      return

   end

 

   begin transaction

      -- Insert Unit

      insert into units

         (product_id,plant_id, model_year,serial, build_group_id, prod_date,num_defects, num_unrepaired_defects,num_repaired_defects,num_confirmed_defects, is_locked)

      values

         (@productID,@plantID, @modelyear,@serial, @buildGroup, @prodDate,0, 0,0, 0,0)

 

      -- Get unit_id for just created unit

      set @unitID = scope_identity()

 

      -- Insert into Checklist Generation Queue, this is not needed when running this stored procedure from a create new unit form, however it is needed when you use comm service to create new units to force checklist creation for the new units.

      --insert into chk_generation_queue (unit_id, is_force_create)

      --values (@unitID, 1)

     

      --Example of how to store build data in unit_lookup_codes. The unit_lookup_code has to be created in Cockpit first.

      --insert into unit_lookup_codes (unit_id, lookup_type_id, lookup_code) values (@unitID, 1, 'TestCode')

     

      -- Example of how to store unit colors. The unit color has to be created in Cockpit first.

      --insert into unit_colors (unit_id, color_type_id, color_id) values (@unitID, 1, 1)

 

   commit

end

go

Please ensure the correct spelling and case is used when specifying the @retMsg and @unitID parameters.

Can we improve this topic?