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.
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.
You can include records on one layout from different tables if the records are related or you include global fields from unrelated tables,
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.