3 Replies Latest reply on Jun 15, 2015 1:53 PM by philmodjunk

    Lookup problem - changing data in looked up field alters it in the lookup database

    VincentAngeloni

      Title

      Lookup problem - changing data in looked up field alters it in the lookup database

      Post

      OK, I am trying to make a little database to write letters to patients, some of which can be handled by a standard template. I created a database for the patient information called "Medical letters" with a "match field" called 'pt template' and the lookup field is "body" (for the body of the letter). The second database is called "Patient Letter Templates" and it contains the match field 'pt template' and the 'body' field which will hold the text that I want to be sent via a lookup. It should be a lookup, because I may need to alter the body field with a few unique details for every case and I don't want that going back and messing up the template text. 

      In the managing relationships dialog, I related these fields.

      Medical letters::pt template  = Patient Letter templates::pt template

      In that same dialog, I double clicked on the schematic equal sign and get a dialog where I click off "allow changes in this database from the other database" on BOTH sides.

      Then I go to "Fields", click "Lookup" and set the lookup to look at 'pt templates' for the match, and if they do match, to place data from the "Patient Letter templates::Body" field into the Medical letters::body field. When I enter a matching value in the match field, the data lookup works just fine, but if I alter it (as I might have to add some details which are unique to that patient), any changes I make are altering the original template in the Patient Letters templates database. How the heck do I prevent this, or what am I setting up incorrectly? What did I miss?   Thanks in advance...

       

       

       

        • 1. Re: Lookup problem - changing data in looked up field alters it in the lookup database
          philmodjunk

          It would appear that you are editing the wrong field. Either that or looking at the looked up date field and mistaking it for the data in the look up table.

          The method you describe sounds like you have correctly set up a look up. (This is one of two methods that you can use), but the "allow creation..." options have nothing to do with this function. Whether you specify them or not has no effect on how a data look up works.

          You'll need to take a closer look at the layout where it appears that this is happening.

          The layout should be based on "Medical Letters" so Layout Setup | Show Records From for this layout should show that name. If you enter layout mode and select the text field on your layout that is supposed to show the looked up data copy of this text from the template table, it should show Medical Letters:: as the first part of its name in the Display Data From box on the Inspectors Data tab.

          • 2. Re: Lookup problem - changing data in looked up field alters it in the lookup database
            VincentAngeloni

            Well, I have to admit, it may be from the various connections and disconnections I made in the same database that messed it up. In the end, I only need a few templates, and I decided just to use scripts to add the text I wanted. (I wanted to also be able to freehand entry in the same field if it was a letter that didn't use a template, and with lookup fields, I couldn't do that when they were empty).

            Thanks.

             

            • 3. Re: Lookup problem - changing data in looked up field alters it in the lookup database
              philmodjunk

              Which is simply one more indication that you did not have your layout set up to refer to the correct field or you would have been able to enter text directly into that field even if you had not looked up text from a template. Sounds like your field on the layout referenced the wrong field.

              A table based system of layouts, BTW, make it much simpler to make changes to them in the future as this then becomes a data entry process that does not require a FileMaker Developer as is the case when you rely on a script to enter this data into the field.