1 2 3 Previous Next 132 Replies Latest reply on Apr 17, 2012 4:36 PM by philmodjunk

    How to refer to each record from same field from different tables

    sapa88

      Title

      How to refer to each record from same field from different tables

      Post

      This is the table structure i have

       

      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

       

      Programme Report

      _fkContract No

      Drawing No

      Due to Commenced On

       

      Drawing Status Report

      _fkcontractno

      Drawing No

      Drawing Title

      Drawing Issued Date

      Due to Commenced On

      Issued Status

      Returned Status

       

      The scenario is. first of all a "contract no" in project details table is created. and all the other tables are linked using contract no as the foreign key. and the drawing register is used to create details of a drawing for a particular contract.

      ie : contract no "1" can have a drawing no "55" and that drawing no's title can be "aaa"

           contract no "1" can have a drawing no "56" and that drawing no's title can be "bbb"

            and there can be many contracts which includes many drawing like this.

       

      once a drawing is created its been checked whether it should be issued or not. if its been issued then the details of that particular contract no,drawing no, drawing title are been entered to the drawing issue sheet. and all these 3 fields can have many records.

       

      what i need to find out is a way to issue drawing no "5" in contract no "2" with its exact drawing title. for an example.

      if i refer to the example above.

      when i enter Contract no "1" in the drawing issue table it should validate and show me that there is such a contract no in the project details table and if not it should show a error message. and after that when i enter the drawing no it should check with the drawing resgister table if there is such drawing no under the contract no i specified. and if there is no such drawing no then it should give an error message but if there is such drawing no then it should auto-enter the drawing title of that particular drawing no.

       

      likewise this validation happends to all other tables i have mentioned above when these three fields comes in.

      please give me an idea how to do this. i tried every possible way but something goes wrong and doesnt give this output which im expecting.

       

      thanks a ton.

       

        • 1. Re: How to refer to each record from same field from different tables
          philmodjunk

          The scenario is. first of all a "contract no" in project details table is created. and all the other tables are linked using contract no as the foreign key.

          As I pointed out in another thread, you can't really link all these tables by contract no as there are cases where you need a field (Drawing no?) that uniquely identifies each drawing so that you can match to records with data specific to that drawing record.

          You seem to have more tables here than you need, but that could just be my not knowing your entire process here. Can a given drawing be "issued" more than once? If they are issued only once, I see no reason for a separate Drawing Issue table--Issuing the drawing could be managed entirely from the Drawing Register table.

          You normally do not need to define different tables in order to produce a report. (Programme Report, Drawing Status Report). Such reports can almost always be produced from layouts based on one of your existing tables instead of needing a table of their own. (Keep in mind that tables and layouts are two different things. You can have many layouts based on the same table--each designed for a different purpose.)

          • 2. Re: How to refer to each record from same field from different tables
            sapa88

            Hi, yeah i started a new one here because the previous one was a mess. i wasn't making it clear.

             

            well i read your reply and now im getting around on this matter. please excuse my ignorance im totally new to FM its been only like 3 weeks since i started using.

             

            i tried before what you have mentioned. by trying to create layouts and do the mapping in certain tables. but when ever i created a table it creates a layout so i was confused and thought may be in order to get a layout i have to create a table. i will try it out to create layout seperate. i hope when i create a layout it will not create a table inside the database.

             

            and i am completely ok in using them in one table instead of having too many tables if it works the way you say.  will you be able to put me into the correct path in making the tables and mapping so that i can continue it further? it would be a great help as per i feel totally lost in FM. so can you explain it with a small exaple if you dont mind? so that i can get it 100% clear. and i hope this way of creating tables will help me to validate/check each record and get the relavant data using auto-enter like i have mentioned in my first post.

             

            Thank you so much.

             


            • 3. Re: How to refer to each record from same field from different tables
              sapa88

              and another thing i should mention. in sql it is possible to validate this kind of situations using a querry. but why isnt this possible in FM?. like in sql it is possible to write a querry to check if the drawing no is in a particular contract and if that the drawing title is in that particular drawing which follows the exact contract no etc etc..i wonder why we cant do like that in FM. :(

              • 4. Re: How to refer to each record from same field from different tables
                philmodjunk

                My last post here seems to have diasappeared. Frown

                I need the answer to this question I asked earlier: Can a given drawing be "issued" more than once?

                The answer to that qustion tells us whether we need a third table or can just use one table for projects and a second table for drawings.

                And another question: Can a drawing be used with more than one project?

                That could also require an extra table to manage links between drawing and project records.

                i wonder why we cant do like that in FM.

                Well you CAN do that in FM though with proper database design in either Access or FileMaker, such validation is rarely necessary.

                • 5. Re: How to refer to each record from same field from different tables
                  sapa88

                  im sorry mate but i didnt see any other post before this. i have no idea how it disseapeared Frown

                   

                  Can a given drawing be "issued" more than once?

                   

                  nope. once a drawing is created with a drawing no and its drawing title(its details) then the person who checks that drawing will check it and issue it. thats it.

                   

                  Can a drawing be used with more than one project?

                   

                  for example if we make a project called "a " then it will have plenty of drawings such as "drawiing no 1, drawing no 2 etc" so under project "a" there can be only one drawing with one number. there can NOT be more than one drawing with the same number under a particular project.

                  but if you create another project called "b" then it can have "drawing no 1, drawing no 2 etc" but it is a different drawing which comes only under project "b"

                  • 6. Re: How to refer to each record from same field from different tables
                    philmodjunk

                    Ok, then two tables are all you need for what we have so far:

                    ProjectDetails-----<Drawings    (----< means one to many)

                    ProjectDetails::__pk_ProjectID = Drawings::_fk_ProjectID

                    __pk_ProjectID should be defined as an auto-entered serial number and functions as the primary key for the ProjectDetails table. Whether or not this is also your contract number depends on whether that number has any special signficance outside of this relationship. You want primary keys to be always unique, devoid of any additional meaning and never subject to change.

                    The name of a drawing should be stored in a text field in drawings and nowhere else. If you need to see it on a layout based on a different table such as Project Details, you add the field from Drawings to the other layout and the relationship linking the tables will control what is seen in that field.

                    A primary key for Drawings, __pk_DrawingID is also likely to be useful. This will also be an auto-entered serial number. It will not serve as your drawing number as you want the drawings for each project numbered sequentially starting with 1, but would be used in relationships to other fields should such relationships become useful as is very likely to be the case.

                    Drawing numbers can be auto-entered from a calculation that uses a self join to another occurrence of Drawings based on projectID so it can enter the max drawing number plus 1, but must be implemented carefully if your database is a multi-user database and it thus might be possible that two users would be creating new drawings records for the same project at the same time, or it is possible to get two drawings records with the same combination of _fk_ProjectID and drawing number.

                    There is one common way to work with multiple Drawings records for a given projectDetails record: Put a portal to Drawings on the ProjectDetails layout. This portal functions like an Access SubForm or SubReport so you can list multiple drawings records in this portal. You can edit, delete and create records directly in this portal and a button inside the portal row can be set up to take you to a layout based on Drawings in order to view and work with a more detailed view of the Drawings record. A container field in Drawings can store a reference to the actual Drawing file if you need to.

                    Drawing Issue, Programme Report and Drawing Status report can all be layouts based on the Drawings table.

                    The __pk and _fk text on these field names are simply a naming convention you can use to make primary and foreign key fields easier to identify. The underscores sort them to the top of alphabetized lists of fields with the __pk field listed first.

                    • 7. Re: How to refer to each record from same field from different tables
                      sapa88

                      Thanks a lot for this description. i will make a back up of the current DB and try this way in a new one. but if you can let me know how to use that so called "such validation is rarely necessary."


                      for this method i used "Exact ( Drawing Register::_fkContractNoID; _fkContractNo )" this function to validate the contract no and it works fine. it checks if there is such contract no and if not it returns an error message. i need to know is how we should check it when it comes to two steps of checking like the drawing no. like check first the contract no and then check whether theres this drawing no under that contract no etc. im curious to know it since you mentioned that such validation is possible in FM like in sql querry. please be kind enough if you dont mind.


                      Thank you so much. you'r a star. Smile

                      • 8. Re: How to refer to each record from same field from different tables
                        sapa88

                        validation on just one field..something like which contract no ---> drawing no(the drawing no goes under the specified contract no) is enough so that i can determine the rest and do it.

                        • 9. Re: How to refer to each record from same field from different tables
                          philmodjunk

                          This is rarely necessary as the values are auto-entered in ProjectDetails as a serial number and automatically entered via relationship or drop down list of values into Drawing records. Thus such error checking should not be needed--the interface design makes such data entry errors impossible to enter.

                          • 10. Re: How to refer to each record from same field from different tables
                            sapa88

                            Hi Phil,

                             

                            I did as you said and got only two tables with me now. one for drawing details and another one for project details.

                            well keep a side the project details table thats not much important because it only contains details of the project such as contact details etc.

                            here we go...in the drawing table, i have included all the details which are related to a drawing and details what happends to a drawing etc. 

                            now i move on to the layout side. first of all when a drawing is identified in real life, we create the drawing by giving it a drawing no , drawing title , drawing created date , and when you click out side the layout it gets stored in the drawing table under those fields.

                            and next comes the issueing part of those particular drawings. what i have at the moment is i created a layout called "drawing issue layout" when i enter the project ID in a text box it shows all the drawings with the details in a portal field which are under that project ID. now next is to issue what ever the drawing i want (which i have not implemented yet). if you can please suggest something for this.

                            what i have in mind is like if i can get a button saying "ISSUE" next to each and every drawing which appears on the portal field(hope you can get the picture?), then once i click that button the "Issue Date" field in the drawing table will be updated with the current date. so that means that drawing is issued and it can be identified because it has a issue date. or else have check boxes or something like that so that user can select the drawings which needs to be issued and have a button at the bottom of the page and once its clicked then the table will be update.so is this kind of things possible to do in FM? if so how to do it?if not can you please suggest me something else to tackle this requirement?

                             

                            because i did something like this in a similar kind of situation where i was working on C# and ASP.NET. so i thought may be its possible in FM but i dont know. its just the idea i gave. please correct me if im wrong and show me the proper way of tackling a requirement like this.

                             

                            Thanks,

                            • 11. Re: How to refer to each record from same field from different tables
                              philmodjunk

                              Both a button or a check box are possible options for this.

                              Here's the non-scripted, check box option:

                              Define the issue date to have this auto-entered calculation:

                              If ( Issued ; Get ( CurrentDate ) )

                              Clear the "do not evaluate if all referenced fields are empty..." check box.

                              Define Issued as a number field. Format it as a check box field on your layout. Use a value list with a single value: 1. After selecting this format, resize the check box field so that only the check box is visible and place the Date Issued field next to it. You can add "Issued" as layout text to your layout just above the portal positioned as the column label for this check box.

                              When you click the check box, today's date will appear in the Issue Date field. Click it again to clear it and it should automatically clear as long as you remember to clear the "do not evalulate..." check box.

                              Using a button instead of a check box field and a script, have your script perfrom this single script step:

                              Set Field [DrawingDetails::IssueDate ; If ( Not DrawingDetails::IssueDate ; Get ( CurrentDate ) ) ]

                              This will work just like the check box field, click it once and a date appears in the field. Click it again to clear it.

                              • 12. Re: How to refer to each record from same field from different tables
                                sapa88

                                thanks for this..arghh but now im having another problem. when i try to create the portal field it does not show the Drawing table in it which allows me to assign the fields which should be displayed in the portal. why is that? but i must state that i have only created the Drawing table. means only one table. which im working around all the layouts. because i want to make sure i can do this so im just taking it on a test phase. but i dont think having just one table is an issue. because i created the layout to enter drawing details and its working and it just doesnt show the Drawing table in the portal setup when i try to enter the fields i need to view.

                                • 13. Re: How to refer to each record from same field from different tables
                                  philmodjunk

                                  You would use two tables and use Manage | Database | relationships to link them like this:

                                  ProjectDetails::ProjectNumber = DrawingDetails::ProjectNumber

                                  In layout setup, select Project Details in "Show Records From". In portal setup, select "Drawing Details" in "Show Related Records From".

                                  This way, you find a ProjectDetails record and the portal will show all drawingdetails records that belong to that project.

                                  • 14. Re: How to refer to each record from same field from different tables
                                    sapa88

                                    Hi thanks a lot i will try this and see. i have a one question regarding the check box method you said.

                                     

                                    "Define Issued as a number field. Format it as a check box field on your layout" does this mean that i have to define another field called Issued and assign the check box into that?if so, do i have to set it as auto increment or just a number field? because i only have a field called "Issue date"  and is it possible to type anything for the value in the value list or do i have to type something which is in the table? and the purpose of assigning the checkbox into a value list is to make sure it supports when multiple fields are displayed? just wanted to clarify these that i got the logic correct :) ..thanks

                                    1 2 3 Previous Next