---
title: "Using UDFs in Query"
canonical: "https://kb.myframeworks.com.au/space/FRAM/28397972/Using%20UDFs%20in%20Query"
format: markdown
---
# Overview

UDF’s are programs written by Sterland developers that can be called from within the query tool passing in the necessary parameters. The program returns the resultant data to the query tool for display and use.

UDF’s are generally used where complex calculations are involved or certain business rules must be matched between the systems.

There are several UDF’s that can be utilised in query reporting.

# How to use a UDF

1. Create a calculated field of the appropriate data type that you want to be returned.
2. Select the ‘Other’ formula type section and select the UDF function.
3. Amend the syntax of the UDF according to the below functions that are available.

> ⚠️ **Note.** All input parameters for a UDF must be from the same grid level in your query as the UDF formula field. In essence, if the UDF formula field is in the second grid, you cannot input the Product ID from the main grid.

## Product & Stock Related UDF’s

**Function:** u_build_product_group

**Purpose: **Returns the fully qualified product group and subgroup string. I.e. concatenates all four fields of the product group and subgroups with ‘/’ as a separator where applicable

**Input Parameters: Product Group, Subgrp1, SubGrp2, SubGrp3**

**Output: Character**

**Example: **UDF("u_build_product_group",`stix.prod.id_grp_prod`,`stix.prod.id_sub_grp_1_prod`,`stix.prod.id_sub_grp_2_prod`,`stix.prod.id_sub_grp_3_prod`)

** **

**Function: **u_convert_uom

**Purpose:  **Returns a qty or price converted from one uom/gtin to a different uom/gtin. Used where a transaction reflects a qty or price in a different uom to that of the base product and you want to convert it to the base uom.

**Input Parameters:  **From GTIN, To GTIN, From UOM, To UOM, Value to Convert, Product Id, Length, Width, Depth, No of Decimals to round to, Price? True/False

**Output: **Decimal Price or Qty

**Example: **UDF("u_convert_uom","","",`stix.trndet.id_uom`,`stix.prod.id_uom[1]`,`stix.trndet.qty_tran,`stix.trndet.id_prod`,`stix.prod.length_prod`,`stix.prod.width_prod`,`stix.prod.depth_prod`,4,"False")

** **

**Function:** u_get_reorder

**Purpose:  **Return the reorder cycle code for the given product and branch. This could be sourced from the branch product record or from the default reorder values table.

**Input Parameters: Product Id, Branch Id**

**Output: **Character** **

**Example: **UDF("u_get_reorder",`stix.branch_prod.id_prod`,`stix.branch_prod.id_branch`)

** **

**Function:** u_get_prod_daily_avg_sale

**Purpose:  **Return the daily average sales qty for the given product and branch. Based on sales within the specified date range and optionally including/excluding promotional sales.

**Input Parameters: **Product Id, Branch Id, Start Date, End Date, Include Promos Y/N

**Output: **Decimal Qty

**Example: **UDF("u_get_prod_daily_avg_sale",`stix.branch_prod.id_prod`,`stix.branch_prod.id_branch`,{FromDate},{ToDate},"No")


**Function:** u_get_soh

**Purpose:  **Return the stock on hand qty for the given product and branch. If branch = 0, returns total soh across all branches.

**Input Parameters: **Product Id, Branch Id

**Output: **Decimal Qty

**Example: **UDF("u_get_soh",`stix.branch_prod.id_prod`,`stix.branch_prod.id_branch`)


**Function:** u_get_12_monthly_sales_qty

**Purpose:  **Returns a string of the 12 monthly sales qtys for the specified product and branch combination. 12 months up to and including the date specified.

Branch 0 can be specified to retrieve sales qtys for all branches.

**Input Parameters: **Product Id, Branch Id, Date

**Output: **String of sales qty's strung together with a pipe delimiter eg "10|20|30|40|50|60|70|80|90|100|110|120"

**Example: **UDF("u_get_12_monthly_sales_qty",`stix.branch_prod.id_prod`,`stix.branch_prod.id_branch`,{Date})


**Function:** u_get_prod_min_gp

**Purpose:  **Returns the minimum gp% setting for the specified product and branch combination

**Input Parameters: **Product Id, Branch Id

**Output: **String of gp%

**Example: **UDF("u_get_prod_min_gp",`stix.branch_prod.id_prod`,`stix.branch_prod.id_branch`)


**Function:** u_is_prod_on_contract

**Purpose:  **Returns a logical yes/no to indicate whether the specified product is on a contract for the customer and date combination

**Input Parameters: ** Customer Id, Product Id, Date

**Output: Yes/No**

**Example: **UDF("u_is_prod_on_contract",`stix.cust.id_cust`,`stix.branch_prod.id_prod`,{Date},`stix.branch.id_company`)


**Function:** u_get_prod_aged_stk_bal

**Purpose:  **Returns a string of 8 aged stock values for the specified product and branch combination

**Input Parameters: ** Customer Id, Product Id, Date

**Output: **String of product code and stock values strung together with a pipe delimiter eg "prodcode|5|2|9|0|5|12|0|0"

Aged buckets are as follows; 0-3 months, 4-6 months, 7-9 months, 10-12 months, 13-18 months, 19-24 months, 25-36 months, >36 months.

**Example: **UDF("u_iget_prod_aged_stk_bal",`stix.branch_prod.id_branch`,`stix.branch_prod.id_prod`)


**Function: **u_get_prod_stock_by_length

**Purpose: **Returns an<span style="color: #172b4d"> emulation of the stock by length screen, as on the product dashboard</span>

**Input Parameters:** id_company, id_branch, id_prod, prod_length

**Output:** ** **String of <span style="color: #172b4d">stock by lengths</span> strung together with a pipe delimiter eg: qty_on_hand | qty_sales_orders | qty_unalloc | qty_purch_orders | qty_avail

**Example: **UDF("u_get_prod_stock_by_length",`stix.branch.id_company`,`stix.branch_prod.id_branch`,`stix.branch_prod.id_prod`,`stix.prod_length.length_prod`)

** **

---

## <span style="color: #003366">Pricing UDF’s</span>

**Function:** u_get_discount_price

**Purpose:** Returns the discounted price for the given product, price and discount group combination. Either pass in a hardcoded discount group or variable. Useful for comparing multiple discount group pricing columns.

**Input Parameters: **Product Id, Price to Convert, Discount group ID

**Output: **Decimal Price

**Example: **UDF("u_get_discount_price",`stix.prod.id_prod`,`stix.prod.sell_unit_std`,"BLD1")


**Function:** u_get_cust_discounted_prc

**Purpose:** Returns the discounted price for the given product, customer and branch combination.

**Input Parameters: **Product Id, Customer ID, Branch No

**Output: **Decimal Price

**Example: **UDF("u_get_cust_discounted_prc",`stix.prod.id_prod`,`stix.cust.id_cust`,"1")


**Function:** u_get_price_gst

**Purpose:  **Return the best GST inc price for the given product, customer and branch combination. Useful for producing a price list.

**Input Parameters: **Product id, Customer No, Branch no

**Output: **Decimal Price

**Example: **UDF("u_get_price_gst",`stix.prod.id_prod`,{customer},{branch})

** **

**Function:** u_get_price_gstex

**Purpose:  **Return the best GST exc price for the given product, customer and branch combination. Useful for producing a price list.

**Input Parameters: **Product id, Customer No, Branch no

**Output: **Decimal Price

**Example: **UDF("u_get_price_gstex",`stix.prod.id_prod`,{customer},{branch})

** **

**Function:** u_get_pricing_method

**Purpose:  **Return the pricing method used to provide the best price for the given product, customer and branch combination.

**Input Parameters: **Product id, Customer No, Branch no

**Output: **Character

**Example: **UDF("u_get_pricing_method",`stix.prod.id_prod`,{customer},{branch})

** **

**Function:** u_get_list_price_method

**Purpose:  **Return the pricing method and prices for the given product, customer and branch combination.

**Input Parameters: Product Id, Customer No, Branch Id, Exclude Promos Y/N**

**Output: **Prices (ex & inc) and pricing method in a pipe delimited string, eg "10.00,11.00,M"

**Example: **UDF("u_get_list_price_method",`stix.prod.id_prod`,{customer},{branch},"Yes")

** **

**Function:** u_get_price_gst_by_date

**Purpose:  **Return the best GST inc price for the given product, customer and branch combination on the specified date. Useful for checking what the best price should have been on a transaction or for producing a future dated price list.

**Input Parameters: **Product id, Customer No, Branch no, Date

**Output: **Decimal Price

**Example: **UDF("u_get_price_gst_by_date",`stix.prod.id_prod`,{customer},{branch},{Date})

** **

**Function:** u_get_price_gstex_by_date

**Purpose:  **Return the best GST exc price for the given product, customer and branch combination on the specified date. Useful for checking what the best price should have been on a transaction or for producing a future dated price list.

**Input Parameters: **Product id, Customer No, Branch no, Date

**Output: **Decimal Price

**Example: **UDF("u_get_price_gstex_by_date",`stix.prod.id_prod`,{customer},{branch},{Date})


**Function:** u_get_next_review_date

**Purpose:  **Return the next contract review date based on the contract start date and review frequency

**Input Parameters: **Contract Start Date, Contract Review Frequency

**Output: **Date

**Example: **UDF("u_get_next_review_date",`stix.contract.date_effective_from`,`stix.contract.dur_mths_review_freq`)

** **

---

## <span style="color: #003366">KPI UDF’s</span>

**Function:** u_get_kpi_last_month

**Purpose: **Returns a value from the kpi tables for the specified record, comparing the specified month to the same data for the previous month. Ie stock value this month compared to last month

**Input Parameters: **Company no, Branch no, Date (monthEnd), KPI Data Type, Sequence no, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value/Qty

**Example: **UDF("u_get_kpi_last_month",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run`,`stix.kpi_data.code_data_type`,`stix.kpi_data.seq_data`,"A")

** **

**Function:** u_get_kpi_12month_total

**Purpose: **Returns the total value for the past 12 months from the kpi tables for the specified record type. Ie total stock or sales value.

**Input Parameters: **Company no, Branch no, Date To (monthEnd), KPI Data Type, Sequence no, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value/Qty

**Example: **UDF("u_get_kpi_12month_total",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run`,`stix.kpi_data.code_data_type`,`stix.kpi_data.seq_data`,"A")

** **

**Function:** u_get_kpi_12month_avg

**Purpose: **Returns the monthly average for the past 12 months from the kpi tables for the specified record type. Ie average stock or debtors value.

**Input Parameters:** Company no, Branch no, Date To (monthEnd), KPI Data Type, Sequence no, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value/Qty

**Example: **UDF("u_get_kpi_12month_avg",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run`,`stix.kpi_data.code_data_type`,`stix.kpi_data.seq_data`,"A")

** **

**Function:** u_get_kpi_last_month_by_group

**Purpose: **Returns a value from the kpi tables for the specified product group and data type combination comparing the specified month to the same data for the previous month. Ie stock value this month compared to last month

**Input Parameters: **Company no, Branch no, Date (monthEnd), KPI Data Type, Product group, SubGrp, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value/Qty

**Example: **UDF("u_get_kpi_last_month_by_group",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run` ,`stix.kpi_data.code_data_type` ,`stix.kpi_data.id_grp_prod`,`stix.kpi_data.sparex1`,"A" )

** **

**Function:** u_get_kpi_12month_total_by_group

**Purpose: **Returns the total value for the past 12 months from the kpi tables for the specified record type. Ie total sales or cogs value

**Input Parameters: **Company no, Branch no, Date To (monthEnd), KPI Data Type, Product group, SubGrp, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value

**Example: **UDF("u_get_kpi_12month_total_by_group",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run` ,`stix.kpi_data.code_data_type` ,`stix.kpi_data.id_grp_prod`,`stix.kpi_data.sparex1`,"A" )

** **

**Function:** u_get_kpi_12month_avg_by_group

**Purpose: **Returns the monthly average value for the past 12 months from the kpi tables for the specified record type. Ie average stock value or average sales

**Input Parameters: **Company no, Branch no, Date To (monthEnd), KPI Data Type, Product group, SubGrp, Data type A/B/F (Actual/Budget/Forecast)

**Output: **Decimal Value

**Example: **UDF("u_get_kpi_12month_avg_by_group",`stix.kpi_data.id_company`,`stix.kpi_data.id_branch`,`stix.kpi_data.date_kpi_run` ,`stix.kpi_data.code_data_type` ,`stix.kpi_data.id_grp_prod`,`stix.kpi_data.sparex1` ,"A" )

** **

---

## <span style="color: #003366">GL UDF’s</span>

**Function:** u_get_gl_period_start

**Purpose:  **Return the start date of a gl period.

**Input Parameters:**

**Output: **Date ie 01102016

**Example: **UDF("u_get_gl_period_start",`stix.company.id_company`,"1016")

** **

**Function:** u_get_gl_period_end

**Purpose:  **Return the end date of a gl period.

**Input Parameters: **Company No, GL Period** **(mmyy)

**Output: **Date ie 31102016

**Example: **UDF("u_get_gl_period_end",`stix.company.id_company`,"1016")


**Function:** u_get_ytd_actual

**Purpose:  **Return the YTD actual balance for a GL account for the specified account, year and period.

**Input Parameters: **Company No, Account No, Fin Yr no, Period No

**Output: ** Decimal ie 2400.00

**Example: **UDF("u_get_ytd_actual",`stix.company.id_company`,`stix.acct.id_glacc`, "19", "07")


**Function:** u_get_ytd_budget

**Purpose:  **Return the YTD budget balance for a GL account for the specified account, year and period.

**Input Parameters: **Company No, Account No, Fin Yr no, Period No

**Output: ** Decimal ie 2400.00

**Example: **UDF("u_get_ytd_actual",`stix.company.id_company`,`stix.acct.id_glacc`, "19", "07")