2 Replies Latest reply on Feb 15, 2011 4:15 PM by BenjaminLea

    Script to check if a record exists



      Script to check if a record exists


      I have a table of categories and subcategories that are used to create value lists for another table of items.  An example of the table is below.

      Category Subcategory
      Office Equipment Books
      Office Equipment Desk Accessories
      Information Technology Laptops
      Information Technology Monitors
      Information Technology PoS Machines
      Bakery Area Mixers

      The subcategory value list is a conditional value list, so for example if an item has "Office Equipment" chosen as its category, it will only list the choices "Books" and "Desk Accessories" as subcategories.

      On the Item form I have two edit boxes that auto-complete from these two value lists.  What I want them to do is when text is entered into these boxes, they trigger an OnObjectSave event to run a script.  I want the script to check the ItemType table (the one described above) to see if the entered pair exists in the table, and if it doesn't, create it.  This way if a new item is created that goes in the same category/subcategory, it will autocomplete it from the value in the ItemType table.

      I've done some basic scripting before, but I don't know how to make this check happen.

      Sorry for the large amount of information, I'm hoping the more info I provide the clearer my intent is.  Thank you in advance for your help!

        • 1. Re: Script to check if a record exists

          Define a relationship matching the value of these two fields to the two matching fields in your categories table.

          MainTable::CategoryField = CategoriesByCatSub::Category AND
          MainTable::SubCategoryField = categoriesByCatSub::Subcategory

          categoriesByCatSub is a separate table occurrence of the categories table you've listed above.

          Double click the relationship line linking these two table occurrences. Enable "Allow creation of records via this relationship" for categoriesByCatSub.

          Now this one line script can be used to keep this table up to date:

          set field [CategoriesByCatSub::category ; MainTable::CategoryField ]

          If the record already exists in the Categories table, nothing visible happens as this step sets categoriesByCatSub::category to a value that it already has. If it does not exist, it creates one with the needed category and subcategory values.

          • 2. Re: Script to check if a record exists

            Thank you so much, works a dream!  You're some sort of FileMaker God aren't you?  Haha.