Use Custom Function in Sample Naming Schema
Copyright © 2020 EarthSoft, Inc • Modified: 24 Feb 2020
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.
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:
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.
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).