Best method to transfer data from one database to another within the inbuilt scripting environment.

What is the best method to transfer data from fields in one related database to fields in another only using the inbuilt scripting.

I am creating a school database and have to transport values from a database called "behaviour level report" to a related database called "welfare record".

It is easy enough to transfer values between layouts using the copy, paste functions etc. however. the ability for one database to issue  commands in an external database (related) appeares to be limited to basic functions such as  "open file welfare record". From that point on you loose controle.  I can't then copy specific field values into fields in this external database.
naubreyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mariano_PetersonCommented:
This is from a previous post of mine at another list:
----------

There are several ways of doing this. I'll describe the method I use:

This exampe assumes you have three files:
Main.fp5
Parent.fp5
Child.fp5

Main.fp5 should have the following fields:

zkg_newID (global number)


Parent.fp5 should have the following fields:

zkp (primary key, number, auto-enter serial)
zkg_newID (global number)
field1 (any data field)


Child.fp5 should have the following fields:

zkp (primary key, number, auto-enter serial)
zkf_parentID (number, foreign key to parent table)
field1 (any data field)


Parent.fp5 should have the following relationship:

Relationship Name: Main.Constant_NOT
Related file: Main.fp5
Left field: zkg_newID
Rigth field: zkg_newID
** When you create this relationship, you'll get an alert warning you that this relationship will not work. This is OK.

Relationship Name: Child.New
Related file: Child.fp5
Left field: zkg_newID
Right field: zkp


Child.fp5 should have the following relationship:

Relationship Name: Main.Constant_NOT
Related file: Main.fp5
Left field: zkg_newID
Right field: zkg_newID
** When you create this relationship, you'll get an alert warning you that this relationship will not work. This is OK.


Child.fp5 should have the following script:
Script Name: "NewRec (external)"
Script Steps:
Allow user abort [Off]
Set Error Capture [On]
Enter Browse Mode []
New Record/Request
Set Field [Parent.constant_NOT::zkg_newID, "zkp"]

---

Now that you've created an infrastructure, we can create related records and set values in them easily.

From Parent.fp5, lets create a script, "CreateChild":
Script Name: "CreateChild"
Script steps:
Allow user abort [Off]
Set Error Capture [On]
Enter Browse Mode []
#
# Create a child record and get a pointer to it
Perfom Script [Sub-scripts, External: "Child.fp5", "NewRec (External)"]
Set Field ["zkg_newID", "Menu.Constant_NOT::zkg_newID"]
Exit Record/Request
#
# Set attributes for the new child record:
Set Field ["Child.New::zkf_parentID", "zkp"]
Set Field ["Child.New::field1", "field1"]
Set Field ["Child.New::field2", ""any data here""]
Set Field ["Child.New::field3", "field3"]
... etc ...


---


This methodology is convenient because it allows you to easily set as many fields as you like in the child file -- and the changes are limited to a single script. Also, by avoiding the use of copy/paste or setting globals in script and then reading them in another, you make the code more compact, less error-prone, and easier to debug.

As long as you're careful to maintain the infrastructure by including the "zkg_newID" field, the "NewRec (external)" script, and the "Main.Constant_NOT" relationship in every file, you can easily create a record in any file at any time without having to add fields, scripts, or relationships.

Hope this helps!

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
naubreyAuthor Commented:
Thanks for your reply
Apologies for the time. I was waiting for some more answers as your answer didn't quite fit my solution and I had to work it through.

Probably not the best solution however:
I ended up getting my result by

setting the value of fields to a global fields.

Using the  "external script function" to paste in the values into the related fields.
0
Mariano_PetersonCommented:
Avoid pasting; it does not offer consistent behavior and is highly error prone.  It is considered extremely bad programming practice to use copy/paste.  Instead, use the set field script step.

Using global fields works fine, but creates the unnecessary overhead and inconvenience of having to add new global fields every time you want to transfer an extra value.

Good luck,
Mariano
0
naubreyAuthor Commented:
Apologies for my use of the word paste. I am using the set function.
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.