Translation Process

It is not intended to identify a single product. A local implementation may choose to further filter the list of products on factors like local availability (stock), local formulary or cost. The order of products listed may also be locally condfigured to promote or demote products based on licensing status or local needs such as paediatric use.

The translation process requires the use of the NHS Dictionary of Medicines and Devices (dm+d) plus a mapping table for UCUM units of measure that use different terms than dm+d.

For example; gram is a dm+d unit of measure with g is the equivalant within UCUM, and UCUM spells liter differently to the dm+d litre.

Step #1: Get child VMPs of the VTM

VMP’s flagged within dm+d as invalid or where actual products are not available must be ignored.

Where the dose-based instruction specifies a coded Route or coded Form then these are used in the query to return only VMPs for the given route and/or form.

In pseudo-code;

return vmp's
  where parent_vtm = {vtm_id}
    and vmp is valid
      and vmp has actual products available
       and vmp_form = {form_id} (if specified)
        and vmp_route = {route_id}  (if specified)

Step #2: Calculate the required quantity of each VMP to fulfil the requested dose

Take the dose from the doseQuantity or doseRange.low structures. This will be a combination of a quantity and a coded unit of measure. If the unit of measure is using a UCUM unit then the SNOMED code needs to be looked-up from dm+d. This is where the addition mapping table applies.

Each VMP contains strength information for the active ingredients. It would not be expected to use a dose-based prescription for combination drugs (e.g. anything beginning “co-“) nor any VTM where some associated products contained multiple ingredents (e.g. Phosphate). In such cases translation from dose to products is not possible.

The strength of the ingredient may need to be converted into the same units as the requested dose for comparision purposes. For example, if the requested dose is 1gram but the VMP ingredient is expressed as 500mg then it would need to be converted into 0.5gram to calculate that the VMP is half the required strength. Whilst most dosage instructions would be expressed in terms of strength, the same conversion is required for volume (litre, millitre etc.) and length (metre, centimetre, etc.).

With all units of measure expressed in the same scaler terms, the amount of the VMP to fulfil the requested dose can be calculated as;

QUANTITY OF VMP = ( REQUESTED DOSE QUANTITY / VMP INGREDIENT STRENGTH ) / VMP UNIT DOSE FORM STRENGTH (where defined for the VMP)

Worked Example

Requested dose-based instruction = Oxytetracycline with a DOSE of 250 milligram

Note. The VMP Oxytetracycline 250mg tablets has a Unit Dose Form Strength (UDFS) defined of "1 tablet"

Step #3 - Order the list of VMPs in a clinically suitable order

This approach does not include where two or more products of different strengths may be used. For example a dose of 75mg requested and fulfilled by one 50mg product and one 25mg product.

This guidance suggests ordering by least divisibility.

1. Whole products that can fulfil the request are listed above products that may have to be divided.

2. Where the quantity calculated from Step 2 is not divisible by 1, hence the product has to be divided (e.g. 1.5 tablets) then push down the list.

3. Where the quantity calculated from Step 2 is less than 1 (e.g. 0.5 tablets) then push lower in the list.

4. In either case, when a product needs to be divided and if the product has a dose form that is not typically divisable then push even lower in the list.

5. Products containing multiple active ingredents are pushed to the bottom of the list as the translation calculatation is not possible.

Using the above example, the resulting sort order would be;

Data Requirements

dm+d

The dm+d data fields used for this process are;

Together with the FORM, ROUTE and UNIT_OF_MEASURE vocabularies from the dm+d LOOKUP data.

When dm+d data is imported into a relational database, concepts marked as INVALID or VMP concepts flagged as "not actual products available" may be excluded from the import.

Mapping between UCUM and SNOMED/dm+d

The following mapping table needs to be available to the implementing system. It is required to identify the units of measure within the UCUM standard that use different different codes to the dm+d.

For example g is a UCUM code for "gram" and the equivalent within dm+d is gram|258682000 so a mapping is required to associate g with the SNOMED code 258682000. This mapping table may need to be extended within a local implementation depending on which UCUM units are to be expected.

Step 1 - Get child VMPs of the VTM

A suitable SQL query to return child VMPs for a VTM, with optional Route or Form constraints would be as follows. This assumes INVALID concepts and VMPs where no actual products are available has been excluded from the database.

SELECT vmp.vmpid AS vmpId
     , vmp.name AS vmpName
     , vmp.udfs_dose_uomcd AS unitDoseFormStrength
     , vpi.strnt_dnmtr_uomcd AS denominator
  FROM vtm
 INNER JOIN vmp 
    ON ( vtm.vtmid = vmp.vtmid )
 INNER JOIN vmpform 
    ON ( vmp.vmpid = vmpform.vmpid )
 INNER JOIN vmproute 
    ON ( vmp.vmpid = vmproute.vmpid )
 INNER JOIN vpi 
    ON ( vmp.vmpid = vpi.vmpid )
 WHERE vtm.vtmid = {insert_vtm_id}
   AND ( vmpform.formid = IN_form_id OR ISNULL(IN_form_id) )
   AND ( vmproute.routeid = IN_route_id OR ISNULL(IN_route_id) )

Step 2 - Calculate the required quantity of each VMP to fulfil the requested dose

Conversion between scaler units of measure, e.g. gram to milligram

A function is required to convert a VTM ingredient strength into the same units as the requested dose quantity.

For example, if a required dose quantity is 12.5 milligram, but a VMP for that drug is expressed with a strength in micrograms e.g. 500 microgram, then that strength needs to be expressed in milligrams before the mathematical function can be executed.

Thus 500 microgram would be converted into 0.5 milligram.

For example;

///
// Convert 500 micrograms into milligrams
// FNC_CONVERT_UNITS(vpi.strnt_nmrtr_val, vpi.strnt_nmrtr_uomcd, dose_uom_cd);
///

SELECT FNC_CONVERT_UNITS(500, 258685003, 258684004);

// returns `0.5`.

Within the dm+d, units of mass have the greatest range; kilogram, gram, milligram, microgram and nanogram.

Due to this range, the data type used within SQL must be a DECIMAL(30,12).

Conversion is required for the following units of measure.

Function for quantity

A suitable SQL function to calculate the quantity of a given VMP to fulfil the requested dose quantity would be as follows.

FUNCTION FNC_CALC_QTY( doseQty DECIMAL(9,3)
                     , numerator DECIMAL(30,12)
                     , denominator DECIMAL(9,3)
                     , unitDoseFormStrength DECIMAL(9,3)
                     )
RETURNS DECIMAL(30,12)
BEGIN

    IF denominator = 0  
        THEN SET denominator = 1; 
    END IF;
    
    IF unitDoseFormStrength = 0
        THEN RETURN doseQty / ( numerator / denominator );
    END IF;
	
    RETURN ( doseQty / ( numerator / denominator ) ) / unitDoseFormStrength;

END

A description of each variable used in FNC_CALC_QTY is contained below.

Step 3 - Order the list of VMPs in a clinically suitable order

Function for ranking / ordering

Uses the FNC_CALC_QTY function from above then calculates a ranking value which can be used to order the overall SQL query.

FUNCTION FNC_CALC_RANK( doseQty DECIMAL(9,3)
                      , numerator DECIMAL(30,12)
                      , denominator DECIMAL(9,3)
                      , unitDoseFormStrength DECIMAL
                      , formId BIGINT UNSIGNED
                      )
RETURNS SMALLINT(5) UNSIGNED

BEGIN
	DECLARE v_qty DECIMAL(30,12) UNSIGNED;
	DECLARE v_rank SMALLINT UNSIGNED;
	DECLARE v_divisable BIGINT UNSIGNED;

	SET v_qty = FNC_CALC_QTY(doseQty, numerator, denominator, unitDoseFormStrength);

	IF ( v_qty < 1 ) 
            THEN SET v_rank = 3;
	ELSE IF ( ( v_qty % 1 ) != 0 )
            THEN  SET v_rank = 2;
	ELSE 
	   SET v_rank = 1;
	END IF;

	IF ( v_rank != 1 )
            THEN SELECT name
                   FROM lookup
                  WHERE valueset = "NOTDIVISABLE"
                    AND id = formid 
                   INTO v_divisable;
	
            IF ( NOT NULL(v_divisable) ) 
                THEN SET v_rank = 4; 
            END IF;
	END IF;

	RETURN v_rank;
END

Where formid is the dm+d code for the requested dose quantity unit of measure.

The rules for the ranking are best shown in a table.

Identification of dose forms that are typically not divisable

The following dose forms are typically not divisible, however this is not always the case.

For example there are some modified-release tablets with a score along the centre to aid division, but in most cases, modified-release products should not be divided.

The same applies for products as capsules. These represent the more common dose forms used within dm+d concepts. Other non-divisible dose forms may exist but their use would be rare, but this reference table can be extended or customised as required for a local implementation.

Complete Stored Procedure

PROCEDURE PRC_VTM_TO_VMP( IN IN_vtm_id BIGINT UNSIGNED
                        , IN IN_dose_qty DECIMAL
                        , IN IN_dose_uom_cd BIGINT UNSIGNED
                        , IN IN_form_id BIGINT UNSIGNED
                        , IN IN_route_id BIGINT UNSIGNED
                        )
BEGIN
	SELECT DISTINCT vmp.vmpid
        , vmp.name
        , FNC_CALC_QTY( IN_dose_qty
                      , FNC_CONVERT_UNITS( vpi.strnt_nmrtr_val
                                         , vpi.strnt_nmrtr_uomcd
                                         , IN_dose_uom_cd
                                         )
                      , vpi.strnt_dnmtr_val,vmp.udfs
                      ) AS qty
        , vmp.udfs_dose_uomcd
        , vpi.strnt_dnmtr_uomcd
        , FNC_CALC_RNAK( IN_dose_qty
                       , FNC_CONVERT_UNITS( vpi.strnt_nmrtr_val
                                          , vpi.strnt_nmrtr_uomcd
                                          , IN_dose_uom_cd
                                          )
                       , vpi.strnt_dnmtr_val
                       , vmp.udfs,vmpform.formid
                       ) AS rnk
	  FROM vtm 
	 INNER JOIN vmp 
       ON ( vtm.vtmid = vmp.vtmid )
	 INNER JOIN vmpform
       ON ( vmp.vmpid = vmpform.vmpid )
	 INNER JOIN vmproute
       ON ( vmp.vmpid = vmproute.vmpid )
	 INNER JOIN vpi
       ON ( vmp.vmpid = vpi.vmpid )
	 INNER JOIN lookup
       ON ( vpi.strnt_nmrtr_uomcd = lookup.id )
	 WHERE vtm.vtmid = IN_vtm_id
	   AND ( vmpform.formid = IN_form_id OR ISNULL(IN_form_id) )
	   AND ( vmproute.routeid = IN_route_id OR ISNULL(IN_route_id) )
	 ORDER BY rnk ASC
        , qty ASC;
END

Products (VMPs) where the VPI strength is expressed as an inaccurate decimal value

The vast majority of VMPs are defined with a Virtual Product Ingredient (VPI) strength as a whole number, e.g. numerator = 5 (mg) and denominator = 1 (ml). A small percentage of VMPs are defined with a numerator expressed as an incurate decimal value.

Two examples are;

1. Oxybutynon 3mg/15ml bladder irrigation vials, VPI strength = 333.33 micrograms / 1 ml

2. Methotrexate 25mg/3ml solution for injection pre-filled syringes, VPI strength = 8.333 mg / 1 ml

This inaccuracy of values like 333.33 or 8.333 not being mathmetically the same as one third of a milligram means the mathematics used in these calcations does not result in a whole number.

For example;

• VMP = Oxybutynon 3mg/15ml bladder irrigation vials
• Requested Dose = 1 milligram
• Calculated Quantity = 3.003003 vials

and

• VMP = Methotrexate 25mg/3ml solution for injection pre-filled syringes
• Requested Dose = 25 milligram
• Calculated Quantity = 1.041667 pre-filled disposable injection

It would be unwise to add bespoke logic to round up to the nearest whole number in such cases as this would require an assumption that this is the intention of the prescriber.

A possible solution that will be discussed with the owners of the NHS dm+d would be to expressed the strength in a way that uses absolute values.

For example;

1. Oxybutynon 3mg/15ml bladder irrigation vials, VPI strength = 1 mg / 3 ml
2. Methotrexate 25mg/3ml solution for injection pre-filled syringes, VPI strength = 25 mg / 3 ml