4 Replies Latest reply: May 15, 2014 10:04 AM by Rusch Mauzy RSS

    External Lookup Field Properties Type

    Rusch Mauzy

      I created an external lookup to an Excel spreadsheet that contains the following fields: Product Number, PM GL and PMGL Name. The Table Filed I am attempting to link is Product.


      The error message keeps coming up: "The Source and Table Window Columns must be of the same type."


      The Table Window field: "Product" is Type: Numeric. Before I exported the "Product Number" filed from another model, I made sure the Type is also, numeric.


      Please help.


      Thank You,



        • External Lookup Field Properties Type
          Data Kruncher

          Hi Rusch,


          Odds are that Monarch is detecting, rightly or wrongly, that the Product field in the external Excel file is Character. In a perfect world, we'd address the root cause of that issue.


          But we can take advantage of Monarch's flexibility and instead take a quicker solution. Add a calculated field to convert your Product to be a character field (CharProduct), and then perform the lookup using CharProduct instead of Product.


          The formula for CharProduct is simply:




          If some of the lookups fail to retrieve proper values, it'll likely be due to extra trailing or leading spaces in the Product field in the Excel file. Those would have caused Monarch to believe that the field type was Character. It scans the first 250 rows of the external table to determine the field type, so any problems will be hiding in that region.