0

Sample Tracker - Despatch

Gustavo Torres 3 years ago in Configuration updated 3 years ago 5

Hello!!

When I add a sample, QC_Type Standard and add its type of standard (metadata - sample tracker) when processing the despatch I get an error.

NOTE: It is correct that the GB_SAMPLE enter QC_TYPE Standard or all must be RUTINE

Image 2354



I enclose an image with the steps I take

Image 2351


2.- 

Image 2352



3.-  At the end of the process it indicates an error alert.

Image 2353


thanks you

Hello, Gustavo!

As I can see from the screenshot#2 that in the Sample/Parent/StandardId column the actual Sample ID of the standard is stored. However, for the samples that has QC_TYPE mapped to system QC type STANDARD the STANDARD_ID value instead of the SAMPLE_ID.

So, in your case it tries to insert the value '10020' to ST_DESPATCH_STANDARD.STANDARD_ID column, and as far as there is no STANDARD_ID = '10020' it throws the foreign key error.


To fix the problem the query for Sample List in the Despatch object needs to be updated.

Please open the configuration manager, then navigate User Interface | Sample Tracker | Despatches in the configuration tree.Then open the properties of the despatch object that you have used.

Once opened, click on the Sample List tab and open an editor to edit your SQL query.

Your query should look something like this:

SELECT
   S.SAMPLE_ID AS SAMPLE_TAG,
   S.QC_TYPE,
   S.PROJECT AS PROJECT,
   S.SITE_ID AS SITE_ID,
   CASE WHEN Q.SYSTEM_QC_TYPE = 'STANDARD' THEN S.STANDARD_ID
   ELSE S.SAMPLE_ID END AS SAMPLE_ID

FROM
[DBO].[GB_SAMPLE] AS S
LEFT OUTER JOIN dbo.ST_XS_QC_TYPE Q
   ON S.QC_TYPE = Q.USER_QC_TYPE

WHERE

  <YOUR_WHERE_STATEMENT>

Also, please notice that the STANDARD_ID value for SAMPLE_ID = '10020' is NULL in your database as I can see on the screenshot #1, seems like the STANDARD_ID value is missing.

With best regards

Taras Yaremchenko

Hello Taras!



SELECT
S.SAMPLE_ID AS SAMPLE_TAG,
S.QC_TYPE,
S.PROJECT AS PROJECT,
S.SITE_ID AS SITE_ID,
CASE WHEN Q.SYSTEM_QC_TYPE = 'STANDARD' THEN S.STANDARD_ID
ELSE S.SAMPLE_ID END AS SAMPLE_ID
FROM DBO.GB_SAMPLE AS S
LEFT OUTER JOIN dbo.ST_XS_QC_TYPE Q
ON S.QC_TYPE = Q.USER_QC_TYPE
WHERE S.PROJECT = '?Select a Project?' AND SITE_ID = '?Site_id?'


When I start the process in the despatch it shows me the blank list.

But when I modified it in the settings part if it showed me the table with the information.



Best regards

+1

Hi Gustavo

Can i ask you to send me the result of the following queries:

SELECT * FROM ST_XS_QC_TYPE

SELECT * FROM GB_SYS_TABMAP WHERE SYS_TABLE_NAME = 'GB_SAMPLE'

SELECT * FROM GB_SYS_FLDMAP WHERE SYS_TABLE_NAME = 'GB_SAMPLE'

If you want we can continue the discussion via email. My email is taras@micromine.com.

Regards

Taras

+1

Hi Gustavo

As discussed via email the problem for the blank list of samples returned is that one of the samples in the returned list has NULL value for SAMPLE_ID field. The reason for that is the NULL value saved for STANDARD_ID field for samples with QC_TYPE = 'STANDARD'. To fix the problem need to fill the STANDARD_ID value for the standard samples.


In the future version Geobank 2020 this process has been improved and the relevant validation errors will be shown for the samples.


Regards

Taras