1 of 1 people found this helpful
You cannot use a variable in the External Data Source reference.
I suggest that you change your methods so that you do not have to rename the files each time.
Why do you rename the files like this? (What problem does that solve?)
You can use FileMaker Advanced's utilities to rename the files and update the data source references, but it would be even better not to need to rename the files in the first place.
Don't rename your new files.
Duplicate (off server into a new directory)
Rename the old files FOLDER
Zip the folder and archive
Or do you need multiple years? If so, why?
Sent from miPhone
We don't do a clean cut-over, so multiple versions are required to be online simultaneously. It's a personnel database, so they start entering in retirements, and we put the new insurance premiums in a few months before the end of the fiscal year. When we do the cut-over, the secretary maintains both databases, which is a lot easier than it sounds, since there isn't much staff turnover at the end of the FY.
I was thinking that was the case, but hoping it wasn't. I imagine I could do it more efficiently by using tables in one file instead of all these different single-table files, but it would probably take decades to pay off the amount of work of changing the file references (maybe 30 minutes a year), that rebuilding everything in tables would require. I'm retiring in 15 years, and I am not sure I could get the conversions done in 7.5 hours!
What you describe does not seem to require creating new files like this every year. Especially if "there isn't much staff turnover at the end of the year".
There are other ways to manage the data in your tables that does not require setting up a whole new file set for each new year.
I agree with you. But converting the existing system to one that would not require new files would take more time than just creating the new files, and converting the links (it really only takes 15-30 minutes per year).
1 of 1 people found this helpful
Yes, but the change over would only be a one time change instead of half an hour every year. Plus you would, at least in theory, gain reporting options that span years where now the data is split over different files.
And the change may not be as extensive as you think.
The main thing in a personnel file handling turnover is to have:
a) a status field so that you know who is "active", "retired", "Terminated", "Pending" or whatever works for your HR policies with a related table logging each status change and an effective date in that related record that tells you when the status will change. So if an employee is retiring at the end of the year, you can enter "Retired", and the date for the end of the year into that related table. The status still shows them as active until that date arrives. no file copying or renaming needed. In the same manner, a "Pending" or "candidate" record for a person still in the process of being officially hired might have a record that says "Active" with an effective date for the first day that they officially join your company.
The problem is that there are certain fields that are constant for one year, then change. Our insurance premiums, for example. I'd have to move those constants into their own table, then change all the calculation fields to take the year into account, to make the correct calculations for the premiums. Then there are the layouts that use the current FY as headers. I would have to make that into a dropdown list or something. Then I'd have to implement exactly what you suggest, as far as having a status flag, and then having the relevant calculation fields changed to respect the flag. That would be a ton of work!
The "constants" in question are all Calculations with a set numeric or text value. As I said before, I could definitely rebuild the solution to be more efficient, but it would take me decades to get a return on the investment of time, since it really doesn't take me much time to do the changes manually. I know it's not the most efficient way, but it was when I wrote it, in 2002. Rewriting it now won't have sufficient benefit to warrant the time it would take.
Thanks for the answer on making the URis use a variable. That is really the only thing that could simplify the solution. I realize it's an esoteric use case, and is certainly not the most efficient way to use Filemaker, so I won't expect FM to add the feature any time soon.
Did you miss the other info in my first post?
I believe that FileMaker Advanced can be used to rename files and update their external data source references as part of the renaming process.
As Beverly points out, simply create an end of the year BACKUP of your solution. Create a zipped file of the backup and name it for the year in question. Now you have a yearly archive and can uncompress it at any time for research.
Your solution continues onward and any changes you make as you describe are not carried backwards.
Of course we are all wondering why you haven't designed the solution to be year sensitive: for instance in the premiums file you can add a field for year and the rates for that year. In the other tables you add a year field for the item. You then add the year as a second link for your relationships which filters the items for just that year.
FileMaker 16 has this functionality!!! Downloading it now!