6 Replies Latest reply on Jun 16, 2017 9:35 PM by philmodjunk

    Lookup from Table A to a restricted set of records in Table B

    ericruff

      I have a field in Table A that does a lookup in Table B and it works just fine.  Until I discovered that not All the records in Table B should be available for the lookup - the values of some records in Table B are off limits and not to be considered in this lookup.  I don't want to simply get rid of the "off limits" records in Table B, I need to preserve the complete set of records for other reasons. So is there a way to have a field in Table A do a lookup to a "found set" of records in Table B or otherwise restrict the values in Table B.

       

      My ham-handed initial approach is to make a copy of Table B, and have Table B-1 and Table-B2.  Then I delete the extra records in Table B-1 and offer this slimmed down set of records for the lookup from Table A.  Table B-2 will preserve the full set of Table B values for other purposes.

       

      Of course this is not optimal because of changes in the future to Table B that will have to be duplicated for both Tables B-1 and Table B-2.

       

      Any ideas?

        • 1. Re: Lookup from Table A to a restricted set of records in Table B
          Philip_Jaffe

          Is there a rule you can express as a calculation to determine which records in table B should not be accessible?

           

          If you are able to express that rule(s) in plain English,  likely you can set up those rules in Filemaker.

          • 2. Re: Lookup from Table A to a restricted set of records in Table B
            ericruff

            Certainly, I can devise a rule the defines which records should be ignored, but I don't see a place to insert such a rule.

             

            In the relationship graph between Table A and Table B, one can define which fields in A match in a particular way with fields in B, but the relationship does not allow a restriction based solely on field values in Table B. At least not that I see.

             

            Other than in the relationship graph, where else is there an opportunity to insert such a rule?

             

            And why is my response to you marked "Correct Answer"?

            • 3. Re: Lookup from Table A to a restricted set of records in Table B
              philmodjunk

              There are two basic options that you can use to exclude records:

               

              Modify the relationship so that records in Table A are not related to the restricted records in Table B.

               

              Set up a calculation, such as one using ExecuteSQL to look up data in a way that excludes the restricted records.

               

              If you can define a simple rule, for this as Philip_Jaffe suggested this becomes something that we can help you with.

               

              An alternative is to add a field, possibly formatted as a check box, to a table B based layout where you click to select the records you want to include or exclude. That field can then be part of the relationship or the calculation in order to exclude records that you want excluded.

              • 4. Re: Lookup from Table A to a restricted set of records in Table B
                ericruff

                I'd like to examine your alternative.  I can easily do as you suggest. Add a field to Table B with a value to mark the acceptable records, say field Acceptable that can have a value of "Yes" or "No."  But where this breaks down for me is where in the relationship can I exclude the the records with this value.  The relationship has to be a comparison between values on Table A and Table B to include or exclude records in B. 

                Are you suggesting I also add another field in Table A like "Acceptable" with a global value of "Yes" and then add this as an additional "rule" in the relationship?

                • 5. Re: Lookup from Table A to a restricted set of records in Table B
                  Philip_Jaffe

                  That would certainly work.  Yes.

                   

                  Keep in mind you may have more than one relationship defined between Table A and Table B in the relationship graph.  You can have as many as you need. 

                   

                  And I don't know why it is marked "correct."  It doesn't show that on my end.

                  • 6. Re: Lookup from Table A to a restricted set of records in Table B
                    philmodjunk

                    You see a button that you can click to select a reply as the correct answer.

                     

                    You can use a calculation field in table A to match only designated records in B.

                     

                    If your original relationship was:

                    TableA::MatchField = TableB::MatchField

                     

                    Change it to:

                     

                    TableA::MatchField = TableB::MatchField AND

                    TableA::constOne = TableB::permitted

                     

                    constOne would be a calculation field with the number 1 as its sole term. Permitted would be a number field formatted with a check box and a single value value list of 1.