productChemicals.cfc

/**
 * @persistent
 */
component schema="LABEL" table="PROD_CHEM" readonly="true"
{
    property name="Product" fkcolumn="PRODNO" cfc="Products" fieldtype="id,many-to-one";
    property name="Chemical" fkcolumn="CHEM_CODE" cfc="Chemicals" fieldtype="id,many-to-one";
    property name="Percent" column="PRODCHEM_PCT";
}

products.cfc

/**
 * @persistent
 */
component schema="LABEL" table="PRODUCT" readonly="true"
{
    property name="Code" column="PRODNO" fieldtype="id" ormtype="int";
    property name="Description" formula="PRODUCT_NAME || ' (' || REGEXP_REPLACE(show_regno, '\s', '') || ')'";
    property name="ShortDescription" column="PRODUCT_NAME";
    property name="RegistrationNumber" formula="REGEXP_REPLACE(show_regno, '\s', '')";
    
    // Pesticide Type Classifications
    property name="Types" fieldtype="many-to-many" cfc="PesticideTypes" linktable="PROD_TYPE_PESTICIDE" fkcolumn="PRODNO" inversejoincolumn="TYPEPEST_CD" readonly="true" orderby="TYPEPEST_CAT";
    
    // Pesticide Chemicals
    property name="Chemicals" type="array" fieldtype="one-to-many" fkcolumn="PRODNO" cfc="ProductChemicals" orderby="PRODCHEM_PCT desc";
}