6 Replies Latest reply on Jun 1, 2012 9:44 AM by Stephen Huston

    lookup ….

    thinkdifferent

      if I have one table with a set of values, how can I import lookuo a related set of values based on two fields matching, wether a value is imported once or many times would depend on the first field matching… surely I can do this ?

      Currently if the lookup table has a value that matches the result is only shown or imported into the first matching record of the second database table.... thanks...

        • 1. Re: lookup ….
          taylorsharpe

          Instead of a Lookup, you could do a Calcluated Value (just above lookup in field definitions).  Make sure that you first make a relationship set up whereby the Table Occurrence (TO) you want to get it from has the two fields defined in the relationship.  Then the calculated value would just be the field name in the other TO. 

          • 2. Re: lookup ….
            thinkdifferent

            also that pulls in the first match but not further ones....

            • 3. Re: lookup ….
              taylorsharpe

              Can you give us more info on your table occurrences, matching fields and cacluation? 

              • 4. Re: lookup ….
                thinkdifferent

                Of course.

                I have a few tables...

                table 1 has windspeed + postcode (both are contant)

                Table 2 has locations based on postcode... some postcodes are the same....

                the matching works but windspeed is only ever imported, looked up / matched in the first instance of a postcode not any others....

                Very odd...

                • 5. Re: lookup ….
                  thinkdifferent

                  arrrggghhhhh...

                  it was corrupt data input, thought I was going mad...

                   

                  How do I clean up data i.e. remove preceding and trailing spaces ? the field type is text...

                  • 6. Re: lookup ….
                    Stephen Huston

                    You can run a backup (before risking a Replace Field Contents process), and then.

                     

                    Find all records. Go to the field which needs cleaning (one field at a time if there are multiple fields).

                     

                    Do a Replace Field Contents... (lower part of Records pull-down menu) using the lower choice: Replace with Calculated Result:

                    • Trim ( fieldname )

                    That should remove all leading and training spaces around the actual data. If there are Returns/Para in some records, you may also want to run a replace using the Substitute function as a separate step, substituting "" for the return character.

                     

                    And, if you do it wrong, you have the backup. There is NO UNDO for the replace process. Back up first in case...