transfere data from base1 to base2 via script

[rasc...@gmx.at]

Hi group,

I use FP10 an Win7. I have 2 databases base1 and base2 who are related by the fields F1 and F2. The main base is base1 where I enter all main data. Additional data is in base2. Via a portal I can see data from base2 in base1.
Until now I do the creation and dataentering in base2 all by hand which is laborious when you have more than one dataset to work on.
Now I want to make a script which creates a new entry in base2 and write in there F1 and F2 from base1. And that with all the chosen entries in base1.

Thanks in advance for every hint

Raimund

[E. Appeldoorn]

schreef in bericht
news:2693c05c-61fc-4691...@googlegroups.com...

Hi Raimund,

First I have a few questions, I hpe you don't mind. Why on earth should you
want that? Anything that looks like duplicating your previous work is
generally bad practice. You say you already have the date you want. You
already have thos two databases related. So any information stored in
database1 can be seen, accessed and used in database2. You should probably
look into seperate tables, not separate files.

Look into the difference between a one-one relation, a one-many relation and
a many-many relation.
very briefly:
one-one... One private person only has one private address
one-many.... one private person may have many friends
many-many... a library may have many books which all may be borrowed by many
members

depending on your needs your set up will vary.
There many ways to solve this problem, and also a few 'wrong' solutions. I
just don't know enough about your solution to help any further. But my
suggestion is: learn about solutions with multiple tables within one
file....

Keep well, Erik

[Helpful Harry]

In article <2693c05c-61fc-4691...@googlegroups.com>,

I'm not sure I quite understand what you're trying to do.

You can use the Portal to enter and edit related data in Base2. Using the
Portal means Filemaker will automatically create any needed new records
and insert the correct data in F2 for the Relationship to work.

If you really need to transfer large numbers of records and data, then you
can use a Script with the Import command. The script can set a Global
field / variable to the data in F1, Import the records into Bas2, and then
use the Replace command to set the F2 data in all the new records. Roughly
something like:

Set Field [F2:g_GlobalField; F1]
Import [Records into Base2]
Replace [F2; g_GlobalField; NoDialog]

The Import can come directly from Base1's Found Set of Records, or from an
external Exported file.

Helpful Harry :o)

[Helpful Harry]

In article <cffb7$51b473c9$53578fa6$39...@cache1.tilbu1.nb.home.nl>, "E.

Appeldoorn" <ursus...@ziggo.nl> wrote:
>
> First I have a few questions, I hpe you don't mind. Why on earth should you
> want that? Anything that looks like duplicating your previous work is
> generally bad practice. You say you already have the date you want. You
> already have thos two databases related. So any information stored in
> database1 can be seen, accessed and used in database2.

Sometimes it's necessary to duplicate the data. It depends on exactly what
Raimund is trying to do though.

> You should probably look into seperate tables, not separate files.

<snip>

> But my suggestion is: learn about solutions with multiple tables within one
> file....

It doesn't say there are two file. Base1 and Base2 could already be Tables
in the same file.

There's nothing wrong with having Tables for the same database in separate
files - it certainly makes no difference to FileMaker itself. There are
pros and cons to both approaches, but it depends on what the database is
designed to do and how it is used.

Helpful Harry :o)

[rasc...@gmx.at]

Hi everybody and thanks for your hints.
I am sorry my first quaetion was not clear enough.
I'll try now to explain better:

I took over a fp5 database-set 10 years ago. It was set up with several databases which held different "sort" of data. There was nothing built in tables as I was used to it in Access. I kept it that way.
Base1 has the adresses, names etc of our contact-persons, Base3 has the data of the meetings we make and Base2 has the data who participated in the meetings.
Base1 and Base2 are related through the AdressID from Base1. Therefore after a new meeting I have to create a new entry and enter into Base2 this AdressID what I am doing now manually.
Because we sometimes have 30 participants in a meeting I would like to do that in a sweep with a script for all these 30 persons at once.
Naturally I would like to insert the MeetingID from Base3 for every participant too, but that is for every participant the same and so I first started with the "mass"-creation question.

Thanks Raimund

[Helpful Harry]

In article <cff4c49f-5bec-4de8...@googlegroups.com>,

But how are you going to find the correct people in Base1?

I guess you could view Base1 as a list and have a tick box next to each
name, then a button to run the Script to copy the names to Base2, but it's
a bit messy and has problems.

Probably the best option would be to simply have a Portal in Base3 that
links to Base2. You create a new Meeting record in Base3, and then type
the names (or ids) of the participants into the Portal rows. The Portal
can have a field that displays all the names (and ids) as a pop-up menu /
drop-down list.

Whatever you do is going to be time consuming - data entry always is.

The exception could be if there are meetings which usually have the same
people attending, e.g. a Staff Meeting might have all the staff attending.
You could create a button to add all those names to the Portal, and then
manually remove those who didn't attend (e.g. someone away sick, someone
else on holiday).


Helpful Harry :o)

[rasc...@gmx.at]

Hi Helpful Harry

> But how are you going to find the correct people in Base1?

I do this in Base1 by choosing the right meeting from a drop down list.

The problem is that I want to make it "fool-proof" for it is not always me who is working on that data-entering. Therefore I had the idea when the participants in a new meeting sign up that in Base1 the participant gets his "mark" (dropdown list) and when finally the whole thing gets running (meetings could also be canceled, participants could sign off) I then have my button to start my script which makes new entries in Base2 and enters the AdressID of the participants from Base1 into Base2.
And since I am not at all familiar with scripts, could you please give me advice if I got your example right and where do I insert AdressID from Base1:

Set Field [F2:g_GlobalField; F1]

This makes the field F2 in Base1 a global field and fills it with the value from field F1

Import [Records into Base2]
This produces a number of new records in Base2, the same number as data-sets were found in Bas1

Replace [F2; g_GlobalField; NoDialog]
This fills the field F2 in Base2 with the value of the global field

Sorry to bother you with such simple things, but I want to do it right

Thanks Raimund

[rasc...@gmx.at]

Sorry Helpful Harry,

I overread the last paragraph in your last posting:

> You could create a button to add all those names to the Portal, and then
> manually remove those who didn't attend (e.g. someone away sick, someone
> else on holiday).

How to do this?
I guess I should do this in Base1 which is filtered on the attending persons. And then the script should add all the AdressID's of them to the portal, each in a new row. And what would be the syntax for that script?

Yours Raimund

[Bill]

In article <2693c05c-61fc-4691...@googlegroups.com>,
rasc...@gmx.at wrote:

Some thoughts:

1. I understand you are not familiar with FileMaker. I suggest you take
the time to play with it, look at the documentation, create some
databases of your own to see how it works. You may find it easy to learn
about, and easy to use to do various complex things.

2. I also understand the solution was built by someone else. You
inherited it. You may not fully understand how it works, or how the
person who developed it intended it to work. You need to study it enough
to figure that out.

3. I am using the term Table in this discussion. Whether the tables are
in the same or different database files does not really matter. The
developer would create a relationship diagram in one or the other
database file, or perhaps in a third file if desired. Table Occurrences
in the relationship digram would be based on tables in one or another of
the data files.

4. Usual good practice is to have in each table a field that uniquely
identifies each record. Normally this is a number field, set up as an
automatically generated serial number, not editable by the user, and
validated to be not empty and unique. Those characteristics are set up
in the field definition. This is the primary key field for that table.

5. Another table related to the first table would normally contain a
number field to hold a value for the relationship to the first table.
The value would normally be set to hold the value of the related Primary
Key field. This field is known as a foreign key field, as it holds a
value derived from a "foreign" table to make the relationship to that
foreign table.

6. Many developers use field names as follows for these important fields:

__kp[NameID] for the primary key field of the table, where [Name] might
be the name of the table, or some other suitable name. kp identifies it
as the primary key field of the table. The double underscore __ makes it
sort first in a name sort of the fields.

_kf[NameID] for a foreign key field for relationship to another table.
[Name] might be the name of the other table, or some other suitable
name. kf signifies it is a foreign key, that is, intended to hold the
value of the primary key from the "foreign" table to create the
relationship. The single underscore _ makes it sort ahead of all other
non-key fields but after the primary key field in the list of fields
when sorted by name.


7. In the relationship diagram, the two tables (call them A and B for
the sake of this discussion) would be related as follows:

A::__kpAID = B::_kfAID

The notation A::[field name] is customary in the literature. It
signifies a field of table A that has the name [field name], in this
case __kpAID

8. Now you need a mechanism to assign the appropriate value to
B::_kfTAID when assigning a record in B to be related to a record in A.
There are basically two methods of doing this:

a. Allow creation of a record in table B via the relationship to A. Use
a portal to B in a layout of A, and create a record in B by way of that
portal. Allowing creation of records in B via the relationship is a
matter of defining the tables and relationships in the relationship
diagram. Now when you create a record in B via the relationship, the
value A::__kpAID is automatically assigned to B::_kfAID

b. Use a script to create a record in B. The script would store the
value of A::__kpAID in a script parameter or script variable, go to a
layout of B, create a new record, and assign the stored value of
A::__kpAID to B::_kfAID.

9. Normally, table B would not contain any data from Table A except the
value of the relationship key field. Once the relationship is
established, other data from Table A can be displayed in a layout of
Table B, and vice-versa, and data from the related tables can be used in
calculations etc. Then, whenever data is changed in Table A, the changed
data will automatically show up everywhere is used.

10. Sometimes there are good reasons to store data from one table in
another table. An example would be price data in the line item of an
invoice. Prices of items may change from time to time. New invoices
should pick up the new price, but the old price should be left unchanged
in old invoices.

11. There are basically two methods of getting data from one table to be
stored in another table. Say the two tables have the field "Price." We
want to store the value of Price taken from Table A in the Price field
of Table B. Two different methods:

a. Field definition based on the relationship:
B::Price defined to be calculated from A::Price, the formula being
B::Price = A::Price

b. By script. The Script stores the value of A::Price as a script
parameter or script variable. The script then goes to a layout of Table
B, finds the appropriate record in Table B, and assigns the stored value
of A::Price to the field B::Price.

You would normally include this in the script that creates the record in
Table B, so that the script stores both the value of A::__kpAID and the
value of A::Price, and then assigns those values to B::_kfAID and
B::Price respectively in the process of creating the new related record
in Table B.

On the other hand, if you want to assign the value of Price later, you
would need to incorporate script steps to Find records in B that contain
the appropriate value of B::_kfAID. However, this can get into trouble
if you have more than one record in B that has that value of of
B::_kfAID. For example, you might have multiple invoice line items
related to the one price list. You need a mechanism in the script to
find the record in B that you want to change, and not the others. For
this reason it is normally better to assign the value of Price when you
create the new record in B.

Hope this helps.

[Helpful Harry]

In article <314bf504-c1f4-4e79...@googlegroups.com>,

rasc...@gmx.at wrote:

> Hi Helpful Harry
>
> > But how are you going to find the correct people in Base1?
>
> I do this in Base1 by choosing the right meeting from a drop down list.
>

> The problem is that I want to make it "fool-proof" for it is not always me =
> who is working on that data-entering. Therefore I had the idea when the par=
> ticipants in a new meeting sign up that in Base1 the participant gets his "=
> mark" (dropdown list) and when finally the whole thing gets running (meetin=
> gs could also be canceled, participants could sign off) I then have my butt=
> on to start my script which makes new entries in Base2 and enters the Adres=

> sID of the participants from Base1 into Base2.

> And since I am not at all familiar with scripts, could you please give me a=
> dvice if I got your example right and where do I insert AdressID from Base1=
> :

Sorry for not replying sooner - it's been a crazy week. :o(

> > Set Field [F2:g_GlobalField; F1]
>

> This makes the field F2 in Base1 a global field and fills it with the value=
> from field F1

No. Set Field simply sets the data stored in the field - you can't change
field types via a script.

This script step is simply to copy the meeting's ID data into a globally
accessible field (rather than an individual record field), you can also
use a script parameter / variable.




> > Import [Records into Base2]
>
> This produces a number of new records in Base2, the same number as data-set=
> s were found in Bas1

Yes, but you'll have to Find all the appropraite records in Base1 first.

> > Replace [F2; g_GlobalField; NoDialog]
>
> This fills the field F2 in Base2 with the value of the global field

Yes. The Set Field command changes the data value in the current record,
while the Replace command changes the data value in all of the Found Set
records (which after an Import should be the newly imported records).

> Sorry to bother you with such simple things, but I want to do it right

ALWAYS, ALWAYS make a backup copy of the database files before making changes.


I'm still not sure this is the best method to do what you want though.


Helpful Harry :o)

[Helpful Harry]

In article <17c6412f-fbb7-4d20...@googlegroups.com>,

The people's records in Base1 would need to have a set of "keywords" or
meeting types they attend (e.g. "Staff", "Management", "Project A", etc.),
but that only helps you find the correct people - it's still going to be
the same process of copying their ID's to the other file / table.


It depends how many people there are, but a better way might be to give
each person an easily rememberable ID which can then be quickly typed into
a portal in the meetings file. For example George Lucas could be "GL",
Peter Jackason could be "PJ".

You could have one Portal in the Mettings file that shows ALL the possible
names, then when a user clicks on a name it gets copied to a portal for
that meeting's attendees.

Of course that's not going to work very well for hundreds of potential
names or entering hundreds of attendees.

Helpful Harry :o)