8 Replies Latest reply on Jan 4, 2012 9:25 AM by compyk

    Help with Validation

    compyk

      Title

      Help with Validation

      Post

      I have deednumber and project name fields.

      I need to make sure there is not the same deednumber on the same project but I am not sure how to validate them.

      The next validation help is with State - county - municipality fields also have to be unique. There might not be a municpality required so how do I check for duplicates on municipality/county level.

        • 1. Re: Help with Validation
          philmodjunk

          If you need the combined values of two or more fields to be unqiue in combination, not as individual fields, do this:

          Add a text field to the table and define it with an auto-enter calculation such as:

          DeedNumber & "|" & ProjectName

          Clear the "do not replace existing values.." option.

          THen set a unique values validation on this new field.

          • 2. Re: Help with Validation
            compyk

            not going to work. I need them to enter deed number and after they do check if there is a deed number already that has a project id that matches. I think I need a self join but I am still new to how they work.

             

            • 3. Re: Help with Validation
              philmodjunk

              Project ID or Project Name? An auto-entered serial number in a projects table is a much better option here for uniquely identifying each project.

              Can the same deed be part of more than one project (what I assumed from your first post)?

              If not, why not have entering the deed number look up the associated project number? (best validation is to design the interface to preclude the error in the first place.)

              • 4. Re: Help with Validation
                compyk

                We have the deed table which has

                deedid - unique id key

                deednumber - the number of the deed (ex deed 3 of project December 2012 A)

                project id - the id of the project the deed belongs too.

                 

                the project table :

                project id - unique id key

                Project name - the name of the project (December 2012 A)

                 

                The project table merely keeps track of the deeds that go with each project.

                             December 2012 A

                                         Deed 1

                                         Deed 2

                 

                There can be hundreds of "deed 2's" but only 1 "deed 2" that belongs to project December 2012 A

                I need when they enter deed number 2 in the deednumber field for it to check whether there is a any deed 2 in the deeds table with the same projectid in the record they are editing.

                EX (projectid 1 deednumber 2 = projectid 1 deednumber 2).

                I am unfamilar with the validation calculation needed

                 

                One to many relationship (1 project with many deeds)

                Deed table                project table

                deedid                     projectid

                projectid                 

                deednumber

                • 5. Re: Help with Validation
                  philmodjunk

                  Deed 1, Deed 2?

                  Does this mean that you have separate fields to list deed ID's in the project record?

                  What is the purpose of using separate fields in one record like this? Is there a difference between Deed 1 and Deed 2 besides the different Deed ID numbers?

                  I had assumed that "deed number" was the public records identifier for a particular deed, but now it appears to refer to a specific field in a specific record of Projects? That seems a very odd structure here so I am asking lots of questions before I proceed with any suggestions.

                  • 6. Re: Help with Validation
                    compyk

                    I have deedid as the unique primary key. It is not on the form and is auto enter serial to keep it unique.

                    the field deednumber is used to identify the number of deed on the project. There will be hundreds of the same number in this field. Each project restarts at 1.

                    Project: September 2011 A

                                          This is a record on the projects table and in the field "projectid", it will have a value of 1.

                    In the deeds table there will 50 records that will have projectid value of 1

                    So my tables would look like this

                    Table Projects    (2 records)                             

                    Fields:  projectid     name

                    values:      1            September 2011 A

                                     2           October 2011 A

                     

                    Table Deeds (50 Records)

                    Fields:      deedid        deednumber       projectid

                    values          1                    1                     1

                                       2                    2                    1

                    ..... jump down 50 records

                                      52                  1                       2             (deed 1 of the october project)

                     

                    the field deedid is unique the deednumber field will have duplicates.

                     

                    What I want to validate is to make sure there is not a record already with the same deednumber and projectid. 

                     

                    • 7. Re: Help with Validation
                      philmodjunk

                      But your deed number is really a field number that identifies a field in the project record?

                      I just don't follow the logic to that and thus am unable to make a suggestion until I understand the reasons for that approach.

                      Still haven't answered this question:

                      What is the purpose of using separate fields in one record like this? Is there a difference between Deed 1 and Deed 2 besides the different Deed ID numbers?

                      • 8. Re: Help with Validation
                        compyk

                        Solved it myself.

                        I needed to make a self join table occurence with a deed table.

                        Then I needed to match deednumber  and projectid keys

                        Then validate for count(JOINTABLE::projectid) < 1.

                         

                        if there was more that 1 matching projectid with the same deednumber it threw an error correctly.