2 Replies Latest reply: May 15, 2014 10:14 AM by lcwalker _ RSS

    External Lookup/ Conversion Help

    lcwalker _

      First post, be gentle

       

      I have three files, all text /Uthat have one linking key field.  The first is the source databse, the second is a price list and the third is a markup file.  Here's my problem.  The source file and the markup file link perfectly on the key, however the price list does not.

       

      When linking as an external data source I get the message "The source and table window columns must be of the same type".  This is confusing to me as they are all three text files, pipe delimited with "" as my text identifier, which is not present on any item numbers.  The ITEMNO appears as a number in the source file and the markup file when imported in, but the cost file apparently is text.

       

      So, as a workaround, i was going to create a calculated field that converts the number formatted ITEMNO to text and link to that; now I get an "Invalid term" error when using the Str() function.

       

      Any help would be greatly appreciated.

        • External Lookup/ Conversion Help
          Olly Bond

          Hello lcwalker, and welcome,

           

          Str() is the right approach - so, if you have a field ITEMNO with data like:

           

          123

          4567

          98765[/CODE]

           

          you can convert this to text using: str(;8;2;"0") which gives:

           

          00123.00

          04567.00

          98765.00[/CODE]

           

          The 8 is the total length of the string, the 2 is the number of decimals, and the "0" is the character to pad out the start of the string where the number is shorter than expected.

           

          Assuming your ITEMNOs don't have leading zeroes or decimal places, then str(;x;0;" ") should do the job.

           

          Best wishes

           

          Olly