11 Replies Latest reply on Nov 12, 2012 6:19 AM by JimMac

    Updating a Related Value List



      Updating a Related Value List


      I have a related TO of local fkCityName.  This related table is use to Lookup addition information about the CityName and fiil in other pkCityName info, such as County, Zip, County Seat, etc.

      As new City is detected [not in value list], I want to give the user the option to add that new info into the related TO.


      I want to detect the new City immeditatly and not wait for the Commit action for the entire Prime record.

      Desired Action...

      Show Custom Dialog to allow related info to create a new record entry.

      Any suggestions?


        • 1. Re: Updating a Related Value List

          I'm not sure I follow your description (May I need a cup of tea or three Wink)

          do you mean that you have this relationship?

          Table1::fkCityName = Cities::pkCityName

          And if the user enters a new value in fkCityName, you want to detect that fact before the current Table1 record is committed? Curious to know why you stated that requirement, but with the OnObjectValidate trigger, you should be able to use this test to see if the value in the field is a member of the current value list:

          IsEmpty ( Filtervalues ( ValueListItems ( get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; Table1::fkCityName ) )

          • 2. Re: Updating a Related Value List

            I needed more Grapefruit juice at this hour!Tongue out

            Contract::pkCityName = Table1::fkCityName

            And if...

            A new Contract::pkCityName is entered [not in value list from Table1]

            I want to allow a new record to be entered into Table1 with additional, at hand, information... thus expanding a list of local commonly used Cities.

            This may sound weird but many Rural cities share a zip code under a Main city post office, etc.

            I read the help on OnObjectValidate trigger, but it seemed to work BEFORE validation and I don't have a validation anyway.  Thus generating this post!


            PS: Took a big gulp of Grapefruit juice.

            • 3. Re: Updating a Related Value List

              but it seemed to work BEFORE validation and I don't have a validation anyway

              Validation occurs as an event on all fields whether you define a validation rule or not. Think of how date fields squawk if you enter an invalid date. That's when you need this script to kick in as this will happen before the record is committed--but I still don't see why that's important here.

              • 4. Re: Updating a Related Value List



                Your idea worked nicely! Thanks!Cool


                "--but I still don't see why that's important here."  

                "beauty is in the eye of beholder"  

                the user has to fill out Legal Address of a Contract Property and I try to fill out the mundane County, State, Zip of many rural cities with hard to find zips.  


                I want to take the mandatory info for a new City [to list] and add a new record to the related file.  


                I created a "learning" List from data entered by user without burden to him, but once.

                I heard too many times, "Hey what is the County and Zip of "Puddledink City?".  

                Searching takes several people to say "Use 'Main City' zip and county!"  

                Why not use a portal?  

                because fields are being filled out, superimposed on the actual Contract in the background and portals adds dont work easily.Wink

















                • 5. Re: Updating a Related Value List

                  Are you popping up a message asking confirmation before adding the new city/zip record?

                  That finally occurred to me as a reason why this couldn't wait until the record was committed.

                  • 6. Re: Updating a Related Value List

                    Are you popping up a message asking confirmation before adding the new city/zip record?

                    You got it!!!

                    When I used your cleaver use of ValueList to return an empty Validate from OnObjectValidate trigger, I ShowCustomMessage with the already filled out new city, county, zip and state  in the 3 input fields that the user has to enter anyway.

                    User is asked to "Add" or "No Add" to Master List... Hoping he double checks before clicking "Add" then I pass the ValueList of the 4 items to the script parameter of an External Script which adds a new record and fills in the new data.


                    The value list of CityName grows, learns and will eventually get filled up without much user burden. 


                    • 7. Re: Updating a Related Value List

                           Hi. I'm having the same problem here:

                           I have a value list from a related table, and I want an easier way to "automatically" add values to that value list, without having to go to another layout and adding it. The idea of the dialog window asking to confirm is exactely the same I had.

                           I think I understood the solution for the condition. It works fine. However, I'm still a little bit new to scripts, and mine is not working. Can you please post the full script you used here?

                           I'm having trouble:

                           1 - Because even using the OnObjectValidate trigger, FM is halting the updating of the Value list, by not Validating the child list field entry.

                           2 - I'm not sure what should I do to guarantee I'm effectivelly adding a new record to the Value List Field of the master table, when using SetField - and not overwritting another because of a misplacing of the pointer. Is there a way to guarantee that?

                           Thanks in advance!

                           All the best,


                           P.S.: My script:


                      • 8. Re: Updating a Related Value List

                             What are you trying to validate exactly?

                             In my example, I wasn't trying to Validate the City.  The User would be ultimately responsible for the accuracy of typing, or the combination of [City,State,county and zip] on the property address for his legal contract.

                        But Phil's idea....

                             Of using  OnObjectValidate trigger to then immediately ask the User to validate the new City [state, county and zip] allowed me to grow the related value list.  Thus teaching the database new information at the time a User had property address information in hand.

                             You can still validate this information also, althought not my primary reason.

                             I will be glad to help when I better understand.


                        • 9. Re: Updating a Related Value List

                               Dear Jim,

                               Thank you for your answer.

                               This is what I'm trying to do:

                               I have a catalog of institutions. Theses institutions have many ways - in history and in different situations - to write their names. For instance: The brazilian Academia Real de Belas Artes also had the name: Academia Imperial de Belas Artes, and Escola Nacional de Belas Artes; and today its name is Faculdade de Belas Artes da UFRJ.

                               So, in the table of Institutions, I want to have one name - the standard one for me - that I'll use as a PK for that table (I prefer natural primary keys, but I know that's a huge discussion, and we don't have to go through it now :-) ). Also, I'll have "Alternate Names" for that institution. To do so, I made a one-to-many relationship between the institutions table and another one institutions|names where I can relate each institution to many different names. Also. I made a single institutions_name_alt table. institutions_name_alt is both related to and used as a Value List for: PK field at Institutions table; and for the alternate names at the institutions|names table. To reduce the possibility of someone cataloging twice the same institution, using different names, I'm also validating the PK at institutions table and the alternate names at the institution|names table by the Value List, where each name is unique. So, when someone is typing a new name, if it already exists, it will not be allowed ad new  (I still have to solve the possibility that someone can really use an existing name to add a new institution, but that's also a different problem).

                               In short: i have two fields in two different tables that are validaded by a Value List that uses a field from a third table. When I'm inserting data in the two first ones, I want the possibility to add a new field to the third one, without having to open it before starting to catalog the name on the first ones. To avoid accidental insertions in the Value List, a pop-up message would prompt for confirmation of the user before adding a new name.

                               I hope I was able to explain the problem now... And that you are able to help me! :-)

                               All the best,


                          • 10. Re: Updating a Related Value List

                                 ("I prefer natural primary keys, but I know that's a huge discussion, and we don't have to go through it now"

                                 You can very easily create the impression of 'natural primary keys' yet still have all of the security and consistency of serialised keys.  Once that's possible, I can't see any reason to have 'natural' keys.)

                            • 11. Re: Updating a Related Value List

                                   Danilo, it may takes several posts in this forum to help separate several issues and ideas.


                                   Lets focus on Files versus Tables.  A File in my example is a separate FMP file that is shared by many separate Databases.  You would see a file name in you directory or folder.  A table is within a File that you know quite well.  So you can't directly use what was suggested by Phil.

                                     IsEmpty ( Filtervalues ( ValueListItems ( get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; Table1::fkCityName ) )

                                   Since the "get ( FileName )" is referring to an external File.

                                   Your idea uses Tables, which is fine.wink  


                                   There is no need to have a separate table for alternate names of institution[institutions_name_alt] for just validation purposes, but you can if it fits your needs better.yes


                                   If I understand  your needs, you want to validate or add a new name to the lists of alias names.  I hope you have your primary key as a Identification number and NOT just the institution's Name to relate institutions [prime] to institutions|names [foreign].   Please explain how these tables are related to each other


                                   OnObjectValidate is used to trigger a logic calculation that validates the object field and returns a True or False to decides when to ShowCustomDialog.  In other words, If  your Institutional Name is in the Value List... do nothing and continue...  If not in Value List... run script with ShowCustomDialog and Add or No Add.

                                   Lets use this as a begining to help  you.  Think out what I noted.  Read Help on OnObjectValidate.  Then we can discuss the next steps.