12 Replies Latest reply on Oct 24, 2016 6:44 PM by jfletch

    Basics - populating from another table

    scfpete

      Hi,

       

      I'm new to Filemaker/RDBs so please forgive my ignorance. I am ultimately trying to develop a database to run all aspects of my finance brokerage. It will do two things...manage each deal and act as a CRM to track meetings/calls with introducers and clients and prompt to follow up.

       

      Each deal will have Client, Introducer, Funder, Product as well as commissions, fees, dates etc.

       

      To start and to get my head around building each table/form I have created a simple DB of two tables...Contacts and Funder. The Contact table has  ContactID and Name (I can split to first/surname later) and FunderIDFK. The Funder table has FunderID, Funder. Each contact can have only one funder but funders can have multiple contacts.

       

      I have populated the Contact table with a number of names. I have also populated the Funder table with a number of names. I now want to be able to populate the contact field in the Funder table by choosing a contact from a dropdown box linked to the Contact table. I managed to do this on a previous try which worked but it was then duplicating the contact names in the Contact table (I messed around with Allow Creation of records and then it didn't work again).

       

      Any assistance with these absolute basics much appreciated.

       

      Pete

        • 1. Re: Basics - populating from another table
          beverly

          Welcome! a drop-down is a value list based on a field (or two).

          "Defining value lists"

          Then the field on the layout (in Contacts) would be a field from Contacts, but the formatting of the field is to be a drop-down.

          "Setting up a field to display a pop-up menu, checkbox set, or other control"

           

          The definition of the value list is the key here. Typically it is two fields with an "Id" as the first column and some text value as the second field. Sort by the second field, only show the second field. But you are actually setting your field in Contacts with that ID. Then you can use related fields or merge field (related) or Lookup to bring the other data into Contacts as needed. Once selected the "id" field can be hidden.

           

          IF the list of values gets too long, then many developers will use a pick-list type of pop-over with all the selections available. You then script setting the ID and other fields.

           

          The use of Lookup is recommended for historical data (invoices typically have historical data). But otherwise just displaying the related data (once the Id is selected), may be preferable. Do not allow changing the related value through the relationship (prevent entry in Browse mode), to preserve valid data.

           

          HTH,

          beverly

          • 2. Re: Basics - populating from another table
            TSPigeon

            scfpete:

             

            I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more feedback on this topic!

             

            TSPigeon

            FileMaker, Inc.

            • 3. Re: Basics - populating from another table
              scfpete

              Hi Beverley,

               

              Thanks for taking the time to assist.

               

              I still can't get it to work! I think I get the value list bit - using the ID and then the actual field. I get the dropdown arrow in the field but when I click on the field it doesn't drop.

               

              I've created a new DB. I'll explain what I've done.

               

              Main table is Deal.

              Child table is Product.

              Each Deal can have many products.

               

              Deal table:

              DealID

               

              3 records

               

              Product table:

              ProductID

              Product

              DealIDFK

               

              3 records - AF, Factoring, CM

               

              DealID linked to DealIDFK

              one to many

               

               

              I didn't understand this bit:

              Then you can use related fields or merge field (related) or Lookup to bring the other data into Contacts as needed. Once selected the "id" field can be hidden.

               

              Thanks,

               

              Pete

              • 6. Re: Basics - populating from another table
                BruceRobertson

                Here's a mod to your example file.

                • 7. Re: Basics - populating from another table
                  scfpete

                  Hi Bruce,

                   

                  There's nothing attached.

                   

                  Cheers,

                   

                  Pete

                  • 8. Re: Basics - populating from another table
                    BruceRobertson

                    "There's nothing attached."

                    Not true. Don't know what method you're using to view the forum.

                    Here's what I see:

                    BFR reply.png

                    • 9. Re: Basics - populating from another table
                      philmodjunk

                      Attachments aren't visible if you are using email to respond. Click or tap the link that opens the discussion in your browser and you'll then see a link that you can use to download the file.

                      • 10. Re: Basics - populating from another table
                        beverly

                        you only have the "DealID" in Deal table, so there is nothing to lookup (no other fields to bring over).

                         

                        beverly

                        • 11. Re: Basics - populating from another table
                          BruceRobertson

                          "I'm new to Filemaker/RDBs so please forgive my ignorance. I am ultimately trying to develop a database to run all aspects of my finance brokerage."

                          While you're at it, take up heart surgery as a weekend hobby.

                           

                          Hire a developer.

                          • 12. Re: Basics - populating from another table
                            jfletch

                            I have a client who is a very brilliant doctor. She has been working on a system that has taken her a decade to build in FileMaker and runs a very large amount of her pathology lab, including a massive amount of record-keeping and paperwork. It does everything she needs it to do, except for the specialized stuff she brought me in to do.

                             

                            She very certainly built a system, but it is starting to creak under the weight of some, um, less than optimal decisions that she made a long time ago. She has no understanding of relational techniques and though she has several files, they are all just overgrown flat files, and very poorly normalized. (Or, anti-normalized, more like.) She has no knowledge of functions and script steps and calculations that could save her a ton of time, file size, and a LOT of maintenance. My job has been to integrate her system with outside medical records systems, and not to fix her core solution much, so it has been very frustrating to work with.

                             

                            Did she write it herself? You bet. Did she know what she was doing and write an efficient system? Nope.

                             

                            I'm with Bruce. If you have ambitious plans, hire someone who knows "where the rocks are." If you want to create a library of your vinyl music collection, then jump in.

                             

                            You're a financial guru. What would you say to a financial newbie that wanted to manage his own retirement portfolio by day-trading it? What would it cost that person in the long (or even short) run to make a ton of mistakes starting out?

                             

                            Embrace that analogy, because it is very, um, "right on the money."

                             

                            Here's another one: Tom Wolfe, in his awesome book, The Right Stuff, talked about the military fighter/test pilots who were the first astronauts. They were the best of the best in the air, but their self-confidence bordering on arrogance got them into an extraordinary amount of auto accidents on the ground. Brilliance/skill/right stuff in one discipline does not necessarily translate to the same level of competence in another.

                             

                            That said, set your initial goals on a simple project while devouring the FileMaker Training Series and Chris Ippolite's terrific lynda.com videos. But, don't be surprised if you want to change careers after that. ::-)