need help with Copy/Paste script...

[Natef]

I've been made the adhoc database designer/maintainer for a person i tutor and i've been able to get by until now....

we have a database where he will plan his roadtrips (he's finding himself doing more and more roadtrips) and the solution that i have come up with that doesn't quite fit his needs is as follows:

we have a database full of campgrounds, with fields for among other things, the city, state, address and notes on the particular camp ground. i also have 3 fields specially for his planning: Arrival Date, Departure Date, and the order of the stops on the currently planned trip. (i realize i could just sort by date, but he fills those in *after* deciding on the order of his stops)

this created a small problem... what if he wants to stay at one camp ground 2, or 3 or more time on one particular trip? well my solution for this problem was to create a relational database: strip the dates, and ordering fields from the above database, and put them in the 2nd database. i used the campground name as the relationship, and i have a system of drop down menus (and secondary relationships) for state and city to narrow down the campground that he wants to stay at.

we both think that using a series of menus is a limiting, and unintuitive way of doing this - you can't pull down the campground menu, without first setting a state and a city, in that order. my solution for *that* issue was to go into the 1st database (where he'll be going anyway to set up new campgrounds anyway) and add a button that fires off an apple script that essentially copies and pastes the contents of the State, City, and Campground fields from the "master" database and the "planning" database. I created the script, and when i fire it from Script editor it works beautifully, but when i fire it from a button in the database, i get an error when it is supposed to open the other database:

FileMaker Pro got an error: Data is being accessed by another user, script, or transaction. (Error -100011).

bellow you'll find a slightly modified script...

i use the "say" bit for debugging purposes, to figure out *where* filemaker is throwing the error

tell application "FileMaker Pro"

	say "setting the variables"

	set cityName to get data cell "City" of current record

	set stateName to get data cell "State" of current record

	set parkName to get data cell "Park Name" of current record



	say "opening the file"

	open file "path:to:the:plan database.fp5"

	

	say "creating the new record"

	create new record

	set cell "State" of last record to stateName

	set cell "City" of last record to cityName

	set cell "Park Name" of last record to parkName

	

	go to last record	

end tell

any ideas? either help me make the script work by getting rid of the error, or give me suggestions (or methods) for making the drop down menu solution more useful....

i hate to say it, but i *think* i could have pulled this off in access - and i don't even like windows

[CoZiMan]

Oh Boy,

1. No Version number. (help us help u)

2. This is where relationships SHINE !

3. We need to identify what the structure SHOULD be. Here's my quick take:

I would start with the individual starting DATE as the unique part of each trip record.

(Only one place at a time

Then add a straight forward portal pulling state, city, and campground

Then BANG, New Record in table for the trips.

All the values stay in place and the next available date appears with existing portal view intact.

All you need then are regular navigation buttons and a report for the completed table.

Build options to the selection, correction and deletion.

Deciding where this WON'T work will be the easy part because you have the methodolgy in hand.

4. What a slick multi-table deal this would be in v7.

[Natef]

ok, i could swear that i added things like what version of filemaker i was using... i'll try out your suggestions, but i'm having trouble envisioning it...

[Natef]

OK, what i am doing now (and it doesn't currently work quite right)

you have a drop down box at the top to choose the state (using a list that is all the states in the other database) and bellow that i have a portal that shows all the the data my client might want (to be refined face to face)

NOW what i want to do is stick a button on the portal that runs a script similar to the one above to copy/paste the relevant fields... now i get a different error, and i'm neerly positive that this is just a syntax problem... any ideas what it *should* be?

atm i run the script after selecting a portal row, and the error i get is "FileMaker Pro got an error: Object not found (Error - 1728)."

tell application "FileMaker Pro"

	say "setting the variables"

	set cityName to get data cell "States::City" of current record

	set stateName to get data cell "States::State" of current record

	set parkName to get data cell "States::Park Name" of current record

	

	say "creating the new record"

	create new record

	set cell "State" of last record to stateName

	set cell "City" of last record to cityName

	set cell "Park Name" of last record to parkName

	

	go to last record

	

end tell