5 Replies Latest reply on May 21, 2010 8:29 AM by philmodjunk

    What's the FMP Way for users to add many-to-many links?

    Rillaith

      Title

      What's the FMP Way for users to add many-to-many links?

      Post

      Version: FMP 10

      Experience level: Strong SQL background, but still fairly new to FMP and the FMP way of doing things

       

       

      I hope you good folks can help point me in the right direction here - I've got a problem that I don't know how to solve in an FMP way - and don't know the right terminology to hit on an answer through Google or in the manuals.

       

      Essentially, I need to know the FMP Way to allow an end user to open an existing record in one table, and add a relational link to an existing item in another table by searching for matching records and selecting the correct one. It's a many-to-many relationship, so I can't just create a new record from one side of the relation and have it work.

       

      E.g. (simplified example) If I have a table of Magazines, and a table of Writers, and my user needs to link all the Writers who have a piece in a Magazine. I think I need to view the Magazine record, and then pop up a window to ask for firstname & surname of the Writer, do a find, present the matching records, and allow the user to select which one they want to link, at which point I record the WriterID & MagazineID in a linking table.

       

      I have seen reference to being able to do the popup window & find bits in the scripting docs, but I don't know at all how to present the user with several matching records, and allow the user to choose which of those records will be linked back to the original record. Any help - even if only pointing in the direction of the appropriate methods - gratefully appreciated.

       

      Thanks!

       

      Sally

        • 1. Re: What's the FMP Way for users to add many-to-many links?
          philmodjunk

          The table structure you need will work the same as you would with SQL: Writers, Magazines, Writer_Magazine 

          Writers----<Writer_Magazine>----Magazine

           

          Writers::WriterID = Writer_Magazine::WriterID (Enable creation of related records on Writer_Magazine)

           

          Magazine::MagazineID = Writer_MagazineID (Enable creation of related records on Writer_Magazine)

           

          If you place a portal to Writer_Magazine on a Writers layout, you can format the MagazineID field as a drop down for selecting a magazine and thus use this portal to link a the current writer record to an existing magazine record. You can include fields from Magazines in this portal to make it more informative to the user.

           

          If you place a portal to Writer_Magazine on a Magazine layout, you can format the WriterID field as a drop down for selecting a writer and thus use this portal to link the current magazine record to an existing writer record. You can include fields from Writers in the portal to make it more informative.

           

          If desired, you can use conditional value lists in your drop downs to list only relevant subsets of the total list of writers or total list of magazines.

           

          Here's a demo file: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

           

          Here's a thread on setting up conditional value lists:  Custom Value List?

          • 2. Re: What's the FMP Way for users to add many-to-many links?
            Rillaith

            Thanks for the direction, Phil. 

             

            I think the only part that I'm missing to complete the loop is creating a conditional list based on user input, because I'm in a situation where I could easily have a thousand records on either side of the relationship. I thought I could see some possible routes to achieve this but they don't seem to be working out. Could you advise on what the preferred FMP way for this would be? I feel like I've nearly got it several times but it just slips away before I figure it out. 

             

            (If I'd been writing this app for my client in PHP & MySQL I'd have been finished by March! Ack!!)

             

             

             

             

            • 3. Re: What's the FMP Way for users to add many-to-many links?
              philmodjunk

              I put a link to a thread on conditional value lists in my last post. Did you read it?

              • 4. Re: What's the FMP Way for users to add many-to-many links?
                Rillaith

                I did, but it didn't seem to address the part I asked for clarification on. Or if it did, not in a way I recognised as answering it.  I need to base the condition specifically on user input - that's the bit I can't understand at the moment: how I can link user input with the creation of a conditional list. Couldn't find anything useful with searching for "conditional list" and "user input" either. :/

                 

                Having allowed everything I read yesterday to stew overnight, I think I'm going to be trying to route of  a global field for user entry, and a relationship (for the list) based on a calculation field containing a concatenation of everything I want to search on in the destination table matching "*global_field*" from the source table - though I'm not sure what the syntax is for that. Does this sound like the right approach?

                • 5. Re: What's the FMP Way for users to add many-to-many links?
                  philmodjunk

                  The tutorial that I posted a link to describes an approach (Option 2) that limits the values based on user input.

                   

                  The user makes a selection in field 1 and that value "filters" the list of values for the value list in field 2 to just those values that fall in that category selected in the first field. That appears to be what you need here.

                   

                  It might help to spell out exactly the situation you are dealing with here. Then I can suggest an approach using elements from your example to show you how it works.

                   

                  In the meantime, heres a demo file with multiple level conditional value lists you may want to look over as a way to see how it works: http://www.4shared.com/file/gd6_fDgn/conditional_value_listDec.html