2 Replies Latest reply on Dec 23, 2013 3:51 PM by WBSimon

    Converting Numbers to accomodate relationship



      Converting Numbers to accomodate relationship



           I'm having a bit of trouble. I have two separate table with nearly identical data they are joined by two text fields Part number and Revision. I use text fields because often there is text in a part number or a revision. The problem is that these tables come from different sources and the problem arises when one source will call a certain part # at revision 2 and the other source describes the revision as 002 then they do not join. How can I either 2 to 002 or 002 to 2 to get a match?


           Working in FM PRO 12



        • 1. Re: Converting Numbers to accomodate relationship

               Inconsistent data will drive you crazy. And there is no magic cure. If the data was strictly numeric, you can put the data in a field of type number, but since you have indicated that there is additional text that can be in this match field along with the numeric digits, this may not be possible to do.

               Ultimately, you need internally generated values to use as your primary keys for linking to other tables and these fields of imported data should not be used for that purpose, but immediately after import, you may still have to use this data as best you can to find and, if needed, generate the needed interal match value.

          • 2. Re: Converting Numbers to accomodate relationship

                 If you really needed to link the two files, you could use a calculation to add "0"' as fillers. 

                 Case ( Length ( Key) = 1 ; "00" & Key ; Length ( Key ) = 2 ; "0" & Key ; Key )

                 Just make sure the field is a text field.