4 Replies Latest reply on Apr 30, 2013 2:19 PM by user10757003

    Check for Unmapped Members in FDM Automation Script


      I have done a bit of research on this forum, and located a script that would check the temp worktable against the lookup table and produce an error message. I want to email the message, which I have the code for. My question is in the script below, where it says "your lookup table" must this be a custom table that one creates with all the account mappings and then you check against that, or is it a table in the FDM database that you query.

      If anyone could maybe guide me or has a working script that does this, I would eternally grateful.

      Sub ImportAction(strEventName)
           If LCase(strEventName)="startmapprocess" Then
                strWorkTable = "[" &API.State.GstrActiveWorkTableName& "]"' WorkTable is temp table for applying mapping rules, etc.
                strTblLU = "[dbo].[Location_Type_LookUp]"' Your lookup table
                ' Query to pull Locations that exist in source data, but not in the Location lookup table
                strSQL = "SELECT DISTINCT entity FROM " &strWorkTable&_
                     " WITH (NOLOCK)" &_
                     " WHERE NOT entity='' AND entity IS NOT NULL" &_
                     " AND NOT EXISTS (" &_
                                         "SELECT * FROM " &strTblLU&_
                                         " WITH (NOLOCK)" &_
                                         " WHERE " &strTblLU& ".[Location]=" &strWorkTable& ".[entity]) " &_
                          "ORDER BY entity"
                Set rs = DW.DataAccess.farsFireHose(strSQL, False)
                With rs
                     Do While Not .EOF
                          strMsngLoctns = .GetString(2,20,,vbCrLf)
                          strErr = strErr & strMsngLoctns & vbCrLf
                End With
                If Not strErr="" Then
                          "ARE MISSING FROM THE LOCATION TYPE LOOKUP TABLE:" &vbCrLf&vbCrLf& strErr &vbCrLf&vbCrLf
                     RES.PstrActionValue = strErr' Set the error message
                     RES.PlngActionType = 2' Display the message in a new window
                     RES.PcolScriptInfo.Item("ScriptInfo").ColFlds.Item("Cancel").varValue = True' Abort the remaining Import events
                     strLoc = LCase(Trim(RES.PcolScriptInfo.Item("ScriptInfo").ColFlds.Item("Location").varValue))
                     strCat = LCase(Trim(RES.PcolScriptInfo.Item("ScriptInfo").ColFlds.Item("Category").varValue))
                     strPer = LCase(Trim(RES.PcolScriptInfo.Item("ScriptInfo").ColFlds.Item("Period").varValue))
                     API.MaintenanceMgr.mProcessLog CStr(strLoc), CStr(strCat), CStr(strPer), 1, False, "Location Type missing from lookup table", Now()' Set process status = fail
                End If
           End If
      End Sub
        • 1. Re: Check for Unmapped Members in FDM Automation Script
          If you are going to use this, running the normal batch process, as an alternatice why not consider switching on the automapcorrect, map missing dimension records to ignore and then get the list of items mapped via the automapcorrect function in the BatchAutoMapCorrect event script. If you need to stop the process continuing i suspect you could set the Validate switch to fail as the normal process would do.
          1 person found this helpful
          • 2. Re: Check for Unmapped Members in FDM Automation Script
            Great idea. Thanks. Have you got any script examples for BatchAutoMapCorrect event script?
            • 3. Re: Check for Unmapped Members in FDM Automation Script
              I have got the automapping correct. I now would like to be able to create an email if suspense mappings occur. I know there is a report, but it seems to need a batch id, and I don't know how to pass that through.
              • 4. Re: Check for Unmapped Members in FDM Automation Script
                Batch id is not needed. the simpler way is in the batchautomapCorrect event script. write each error to a file, using the API.POVMgr.fGetLocationName(lngPartKey) to translate the location key to the location name. (Remember this event will get executed for each error (i think) found rather than once). then in the script used to execute the batch process, (or in the batch action script) after the batch has processed, add the vb code to send the e-mail, and attach the file created.

                Another option is that at the end of the batch process, do a read of the mapping table and select based on a description of (i think) "auto map suspense". when automapcorrect finds a record, it wil create a mapping entry on the table with specific description.