2 Replies Latest reply on Jun 23, 2010 12:04 PM by FentonJones

    Parts Master Lookup Problem

    PeterNeathway

      Title

      Parts Master Lookup Problem

      Post

      I am new to databasing and I am using FMP 10, so this might be a really silly problem.

      Basically, I am trying to use a relationship (between 3 fields in one table and one field in another) to create a lookup for 3 other fields in the first table. Whenever I try to perform the lookup, information is only auto-entered into the fields after matching information has been entered into all 3 of the original fields. 

      Any assistance is much appreciated!

      Thanks,

      Peter

        • 1. Re: Parts Master Lookup Problem
          philmodjunk

          Please post and Exact description of this relationship. You can use a format such as this:

           

          Table::field1 = Table2::Field2 AND

          Table::field3 = Table2::Field4

          • 2. Re: Parts Master Lookup Problem
            FentonJones

            As Phill says, it would be better to explain a bit better. For me, it is a little hard to imagine what you'd want this for. But I'll try and answer according to what I think you want.

             

            If you want 3 fields to target 1 field in the other table, and you only want to enter data into 1 of the 3 in order to cause a lookup, you could create a 4th field in the 1st table, a calculation, result Text, could be [x] Do not store; List ( field 1; field 2, field 3). If you use that in the relationship instead of your 3 fields, it will give you more or a less an "Or" relationship. The List() function produces a multi-line result (of multiple inputs). Each line of a multi-line field will be seen as a possible match in a relationship. Sort of a "poor man's join table" in functionality.

             

            If the mechanism used is "Lookup," it will be triggered the 1st time (and any time) you enter a matching value in either of the 3 fields in table 1.

             

            With subsequent entries (or clearing) in the 3 source fields, if more than 1 of the 3 has a match, it will lookup the value from the 1st matching record, in creation order by default or according to the sort order of the relationship, if it is sorted.

             

            If on the other hand it is an auto-enter by calculation, with the default option of [x] Do not replace existing value, then it will only auto-enter when the 1st match happens, and will not do it again. If you uncheck that option, then it will behave much as the Lookup did.

             

            It will clear however if all 3 fields are later cleared. Whereas the Lookup will only clear when that happens if you use the option for "If no exact match, use: " & also choose "Use: " with nothing in it. Likely best to use the auto-enter by calculation if that's the behavior you want, as it is the default.