5 Replies Latest reply on May 13, 2010 2:41 PM by philmodjunk

    Creating a New Record using a complicated script

    xxx202xxx

      Title

      Creating a New Record using a complicated script

      Post

      I have 3 tables in this example that I am seeking help with.  They are as follows;  CM (client mgmt), AM (asset mgmt) and AH (asset history).  All 3 tables are related to each other.  CM relates to AM by the clients first and last name.  AM is related to AH by an  ID#.  CM is related to AH by first and last name.

       

      If I have a client named Jane Doe and she was issued a laptop (L-039) on 5-5-05, returned the laptop on 5-5-06.  I want the AH table to reflect a record showing that Jane was assigned L-039 laptop during the time mentioned by creating a record in that table noted by an automatic AH record number.

       

      At the same time I want the laptop (L-039) to stay in the AM table, losing only the client's name assigned to it (Jane Doe) so that I may be able to re-issue it out to John or Paul Doe.

       

      So. . .with that being said here are the particulars;  I have a CM layout that has a portal from the AM table that displays both (::issued date//::returned date) fields on a row along with other important fields such as, (::ID, ::Type, and ::name) for the assets assigned to the Jane.

      I want to be able to enter a (::returned date) in the return date field within the AM portal on the CM layout, thus causing a chain reaction of:

       

      1. creating a new AH record in the AH table
      2. copying all the key fields associated with that portal row and pasting that information into matching fields on the AH table
      3. erasing the clients name from the asset record on the AM table leaving Laptop (L-039) in the table but without Jane's name associated.
      4. and returning to the original CM Layout after being prompted with a note asking "Are you sure you want to make these changes at this time?" 

      5. "Yes or No" Yes will take you back to the CM Layout with all the changes made and No will return you back to the CM layout with no changes made.

       

      WOW!, I hope this is clear because I think I have just confused myself.


        • 1. Re: Creating a New Record using a complicated script
          philmodjunk

          I suggest restructuring your tables and relationships to be:

           

          CM----<AH>----AM

           

          Put a portal to AH on your CM layout and include fields from AM in the portal. You date issued and date returned fields should be defined in the AH table.

           

          One additional note, linking your tables by first and last name is not the best option here. Imagine what happens when Jane Doe marries John Smith and changes her name to Jane Smith. You'd either have to continue using her previous name in the database or go to the trouble of finding all related records and updating their first and last name fields. You'll have similar trouble if you enter a person's name in the system and then later discover you mis-entered it and have to enter a corrected name.

           

          A more detailed look at your relationships:

          CM::ClientID = AH::ClientID (enable creation of related records for AH)

          AM::AssetID = AH::AssetID (enable creation of related records for AH)

          • 2. Re: Creating a New Record using a complicated script
            xxx202xxx
            Sorry it took so long for my reply but it took some time adding a unique client ID and making sure the change in relationships did not cause any problems. 

             

            I have everything set up according to your example with the exception of the AH portal on the CM layout having AM fields.  When I tried setting it up that way I was getting duplicate data for every AH entry.  To alleviate that I decided to create tabs, separating the two portals.  AM on Tab 1 (represents current assigned assets) and AH on Tab 2 (representing assignment history).

             

            Thanks for that bit of important info. that makes better sense.  Now I am ready to proceed.  How do I finish.  Please view original post.

            • 3. Re: Creating a New Record using a complicated script
              philmodjunk

              I have everything set up according to your example with the exception of the AH portal on the CM layout having AM fields.  When I tried setting it up that way I was getting duplicate data for every AH entry.

               

              That shouldn't be the case. Can you explain what you mean by "duplicate data for every AH  Entry"? Is this something you saw in the portal or in the AM table separately?

               

              You might want to upload a copy of your file to a file sharing site and then post the link to it here so I and others can take a look at it. You can upload a clone if you have real contact data already entered into your file.

              • 4. Re: Creating a New Record using a complicated script
                xxx202xxx

                I would upload but I have sensative info that I cannot copy.  I will try to duplicate it the best way I can.  Here we go.  Under the portal (AH) on the CM layout the fields are:

                 

                Event:    ID#:   Asset Type:    SN#:     Issued Date:     Returned Date:    (Event: represents the AH table's record identifier)

                (AH)       (AM)        (AM)            (AM)          (AH)                     (AH)            ---->(represents the table the field is being pulled from.

                 

                (I failed to mention on my original post that the (AH table) only has 1 record thus far for only 1 asset was returned.  This will definitely grow as time goes on.  That is why I need to create new records when the return date is populated.)

                 

                 

                The AH and AM tables have the same data with the exception of the Event: (AH table) and other Asset only information (AM Table) that I did not list.

                 

                When I mentioned duplicate data, my portal was populating two rows for the same data.  My example is listed below.  Sorry for not inserting field data.

                 

                Row 1 ----->   Event:    ID#:   Asset Type:    SN#:     Issued Date:     Returned Date: 

                Row 2------>                  ID#:   Asset Type:    SN#:

                 

                I hope this helps.  

                • 5. Re: Creating a New Record using a complicated script
                  philmodjunk

                  Looking at your example it looks like you have two Asset History records that have the same ID# entered. This would give you two portal rows that list the same asset but would not list the same asset history data and your example appears to show that those fields are blank in the second row so that fits. You should be able to simply delete the second row or enter/select a different ID# in that row to log issuing a different asset to that individual.

                   

                  As to uploading a file with sensitive data to a share site, don't. Instead, use the save a copy as function and select the clone option to save your copy. This copy will be empty of all data and thus should not contain any sensitive data. If you have layout text that is sensitive, simply delete this text from any of your layouts (you can delete the other layouts all together. Now you have a copy that should be devoid of any sensitive information.

                   

                  You can also use the same method to upload screen shots if that helps. (You can edit your screen shots if needed to hide any sensitive data that's visible.)

                   

                  Note: once you have this working, you can specify either a sort order on the portal or set up a filtered version to hide the AH records for assets that have been returned.