storing a keyvalue while displaing other values

Hi everyone,

I'm new here, familiar with relational DBMS but not Filemaker. I have
a question from a collegue who is making something in filemaker pro9.

The question is how to store an ID filed in a table, reference to it
from another table while not showing it in the GUI.

Master table CUSTOMER has fields ID and NAME
Secondary table ORDER has fileds ID, CUSTOMER and SHIP_DATE

The tables have a 1:n relationship on CUSTOMER.ID to ORDER.CUSTOMER

In the screen where the end-user can see and input ORDERs, I want to
show a pulldown with CUSTOMER.NAME visible, but when storing the
screen, the corresponding value of CUSTOMER.ID should be stored in
ORDER.ID. Also when displaying an ORDER, the NAME of the customer
should be displayed instead of ID.

Is this possible in FMP9, and how would I do this? Thanks in advance
(and sorry if this is a FAQ)

Re: storing a keyvalue while displaing other values

In article <4754176f$0$233$e4fe514c@news.xs4all.nl>,
Joolz wrote:

> Hi everyone,
>
> I'm new here, familiar with relational DBMS but not Filemaker. I have
> a question from a collegue who is making something in filemaker pro9.
>
> The question is how to store an ID filed in a table, reference to it
> from another table while not showing it in the GUI.
>
> Master table CUSTOMER has fields ID and NAME
> Secondary table ORDER has fileds ID, CUSTOMER and SHIP_DATE
>
> The tables have a 1:n relationship on CUSTOMER.ID to ORDER.CUSTOMER
>
> In the screen where the end-user can see and input ORDERs, I want to
> show a pulldown with CUSTOMER.NAME visible, but when storing the
> screen, the corresponding value of CUSTOMER.ID should be stored in
> ORDER.ID. Also when displaying an ORDER, the NAME of the customer
> should be displayed instead of ID.
>
> Is this possible in FMP9, and how would I do this? Thanks in advance
> (and sorry if this is a FAQ)

Couple of easy ways:

1. Use a script to create the Order. Have the script create the Order
record and use the CustomerID to set the relationship key field in the
Order record. Can do this by means of a script variable or a script
parameter. Use a pushbutton on the Customer layout to call the script.

2. In a portal of Order records in the Customer layout. Allow creation
of Order from Customer. Show, say, OrderDate in the portal. Find the
customer. In the first empty portal row, enter the date. That creates a
new Order record with the Customer ID automatically filled in by way of
the relationship.

Best to start the process of creating an Order from the Customer record,
for either of the two methods given above. That makes it very easy,
instead of creating a blank order and then assigning the customer. Can
be done starting from a new Order record and then assigning the
Customer, but this creates more difficulties especially if you have a
large number of customer records, and the name alone does not
discriminate between customers.

Also, some often-used conventions on naming key fields:

kpCustomerID is the primary key field in the Customer table, and is
normally an automatically generated serial number

kpOrderID is the primary key field in the Order table, also a serial
number

The notation
Customer::kpCustomerID means a field that belongs to the Customer table
and has the name kpCustomerID.

So,
Order::kfCustomerID means a field that belongs to the Order table and
holds the foreign key kfCustomerID

Some people put an underscore in front of the names of key fields so
that they sort to the top in a display of fields by name.

--
Bill Collins
For email, change "fake" to "earthlink"