Thanks for your thoughtful exercise. As is obvious from your example, it is
not easy. I used to work in another database system where one could create
and break relationships on the fly and all tables were available at all
times just by using the table name concatenated with the field. It was all
very slick.
----- Original Message -----
From: "Geoff Graham" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Sunday, January 20, 2008 6:10 PM
Subject: Re: Adding records to a related file
On Jan 20, 2008, at 4:42 PM, Nicholas Geti wrote:
I am running FM 5.5.
I had problems with all three of your suggestions.
1. A script in the child table will create a new record but it needs the
key value which can only be passed into a Global variable from the
parent script. Then issuing another SetField() from the parent script
will write over the current record in the child. If there is more than
one record with the same key, the parent will then always replace the
first record. I could not find anyway to step through the child records
to the one I want.
Yeah, you have to run all the SetField()s in the child file.
You need a global relationship from the parent to the child:
gChild, which is gParentConstant::gChildConstant
gParentConstant is a global number in the parent with a value of 1;
gChildConstant is a global number in the child with a value of 1. When
you make that relationship, Filemaker will warn you that it will not
work. Make it anyway, because it's only used to set a global in child,
we'll call gKey.
Parent Script:
SetField(gChild::gKey, Key)
Perform Script(External, New Child Record)
Child Script:
Enter Browse Mode
New Record/REquest
SetField(Key, gKey)
Exit Record/REquest
I assume you already have a relationship from the Child back to the
Parent, just use that now to populate the Child:
(still in the child script)
SetField(Name, Parent::Name)
SetField(Address, Parent::Address)
...
2. Cannot import from the parent. Remember that there is a relation
between the parent and the child. If I am in the child, I would need a
relation back to the parent in order to see any fields in the parent.
This gave me lots of trouble actually trying to make it work so I gave
up.
Say you wanted one new child record:
Parent Script:
Show All records
Omit Record
Show Omitted
Perform Script(External, Child Import)
Child Script:
Import Records(Parent.fp5, Add New Records, Auto Enter)
If you don't know how to store an Import in a script step, just ask back.
3. The portal technique was a mess. I couldn't get it to show more than
one child record in the parent window.
The portal has to have "Allow Creation of Related Records" checked. You
have to be on the layout with the portal and in browse mode. Say the
relationship the portal uses is called "Child":
Enter Browse Mode
Go to Layout("Layout with Portal")
Go to Field (Child::Name)
Go to Portal Row(Last)
Set Field(Child::Name, Name)
Set Field(Child::Address, Address)
Set Field(Child::Phone, Phone)
...
Note that this way, the Key (or whatever field the relationship is based
on) will be automatically set by the portal relationship when the first
SetField() is done.
Set Field(Child::Name)
After a lot of hassle I finally got something working. From the parent I
used SetField() for each field in the child record to create a new
record. Then I used the External option to run a child script that saved
the key field, put a 9999999 in the key field, then issued New to create
a new record and put the original key in it. Then going back to the
parent, I issued SetField() which wrote over the new record. This
sequence was repeated for each child record of the same parent key.
After each set of child records for the current parent, I issued another
child script that replaced all the 9999999 values with the saved key.
This is a real mess. It just wouldn't be practical for an application
that is writing lots of child records on a random basis.
Yeah, that seems complicated. I guess you're using a temporary
relationship, kind of like I did in the first example, but I'm just using
the real key in a global, instead of some made-up key. That way I already
have it in the Child file and can kill two birds with one stone, so to
speak. The first way is my favorite way, by the way.
geoff
----- Original Message ----- From: "Geoff Graham" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Saturday, January 19, 2008 5:19 PM
Subject: Re: Adding records to a related file
On Jan 19, 2008, at 4:07 PM, Nicholas Geti wrote:
How do I add additional records to a child (related) table from a
master table. These tables are related on the Invoice Number field in
each table and I can add one record just by doing SetField() on the
child's invoice field but if I issue the command again it simply
writes over the previous one as expected. How do I force creating a
second record having the same invoice number?
Use a script in the child table to make new records;
Use a script in the child table to import from the parent;
or, have a portal in the parent table to the child table that allows
creating records, go to the last portal row, then create your child
record by doing the SetField().
Those are 3 ways off the top of my head.
geoff
