7 Replies Latest reply on Jan 13, 2009 9:03 AM by jfilar3

    New user needs help!

    jfilar3

      Title

      New user needs help!

      Post

      Hello. I'm fairly new to "programming" in FMP9, so please bear with me. I'm working with an inherited DB, and I am not able to completely re-do it, so I'm stuck trying to fix the little glitches. Here is what I have.

       

      The DB itself contains many tables. The main table, called "Details," holds most of the case information. There are dozens of forms the users access to send out to their clients, and each form is its own table (to allow for the storage of the MANY records). There is another table called "Forms Server" that links the relationship between "Details" and each of the form tables. The primary key between "Details" and "Forms Server" is called "Request ID." So, "Forms Server" is related to each form table through the key "Form ID" and to "Details" through "Request ID," which means "Details" is indirectly related to each form table through their connection to "Forms Server." Still with me?

       

      Ok, on the "Details" layout, there is a portal that displays some of the pertinent fields from any form associated to the current record in "Details." EX: If I create a new record for Bilbo Baggins in "Details," then send him form 1120, I can see that form in the portal. I can also click a button next to that form to open it up and see it displayed anytime in the 1120 layout. This all works swimmingly well.

       

      Here is the problem. Out of the 30+ forms in this database, 2 of them are follow-up type forms. Form 1124 and 1125, when created, are supposed to pull information from 2 fields from form 1123. EX: I send Bilbo form 1123 requesting more information. If he never replies, I send 1124 that pulls in the creation date from 1123 AND another field from 1123 (# of days). 1125 is similar, but it's used when Bilbo DOES respond, but w/o a good reason, so it's telling him that his reason doesn't hold water. Again, it needs to pull those 2 fields in from 1123.

       

      The original programmer used a very simple script to do this. Unfortunately, it was simple because it didn't work. The corresponding fields in 1124 or 1125 are left empty. Here is what WAS there:

       

      # Get Number of Days from DE1123 form

      Go to Layout ["DE1123" (DE1123)]

      Copy [Select; DE1123:: DE1123 Days]

      Go to Layout ["DE1124" (DE1124)]

      Paste [Select; DE1124:: DE1124 Number of Days from DE1123]

       

      Ok, that one obviously didn't work as it just pasted in blank info from a blank 1123 (instead of pulling the field data from the previously completed 1123). I've since tried numerous ideas from other threads on this site - mostly variations of GTRR, Set Field, etc. After a day and a half of trial and error, I'm no closer to a solution and am throwing in the towel. Please, if anyone can help me, I'd appreciate it!

        • 1. Re: New user needs help!
          Jens Teich
             First we have to find out wether it is really necessary to transfer information at all. Why not use the same field on 1123 AND 1124. Depends wether in both cases the same table and record is active.

          If this is not the case we can try to build a relationship between 1123 and 1124 table and record combination and also use the original field directly. This time via relatiionship.

          If this does not work either you have to transfer data from one field to another. Use of copy and paste is considered very poor style. In recent FM versions you can use $ variables for this purpose. Set them with SetVariable() and fill the destination field with SetField( field ; $variable ).

          Jens


          • 2. Re: New user needs help!
            jfilar3
               Thank you. I thought of the relationship idea, too, but got the error about not having more than one relational path between any two tables. It offered the option of adding another table (1123 2), but I wasn't sure if that was safe or not. I would much prefer the relational path - it's MUCH cleaner. I agree about the COPY and PASTE, hence why I was trying all those other methods. If you can guide me just a little more about the relationships, I would love to head that way (i.e. is it best / safe to create 1123 2?)!
            • 3. Re: New user needs help!
              Jens Teich
                 It is not possible to show the correct relationship without knowing much more about the data model.

              But normally it is no problem to create additional table occurrences.

              Jens

              • 4. Re: New user needs help!
                jfilar3
                   Should I send you a few screen shots? Again, I'm pretty new to this, so not sure exactly how to set it up so that the 1124 or 1125 are populated with the data from the original 1123? As it stands, the "Form Server" table is related to all of the forms through the key "Form ID" (as each form creates a unique ID when generated and stores it in the respective table, i.e. issuing form 1124 to a client creates form id 0001 and stores that in table 1124 for future retrieval). I created 1123 2 and 1123 3 and connected the relationships in 1124 and 1125. Do I need to create a particular script step now that pulls the 1123 data over? If so, can you give me an idea of what those steps should look like? Is it a GTRR step and then a Set Field? Sorry for my ignorance!
                • 5. Re: New user needs help!
                  Jens Teich
                     So you already have a connection between 1123 and 1124. You can try to use this connection without adding new TOs.

                  Simply place on layout 1124 a field 1123::fieldname and see if the correct data appears as expected.

                  Jens

                  • 6. Re: New user needs help!
                    jfilar3
                      

                    It still displays blank. It doesn't seem to go out to the previously created 1123, and instead plugs in the data from a blank 1123. I even went into the Field setup and assigned it as a Lookup, and that didn't work. I'm really at a loss.

                     

                    Is there a way to create a script that finds that original 1123, opens it up, grabs the data from those 2 fields, then closes the 1123 again? In other words, is it possible that I have to have the original 1123 open to have those merge fields grab the info for the 1124 layout?

                     

                    Thanks again for your patience and advice!

                    • 7. Re: New user needs help!
                      jfilar3
                         Ok, I finally got it fixed. I still had to use the copy / paste feature to get things to work, but your help, Jens, got everything connected correctly. Thanks again!