Using source table data more than once in new record of related table

[rockbox]

Hello,

I am in a small freight business, and working to automate dispatching, reducing steps and paper.

I have a list of Companies in one table (hundreds of them) - fields are all the normal stuff, name, address, city, state, zip, email, phone, fax, etc.

These companies can be either a) shipper, receiver c) billing party. on one shipment they could be a shipper on another shipment they could be the receiver.

I have another table called Shipments, which has fields for shipper (address, phone, fax, etc.), receiver (ph, fx, addr), billing party (ph, fx, addr).

My goal is to only have 3 pull down lists to provide quick selection of the shipper, receiver, and billing party, and have all related ph/addr, info populate automatically.

I can get everything to work great, with auto populating fields and pull down for the first of the 3 items (Shipper), however when trying to select the Receiver all my shipper fields change also. How can I avoid having to setup 3 tables (shippers, receivers, billers) with identical data just to get them all into one Shipment record?

I think my problem could in the relationship aspect? Not sure.

I need all these fields to be "lookup" fields also, so they won't change when/if I change the Company info in my source table.

I hope I explained this.

I am not an IT PRO, but not a total rookie either.

thanks,

Scott

[bcooney]

Welcome, Scott.

You are correct, all the companies stay together in one table. You simply need three foreign keys in Shipments. Using my naming convention they would be:

_kF_ReceiverID

_kF_ShipperID

_kF_BillingID

Then, create three relationships from Shipments back to Companies, one for each key.

Each would have a popup menu value list of Companies (ID and Name).

As far as the addresses, you have two approaches:

1. Create three sets of all the address fields that you want to lookup.

or

2. Create three "full Address" fields in Shipments, one for each company type. Create an Addr_full field in Companies that is a concatenation of the separate addr fields. Then lookup this full address into its respective full address field in Shipments. It reduces the amount of address fields that you'll need in Shipments.

The next thing to tackle will be a better interface than the popup menus bcs the company list is so long. I typically turn to a popup window selector ( Link ).

hth,

Barbara

[rockbox]

Hello, thanks for the quick reply, however that won't work for 2 reasons. 1) having multiple relationship screws up the auto-populating for some reason, I had this problem before. Maybe I am doing something wrong, but I was at least able to get one set of contact info to pull in automatically based on selection of Shipper Company Name.

Also, I don't want to spend extra steps to select the CompanyName, then have to select the address, that should happen automatically in my opionion, simply based on selection of the related company name.

any other suggestions? I am all ears/eyes.

thanks in advance,

Scott

[bcooney]

Time for a demo.

Shipments.fp7.zip

[rockbox]

Barbara,

you rock first of all.

2ndly, it will take me a while to fully digest your demo, I am still trying to figure out the 2 tables that don't store any data, but I will get there eventually. I understand totally what you are doing and this will fix my problem.

one reason I am taking on this project is becuase I think it is within my skill level, but I like stuff to work and be easy also, and sometimes I need a little tidbit here and there to keep pluggin along.

thanks again,

Scott

[bcooney]

Scott,

I didn't complete the demo (I just did the lookup, popup menus for two companies).

What two tables that don't store data?

[rockbox]

You have 2 tables, shipper and receiving that have a source table of companies. each has the same basic fields, that all I can tell is to help the lookup in shipments table determine the relationship to the companyID and pull the info from companies.

[rockbox]

I have already made the mods to my dbase and things are working great. thanks so much again!!!

[bcooney]

Subtle point here, but very important to your overall understanding of FM.

I do not have two tables, Shipper and Receiving. I have two additional table occurrences of the Companies table. I choose to name them for what they are.

Yes, as you'll see, they are distinct relationships to Companies from the respective foreign key in Shipments.

[rockbox]

Gotcha, I found the section on TO's in my FMP11 TMM book. thanks again for the advice.