Database Reference
In-Depth Information
POWER USERS' CLINIC: SERIAL TEXT
As funny as it sounds, you can auto-enter serial numbers into text fields, and the values
themselves
can contain text. FileMaker looks at the text you've specified for “next value” and tries to find a
number in it somewhere. When it comes time to generate a new value, it pulls that number out, in-
crements it, and stuffs it back in its place.
Say your “next value” is C000LX, and “increment by” is set to 10. The first record you create gets
C000LX. The second gets C010LX and then C020LX, and so on. When you get to C990LX,
FileMaker doesn't just give up. Instead, it makes more room: C1000LX.
If your “next value” has more than one embedded number (C000LX22, for example), then
FileMaker uses only the
last number
. If you don't have a number at all, it simply adds one to the
end.
Join Tables
A many-to-many relationship is more complicated than its one-sided brethren. Back on
Many-to-Many Relationships
,
you learned that a join table is necessary to make the many-to-
many relationship work. Remember: The database you're designing tracks payments from
your customers. A customer could send a check to cover
two
invoices, or a check could cov-
er
part
of an invoice only. So an invoice can have multiple payments, and a payment can be
for multiple invoices: That's the concept that drives this many-to-many relationship.
How do you build a relationship like this? If you put the Payment ID in the Invoices table,
then a payment can be applied to more than one invoice (just put the same Payment ID in
each invoice record). But an invoice could have only one payment, since it has just one Pay-
ment ID field. If you put the Invoice Number field in the Payments table, you get the same
problem in the other direction. You may be tempted to try putting a foreign key field in
both
tables. In other words, add a Payment ID field to the Invoice table, and an Invoice Number
field to the Payments table. Dig a little deeper, and you see that this has a whole
host
of prob-
lems:
▪ An invoice now has a field called Payment ID, but that field
doesn't
identify the pay-
ments for that invoice. To find the payments for an invoice, you have to search the Pay-
ments database, using the Invoice Number field. That's just plain confusing.
▪ Instead of one bidirectional relationship, you have two unidirectional relationships. The
Payment ID in the invoice matches the Payment ID in the Payments table, but this tells
you only which invoices belong to each payment. You need the other relationship (based
on Invoice ID) to figure out which payments belong to each invoice. If you connect a
payment to an invoice by putting the invoice number in the payment record, then you
