====== Overview ======

This article will help you change the measurement units in Control from US/Imperial to Metric.

First, you must update the Imperial to Metric user constants. Then, you must run a SQL query on your database.

======   ======

====== Change User Constants ======

First, you will need to change your user constants to metric units.

  - Go to the Setup menu in Control
  - Select Pricing Setup > All Pricing Setup
  - On the left side, select User Constants
  - In the next pane, select Imperial to Metric
  - Click Edit, then change the values to match the image below.
  - Click Save.

{{:imperialtometric.jpg?nolink&|imperialtometric.jpg}}

======   ======

====== SQL Query ======

==== Risk of Data Corruption if Run Improperly ====

**High**. Data is modified in this query. Do not run this unless you are an expert in SQL and fully understand the ramifications of performing this action. Before attempting anything be sure to make a full backup and a compressed (.zip) format backup as well. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and non-reversible**.

====   ====

==== SQL ====

<code sql>
-- Inches to MM (linear, square, and cubic) --UPDATE PricingPlanParam SET PricingUnitID = 7 WHERE PricingUnitID = 2 --UPDATE PricingPlanParam SET PricingUnitID = 13 WHERE PricingUnitID = 8 --UPDATE PricingPlanParam SET PricingUnitID = 19 WHERE PricingUnitID = 14
UPDATE Variable SET OutputUnitID = 7, SeqID=SeqID+1  WHERE OutputUnitID = 2
UPDATE Variable SET OutputUnitID = 13, SeqID=SeqID+1  WHERE OutputUnitID = 8
UPDATE Variable SET OutputUnitID = 19, SeqID=SeqID+1  WHERE OutputUnitID = 14
UPDATE Variable SET InputUnitID = 7, SeqID=SeqID+1    WHERE InputUnitID  = 2
UPDATE Variable SET InputUnitID = 13, SeqID=SeqID+1   WHERE InputUnitID  = 8
UPDATE Variable SET InputUnitiD = 19, SeqID=SeqID+1   WHERE InputUnitID  = 14
UPDATE Part SET UnitID = 7, SeqID=SeqID+1   WHERE UnitID = 2
UPDATE Part SET UnitID = 13, SeqID=SeqID+1  WHERE UnitID = 8
UPDATE Part SET UnitID = 19, SeqID=SeqID+1  WHERE UnitID = 14
UPDATE PartInventoryConversion SET UnitID = 7, SeqID=SeqID+1   WHERE UnitID = 2
UPDATE PartInventoryConversion SET UnitID = 13, SeqID=SeqID+1  WHERE UnitID = 8
UPDATE PartInventoryConversion SET UnitID = 19, SeqID=SeqID+1  WHERE UnitID = 14
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 7, SeqID=SeqID+1   WHERE ConsumptionUnitID = 2
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 13, SeqID=SeqID+1  WHERE ConsumptionUnitID = 8
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 19, SeqID=SeqID+1  WHERE ConsumptionUnitID = 14

-- Feet to M --UPDATE PricingPlanParam SET PricingUnits = 5 WHERE PricingUnits = 3 --UPDATE PricingPlanParam SET PricingUnits = 11 WHERE PricingUnits = 9 --UPDATE PricingPlanParam SET PricingUnits = 17 WHERE PricingUnits = 15
UPDATE Variable SET OutputUnitID = 5, SeqID=SeqID+1   WHERE OutputUnitID = 3
UPDATE Variable SET OutputUnitID = 11, SeqID=SeqID+1  WHERE OutputUnitID = 9
UPDATE Variable SET OutputUnitID = 17, SeqID=SeqID+1  WHERE OutputUnitID = 15
UPDATE Variable SET InputUnitID  = 5, SeqID=SeqID+1   WHERE InputUnitID  = 3
UPDATE Variable SET InputUnitID  = 11, SeqID=SeqID+1  WHERE InputUnitID  = 9
UPDATE Variable SET InputUnitID  = 17, SeqID=SeqID+1  WHERE InputUnitID  = 15
UPDATE Part SET UnitID = 5, SeqID=SeqID+1  WHERE UnitID = 3
UPDATE Part SET UnitID = 11, SeqID=SeqID+1  WHERE UnitID = 9
UPDATE Part SET UnitID = 17, SeqID=SeqID+1  WHERE UnitID = 15
UPDATE PartInventoryConversion SET UnitID = 5, SeqID=SeqID+1  WHERE UnitID = 3
UPDATE PartInventoryConversion SET UnitID = 11, SeqID=SeqID+1  WHERE UnitID = 9
UPDATE PartInventoryConversion SET UnitID = 17, SeqID=SeqID+1  WHERE UnitID = 15
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 5, SeqID=SeqID+1  WHERE ConsumptionUnitID = 3
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 11, SeqID=SeqID+1  WHERE ConsumptionUnitID = 9
UPDATE GoodsItemPartLink SET ConsumptionUnitID = 17, SeqID=SeqID+1  WHERE ConsumptionUnitID = 15

--This query looks for all instances of SqFeet and Inches in the PreDiscountFX field within the PricingPlan table. It replaces SqFeet with SqMeters and Inches with Millimeters.
UPDATE PricingPlan SET SeqID=SeqID+1, PreDiscountFX = LEFT( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%SqFeet%', PreDiscountFX)-1) + 'SqMeters ' + SUBSTRING( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%SqFeet%', PreDiscountFX) + 6, 2048 ) WHERE CAST(PreDiscountFX AS VARCHAR(2048)) LIKE '%SqFeet%'
GO
UPDATE PricingPlan SET SeqID=SeqID+1,PreDiscountFX = LEFT( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%SqFeet%', PreDiscountFX)-1) + 'SqMeters ' + SUBSTRING( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%SqFeet%', PreDiscountFX) + 6, 2048 ) WHERE CAST(PreDiscountFX AS VARCHAR(2048)) LIKE '%SqFeet%'
GO
UPDATE PricingPlan SET SeqID=SeqID+1,PreDiscountFX = LEFT( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%Inches%', PreDiscountFX)-1) + 'Millimeters ' + SUBSTRING( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%Inches%', PreDiscountFX) + 6, 2048 ) WHERE CAST(PreDiscountFX AS VARCHAR(2048)) LIKE '%Inches%'
GO
UPDATE PricingPlan SET SeqID=SeqID+1,PreDiscountFX = LEFT( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%Inches%', PreDiscountFX)-1) + 'Millimeters ' + SUBSTRING( CAST(PreDiscountFX AS nvarchar(2048)), PatIndex('%Inches%', PreDiscountFX) + 6, 2048 ) WHERE CAST(PreDiscountFX AS VARCHAR(2048)) LIKE '%Inches%'
</code>

======   ======

====== Additional Changes for v3 Dataset ======

==== Verify Variable Consumption Formulas ====

  - Navigate to Setup > Pricing Setup > All Pricing Setup > Variables.
  - Find your Material variables. For Digital Prints, you are navigating to Standards > Material > Material.
  - Under Default Part Consumption Formula, change Unit from Foot to Meter.
  - Click Save.

{{  :wiki:material_consumption.png  }}

==== Verify Pricing Form Display Components ====

With the metric conversation, some of your pricing form component may need to updated as well. Please make a list of areas that are not updating automatically, and your consultant can make the necessary adjustments inside your system.

==== Update Display Units for Roll Materials ====

=== Risk of Data Corruption if Run Improperly ===

**High**. Data is modified in this query. Do not run this unless you are an expert in SQL and fully understand the ramifications of performing this action. Before attempting anything be sure to make a full backup and a compressed (.zip) format backup as well. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and non-reversible**.

====   ====

==== SQL ====

<code sql>
DECLARE @PartType INT = 0 -- Part Type is Material
        ,@UnitID INT = 54 -- Inventory Unit Type is Roll
        ,@ModifiedDate DATE = '2019-03-06' -- Date range of parts to update. Can be excluded on a new dataset.

--Create Table to store Material Height UDF outside ot Part UDFXML
CREATE TABLE #PartLength
    (PartID INT
    ,MaterialHeight INT)

INSERT INTO #PartLength
    SELECT P.ID AS PartID
    ,UDFTable.MaterialHeight
    FROM Part P WITH(NOLOCK)
    LEFT JOIN

        (
        SELECT
            ID AS PartID,
            -- if returning the text() value, we must tell it the data type
            ThisUDF.value('(Material_Height/text())[1]', 'varchar(15)') AS MaterialHeight,

            -- returns all UDFs as an XML datatype (with no root node in this case)
            ThisUDF.query('(node())') AS VariableNode

        FROM
        (
            SELECT ID,
                    CAST(UDFXML AS XML) AS XMLField
            FROM Part
            WHERE UDFXML IS NOT NULL
        ) Temp

        -- Cross Join this with a row for each ID in the UDF
        CROSS APPLY XMLField.nodes('//UDFs') AS PartUDFs(ThisUDF)
        ) AS UDFTable ON P.ID = UDFTable.PartID

    WHERE P.PartType = @PartType
        AND P.UnitID = @UnitID
        AND UseInvUnitsForDisplay = 1
        AND CAST(P.ModifiedDate AS DATE) = @ModifiedDate

--Update Part Table to Display Unit Values
UPDATE Part
    SET SeqID = SeqID +1
        , DisplayUnitID = 5
        , DisplayUnitText = 'Meter'
        , DisplayUnitType = 3
        , UseInvUnitsForDisplay = 0
        , DisplayConversionFormula = '1/' + CAST((SELECT MaterialHeight FROM #PartLength WHERE PartID = Part.ID) AS VARCHAR(25))
    WHERE PartType = @PartType
        AND UnitID = @UnitID
        AND UseInvUnitsForDisplay = 1
        AND CAST(ModifiedDate AS DATE) = @ModifiedDate
;
--Insert Conversion Formula into PartInventoryConversion Table
INSERT INTO PartInventoryConversion
    SELECT
        (SELECT dbo.csf_chapi_nextid( 12016, 1 )) AS ID
        , -1 AS StoreID
        , 12016 AS ClassTypeID
        , ModifiedByUser AS ModifiedByUser
        , ModifiedByComputer AS ModifiedByComputer
        , ModifiedDate AS ModifiedDate
        , 0 AS SeqID
        , 0 AS IsSystem
        , 1 AS IsActive
        , ID AS PartID
        , 0 AS IsAutomatic
        , 0 AS IsDefault
        , DisplayConversionFormula AS ConversionFormula
        , 1 AS ConsumptionFormula
        , 'Always' AS InclusionFormula
        , 5 AS UnitID
        , 3 AS UnitType
    FROM Part WITH(NOLOCK)
    WHERE
        (SELECT COUNT(ID)
            FROM PartInventoryConversion PIC WITH(NOLOCK)
            WHERE PIC.PartID = Part.ID AND PIC.UnitID = 5) = 0 -- Figure out if the conversion already exist. If 0 returned, insert conversion.
        AND PartType = @PartType
        AND UnitID = @UnitID
        AND CAST(ModifiedDate AS DATE) = @ModifiedDate
;
DROP TABLE #PartLength;

--Declare records that need to be refreshed
DECLARE @PartUpdate RefreshRecordsTableType
;

INSERT INTO @PartUpdate (ID, ClassTypeID)
SELECT ID, ClassTypeID
FROM   Part
WHERE  CAST(ModifiedDate AS DATE) = @ModifiedDate
;

INSERT INTO @PartUpdate (ID, ClassTypeID)
SELECT ID, ClassTypeID
FROM   PartInventoryConversion
WHERE  CAST(ModifiedDate AS DATE) = @ModifiedDate
;

-- Refresh all modified records since users may be logged in.
EXEC sp_RefreshRecords @PartUpdate
;
</code>