6 Replies Latest reply on Apr 17, 2016 12:22 PM by erolst

    Error with connect fields between tables

    nathanbone

      Hi All

       

      New to filemaker - loving what I see so far, but hit a brick wall.

       

      Trying to map data from table (Customers) over to another (Orders)

       

      I have primary keys get up on Customers and child keys on Orders.

       

      I can get the Customer name to transfer over, but the telephone number does not.

       

      My tester file is attached, I'd be grateful if anyone can help me as I want to know what am I doing wrong?

       

      Thanks in advance

       

      Nathan

        • 1. Re: Error with connect fields between tables
          BruceRobertson

          The file works correctly as is.

          The customer name and telephone DO transfer when you enter a valid customerfk in Orders.

          Maybe you can explain more about what you are looking for.

           

          One problem may be that you are using a dropdown menu in the orders customer name field.

          If you're going to take this approach to selecting the customer, you should instead set up the customer value list to use customer ID.

          • 2. Re: Error with connect fields between tables
            BruceRobertson

            Your example file is of course, very basic at this point. Attached is a file with a few mods; including a value list based on the Customer ID.

            • 3. Re: Error with connect fields between tables
              nathanbone

              Bruce, you are a star! thank you

               

              I am trying to build a CRM system, it a big project but starting off with the bits that are priorities for us as a company - that is marrying up customer info with orders. Eventually we want to run our accounts through it, plus integrate it into our emails (not sure the last bit is possible yet...).

               

              Trying to get my head around the technicals atm, using lynda.com for the base of my training, but it is only getting me so far and needed the help of an expert!

               

              Thanks again!

               

               

              • 4. Re: Error with connect fields between tables
                BruceRobertson

                There are a variety of existing solutions which can do those things, including example files which come with FileMaker;  and commercial files; and freeware files; such as RCC FMStarting Point.

                 

                One important point: it will be helpful to learn the distinction between when you want to copy over "snapshot in time" information; and when you want to merely display related data. As for "copying over" the information, you will more likely want to use auto-enter calculations.

                 

                Prices, for instance, are definitely values that you want to "copy over" from the price table. An invoice should show the prices set up for that invoice, and the invoice should not change over time even as your product price table gets updated. It should be "snapshot in time".

                 

                Some other types data should instead display the related data from the parent or related record. Generally in a way that does NOT allow editing in this view. Here's a mod to your file, in which the company name and telephone are displayed by showing the related fields.

                • 5. Re: Error with connect fields between tables
                  nathanbone

                  Hi Bruce,

                  Basically I run a design agency, but noted there is a need to now track our sales targets, prospects, estimates, orders and invoicing. Due to the nature of our business there are certainly features we have to consider in our process - this is why we were thinking of a bespoke build as this caters for more of what we do.

                   

                  That said if you know of any good templates at that are customisable - I am all eyes and ears.

                   

                  Thanks again for your help / advise

                   

                  Nathan

                  • 6. Re: Error with connect fields between tables
                    erolst

                    Hi Nathan –

                     

                    here's another MOD of your tester – well, more like a rebuild, demonstrating a simple setup for basic CRM and order management.

                    I already started modding this when Bruce came along, so didn't want to let it go to waste

                     

                    Add a simple invoice layout, and you can start doing productive work.


                    Have fun,

                    Oliver