5 Replies Latest reply on Jan 6, 2014 11:48 AM by philmodjunk

    Data Entry in Layout



      Data Entry in Layout


           I have created several tables of static data (product, client, analyst etc) and then have them related to another table (Sales Coverage) via a field in each called ID (which is just a serial id for records).  

           sample:  https://www.evernote.com/shard/s192/sh/481e5a44-08ac-4fb5-96b2-d45d6a42a2e1/19c4988f37b7b283c367df4bf3c5bf57

           Sample relationship - https://www.evernote.com/shard/s192/sh/e4544f98-d9b4-4918-8a4c-37d732cea3f1/6e065bdb0fab828caab2238d86344a6d

           I then created a layout that pulls various elements from the tables to display the actual values/attributes.  I would like to be able to use the layout for data entry/updates, but as it is set up now, if I change a data point in the layout (for example, change the frequency) it updates all records in that field and overwrites the static table "Frequency".  

           How would i configure this set-up so the layout will only update the "Sales Coverage" table and not the static tables that are related to it.  

           Any help would be appreciated (i am very new to this, so please excuse if this is a very basic question) Thank you

        • 1. Re: Data Entry in Layout

               I would assume that ID in each of the fields other than Coverage are defined as auto-entered serial numbers? And the matching fields in Coverage are number fields?

               It would appear that your database is doing exactly what you set it up to do, it's just not what you want it to do.

               You have multiple one to many relationships with Coverage on the "Many" side of each relationship. That means that any record in Coverage will link to one one only one record in each of the other tables. But any one record in those other tables can link to any number of records in Coverage.

               Thus, if you have a record in Coverage with 3 in the Client ID field, and you edit a client name field, all coverage records with 3 in the Client ID field will see this change. But records with any other value in the Client ID should not see this change.

               If you are seeing the same value no matter what value is in the matching ID field in Coverage, I'd check that field's storage options in Manage | Database | Fields to make sure that it does not have global storage enabled.

          • 2. Re: Data Entry in Layout

                 Thank you.  This is very helpful.  Your description of the current set-up is correct.  I checked the serial ID field of "Coverage" and it does not have "Global Storage"enabled.  Are there other options I should consider or are there design changes I need to make?  For example, should I have the serial IDs not repeat? Appreciate your help.  Thank you

            • 3. Re: Data Entry in Layout

                   I don't know that you need to make any changes. I can see how your DB currently works, but can't tell you what behavior needs to change in order for it to work the way that you want it to. You'll need to describe the issue in more detail.

              • 4. Re: Data Entry in Layout

                     OK.  Thanks.  What I am trying to do is set up the coverage layout to allow me to add new entries or update existing ones without affecting other records (which is happening now because updates affect the static tables).  I can provide more details with screenshots if that is helpful

                • 5. Re: Data Entry in Layout

                       I will assume that "entries" refers to records in Coverage.

                       But if two records in Coverage link to the same record in Clients, changing the data in a field from Clients SHOULD change this for both records. That's the inherent nature of the database design that you've used.

                       There are indeed cases where this is not desirable. In an invoice, for example, most businesses want to see the customer contact info that was current at the time the invoice was created. If they later get an update for this customer's info, they want it to show on new invoices but not the previously created ones. (Customer calls up: "why didn't Order #45678 arrive?", Business: "I see that the shipping address on that invoice is to 333 Main Street....")

                       If that's what you need here, then you should add fields to Coverage that copy data from fields in the related tables. This can be done by using field options to specify auto-enter calculations or looked up value settings. If you do that, then a subsequent change to data in a related table will not modify the data in existing records in coverage, only new Coverage records will show the change.