joshua-r2
10/17/2017 - 5:32 PM

Data Loader - Mapping a Value From a Query

How to look up a value based on some inputs in a query and return a key (which might not otherwise be available in the source spreadsheet).

For Example, if we wanted to load Checkpoints in Operator Rounds, when we load the checkpoints, we would have to find a way to look up Equipment Entity keys, because the user likely entered an SAP number or Equipment Technical Number. In this case, we would make a query to return an Entity Key based on SAP Number and Filter Code. That is what the below example demonstrates

*Note this is the Code In a Data Load Interface's Data Load Mapping's Mapping Code field.

ReferencedQuery
content_copyfile_download
SELECT [MI_ROUTE000].[MI_ROUTE000_ROUTE_ID_C] "Route Id", [MI_ROUTE000].[MC_FILTER_CODE_C] "Filter Code", [MI_ROUTE000].ENTY_KEY "ROUTE_KEY"
FROM [MI_ROUTE000]
WHERE ([MI_ROUTE000].[MI_ROUTE000_ROUTE_ID_C] = (? :s :caption='Route ID' :id=route) AND [MI_ROUTE000].[MC_FILTER_CODE_C] = (? :s :caption='Filter Code' :id=filtercode))
MappingCode
content_copyfile_download
Dim iPos As Integer
Dim strRoute as String=AutoTrim(src("Route")).ToString

if not ISDBNull(strRoute) then
            'Drop Suffix (If exists)
            iPos = strRoute.LastIndexOf(" ")
            If iPos > 0 Then
                strRoute = strRoute.Substring(0, iPos).Trim()
            End If
            iPos = strRoute.LastIndexOf("(")
            If iPos > 0 Then
                strRoute = strRoute.Substring(0, iPos).Trim()
            End If
            SRC_ROUNDS_QUERY_ROUTEKEY_LookupKeys("route")=strRoute
            SRC_ROUNDS_QUERY_ROUTEKEY_LookupKeys("filtercode")= AutoTrim(src("Filter_Code"))
  
end if