I buy and sell stuff. I buy from small mom and pops and sell, usually,
to one company. When I buy enough to fill a truck, I hire a truck to
go to all the sellers' locations and pick it up, then they deliver it
to the buyers location.

I created a db to record the truck (carrier) information and the pick
ups and deliveries (shippers/receivers). I have another section to
record the bill to information, for the person/company that is the
actual buyer, and another to record my commission.

So, since any one of the shippers/receivers and bill to's can be, at
some point in time, either a shipper/receiver or bill to, I have
created 8 sections to record their information.

Like this:

ship/rec
address
city
st zip
phone
commodity

....and I have replicated this 7 times (2 rows of 4), giving me a total
of 8 sections in which to record each pick up and/or delivery.

Here's the problem: I managed to get a value list based on a
relationship with the address book to allow me to toggle it, select
the name I need, and, after tabbing out, fill in the rest of the
information in the above fields. BUT, when I select a different name
to fill out in the other sections, it displays the address information
from the first section. To make matters even worse, it overwrites
information in the address book! Arggghhh!!

So, I created 7 more of the above fields (address and so on) and
appended each with a 2,3,4, etc through 8. That doesn't work either. :
(

Another fella said I need to create 8 address book files, and have a
script to "sync" them all each time a change is made.

Now, I'm more lost than before. (as if that's possible)

Any ideas? Anyone want to hold my hand and walk me through this one?
Please?


Thanks

On Jun 18, 4:25 pm, Defender of Truth <pizam... (AT) yahoo (DOT) com> wrote:
I should have pointed out that I'm using FMP 5.5 and winxp., and I
have 3 address books. Carriers, shipers/receivers, and bill to.

Sorry.

In article <1182198300.658291.220220 (AT) q69g2000hsb (DOT) googlegroups.com>,
Defender of Truth <pizamoto (AT) yahoo (DOT) com> wrote:

Firstly, you do NOT need "8 address book files" - that's simply
ridiculous database design (imagine getting a change of address and
having to go through all eight files to find the record). You probably
don't even need three of them.

It's a bit confusing what you're trying to achieve with this system and
it sounds overly complicated.


If by "replicated this 7 times" you mean you used Copy / Paste or
Duplicate or dragged new Field boxes onto the layout to get a second
version of the field on the layout, then they will all show the same
information - it is the exact same field after all.

When you created actual new fields (the ones with 2, 3, 4, etc.
appended to the field name) then this still won't work because you're
still using the same relationship based on the original field. You will
need to also create seven extra relationships based on the new fields.
For example, if the existing relationship uses Name, then you will need
to create relationships based on Name2, Name3, Name4, etc. and then
change the fields Address2, Address3, etc. to use the appropriate new
relationship.


BUT, having said that, you might be better off with a much simpler
system: ONE file called "Items" that lists each item as a separate
record. Each of those records can have fields for the item details:

Item Name
Item Description
etc.

and the three sets of fields for the peoples' details:

Purchase: Date Bought
Name BoughtFrom
Address BoughtFrom
etc.

Carrier: Date Carried
Carrier Name
Carrier Address
etc.

Selling: Date Sold
Name SoldTo
Address SoldTo
etc.


If you then want to save the time entering the same address manually
each time, then you could have a SECOND file "Address Book" that lists
the contact details for all the people:

Name
Address
Phone
etc.

You could have another field here that uses a normal typed-in Value
List to display Checkboxes for "Seller", "Buyer" and "Carier" if you
need to be bale to separate the records for mailouts or something.
Using checkboxes means a record can be in the set for any combination
of those values.

You then enter all the contact details for people into this file. You
will need to create an Address Book record for new people before using
their name in the Items file.

You can then create three relationships from the Items file to the
Address Book file:

rel_Purchase From Items to Address Book
when Name BoughtFrom = Address Book::Name

rel_Carrier From Items to Address Book
when Carrier Name = Address Book::Name

rel_Selling From Items to Address Book
when Name SoldTo = Address Book::Name


Now the three Address, Phone, etc. fields in the Items file can either
be Calculation fields or normal Text fields which use an Auto-entry
Calculation - it depends on whether you want a historical record where
the address do not change OR whether you want the address, etc. details
of old records to change when you change the Address Book file.

If you want a historical record (ie. the details do not change when the
Address Book record is changed), you will need to use normal Text
fields using the Auto-enter by Calculation option.
eg.
Address BoughtFrom Text, Auto-enter by Calculation
= If (IsEmpty(Name), "", rel_Purchase::Address)

Carrier Address Text, Auto-enter by Calculation
= If (IsEmpty(Name), "", rel_Carrier::Address)

Address SoldTo Text, Auto-enter by Calculation
= If (IsEmpty(Name), "", rel_Selling::Address)

The problem here is that the Address details will not change once
auto-entered, even if the Name is changed. If you make a mistake
entering the name (and then press Tab or Enter or exit the field), then
you will either have to manually change the Address or delete the
record and start again. Another way would be to use a script to update
the fields in the current record based on the new name.

If you do want the old Items records to be updated with new changes to
contact details, then you need to use Calculation fields instead.
eg.
Address BoughtFrom Calculation, Text Result
= rel_Purchase::Address

Carrier Address Calculation, Text Result
= rel_Carrier::Address

Address SoldTo Calculation, Text Result
= rel_Selling::Address

This also has the bonus of being able to change the Name if you make a
mistake and have the Address update itself.


Either way, you will then also need similar versions for Phone field
and whatever other fields you want copied across from the Address Book
file.


ALL DONE!



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

----- Original Message -----
From: "Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com>
Newsgroups: comp.databases.filemaker
Sent: Tuesday, June 19, 2007 8:36 PM
Subject: Re: FMP 5.5 Extreme dilemma, need assistance with relationships or?



Harry,

Thanks. As always you are there to help. Unfortunately, I am still not
getting it.

I have 4 files total. They are...Dispatch, Carriers, Bill To, and
shiprecaddressbook. In the Dispatch file I have several layouts using the
same information and fields in different ways, mostly different print setups
and two reports. Each of the last three are address book files, with a
single layout each.
As I noted before, I have 8 identical sections for displaying the
information needed to complete
either 8 pickups or 8 deliveries, or a combination.

I have the following fields in the "Main" layout...

shiprecname1
shiprecname2
shiprecname3
shiprecname4
shiprecname5
shiprecname6
shiprecname7
shiprecname8

shiprecaddress1
shiprecaddress2
shiprecaddress3
shiprecaddress4
shiprecaddress5
shiprecaddress6
shiprecaddress7
shiprecaddress8

Also numbered 1-8, I have...

city, state, zip
phone1
phone2
fax 1
fax2

--------------------------------------------------------------------------------
I have ONE address book for this, shiprecaddressbook. The other two are for
different sections, have no commonality, and they work fine. On it's layout,
I have...

shiprecname
shiprecaddress
shipreccity
shiprecstate
shipreczip
shiprecphone1
shiprecphone2
shiprecfax1
shiprecfax2
----------------------------------------------------------------------------------

My objective is, using a drop down menu, select a company name
(shiprecname1) from the value list and have the related information filled
in-in the fields below it.

I have a relationship matching the data from the Main layout in the
shiprecname1 field to the shiprecaddressbook field of shiprecname.

The shiprecname1 field on the Main layout uses a value list of
shippersreceivers located in the
Main layout's Relationship list.

The related fields are set to use data from the like named and content
fields in the related shiprecaddressbook. This works, but only for
shiprecname1, as you noted it should.

You suggested I might create 7 additional relationships, which makes perfect
sense to me, and I like that idea. The problem is, I can't. I have tried,
but they don't work. Once I select a company name from the value list, the
remaining fields either remain blank or, they fill in properly, but, change
the other 7 sections to match. I think I'm doing the relationship wrong, and
can't figure it out.

Would you be so kind as to step me through this with a couple of examples?

Thanks again.