4 Replies Latest reply on Jan 26, 2010 5:28 AM by specpharm

    Prevent related table field from being entered more than once

    specpharm

      Title

      Prevent related table field from being entered more than once

      Post

      Hey all, 

       

      I have a problem which would seem as if it's painfully obvious to solve ... but I can't find the solution at all.

       

      I have a layout that includes a couple of fields from a related table. Every time data is entered into one of the fields it creates a new record in the related table. The problem is that if the user adds data to the field, commits it and then decides that they want to go back and change it, both sets of data will be added to the related table in the form of two separate records. 

      I've fiddled around with scripts to lock the field once it's been entered, but it doesn't work so well. There's gotta be a more elegant solution to this...

       

      Thanks in advance  

        • 1. Re: Prevent related table field from being entered more than once
          philmodjunk
            

          With correct design, simly editing an existing record should not result in creating a new record.

           

          How is your layout set up? Are you using a portal? If yes, how many rows of related records do you need to be able to display in the portal?

           

           

          • 2. Re: Prevent related table field from being entered more than once
            specpharm
              

            It's not a portal, it's just a field belonging to another table. The tables are related via two fields and every time data is entered into the field a new record is created in the related table. It's a very complicated setup but I guess it'd best be explained:

             

            Amounts::IngredientID (child) <---------> (parent) Mixtures::IngredientID <---------> Ingredients::IngredientID

            Amounts::MixtureID (child) <---------> (parent) Mixtures::MixtureID

             

            So the mixtures layout has five fields for the ingredients, each linked to a whole bunch of TOs on the relationship graph in order to pull down related information like expiry dates. Every time a number is added to the amounts field a new record of that amount with associated IngredientID and  MixtureID is created in the amounts table. Every Ingredient record in the Ingredients table has a portal of these amounts which it deducts from its total, showing how much is left. 

             

            If I add an amount in the Mixtures layout, click out of it, delete the number I just put in and then write another there'll be two entries in the amounts database, which will in turn yield misleading results in the associated Ingredients record. 

             

            Any thoughts?

             

            Cheers 

            • 3. Re: Prevent related table field from being entered more than once
              philmodjunk
                

              That's definitely odd, and I think I'd have to see your layout and examine the design of your database to see why that's happening. If you place a field from a related table on a layout, the "allow creation of records via this relationship option" will make it possible to create a related record when you enter data into this field for the first time, but editing the field--even clearing the field completely and then returning to insert new data into the field at a later time shouldn't create a new additional record--it should just modify the record originally created when you first entered data into the empty field.

               

              You got something else going on here, but I can't see what that is from here. Do you have any script triggers set to perform a script when the field is modified or exited? When the layout record is committed? If there's any chance of that and you have filemaker advanced, you might want to start the script debugger and then edit the field as you describe to see if the debugger captures any such scripts when they trigger.

              • 4. Re: Prevent related table field from being entered more than once
                specpharm
                   Well now I feel like an idiot. Yes there is a lot going on but I just tried it again and, bingo, it worked like you said it should. I think I was moving between layouts a lot during the testing process and that may have confused it. That and I've made a bunch of changes since I last witnessed the problem so maybe I inadvertently fixed it. In any case it appears to work now. Thanks for your help!