How to Use UDF Calculations

UDF (User Defined Functions) are advanced calculations which replace calculations that used to start with "SQLX...". In V8 the SQLX calculations would not calculate with any blank fields in the sample, so when a calculation is two level then it would not calculate at all.  UDF code is in the SQL database of the site where it's being used.  You can find the code in: Programmability -> Functions -> Scalar-valued Functions. They all start with "dbo.SF_XXXXXXXX". A UDF must exist in the DB before being used in LIMS.  This tutorial will cover the various UDF's that exist in the West Haven LIMS system today and how to use those UDFs.

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox
WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox
WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox
  1. UDF are in the same text box where SQLX and simple formulas are.  Click on the [...] to open the dialog for Calculations
  2. Enter UDF function into this field
  3. Click Checkbox to close the dialog box
  4. Click save button
    1. When making a change on an approved method save button in the Method Parameters pane will be disabled, so you must save the calculation by pressing the Save button on the method manager's main tool box panel
    2. When making a change to an unapproved method  save button in the Method Parameters pane can be used

2. Get Raw Data UDF

This is the most popular application of UDF.  A finished product requires results from the raw material that was used in the manufacturing of it.  One of the results parameters must contain the lot number of raw material product.

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox

UDF(SF_GetRawData_BK({SAMPLEPARAM_ID}, 'Raw Lot Number', 'MSA-QC', '805725'))

 

  1. First part of the formula stays unchanged (black font text above is unchanged)
  2. Next string contains the name of the parameter that has the Lot number of the raw material it its result field (red font text above)
  3. Next string contains the name of the parameter on the raw material which result you need to bring to the finish good as the result of this parameter. (Green font text above)
  4. Last part of the string hold the bulk code of the raw material (blue font text)
  5. Close out the formula by two parenthesis
  6. Press Checkbox to close this dialog and save the changes

All strings must be surrounded by single quotes and separated by a comma.

Parameter names must be exact, thus copying and pasting is the best practice.  When parameter name contains an extra space the input into the formula must have an extra space as well.

3. Filter Usage Data

This formula calculates the difference in days between two date fields.  One of the date fields is the "Manufacturing Date" directly from the sample and the other date field is a parameter usually called "Installation Date" or "Filter Installation Date".  The only variable that changes here is the name of the parameter that has date of when the filter was installed

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox

UDF(SF_FilterUsage({SAMPLE_ID},'Filter Installation Date'))

  1. First part of the formula stays unchanged (black font text above is unchanged)
  2. Next string contains the name of the parameter that has the Filter Installation Date. (Red Font)
  3. Close out the formula by two parenthesis
  4. Press Checkbox to close this dialog and save the changes

All strings must be surrounded by single quotes and separated by a comma.

Parameter names must be exact, thus copying and pasting is the best practice.  When parameter name contains an extra space the input into the formula must have an extra space as well.

4. Lot Number Coding (With LIMS Sample ID)

For items that require Lot number of raw materials to show up on the COA we can code the lot numbers and the CoA will show the LIMS Sample ID instead of the Lot.  This formula has one input which is the name of the parameter that has the lot number.

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox

UDF(SF_GetSample_ID({SAMPLE_ID},'4-VINYLPYRIDINE Lot #'))

  1. First part of the formula stays unchanged (black font text above is unchanged)
  2. Next string contains the name of the parameter that contains the lot number of the raw material. (Red Font)
  3. Close out the formula by two parenthesis
  4. Press Checkbox to close this dialog and save the changes

All strings must be surrounded by single quotes and separated by a comma.

Parameter names must be exact, thus copying and pasting is the best practice.  When parameter name contains an extra space the input into the formula must have an extra space as well.

5. Date Difference Calculation

This UDF calculates the difference in days between two date fields which are results of parameters on a sample.

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox

UDF(SF_DateDifference({SAMPLE_ID}, 'Raw Tin Lot 1 Refining Date (mm/dd/yyyy)', 'RM Alpha Emission EntOn Date Lot 1'))

  1. First part of the formula stays unchanged (black font text above is unchanged)
  2. Next string contains the name of the parameter that contains one of the dates to be used in the calculation (Red font text)
  3. Next string contains the name of the parameter that contains the other date to be used in the calculation (Blue font text)
  4. Close out the formula by two parenthesis
  5. Press Checkbox to close this dialog and save the changes

All strings must be surrounded by single quotes and separated by a comma.

Parameter names must be exact, thus copying and pasting is the best practice.  When parameter name contains an extra space the input into the formula must have an extra space as well.

6. Raw Material ENT_ON Date

This UDF is used for one product manufactured in West Haven: MICROFAB SN002; Bulk Code: 424439. This calculation returns back the Entered On date for a parameter on a raw material.

WinLIMS - manage Methods (Home>Static Data) - Mozilla Firefox

UDF(SF_Param_ENT_ON({SAMPLEPARAM_ID}, 'Raw Tin Lot 1 - Lot Number 1', 'Tin90 (Alpha Emission)', '806207'))

  1. First part of the formula stays unchanged (black font text above is unchanged)
  2. Next string contains the name of the parameter that contains the lot number of the raw material. (Red font text)
  3. Next string contains the name of the parameter on the raw that you need to return ENT_ON date for (Green font text)
  4. Last part of the string hold the bulk code of the raw material (blue font text)
  5. Close out the formula by two parenthesis
  6. Press Checkbox to close this dialog and save the changes

All strings must be surrounded by single quotes and separated by a comma.

Parameter names must be exact, thus copying and pasting is the best practice.  When parameter name contains an extra space the input into the formula must have an extra space as well.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.