4 Replies Latest reply on Jul 8, 2013 8:17 AM by philmodjunk

    Can I run a script on two tables?

    AssistantDirector

      Title

      Can I run a script on two tables?

      Post

           At our library we track our book orders in a Filemaker table.

           Each year we create a new table for that year. Within each table we run a script to identify duplicates that meet certain criteria?

           Can I set up the tables so that when I run the duplicate script it will find duplicate books in the current year table and also  any duplicates  in the table  from the previous year.

           Note the fileds in the two tables are exactly the same. The script looks at multiple fields to determine if an order is a duplicate since the same title can be ordered for different locations.

           Also note: I inherited this database and although I like Filemaker I am an unsophisticated user. If you show me the path, i can figure out the details. Thanks in Advance.

            

        • 1. Re: Can I run a script on two tables?
          schamblee

               In filemaker tables are openned by going to another layout.   You can use "freeze screen" in your script so the user doesn't see the script changing layouts.  You would then Go to Layout Prior year run the script then go to layout Current Year and run the script. 

               Just additional information.   You don't have to move prior years out of the current table.   You could create a relation between the year you are viewing (global field) and the current year.  This would exclude all other years.   You would have another relationship to include all years.   Another method would be just to perform a find based on the current year, then only years included in the find would be included in the results.

          • 2. Re: Can I run a script on two tables?
            AssistantDirector

                 Thanks S Chanblee.

                 how does this sound?

                 1.Add a new  table for budget year 2014 purchases, keeping the table for purchases 2013.

                 2. Create a layout that includes the relevant fields from both tables ( 2013 & 2014).
                 3. Run the duplicate script on the layout

                 This should give me the duplicate titles that were ordered anytime in the last two years.
                 I am going to read up on layouts and see if step 2 is possible.

                 I am inclined to keep the budget years in separate tables as we have a lot of formulas in place hat look to the different fields for calculations.
                 Thanks again it is very helpful to chat with someone who actually knows filemaker.

            • 3. Re: Can I run a script on two tables?
              schamblee

                   You can include records on one layout from different tables if the records are related or you include global fields from unrelated tables,

              • 4. Re: Can I run a script on two tables?
                philmodjunk

                     I recommend against using different tables for each new year. That approach will greatly complicate the design of your database and you will need to make major design changes to your file each time you add a new table for the next year's data.

                     It almost always works better to keep all of this data in the same table and use a date or year field to distinguish between the records for each subsequent year. Then you are doing a data entry task to add records for a new year instead of a database redesign task.