6 Replies Latest reply on Mar 8, 2012 11:26 AM by philmodjunk

    How to refer to a field from one table to the same field which is in another table?

    sapa88

      Title

      How to refer to a field from one table to the same field which is in another table?

      Post

      Hi,

       

      im having a main table called "project details" and the primary key of that table is "contract no" which acts as the foreign key for all other tables to create relationship with the main table(project details).

       

      i have another child table called "Drawing Status" which includes some of the same fields which are in the "project details" table and those fields are such as "Drawing No" , Drawing Title". and this table includes "_fkcontract no" as the foreign key to connect with the "project details" table.

       

      what i want to do is when i enter a value in "_fkcontract no" in the "Drawing Status" table, it should check with the "contract no" in the "project details" table and if there is a matching "contract no" then it should automatically fill the fields in the "Drawing Status" table which i mentioned above ("Drawing No" , "Drawing Title")

       

      kind regards,

        • 1. Re: How to refer to a field from one table to the same field which is in another table?
          philmodjunk

          Why do you need the same data entered into two different tables?

          Assuming this relationship:

          ProjectDetails::Contract no = Drawing Status::_fkcontract no

          Then your Drawing NO and Drawing Title fields in Drawing Status can be set up with looked up value settings to copy the data from those fields whenever the value in _fkContract no is entered and/or changed.

          BUT

          You can also remove the Drawing No and Drawing Title fields from the Drawing Status table and just add the fields of the same name from ProjectDetails to your Drawing Status layout or portal and you'll see the same result without needing to store the same data in two tables.

          The difference will be in what hapens should you modify the data in the fields in ProjectDetails. If you use the extra fields with the looked up value setting, the data in Drawing Status will not automatically change when you edit the data in ProjectDetails unless you take steps to deliberately trigger the update.

          • 2. Re: How to refer to a field from one table to the same field which is in another table?
            sapa88

            Hi,

             

            Thanks for this but the issue is a contract no can have plenty of drawing no's. so how to is there a way to get this done using looked up values ? something like when i enter the contract no it will show the related drawing no's and its drawing titles.

             

            regards,

            • 3. Re: How to refer to a field from one table to the same field which is in another table?
              sapa88

              Hi,

               

              if i can make it more clear. this is the calculation which i tried doing. but its not working or giving  the output im expecting but atleast it will give you a clear idea of what i am trying to do. please check and help.

               

              If ( Exact ( IsValid ( Drawing Register::DrawingNo ) ; IsValid ( Drawing No ) ); "true"; "Invalid Drawing No" )

               

              kind regards,

              • 4. Re: How to refer to a field from one table to the same field which is in another table?
                philmodjunk

                Thanks for this but the issue is a contract no can have plenty of drawing no's

                But where do you store the information unique to each drawing? Do you store it in Project Details, Drawing Status or some third table not yet identified by you. A contract number only identifies the project, not the specific drawing so it looks like we need a different relationship here that will match up by a Drawing Number instead of the Contract Number.

                Is it possible that you have this set of relationships?

                Projects----<Project Details----Drawing Status

                If so, is there only one drawing for a given Project Details record?

                • 5. Re: How to refer to a field from one table to the same field which is in another table?
                  sapa88

                  contract no is in the project details table and all the drawing details are in a different table called drawing register. and the contract no is included in this table as a foreign key to create a relationship with the project details table. and one project can have plenty of drawings. and for each drawing no there is a drawing title aswell. so the drawing  title is always equal to that particular drawing title aswell.

                   

                  project details table


                  contract no

                  contract name 

                  etc

                   

                  Drawing Register

                  _fkcontractno

                  Drawing No

                  Drawing Title

                  etc

                   

                  Drawing Issue Sheet

                  _fkconractno

                  Drawing No

                  Drawing Title

                  Drawing Issued Date

                   

                  Drawing Status Report

                  _fkcontractno

                  Drawing No

                  Drawing Title

                  Drawing Issued Date

                  Drawing Status

                   

                  If i enter a Drawing No and a Drawing Title for one drawing that particular entry should be the same all the other table which contains these two fields. by using the exact(original text,comparison text) function i managed to validate the Drawing No which is entered in the Drawing Register 

                  for example i enter a particular drawing details in Drawing No and Drawing Title in Drawing Register. and next time the user wants to issue that particular drawing by entering the fields in the Drawing Issue Sheet. so by using the "exact" function i managed to give an error message if the user enters a Drawing No which is not in the Drawing Register(But i dont know how to validate it in a way that it checks if that drawing is in the exact contract no???). and i did the same for the contract no aswell which checks whether that particular contract no exist in project details table anf if not it gives an error message

                   

                  So likewise i want to do the same with Drawing Title And Drawing Issued Date

                  when the user enters the Drawing Title in Drawing Issued Sheet it should check if that Drawing Title refers to a particular Drawing No in a contract no. and then for drawing title it should check the contract no and get the drawing no of that contract no and refer to the drawing title in drawing register , refer to the Drawing  Issued Date in Drawing Issue Sheet and then automatically fill in Drawing Issue Date , Drawing Title in "Drawing Status Report" once i enter the contract no and drawing no

                   

                  this is the exact scenario and exactly what i want to do in detail.

                   

                  im really sorry for been a pain. it would be a great help if you can figure this out for me.

                   

                  thanks.

                  • 6. Re: How to refer to a field from one table to the same field which is in another table?
                    philmodjunk

                    I see you've started a new thread (this one arrived at 4:19 am my time and got buried by other posts) and I've responded there.