14 Replies Latest reply on Feb 28, 2012 3:07 PM by brettJames_

    How would I most likely want to setup a Revision field for Project?

    brettJames_

      In the application I am building Projects need to be able to have Revisions. So when a user is on a specific Project's detail view I want them to be able to switch to a different Revision of that same Project from a popup menu (ex, Select Revision: 1, 2, 3, 4). The only thing we need to know about a Revision is the "Revision Number and Date." I'm assuming a project_REVISION would have its own unique ID.

       

      Is the proper way to go about this by making a completely separate table called Revisions and create a One to Many relationship? Or would something like a Self-Relationship be what I need to do there?

        • 1. Re: How would I most likely want to setup a Revision field for Project?
          Mike_Mitchell

          Hello, brettJames.

           

          If you want users to be able to view a history of revisions, then yes, I would recommend a separate table.

           

          Mike

          • 2. Re: How would I most likely want to setup a Revision field for Project?
            Stephen Huston

            Also consider making the fields of the Projects table which match fields in the Revisions table part of that related table, accessible thru the Project record as Revision 0 (Zero).

             

            That way you will have a narrower table for the Project record (fewer fields) and you can run reports from the Revisions table which can include the same fields from the original Project entry within that same table.

             

            You just put a single-row (showing 1/first related record) and place the needed fields for the Project there, with the data actually residing in the realted table.

             

            [ I wish that had been done with a project I inherited years after it went live, as I have constant need to report revisions/changes to the original order/project with the option to include the original. Our long-term work around has been to do mutliple imports from both tables into a combined reporting table. Had the matching fields of the Project been in the table with the changes, no imports would be needed, and a filtered portal in Projects or a simple Find in the Changes table could replace a huge reporting process. ]

            • 3. Re: How would I most likely want to setup a Revision field for Project?
              brettJames_

              Thanks for the great reponse Steve.

              Although, I am a little confused of how you are saying to set my tables and fields up. Below is what I think you possibly meant:

               

              Create 2 tables. Project and Revision.

              Add project specific fields to the Project table and add Revision specific fields to the Revision table.

               

              Then create a join table called ProjectRevision even though it is not a Many to Many relationship. (A Project can have many revisions and a Revision can be related to only 1 Project, unless I am wrong about this). Then finally, in the join table make sure that I include any fields that are the same in the Project table and Revision table. I believe this last part is the key thing that you were telling me to do.

               

              Below is an example of what I think you are saying:

               

              PROJECT

              __kpProjectID

              ProjectName

              DateCreated

              DateModified

               

              REVISION

              __kpRevisionID

              RevisionNumber (ex, 1, 2, 3, 4)

              RevisionDate

              DateCreated

              DateModified

               

              PROJECTREVISION

              _kfProjectID

              _kfRevisionID

              DateCreated

              DateModified

               

              Since nothing is the same I didn't add any fields to PROJECTREVISION, but if something comes along that is the same I will add it. Or do you mean within the edit relationships menu instead of just matching the primary and foreign keys I should match everything between PROJECT AND REVISION.

               

              Lets say I want to access revisions from projects through table occurences.

               

              Instead of a join table shouldn't I just be creating a table occurence like: project_REVISION (with the occurence being the Revision table)

              Or lets say I am supposed to create the join table would I make a table occurence like: project_revision_PROJECTREVISION (with the occurence being the ProjectRevision table).

               

              Once again thanks for all of your help.

              • 4. Re: How would I most likely want to setup a Revision field for Project?
                Stephen Huston

                Hi Brett,

                 

                I would simplify this a bit further, making it just 2 tables: Projects & Revisions.

                 

                In the Projects screen, the Revision-specific fields would be single-record portal fields to the first matching record in Revisions. You can even set the relationship as allowing creation, and the record will generate when you enter something into any on of those fields.

                 

                You may want a special TO for this instance of the Revisions table, setting it up to include the fKey AND a revision number which matches a constant "0" in the Projects table, thus numbering the first revision as the fields are entered.

                 

                So you would have a relation from Projects to Revision based solely on the pKey from Projects for matching ALL revisions, and special relationship to another Revison TO using both the pKey and the Zero-constant to isolate the initial record for each project in the Revisions table.

                 

                You can then do all your reporting from the Revisions table, and you maintain a one Project to many revisions relationship with one special TO that is 1-to-1 for the initial values used in the Revision-level fields for the Project itself. No need for the join table at all.

                 

                Stephen Huston

                • 5. Re: How would I most likely want to setup a Revision field for Project?
                  brettJames_

                  Great! I understand everything your saying now except for revision number which matches a constant "0"

                   

                  So two tables "Project" and "Revision"

                   

                  Project

                  __kp_ProjectID

                  ProjectName

                   

                  Revision

                  __kp_ProjectID

                  _fk_ProjectID

                  RevisionNumber

                   

                  I understand setting up the Revisions portal within my Project Detail View or possibly a Revision Popup menu so Revisions can be added to a project from the Project Detail View.

                  Could you please explain further what you mean by setting my RevisionNumber field to match a constant "0." Do you just simply mean make sure I make the ProjectID increment like: 1, 2, 3, 4, 5 and have it match _kf_ProjectID just like most relationships.

                   

                  Sorry for bugging ya, This should be the last but of info I need to get started.

                  • 6. Re: How would I most likely want to setup a Revision field for Project?
                    Stephen Huston

                    Add a field in the Project table (in place of all that stuff you can now offload into the Revisions table) with a calculated result of Zero ("0"). Use it as part of the relationship match to the Revisions table for the revision number field there to return just the first revision (0). (You could number your revisions starting with 1 instead of 0 if you prefer; in my mind it makes it clearer to make it 0 since it predates the first real-world "revision".)

                     

                    Since this relationship for the special TO of Revisions only needs to work from the Project side of the relationship, you may be able to get away with making it global storage instead of indexed. (Relations from the the main TO of Relations can read Projects using only the _kp_ProjectID since they don't need to be limited to the one revision used in the Project layout.)

                     

                    You might name this calcualtion field in the Projects table something like _kp_Project_K0 (math geeks like to use K for constants).

                    1 of 1 people found this helpful
                    • 7. Re: How would I most likely want to setup a Revision field for Project?
                      brettJames_

                      Thanks a lot buddy. Now I understand.

                      • 8. Re: How would I most likely want to setup a Revision field for Project?
                        brettJames_

                        So Sorry, I went to do it and kind of got stuck. One final thing just to make sure I got this right. Could you look at my Table and TO setup below and reply in the same fashion with anything that I am doing wrong. Dont feel obligated to and No rush.

                        PROJECT

                        __kp_ProjectID

                        __kp_Project_K0 (calulation field with the value of "0")

                        ProjectName (field for naming the "OVERALL PROJECT" regardless of revisions) Do I want this in this table?

                         

                        project_Revision - TO (For showing data for all of a project's revisions)

                        __kp_RevisionID

                        _kf_ProjectID (Match to ProjectID for the relation)

                        _kf_Project_K0 (Do nothing for this TO, not sure if I need this field)

                        RevisionNumber (Do nothing with for this TO)

                         

                        project_RevisionK - SPECIAL TO (For showing data for a project's individual revision)

                        __kp_RevisionID

                        _kf_ProjectID (Match to ProjectID for the relation)

                        _kf_Project_K0 (Not sure if I need this field, but if so match it to _kf_Project_K0)

                        RevisionNumber (From what you said I believe this is the field I am going to match with the field _kf_Project_K0) So the value of this will always be 0.

                        • 9. Re: How would I most likely want to setup a Revision field for Project?
                          Stephen Huston

                          See my notes re your list of tables and fields:

                           

                          PROJECT

                          __kp_ProjectID

                          __kp_Project_K0 (calulation field with the value of "0")

                          ProjectName (field for naming the "OVERALL PROJECT" regardless of revisions) Do I want this in this table?

                           

                          Yes, this is where you want the Project-specific stuff such as ProjectName.

                           

                          project_Revision - TO (For showing data for all of a project's revisions)

                          __kp_RevisionID

                          _kf_ProjectID (Match to ProjectID for the relation)

                          _kf_Project_K0 (Do nothing for this TO, not sure if I need this field)

                           

                          No you don't want a K0 field in the revision table at all, just the revision number to match to K0 in Project for this TO.

                           

                          RevisionNumber (Do nothing with for this TO

                           

                          not used in the main TO.

                           

                          project_RevisionK - SPECIAL TO (For showing data for a project's individual revision)

                          __kp_RevisionID

                          _kf_ProjectID (Match to ProjectID for the relation)

                          _kf_Project_K0 (Not sure if I need this field, but if so match it to _kf_Project_K0)

                           

                          No you don't need this field in Revisions table at all; using the Rev# to match to the K0 field in Projects for this TO-relationship only

                           

                          RevisionNumber (From what you said I believe this is the field I am going to match with the field _kf_Project_K0) So the value of this will always be 0.

                           

                          The value of the RevisionNumber field will set to Zero when creating a revision via the portal-fields for the special TO.

                           

                          Those Portal Fields need to be on the Project screen as fields from the special revision TO in a portal set to show ONLY the first row of related records. Thus, when data is entered into one of them, it will created Rev 0 automatically.

                           

                          It's a bit out of the norm for how FM is usually setup, but it works and simplifies the data structure when you get it running. You want the Rev 0 creation portals set to be as invisible as possible so it looks like you are using normal fields.

                           

                          Your graph will also want the regular Revisions linked to Projects via the Project pKey alone, not any Rev# or Konstant fields.

                          • 10. Re: How would I most likely want to setup a Revision field for Project?
                            brettJames_

                            Alright there we go, it is all setup how it should be. I understand that when a revision is created through the Special TO's Portal from the PROJECT page 0 is automatically added for the RevisionNumber. I am a little bit confused of how and when RevisionNumber 1, and RevisionNumber 2 would eventually be created if a user needs to create more then just the original "0" Revision.

                             

                            I guess we can think of it in a real world situation. Someone creates a new project called "Pine Hill Apartments" (automatically this project will be set as Revision "0"). Then lets say months down the road all new plans come in for the project (there is now updated information that we don't want stored with the original revision "0" unlike the ProjectName which we do want to stay the same between revisions) so the user would have to create a new revision of the same project "Pine Hill Apartments" Revision "1." What table allows this to happen in the setup that you helped me come up with? Revision 2, Revision 3, Revision 4, and so on. Would it be the regular TO project_REVISION that allows the user to type a revision number in or choose from a value list anything other then "0"

                            • 11. Re: How would I most likely want to setup a Revision field for Project?
                              comment

                              brettJames_ wrote:

                               

                              there is now updated information that we don't want stored with the original revision "0" unlike the ProjectName which we do want to stay the same between revisions

                               

                              What fields would you like to have in the Revisions table? IOW, which attributes of a project do you foresee as changing with the revisions - as opposed to the project name that will remain constant?

                              • 12. Re: How would I most likely want to setup a Revision field for Project?
                                brettJames_

                                Yeah things like ProjectName have to stay constant throughout revisions so these fields would all be created in my PROJECTS table I am assuming.

                                Anything about the project that will vary between revisions will be fields within my Revisions table correct?

                                 

                                I just need to find a way to allow the user to create a new revision from the PROJECTS view.

                                • 13. Re: How would I most likely want to setup a Revision field for Project?
                                  comment

                                  brettJames_ wrote:

                                   

                                  Anything about the project that will vary between revisions will be fields within my Revisions table correct?

                                   

                                  Yes.

                                   

                                   

                                  brettJames_ wrote:

                                   

                                  I just need to find a way to allow the user to create a new revision from the PROJECTS view.

                                   

                                  Why is this so difficult? You could use a portal, or give the user a button to click and a script to do it for them.

                                  • 14. Re: How would I most likely want to setup a Revision field for Project?
                                    brettJames_

                                    I understand how a new Revision would be created for a project. I also am very familiar with how database relationships work. This was all just confusing to me for some reason. I think I finally figured it out. I was getting a little confused with the difference between Revision "0" and all of the other Revisions. Now I understand that Revision "0" and the Special TO that Stephen helped me create helps me always reference the original information of the project. Then from here Revision 1, Revision 2, and so on will be easy to reference just through the regular Revision TO.