8 Replies Latest reply on Feb 14, 2017 8:21 AM by philmodjunk

    Multiple users creating duplicate records

    marcm

      I have an issue with multiple users creating records and it duplicates my record numbers.

       

      I have 2 tables. Table one is the entry table and the records are displayed in a portal.

      Table 2 holds the records.

      User click the New Records button in table on is sets the Key# filed from table 1 in a variable and creates the records in table 2 , sets the key # field in table 2 record with the Key# and creates a sequence # based on the number of found records.

       

      To get the sequence # I find the records, set a variable to the number of found records and set the sequence # in the new records to the variable.

       

       

      The records are then displayed in a portal in table 1.

       

      The issue is if 2 users click the new button in table 1 at the same time or within a second or 2 it duplicates the sequence # in table 2 on both records.  The users will be working at the same time. Not an issues if there are a few seconds between clicks but users are in different locations and will be using this frequently.

       

      any ideas?

       

      I tried putting multiple commit records steps between script steps...but didn't work.

       

      thanks in advance,

        • 1. Re: Multiple users creating duplicate records
          coherentkris

          Calculated primary key values based on found records?

          This is the problem.

          Each record(row) in a table needs to have a guaranteed unique, not empty primary key to identify the row.

          This condition needs to be enforced with validation to prevent invalid primary key values.

          You should set up your primary keys with either get(UUID) or auto enter serial number and set the validation options to unique and not empty.

          Otherwise you will continue to have data collisions.

          A user should never be able to alter key value and, most of the time, they dont even need to see it..

          • 2. Re: Multiple users creating duplicate records
            marcm

            The seq# comes from the found records not the key number. The Key is set in with an auto enter serial number in table 1. It the sequence numbers that are duplicating. The sequence # is just so I can reference which item it is in the key record. The sequence is just the records within the key records

            • 3. Re: Multiple users creating duplicate records
              coherentkris

              what do you mean "The sequence # is just so I can reference which item it is in the key record"

              Whats the difference between key record, sequence number and primary key?

              What are you trying to capture with this system? What does it do? Inventory management? financial transactions?

              Maybe you should post your file?

              • 4. Re: Multiple users creating duplicate records
                marcm

                It's an inspection program. The key field is created for a project. The project then is inspected. In table 1 iso the projects and table 2 is the inspection records. Each inspection record has the key number from the project. The problem is each inspection record needs to be labeled with a sequence number.

                Ie....project table -  project key # 999

                        Project table project key # 777

                 

                Inspection table project key 999 seq 1

                Inspection table project key 999 seq 2

                 

                Inspection table project key 777 seq 1

                Inspection table project key 777 seq 2

                 

                The problem is if 2 users create a sequence on the same project within a few seconds of each other the seq is the same number.

                Inspection table project key 999 seq 2

                Inspection table project key 999 seq 2

                • 5. Re: Multiple users creating duplicate records
                  coherentkris

                  If project can have zero or more inspections then:

                  Projects (table)

                  project_pk

                   

                  Inspections (table)

                  inspection_pk

                  project_fk

                  sequence (auto enter calc field)= project_fk & "-" & inspection_pk

                   

                  no?

                  • 6. Re: Multiple users creating duplicate records
                    marcm

                    The sequence number has to be 1, 2, 3, etc per project

                    Edit:

                    I mean per inspection per project

                    Project 999 seq 1

                    Project 999 seq 3

                     

                    Project 777 seq 1

                    • 7. Re: Multiple users creating duplicate records
                      coherentkris

                      In this case i would script the process of creating inspections from a project record.

                      Set the inspection sequence number field validation to unique & not empty.
                      From the projects layout user ciick button>user is taken to an inspection data entry device>user performs an action to confirm and save the entry. The script calculates the next sequence number and puts it into the sequence field, commits the record, and responds appropriately to failure of validation.

                      1 of 1 people found this helpful
                      • 8. Re: Multiple users creating duplicate records
                        philmodjunk

                        You might set up a field that auto-enters a time stamp.

                         

                        When displaying the records, sort them by the times stamp field.

                         

                        Then use  the layout symbol for record number added from the insert field to show a sequence number on each record.

                         

                        There are limitations to this approach--the value will be correct only when all records for a project (and only that project) are in the found set (or a portal) and they must be sorted into correct order.

                         

                        There are also ways to use a running total summary field to number them in similar fashion, but now you can list records from multiple projects and each set can be numbered with the first record starting at 1.

                        1 of 1 people found this helpful