Administration: Smart Quote Attributes

SQL Queries for Dynamic Lists

You can populate attribute dropdowns by querying the Items Catalog. This ensures that the Sales Rep can only select materials or components that actually exist in your inventory.

Example: Retrieving items from a specific category This query fetches all item numbers from the 'Panels' category to populate a dropdown.

<queries>
    <valuesvalid attribute="PANELNUMBER">
        <sql>
            SELECT Number AS PANELNUMBER
            FROM catalog_items
            WHERE
            Category='Panels'
            AND InstanceID=?paramInstanceID
            ORDER BY Number ASC;
        </sql>
    </valuesvalid>
</queries>                

<queries>
    <test>
        <attribute name="GRD">17-4</attribute> <!-- Used for testing the query in the admin UI -->
    </test>
    <valuesvalid attribute="MATCOND">
        <sql>
          SELECT
          JSON_UNQUOTE(JSON_EXTRACT(AttributesJSON, "$.COND")) AS MATCOND
          FROM catalog_items
          WHERE
          InstanceID=?paramInstanceID
          AND Category='Raw Materials'
          AND ?paramGRD=JSON_UNQUOTE(JSON_EXTRACT(AttributesJSON, "$.GRD"))
          GROUP BY MATCOND
          ORDER BY MATCOND
       </sql>
    </valuesvalid>
</queries>

---

Best Practices for Attributes

1. Consistent Naming: Use clear, uppercase names (e.g., WIDTH, MATERIAL_TYPE) to make your Rules easier to read.
2. Use Default Values: Provide sensible defaults for optional selections to speed up the quoting process.
3. Validate via Rules: Use Error Logging to alert users if an attribute combination is technically impossible.
   • Pro Tip: Whenever possible, use Attribute Valid Value sets to filter options before a selection is made, rather than relying on error messages after the fact.