6 Replies Latest reply on Jul 27, 2016 9:35 PM by philmodjunk

    Related Tables: How to automatically conform records in matching fields?

    dbarthold

      If I use one table as an intake form, how do I cause values in matching fields on related tables to conform?

        • 1. Re: Related Tables: How to automatically conform records in matching fields?
          philipHPG

          What do you mean by conform?

           

          If you want values to be copied from one table to a related table you can use an AutoEnter Lookup on the field in the related table. (In Manage Database, click on the Options buttons for the field, then under Auto-Enter choose Looked-up value and specify the source table and field.)

           

          Or if you want to use a field in one table to limit the possible values for a field in the related table you can create a value list from the source field and use that in the Validation for the field in the related table.

           

          But I'm not sure exactly what you intend. Can you give an example?

          1 of 1 people found this helpful
          • 2. Re: Related Tables: How to automatically conform records in matching fields?
            dbarthold

            If I'm inputting data for a student I just want to have that data "synced" to matching fields on related tables. 

             

            I tried your suggestion and it works. 

             

            Thanks!

            • 3. Re: Related Tables: How to automatically conform records in matching fields?
              philipHPG

              Glad to hear it helped.

               

              As a follow-up, AutoEnter Lookups certainly have their place, but sometimes the same effect can be accomplished in a better (more relational) way. So rather than copying a student's name (using a Lookup) from the Student table to the CourseEnrollment table (just as an example), it would be better to store the Student ID in the CourseEnrollment table and always display the name from the Student table. You may already be aware of this, but I just thought I would mention it. Many times I've seen AutoEnter Lookups used where a relational structure would be better.

              • 4. Re: Related Tables: How to automatically conform records in matching fields?
                philmodjunk

                Where lookups can go wrong for a database is when you want a change in one table to automatically update the copy in the other. For example, if you find that you mis-entered a name and have to correct the error, you don't want to have to make identical changes in multiple tables in your solution. In such cases, having that name in one record of one table with relationship links that allow you to access/view/show/edit the name from the context of any of your related tables avoids this issue.

                • 5. Re: Related Tables: How to automatically conform records in matching fields?
                  beverly

                  except in those cases where a lookup is historical data and you don't want to have the data change.

                   

                  Take an address. A person/company can have a shipTo address (which can change). But you want to keep the address (lookup) as is for old records (where orders were shipped). You can change the address and new orders will lookup the current shipTo address.

                   

                  OK So this topic may not be about historical lookups, but the point needed to be made.

                  beverly

                  • 6. Re: Related Tables: How to automatically conform records in matching fields?
                    philmodjunk

                    Thanks Beverly. That's exactly the opposite side of the coin here and why we have lookups in databases.

                     

                    The classic example is an invoicing system where you enter a catalog number and the item price is copied into the line item record. If the value were not copied, a future price change would automatically update past invoices and screw up data that should not be altered.