11 Replies Latest reply on Nov 21, 2014 7:13 AM by philmodjunk

    Dropdown box to select a record in a 1 to N relationship?

    FredH

      Title

      Dropdown box to select a record in a 1 to N relationship?

      Post

      Hi experts,

       

      I have two tables with a 1 to N relationship.
      In my layout of table A, I would like to have a dropdown box that shows my all linked records in table B - via a specific field (i.e. __pkTableBID). 

      When selecting a value (record) in that list, I would like to have the other fields related to table B showing the content of the chosen record.

      I think it is easier to do - for your - than to explain - for me - but I am not sure how to set this up correctly.
      Thanks in advance for your input!
      Kind regards,

      _Fred_

       

        • 1. Re: NoFields
          SteveMartino

          Why not just use a portal with a Go to related Records (new window)?

          • 2. Re: NoFields
            FredH

            Hi Steve Martino, 

            In fact, my start layout is rather complex because I had to reproduce something that was earlier created in Excel.

            Therefore, I have to stay with a somewhat rigid layout where a portal will not help. 
            In fact, I have a layout that shows some information on a project (coming from table A - 1 record) together with the parameters I use for a standard scenario (table B - 1 record) as also the financial outcome for that project (table C - 5 records -> portal).

            As I would now like to make some alternative scenarios with alternative financial outcomes (on another specific layout -> see my other post), I would like to be able on my main layout to choose from a list which scenario is shown ("Base", "Simulation 1", etc).
            Based on the choice I make in a dropdown list, my record from table B + records from table C should be updated... if possible of course...
            :-)

            To be honest, my main concern is to create these alternative scenarios (see other post) but if I can also manage to have this part working, I would answer the needs @ 100% instead of 99%
            ;-)

            Thanks in advance for your input!
            Kind regards,

            _Fred_

             

             

            • 3. Re: NoFields
              schamblee

              Fields in the current table and related tables can be placed on the layout. Select the field object, place the field on the layout at the desire location then select the field tableb::field.

              • 4. Re: NoFields
                philmodjunk

                Sounds like you need two relationships to two different occurrences of the same Table B. One is the one to many relationship that you already describe. A second would use a field you set up as a value list of ID's from the related Table B records using your original relationship, but this field is a match field to a different occurrence such that when you select a value the current record in A is now linked to this one selected record and then fields from this occurrence, if put on the layout as described by S Chamblee, will show data from the selected record.

                TableB|Selected>----TableA----<TableB

                TableA::__pkTableAID = TableB::_fkTableAID
                TableA::SelectedTableBID = TableB|Selected::__pkTableBID

                TableA::SelectedTableBID would be set up with you conditional value list--a "use values from field" value list listing values from TableB where __pkTableBID would be the first field and you'd select the "include only related values, starting from TableA" option.

                You'd be able to include fields from TableB|Selected, a second Tutorial: What are Table Occurrences? of TableB on your layout to show data from the selected Table B record.

                For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                To learn more about conditional value lists, see "Adventures in FileMaking #2 - Conditional Value Lists".

                • 5. Re: NoFields
                  FredH

                  Hi PhilModJunk & S Chamblee, 

                  Thanks a lot for your input!  The example is very clear and I should be able to work it out as from here!  
                  As soon as I will have managed to created my different records for my TableB, I will give it a try :-)

                  Will let you know!
                  Kind regards,

                  _Fred_

                  • 6. Re: NoFields
                    FredH

                    Hi PhilModJunk,

                    Unfortunately, I see that I still overestimating my skills because I cannot put your example into practice. :-s
                    What I have : 
                    Table A ---< Table B
                    Table B ---< Table C
                    TableA::__pkTableAID = TableB::_fkTableAID
                    TableB::__pKTableBID = TableC::_fkTableBID
                    Table B has a field called "bCode" and Table C has a field called "cCode".

                    What I would like : 
                    On my layout of table A, I would like that the related record shown from Table B is always the one that matches bCode = 0 and for Table C, it should always match cCode = "Base".

                    I suppose that I should do that using what you explain with the Table Occurrence but how should I translate this to this "double" table occurence case?

                    Thanks in advance for your input!
                    Kind regards,

                    _Fred_
                     

                    • 7. Re: NoFields
                      philmodjunk

                      Where did Table C come from? Your original post only described two tables. Is Table C an occurrence of one of the other two tables? Which one?

                      • 8. Re: NoFields
                        FredH

                        Let's say that I often try to simplify the situation in order to explain clearly but afterwards, I sometimes notice that I simplified too much which makes it not possible for me to adapt your explanation to my example...
                        Sorry for that!

                        Table A, B and C are totally different tables.  
                        A = Project
                        B = Investment
                        C = Scenario

                         

                        • 9. Re: NoFields
                          philmodjunk

                          Using letters for table names actually makes it harder, not easier to discuss a data model.

                          Project----<Investment------<Scenario

                          Project::__pkProjectID = Investment::_fkProjectID
                          Investment::__pkInvestmentID = Scenario::_fkInvestmentID

                          On my layout of Project, I would like that the related record shown from Investment is always the one that matches InvestmentCode = 0 and for Scenario, it should always match Scenario Code = "Base".

                          This is a completely different set up from your original post. I see nothing in your more recent posts about using a drop down to select records.

                          You can put portal filter expressions on each of the portals to Investment and scenario specifying those values in set of related records.

                          • 10. Re: NoFields
                            FredH

                            Hi PhilModJunk,


                            In order to get things working, I decided to work in two steps :
                            First having the "basic" investment and "base" scenario (and related NOP records) showing up in the Project Layout.
                            Afterwards having the possibility to change the scenario that is shown on the layout based on a dropdown box.

                            For the first steps, I went through your Table Occurence example and created this basic example in FileMaker.  Based on this, I think that I managed to finalize the "fixed" filter on "basic" investment and "base" scenario.  My layout looks more stable now as before, my NOP portal sometimes showed strange behavior.
                            Once I can confirm that this is OK, I will try to adapt the example to also add the dynamic filter on Scenario.

                            One additional question : TableBSelected in your example is an exact copy of TableB? Like PhoneNumbers2?

                            Thanks again for your help and time & sorry for the unclear explanations!
                            KR,

                            _Fred_

                            • 11. Re: NoFields
                              philmodjunk

                              TableBSelected in your example is an exact copy of TableB? Like PhoneNumbers2?

                              It depends one what you mean by "exact copy". It's NOT a copy of the table. I IS a copy of the TableB table OCCURRENCE. If that is confusing, I suggest clicking the link in the following statement quoted from my original response:

                              You'd be able to include fields from TableB|Selected, a second Tutorial: What are Table Occurrences? of TableB on your layout to show data from the selected Table B record.