Workaround on FieldMap display

Background

Import Datasets under Information Management (aka Data Import tool) is a powerful function in the BMS to import existing studies from Excel.  Most users would have their nurseries and trials in Excel format, thus, this has become a highly sought-after component of the BMS.  Our users who benefit regularly from this process are CAIGE network and Edstar Genetics. 

Excel File format

Edstar uploaded several of their 2019 studies in the format below.  It usually includes Fieldmap Range and Fieldmap Column values to correspond on their map layout.  

2019AWT2-Randomiz - Copy.xlsx


Problem

Users would like to display the existing field map of their studies straightforwardly via Data Import tool.  Since field map isn't created within the BMS, this is not possible at the moment. There are other values required by the BMS when designing maps such as the following Field and Block Details:


Workaround

a) Re-load all the studies and remove the Fieldmap Range and Fieldmap Column values, then, re-create field maps within the BMS. 

Having already existing Fieldmap Range and Column values prevent users to create a field map within BMS since those values have been mapped already via Data Import tool. However, this field map cannot be viewed. 

https://bmspro.io/1798/training/bms-user-manual/make-field-map

b) Store required values directly in the database

This is the preferred solution so users won't have to re-load their studies and remove the Fieldmap Range and Column values already mapped using Data Import tool.


Adding missing entries in the database

  1.   Add unique Fields and Block in location table.  Names of field and block are arbitrary.

600000103 and 600000104 are the succeeding numbers in the location table.

locid

ltype

nllp

lname

labbr

snl3id

snl2id

snl1id

cntryid

lrplce

nnpid

program_uuid

600000103

415

0

Field 1

F1

0

0

0

0

0

0

\N

600000104

416

0

Block 1

B1

0

0

0

0

0

0

\N

2.  Add related entries on locdes table where 600000041 is the LOCATION_ID provided by the user via Data Import tool. 

v12

ldid

locid

dtype

duid

dval

ddate

dref

574

600000103

312

9021

600000041

20190827

0

575

600000104

313

9021

600000103

20190827

0

576

600000104

306

9021

18

20190827

0

577

600000104

307

9021

12

20190827

0

578

600000104

308

9021

1

20190827

0

579

600000104

309

9021

1

20190827

0

580

600000104

310

9021

1

20190827

0


v13 - new column duid_bkp;  Create one set for every instance or environment where locid refers to the Field and Block.


ldidlociddtypeduidduid_bkpdvalddatedref
5746000000893122\N600000081201912130
5756000000923132\N600000089201912130
5766000000923062\N36201912130
5776000000923072\N3201912130
5786000000923082\N1201912130
5796000000923092\N1201912130
5806000000923102\N1201912130
5816000000903122\N600000069201912130
5826000000933132\N600000090201912130
5836000000933062\N18201912130
5846000000933072\N6201912130
5856000000933082\N1201912130
5866000000933092\N1201912130
5876000000933102\N1201912130
5886000000913122\N600000088201912130
5896000000943132\N600000091201912130
5906000000943062\N18201912130
5916000000943072\N6201912130
5926000000943082\N1201912130
5936000000943092\N1201912130
5946000000943102\N1201912130


3.  Add entry in nd_geolocationprop table.

nd_geolocationprop_id

nd_geolocation_id

type_id

value

rank

94

26

8583

600000104

3

Result

Field map taken from Edstar production server having a 12 by 12 rows and ranges.  Maps of their studies can now be displayed in the BMS after storing the required values in the database.

BB1-20190918.xls