Use Custom Function in Sample Naming Schema

<< Click to Display Table of Contents >>

Use Custom Function in Sample Naming Schema

This feature allows a custom database table-valued function to be used in the building of the Sample ID. The Sample ID can then vary depending on other items such as sample type, matrix code, etc.

 

When the Custom DB Function is used in the Sample Naming Schema, SPM will call this function for each created sample, pass parameters to the rest of the Sample ID function, and generate a Sample ID based on the configured items.

 

Edit the Custom DB Function

 

1.Modify the spm5.CUSTOM_SAMPLE_SCHEMA function found under SSMS / [your db] / Programmability / Functions / Table-value Functions to meet the design of the desired Sample ID. A knowledge of SQL Server Management Studio is recommended.

 

The following items may be used in the custom function:

facility_id

sys_loc_code

sample_type

sample_date

matrix_code

task_code

start_depth

end_depth

depth_unit

sampler

 

The image below is an example of a Custom DB Function in SQL Server Management Studio.

 

SPM-CustomSampleSchema

 

2.The Custom DB Function needs to be added to the Sample Naming Schema. Open a Planned Task and select the Samples Tab. For the Sample Code, select the Edit button. In the Sample Naming Schema window, add Custom DB Function to the Selected Items. It can be the only item, or one of several items, as shown in the second image below. The Custom DB Function will then be available as a drop-down menu item.

SPM-Custom_DB_Function

SPM-SampleNamingSchema

 

Other Available Items

 

As of Build 19300, two other items were made available to be used in the Custom DB Function script:

prevschema (varchar(40)) – The previously set SYS_SAMPLE_CODE. This value can be used to create a new or next SYS_SAMPLE_CODE sent to SPM by the Custom DB Function script. For example, if the script uses a sequence numbering scheme, the prevschema value can be used to determine what was the last sequence number.

planning – This is a bit value of 1 or 0, where 1 means the planned SYS_SAMPLE_CODE is being set and 0 means the scheduled SYS_SAMPLE_CODE is being set. Note that when the Custom DB Function is used in the Sample Code Schema, the same function is used to create both the sample's planned SYS_SAMPLE_CODE and scheduled SYS_SAMPLE_CODE. If a different schema or SYS_SAMPLE_CODE is needed between planning and scheduling, then the script needs the planning bit to send back the proper SYS_SAMPLE_CODE.

 

 

Note: Existing Custom DB Function scripts need not be modified to include the two new additional items. The current script will function properly even without the two new items. New scripts can be written using only a subset of the 12 items (10 previous items and 2 new items).