Hi --

I've been learning a lot of new concepts and strategies lately thanks to
all the great pointers here on this newsgroup. One of them is on p. 19
of the 'Whitepaper for FMP Novices", where the author suggests *not*
allowing data entry within portals but rather a simple strategy for
creating a sort of platform with fields for data entry via a new
relationship based on a global field:

"How then are you supposed to create portal records?
Simple. Create an additional relationship to the table in question. Make
it a one-to-one relationship using a global field as the primary key and
the existing ID of the related record as the foreign key. Put fields
from that relationship on a platform on the layout, along with a New
Record button. Attach this button to a script that creates a new record
in the related table, grabs the ID number of the new record and inserts
it into the new global primary key field."*

My question (which is probably cringingly basic) is how you "grab" the
ID number and insert it into the global key field *without* using the
"copy/paste" approach, which I've learned is undesireable since it
clears the computer's clipboard -- and requires the fields to be on the
layout.

I assume it's some use of "Set Field" -- but I can't figure out how to
specify the field data I want the target field set to!

Thanks again
albert

*An additional question -- in scripting the New Record button, wouldn't
you also need to add a step that enters the primary keyfield of the
Parent record into the foreign keyfield of the new record, since you
aren't creating the record through a portal? I'd think so...

On Jul 3, 3:12 pm, Albert <a... (AT) mindspring (DOT) com> wrote:
You don't say what version of FM you're using, I'll assume the most
recent, 8.5.

To answer your last question first, no. A portal is simply a way of
view related records. Whatever rules apply to relationships apply
whether a portal is there or not, except that you only see the first
record of the relationship (as determined by the relationship sort
order) if there's no portal (unless you're using the GetNthRecord
function). If there's no related record, Set Fielding a child record
will create one with all the criteria for that relationship coming
true (including the foreign key filling in).

I think I know the Whitepaper you're talking about. The title's a
little misleading, there are lots of things in there that are clearly
beyond "Novice", but it's a good resource and I agree that not
allowing record creation in a portal is generally poor practice and
the general technique provided is a good one. I do it slightly
differently.

Like the whitepaper, I set up two relationships. One for viewing in
the portal, the other for new child records. The criteria for the
relationships are the same except for one difference. I have two
fields in the parent table, one called _kg_AccountName which is a
global set by my opening script to the user's account name, the other
_kg_1, a global number field set to 1 on opening. I have two non-
global fields in the child table, one called _kk_AccountName, the
other _kc_AccountNameEmpty. _kc_AccountNameEmpty is a calc field =
IsEmpty(_kk_AccountName).

To whatever criteria you already have for a relationship, add:
For the New record relationship, _kg_AccountName = _kk_AccountName.
Set it to allow Creation.
For the View relationship _kg_1 = _kc_AccountNameEmpty. Set it to NOT
allow Creation.

I don't know how you're capturing the info to populate a new child
record, but I like to put all the required fields from the "New"
relationship above the portal with a "Submit" button that runs the
creation script.

Once the user enters info into those fields a new record is created,
but it won't show in the portal. User hits "Submit" and the script
validates the new record's fields, then clears the child
_kk_AccountName (using Set Field[_kk_AccountName; ""]) which
invalidates the New Record relationship and validates the View portal
relationship. The record disappers from the entry boxes and appears
on the portal.

Voila!

The benefit of the AccountName rigamorale is that other users won't
see each others "records in progress" and won't be able to screw up
their entry.

Hope that helps.

Albert wrote:

Hi Albert,

to grab (parse) the ID

Field g_Field in table uUser
R=ID to grab in related table Clients
the commit at the end refreshes the window equivalent to clicking
outside a field

Script:
parse - related ID to global
Set Field [ uUser::g_Field; Clients::Client ID ]
Commit Records/Requests
[ Skip data entry validation; No dialog ]


If you want to create a new related record, you have a rel between the
current record ID, and the equivalent ID field in the related table,
plus key fields to make that acrr rel unique (ID::ID is unique but will
only create ONE related record, and pushing additional data will
overwrite the existing related value). Using a rel based create
related, you need a second key field for the acrr rel (a second rel to
the existing ID::ID display rel). Something like key_n, which is a count
of related records + 1 (calc as below, or scripted to gloabl)
e.g
key_N = Count(TOC_B::A_ID)


You can alternatively use a script to:
parse the ID to a variable
go to the related table,
create a new record in the related table
hard code the variable (ID) value to the appropriate related table field
return to the original layout



regards

Chris

Actually, I'm maybe getting in over my head, as I'm finding the replies
a little opaque. But maybe I should describe a little more clearly what
I've got.

I'm using FMP 8.5, and designing a simple single-user database for
cataloguing collections of films. I have a series of one-to-many table
relationships breaking down thus:

Donations >> Collections >> Films

At present, I have a portal in the Donations layout allowing creation of
Collections records (a donor may bestow several distinct collections),
and likewise a portal on the Donations layout allowing creation of film
records (each collection is made up of many films). So starting in the
Donation table I have

ID_Donation_pk = ID_Donation_fk in the Collection table
ID_Collection_pk = ID_Collection_fk in the Film table.

Trying to avoid using the portal for data entry, I've created a global
field in the Donation table IDg_CollectionEntry_pk and linked it in a
1-to-1 relationship to ID_Collection_pk in the Collections table.

Over the fields in the portal displaying Collections information, I've
placed a button which goes to the related record in Collection, copies
the ID_Collection_pk, goes back to the Donations layout and pastes it
into that new global field, so that the Collection fields that I've
placed on the layout using the global relationship appears in a 'data
entry' area of the layout.

Then I've got a button for 'New Collection' that runs a similar script,
going to the Collections table, creating a New Record, entering the
ID_Donation_p and bringing back the ID_Collection_pk to the Donations
global field -- again using the Cut-and-Paste method, which I understand
to be undesireable.

It basically works - though a few cosmetic features need attention --
(the new record appears in the portal list at the top rather than
bottom, and I'd like to make a portal row be grey when its field data is
being displayed in the dat entry) -- but I have a feeling it's inelegant.

Again, it may be that I'm just over-reaching with the degree of
knowledge I have, and I don't want to seem like I'm asking you guys to
tutor me. I'm reading a lot of texts and trying to tap into some
useful videos at the ISO site, but I'm finding it hard to locate the
relevant lessons.

Albert

On Jul 4, 8:28 am, Albert <a... (AT) mindspring (DOT) com> wrote:
Albert,

Iif it works, it works, so don't feel compelled to change it. But if
you're interested in streamlining things...

We're talking about two scripts. One to edit Collections records, one
to create Collections records.

In the first script, you don't need to go to the related record. The
script can capture the Collections ID right from the portal. The
script could be as simple as:

Set
Field[Donations::IDg_CollectionEntry_pk;Collections::ID_ Collection_pk ]

That's sufficient to replicate what it sounds like you have.


There's different ways of creating new related records. You can
create them in the child table. You can create them from the parent
table using a relationship. You can have a user enter all the info
into globals before creating the record. You can create the record
then have the user enter the info. I explained my preference in my
previous post, but I'll run with what you're doing.

In the second script, you're basically creating the record and then
doing the same thing the Edit script is doing. To eliminate the Cut n
Paste (which I strongly recommend), all you need to do is after the
"New Record" script step, again just add:

Set
Field[Donations::IDg_CollectionEntry_pk;Collections::ID_ Collection_pk ]



As far as your other feature requests...

You can sort the portal by double clicking on the portal in Layout
mode. Sorting by the pk (ascending) is probably the simplest (you
should also have a timestamp field for record creation and you can
sort by that). You can also sort the relationship itself, but I
recommend just sorting the portal.

To gray out the portal row, you'll need two new fields. One global
container field to hold the color gray (gGray can be a 1 px x 1px
sample cut from layout mode and pasted in browse mode). The other a
calculated field in the Collections table with a container result set
to Do not store...

cGraySelection =
Case( Donations::IDg_CollectionEntry_pk=Collections::ID_ Collection_pk ;
gGray)

Place that field behind all the fields on the portal row set the
graphic options to Enlarge, Do not maintiain original proportions.

G

In article <1183587891.611368.92900 (AT) n2g2000hse (DOT) googlegroups.com>,
Grip <grip (AT) cybermesa (DOT) com> wrote:

Thanks very much for the coaching here. The simple fix for the 'edit
record' script works fine, but I'm still confused about how the 'New
Record' process is supposed to work. I think I am muddled about how
scripts work through 'relationships' and/or 'portals.'

My 'New Record' button is in the Donations layout, so the first step in
the script has to be 'Go To Collections Layout' -- otherwise I get a new
*Donation* record. Right? (I don't see any options in Scriptmaker for
the "New Record" script line -- it just makes a new record in the
current table/layout).

So I'm not seeing how, when I do this, I'm getting the appropriate
Parent/Child relationship between the Donation record and the new
Collection record. When the new Collection record is made, the foreign
key linking it to the Donation record (which would *make* it a child)
remains blank. From your responses, I get the feeling, it should be
filling in this key field automatically (via the relationship), but I
don't see how. Given that I'm "in" Donations, how do I script the New
Record step to create a record related to the active Donation record?

I guess my blind spot is that the only way I've learned to create
'child' records between related tables is through a portal, which enters
the appropriate keyfield for you. Now that I'm not using the portal,
I'm not seeing what's taking its place.

I have both of the relationships between the tables set to 'allow'
creation of records in the child table. Is that okay?

Do all these key fields for the two tables involved need to appear on
the layouts? I'd think no, but want to make sure.

Sorry if this is really basic -I'm feeling pretty stupid.
Albert

On Jul 5, 3:15 pm, Albert <a... (AT) mindspring (DOT) com> wrote:
When you create a new record in the child table, you set the global in
the parent table. A global can be set from anywhere. Then you go
back to the parent table and the record should be "showing" in the
entry part of the layout, though all the fields are blank. Then you
Set Field whatever criteria you want to meet your other (view)
relationship requirements.

G

Ahhh . . that did it. The penny finally dropped, though it took me
another hour of poking around to discover my blunders, which were that
I was identifying some of the fields in the Set Field commands via the
wrong relationship.

Armed with the improved sense of concept, I experimented with the
scripts and have arrived at a smoother version where I have the user
enter the new data into global fields during a pause in the script
before creating the new child record, dumping the data into the
appropriate fields of the new record, and performing a sort on the
display portal that shows the new record right where it should be,
highlighted via that nifty gray label.

This has been a real learning experience - thanks for taking the time
to bring me along on this, Grip, and Charlie and others who make this
board so useful.

Albert