Relinking duplicated and moved referenced files

[John Browning]

I need to be able to relink a moved file to other files in a master folder. The FMP setup is as follows:

I have a cost estimating solution I have written in FMP. The solution relies on scripts and lookups to move cost data from a set of master solution files into a job CostEst file. For each new costing one file, the CostEst file, is duplicated and moved into a new folder elsewhere on the hard disk. It is then given a new name, say CostEst.230204. The rest of the solution files stay in the solution folder.

The problem is this:

The duplicated CostEst file relationship keys still relate to the solution files as the solution file location hasn't changed. However the solution files cannot link to the moved file as both its location and its name have changed. Hence, when a script is run from the CostEst.230204 file it happily goes to the relevant Master solution file but then cannot move data back into the CostEst.230204 file as it doesn't know where it is!

I can fix this by opening the relevant script in the Master solution file and relinking the "External script" to the new CostEst.230204 file. However this is cumbersome. I also noticed that the External script referenced file is always the last new CostEst file created, which is logical but dangerous as the script then opens the previous CostEst file and pastes data into it, thus modifying the wrong file!

I need either a work around or a plug in or some suggestion as to how to enable this link to be moved to the new CostEst file for each new CostEst file created.

Incidently the solution is used on both Apple and Window OS's.

[bruceR]

John Browning said:

I need to be able to relink a moved file to other files in a master folder. The FMP setup is as follows:

I have a cost estimating solution I have written in FMP. The solution relies on scripts and lookups to move cost data from a set of master solution files into a job CostEst file. For each new costing one file, the CostEst file, is duplicated and moved into a new folder elsewhere on the hard disk. It is then given a new name, say CostEst.230204. The rest of the solution files stay in the solution folder.

The problem is this:

Nope, that's not the problem. The problem is that you're doing things you shouldn't be doing. You are doing what you would do in Word, or maybe Excel. That is not how you use a relational database.

All the cost data belongs in ONE file, with identifier fields such as Job Number for each Cost record, so that it is is tied to the job.

[John Browning]

Thanks for the reply to my posting.

I agree that keeping all the cost data in one file is a "solution" and possibly the "correct" solution but is it not a practical solution in this case. I developed this DB system in 1995 and have used it since to complete hundreds of costings, some very large. For simplicity and security it works best to keep a CostEst file as a separate entity in the folder for each job costing. This is also the way my users prefer to operate. Aside from this problem it works well as a relational database solution to the problem. Excel is definitely not the answer to the problem. Been there, done that!

The method I have set up at present for each new costing is to use a script to create a copy of the master CostEst file and then save it into a selected folder, normally the current client folder for the job being costed. Providing the immediate previous CostEst file has had its name changed after the costing is complete FMP is then forced to ask for the location of the file as it cannot find it due to the name change. The dialogue box then enable the user to open the just saved new CostEst file and the links are then reestablished to the new CostEst file. This is cumbersome!

If I am to perservere with this workaround I need some method to rename a file without leaving FMP and manually renaming the file in the folder.

Any further suggestions, anyone???

[Ugo DI LUCA]

Hi and welcome,

Sorry to be brutal, but you shouldn't consider Bruce post as an idea, but rather as an advice.

You've definitely not chosen the "light" version by doing so, and you can't reasonably say that your users like it better, as they didn't tried the other way.

Take a second look at his answer as any workaround wouldn't solve the logical steps you missed.

My 2 cents

[stanley]

John:

I've got to agree with Bruce and Ugo: there's a pretty serious fundamental flaw to what you're doing, from a database perspective. I understand the concept of having the file reside within the job folder, but how do you reconcile the data across all the jobs? Which file holds the "real" information? If you've got an original CostEst file, what data does it have in it? Or are you putting an empty CostEst file (a clone) into the job folder?

Also, considering that you say this has worked for nine years, why change now? By which I mean to ask, what is the desired end of what you're looking for?

-Stanley

[bruceR]

There are even more problems. Is this a multi-user solution? How are the files being served? If you're using FIlemaker server, the user should never even be able to see the data files and so their location in a particular folder is meaningless. Have you ever expericenced file crashes?