<< Click to Display Table of Contents >> Navigation:  SPM > SPM > Tasks > Samples > Use Custom Function in SPM 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.

 

 

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.

 

Other Available Items

 

Other items available for use in the Custom DB Function script:

•prevschema (varchar(40)) – Added in Build 19300. Based off 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.
 
The original design or normal use of prevschema is when the schema for the Sample Code is just “custom DB function” with no other schema items. Thus, the database function itself has the logic of putting together the sample’s sys_sample_code (instead of SPM). SPM receives and applies the returned function value, then on the next sample SPM sends to the function the previously received function value (thus, the name prevschema), which the function uses then sends back to SPM an updated function value for the next sample. To help the function, in addition to the planning bit (see above), SPM resets or sends a null prevschema whenever the task code or location changes which tells the function that the next sample is from a different task code or a different location.

•planning – Added in Build 19300. 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.

 

•prevschema2 (varchar(40)) – Added in Build 7.22.1. The difference between the new prevschema2 and older prevschema is described here:
 
In the older prevschema, SPM will reset or send a null prevschema whenever the task code or location changes which tells the function that the next sample is from a different task code or a different location.
 
The new prevschema2 was added to ignore the task code and location change. Simply, prevschema2 sends to the database function the previously received function value. This prevschema2 is ideal for just generating a sequence number. It can only be used in the Sample Code Schema, not the Sample Name Schema. Unlike the older prevschema, prevschema2 can exist with other schema items, effectively replacing the “sample #” commonly appended at the end. It is important to note, that if other items are specified at the left of custom DB function, then sample_type must be included, else when the QA sample is created, the sample_type will automatically be appended at the end. A Sample Code schema can be – [task_code]-[sys_loc_code]-[sample_type]-custom DB function.