8 Replies Latest reply on Aug 3, 2017 3:36 PM by kiwimikey

    try to use data in the same table in other tables on same database

    kiwimikey

      Hello

      I need some help with the following. First I am using file maker pro advanced 11. On the database I have a table called “MASTER” where I have names address etc.

      I would like to be able to pull the same data from the table “MASTER” on other tables on the same database, ie for example a table called “CREDIT APPLICATION” and another table called “INVOICE”

      At the moment I have been able (using a drop down menu) to do the table “INVOICE”  (which fills in all the data from the MASTER table that I need, but the table called “CREDIT APPLICATION” will not work. I have put a drop down menu on “CREDIT APPLICATION” where I get only one line of data from the table “MASTER”. No other data is transferred. This one line is set up using the edit value list. When I run the the table called “INVOICE” it comes up with the drop down arrow which I click on and the names from the “MASTER” table is displayed. Select the person  you want and it fills the line in “CREDIT APPLICATIOPN” but none of the other data is displayed like address phone number etc

      I have made sure that the units relationship is connected under manage database/relationships in both tables (CREDIT APPLICATION AND INVOICE). Have made sure the settings in the inspector are the same as the one that is working except change the setting to suite that current table.

      I have tried as well to keep “CREDIT APPLICATION” clear of the “MASTER” by trying to pull the info from the table “INVOICE”. Have tried to duplicate the table “MASTER” to see if that was the problem.

      Cannot use portal, even though I have tried and the problem is that even when I tried it the data did not come through

      Thank You

        • 1. Re: try to use data in the same table in other tables on same database
          philmodjunk

          I suggest posting screen shots of your relationship graph and the dialogs used to set up your value lists. There are a number of important small details that may be at fault here.

          • 4. Re: try to use data in the same table in other tables on same database
            kiwimikey

            the master you can see, with name address etc

            credit app as well is the one that I can only bring up one of the data in the file

            the quote menu is the invoice and is the one that is working correctly

            • 5. Re: try to use data in the same table in other tables on same database
              philmodjunk

              You have problems.

               

              For one, your relationship between Master and Credit Application is not valid. The relationship originally matched 4 different pairs of fields in one table to the other, but one of those fields was later removed from Credit application. I can tell from the connection line that connects to the top of the table occurrence "box" instead of to a field.

               

              The fact that you match on multiple fields alone is likely to keep a value list formatted field in your credit application table from being able to trigger a look up of data from the Master table. Selecting a value in that value list enters data into only one field, but you need matching data in multiple fields of your credit application table before it can link to any record in Master in order to look up or link to data in that table.

               

              You need a single customer ID field in Master to use for this purpose. Since you are using v11, this would have to be an auto-entered serial number. (Ok other options are possible, but let's keep this simple to start.) You'd then set up a "use values from field" value list that lists this ID field as field 1 and the customer name as field 2. This allows you to select a customer by name but link a credit application to a record in Master by the ID number. Since you are entering a single value in order to link to a record in Master, your value list now works to look up information.

              • 6. Re: try to use data in the same table in other tables on same database
                kiwimikey

                I have a mater one called key see photomaster.JPG

                • 7. Re: try to use data in the same table in other tables on same database
                  philmodjunk

                  That's useful. Notice that the link from quote menu also matches by just one field. That's why it works for you. But also note that it has pretty severe limitations. You could easily have two or more customers with the same last name and if you link by last name, then discover that you've misspelled the name, correcting the spelling would disconnect the quote from the previously matching Master.

                   

                  To use Key, set up a "use values from field" value list. Select Key from Master as the first field. Select Surname as the second. A calculation field that combines first and last names would be even better for field 2. You can choose to hide the first field and then your value list will only list names in the value list, but still enters the value of Key.

                   

                  Now update your relationship to match Master::Key to a corresponding Number field in Credit Application. You can then either set up fields in Credit Application that use looked up value or auto-enter calculations to copy over data from master or you can just add fields from Master to your Credit Application layout. Note that if you copy over the data, future changes to the data in Master will not automatically appear on the Credit Application. If you add the fields from master, such updates will automatically appear. It depends on your business needs and legal requirements as to which option is best.

                   

                  PS. even with this value list, there are significant limitations with what you can do with it, but it's a good starting point. There are quite a few ways to enhance this value selection process. Some of which work in FileMaker 11, but quite a few more that need a newer version of FileMaker.

                  • 8. Re: try to use data in the same table in other tables on same database
                    kiwimikey

                    Have problem sorted out now working correctly once I had deleted and re done the relationship then created another customer value list using the different info that I wanted

                    Thank you for your help

                    Mike