8 Replies Latest reply on Aug 8, 2013 3:21 AM by Kaps_1

    Create subset of table

    Kaps_1

      Title

      Create subset of table

      Post

           Hello,

           I have a table of records with fields "Speciality" and "Yes No" in a table called specialities.

           Bricklayer Yes

           Bank Manager Yes

           Accountant Yes

           Chemist No

           Farmer Yes

           Engineer No

           I am trying to make a related table which consists only of those records where "Yes No" equals Yes. I have done this by having a third field called "Always Yes" - which equals yes. I have created an instance of my "specialities" called "active specialities".The attached screenshot shows my relationships - but I cant seem to make it work. Any thoughts ? thanks

           regards

           Kaps

            

            

            

      bni_crm4.png

        • 1. Re: Create subset of table
          philmodjunk

               As designed, your relationship looks correct if you want to match to all specialty records where yes no = yes. But make sure that always Yes has text selected as the result type.

               The second pair of match fields that use the cartesian join operator (x) can be removed and the relationship will still match to the same set of active specialty records.

               If you want to match by both id fields and yes no status, the second pair should use the = operator.

          • 2. Re: Create subset of table
            Kaps_1

                 Thanks - I tried this but when I create a value list, I don't get the expected results. Here's a screen shot of my raw data and there is a value list in the header. I would expect to see Bank Manager and Roofer in my drop down but I don't. I have also attached an updated relationship diagram and a screenshot showing how the value list is created.

                  

                  

            • 3. Re: Create subset of table
              Kaps_1

                   and :-

              • 4. Re: Create subset of table
                Kaps_1

                     and :-

                      

                • 5. Re: Create subset of table
                  philmodjunk

                       Why is the yes no field selected as the first field in your value list setup? Once you have it set up correctly, all values will have the same first field value of yes. If you specify sorting on the first field--which is the default setting, you'll get just one entry for each value in the first field--which is why you have just one yes value and one no value in your screen shot.

                       I don't see this option selected for your value list:

                       Include only related values starting from Specialties. (And your layout shown should be based on specialties, not Active Specialties. I can't tell which you have selected in Layout Setup... so make sure that you have selected Specialties.)

                  • 6. Re: Create subset of table
                    Kaps_1

                         I put the yes.no field in as a test to see what records were being picked up.  However, I have made the changes you suggested but still dont get the required result.  indeed I get a blank drop down list !!

                    • 7. Re: Create subset of table
                      philmodjunk

                           Hmmm, think I got that one backward when I look at the relationship in your first post. We could swap everything around, but I suspect that we can get things working more quickly if we swap the match fields in the relationship so that you have:

                           Specialties::always Yes = Active Specialties::yes no

                           And you'll need to check over the design of your layout. If you had to go into layout setup and change Show Records From from Active Specialties to Specialties. All fields that refered to Active Specialties need to be double clicked and switched over to the same field name, but from the Specialties table occurrence instead of Active Specialties.

                      • 8. Re: Create subset of table
                        Kaps_1

                             thanks - that sorted it. knew I was close but could not see the wood for the trees