12 Replies Latest reply on Dec 15, 2013 1:00 PM by JennySemmler

    Using conditional lists with many-to-many relationships

    JennySemmler

      Title

      Using conditional lists with many-to-many relationships

      Post

           Hello folks,

           I have a many to many relationship set up through a join table, which currently has 3 fields: the serial number key, and a field for the key from the two tables with many to many relationship. 

           I would like to set up a conditional value list from one side of the relationship to the other:

           Component ID (key) Status (available, consumed) Component code --> join table --> Blend ID (key) Status (drop down from component), Component code (related to component status).

           I can get the status to drop down in the blend layout, but not the component codes. (I have set up the menu/order tutorial form the help file and found that the insertion of a join table stopped this working too.) It sort of makes sense that this should be the outcome given that the join table is acting as an index. At one stage when I was fooling around I got the component value list to work, sort of, but it kept making new records in the component list, instead of just looking them up, which was not right either. I can't even get that to happen now.

            

           Any suggestions?

           Many thanks and regards

           Jenny

        • 1. Re: Using conditional lists with many-to-many relationships
          philmodjunk

               Is this the relationship?

               Components----<Join>----Blends

               Components::ComponentID =Join::ComponentCode
               Blends::BlendID = Join::BlendID

               And status is a field defined in Blends? and how are these relationships supposed to "filter" the values in your value list?

          • 2. Re: Using conditional lists with many-to-many relationships
            JennySemmler

                 The relationship is

                 components ---<join>---blends  (I apologise for the error in my post - it was pretty late at night)

                 Component:ComponentID = Join:Compmonent ID  

                 Blends:BlendID = Join:BlendID

                 I also have Join:CompBlendID as a key so that if the component is accessed a second time it is recorded separately

                 Status field is defined in components as being "available"  or  "consumed" so that I can select "available" components to add into the blend in a portal field (thats the plan), since many components may go into the blend. There will be a total blend volume field (summary) and a calculation for total percentage of the component on the blend, too meet our legal obligations.

                 The plan is that once this is working well I will be able to make a new component from a blend via a script which records the old blend code as a cross reference, assigns a new component code and makes the new component available for further blending. A nightmare for traceability.

                 I was happy to upgrade from Bento because that was pretty clunky and everything was a workaround, but the complexity of what happens between components and blends in real life has just about defeated me with FM. Everything else in the database seems to be working pretty well  before and after the component/blend transactions.

                 There are probably multiple errors in the way I've set it up, but I found I wasn't getting results through self-joins either. I'm starting to panic a bit because vintage starts next month and this has to be up and working by then.

                 Yours sincerely 

                 Desperate

            • 3. Re: Using conditional lists with many-to-many relationships
              philmodjunk

                   There are two ways to set up a conditional value list of only available components. The simplest "hardwires" your value list to only "available" components, but does not require adding a new table occurrence of components nor as many new fields either.

                   I would guess that your current value list of Components lists the ComponentID as the first field and a name or description field as the second field in the value list. If so, use this calculation field if you are only listing the name/description field or are sorting your values on that field:

                   If ( Status = "available" ; DescriptionFieldGoesHere )

                   Be sure to select "Text" as the "result type".

                   This field will be blank when the component is not available and will show the description text if it is not. Now update your value list to refer to this calculation field in place of the Description field and your unavailable components will no longer appear in the value list.

              • 4. Re: Using conditional lists with many-to-many relationships
                JennySemmler

                     When I create the value lists the status drops down OK but not the Component Code (before I take on your suggestion). Does this mean I have a fundamental design error?

                • 5. Re: Using conditional lists with many-to-many relationships
                  philmodjunk

                       I don't know what you mean by "before I take your suggestion" in this context. Exactly how did you set up the value list for that drop down?

                  • 6. Re: Using conditional lists with many-to-many relationships
                    philmodjunk

                         And this would not be for the status field, it would be for selecting an available component--a different value list set up on a different field.

                    • 7. Re: Using conditional lists with many-to-many relationships
                      JennySemmler

                           Table Component - Component:Component ID,  Component:Component Code, Component:Status

                           Table BlendCompJoin  BlendComp:BlendCompID, BlendComp:ComponentID, BlendComp:BlendID'

                           Table Blend Blend:BlendID, Blend:Status (With attached value list Status below), Blend:Component (With attached value list Component below)

                           Relationships: ComponentID---<join>---BlendID    with "create records" ticked on all relationships

                           Value list: Status - Value list from field Component:status, all values

                           Value list: Component - Value list from field Component:Component Code, from related field Blend

                           With this setup I can drop down the status, but not the Available Component Code. 

                            

                           Thank you for your patience. 

                            

                      • 8. Re: Using conditional lists with many-to-many relationships
                        philmodjunk

                             With this setup I can drop down the status, but not the Available Component Code.

                             That makes no sense and I don't see why you want the component code instead of the component ID.

                             As I understand your latest post, you need a value list of all the "available" component codes. Correct?

                             If so, then define cAvailableCompCode as:

                             If ( Status = "available" ; ComponentCode )

                             Select the same result type as you have specified for ComponentCode.

                             Then Specify that your value list list values from cAvailableCompCode instead of ComponentCode.

                        • 9. Re: Using conditional lists with many-to-many relationships
                          JennySemmler

                               It looks as if I have a problem with the join table not functioning, which is why the original conditional list wasn't working. I'm currently writing a script to "stamp" the blend & component into the join table to complete the many to many relationships. i'll let you know how I go. Thank you.

                                

                          • 10. Re: Using conditional lists with many-to-many relationships
                            JennySemmler

                                 Update. Hopefully this is the last post I make on this entire database as I've had a breakthrough. I used this, in conjunction with the forum post http://forums.filemaker.com/posts/c167827ea7 so can pretty much avoid scripting this step.

                                 One comment, however - the download referenced in this forum post has expired in FMP7, so can't be opened in FMP12. 

                                 Now all I have to do is check my calculations do as they should, check my reports are working well, and clean up all the junk I've generated as I've fumbled my way through this.

                                 Many thanks and Merry Christmas!

                            • 11. Re: Using conditional lists with many-to-many relationships
                              philmodjunk

                                   .fmp7 files can be opened from FileMaker 12 an 13. You launch FileMaker and then use the Open menu option from the File menu to open the file. FileMaker 12/13 will then convert the file to the .fmp12 file format so that you now have a copy you can open and work with.

                              • 12. Re: Using conditional lists with many-to-many relationships
                                JennySemmler

                                     Thank you. Got it sorted.