9 Replies Latest reply on Sep 4, 2010 5:19 AM by revmk

    Value List Ignoring Relationship

    revmk

      Title

      Value List Ignoring Relationship

      Post

      Hi all (again),

      Should value lists ignore a table relationship.  For example.

      Table 1 [products] - start date, end date, product code
      Table 2 [quotes] - product code
      Relationship = product code = product code AND start date > today AND end date < today.

      (Product code is not a unique field as the product can be replaced by a new 'version' of the product).

      The layout is based on Table 2, and a drop down allows the user to select the required product.  This works well unless the end date is passed.  At this point the drop down still shows all the products - however it will not populate the other (unmentioned) fields, it is just contained and selectable in the list.  Am I misunderstanding how the list works or have I gone wrong somewhere?

      Thanks

        • 1. Re: Value List Ignoring Relationship
          philmodjunk

          Shouldn't that relationship be Start date < today and end date > today?

          In your value list setup, did you select the "Include only related values..." option?

          Did you select the correct "starting from" table occurrence? (A table occurrence is a "box" in Manage | Database | Relationships.)

          • 2. Re: Value List Ignoring Relationship
            revmk

            Hi,

            Forum just lost my reply!

            1) Yes you are right re: the relationship.  I had set it to be this way just failed to show it in my post!

            2) I am unable to set 'related' values as the [quotes] is only related to [products] by the date and the field I am setting.

            3) There is only one table occurance of [products], so I must be selecting the correct table.

            I've confused as selecting the product in the drop down will populate the fields, unless the relationship is not met at which point only the drop down works all the other fields fail to populate.  This leads me to believe that the relationship is working well it is an error in my listbox somewhere. :(

            Thanks

            • 3. Re: Value List Ignoring Relationship
              philmodjunk

              If you don't/can't use the show related records option, then the value list displays all values from the table. FileMaker doesn't have any input from you to do otherwise.

              To get the results you want, you'd need to set up an additional table occurrence of products that relates to your current record by just the date fields. You can then select this table occurrence as the data source ( The "use values from" drop down ) and specify the table occurrence on the other side of this relationship as the "starting from" table occurrence.

              • 4. Re: Value List Ignoring Relationship
                revmk

                Hi, almost there.  I now have it working as you described, however when selecting a value sometimes the following will occur.

                1) Works no problem, user can select an option and change their mind.  Value shows correctly from first and second field.

                2) Second field value does not show once selected, sometime reselecting the option can fix this.

                3) Selecting a value works, but on clicking again a 'non-values' (or similar) message is displayed.

                I thought it might be refresh problem but this does not appear to be the case.  Any ideas?

                Thanks for your help *again!*

                • 5. Re: Value List Ignoring Relationship
                  revmk

                  OK, I think I have fixed it by performing a commit after selection.  Not idea but it helps keep the record safe incase of problems!

                  Thanks for your help as always.

                  • 6. Re: Value List Ignoring Relationship
                    philmodjunk

                    What do you mean by "second field value"?

                    • 7. Re: Value List Ignoring Relationship
                      revmk

                      Sorry I meant the 'Display values from second field'.

                      • 8. Re: Value List Ignoring Relationship
                        philmodjunk

                        2) Second [column] field value does not show once selected.

                        Does not show when you pull down the menu or after you exit the drop down? In a drop down list the second column value will not be visible after you exit the field. You'd have to add the related field to show the data. If you set up your field as a pop-up menu, then the second column value will be displayed if you have selected "show values only from second field", but the first column value is actually entered into the field.

                        • 9. Re: Value List Ignoring Relationship
                          revmk

                          Hi,

                          Thanks for the clarification.  I am using a popup menu, working as expected :)