Value List and Look Ups Trouble

[jessieleah]

Using FM8 of a Mac with 10.3.9

I have a Customer DB, the records can serve as Customer, Bill To Contacts, Load At Info and Deliver To info. Customer and Bill To can have same address but different contact (hence the two).

(I have a File of just Customers and another File that contains Job and Work Orders tables - Job Table has fields for Customer and Bill To information. Work Orders Table has field for Load At and Deliver To information. I wanted to make that clear)

Each Customer record has a field with a unique code to idenitfy the customer and contact person. I created a Value List using the Customer Code from the Customer DB.

(I have a File of just Customers and another File that contains Job and Work Orders tables - Job Table has fields for Customer and Bill To information. Work Orders Table has field for Load At and Deliver To information. I wanted to make that clear)

Customer and Bill To fields are in one table called Project and Load At and Deliver To are in a different table called Work Orders. There can only be one project for a customer but have multiple Work Orders.

The fields are all Look Ups except the value list. However, when I do a Look Up for Customer and the info is filled in the Bill To, Load At. and Deliver To all end up with the same data as Customer. Why is this happening?

All field names are unique - do I have to have four different Value Lists? Can't one Value List serve all four sections. I hope I have made sense, because I am now confused. Any help you have to offer, would be greatly appreciated. If you need more info, let me know.

Jessica :)

[comment]

I am confused, too. Can one Customer have more than one billing address? More than one loading address? Delivery address?

[jessieleah]

I am confused, too. Can one Customer have more than one billing address? More than one loading address? Delivery address?

Yes, example - Jane Doe at Acme Enterprises in New York will hire us to move a vessel (she's the Customer). Loading will take place at Acme's factory in Atalnta and our contact in Atlanta in John Smith (he's Load At). We deliver to Widgets, Inc. Minneapolis. Widget's, Inc. is a customer of Acme Enterprises. The contact at Widgets, Inc. is Tom Jerry (he's Deliver To). After job is complete we send an invoice to Acme Enterprises billing department which is located in Toledo and the contact is Bob N. Frapples (He's Bill To).

Some of the time the Customer and Bill To are the same, but rarely is Load At and Deliver To the same as Customer. Sometimes the Load At location is a sea port or rail yard and the Deliver To is a factory, sea port, rail yard, construction site. I will include a link to my company's website - may help - http://www.perkinsonline.com

Jessica

[comment]

I think you should put all your contacts (customers, their customers - in short, every person/company) into one Contacts table.

If Jane Doe works for Acme, then her record should have Acme's ContactID entered into the CompanyID field (which will be empty in Acme's record). The same for Widgets. You will then have a self-join relationship (or two relationships) joining your actual customers to their contact people and their customers.

I am still not clear on the address thing. If I select Acme as the customer, should I automatically get Bob Frappless as the billing contact (and his address as the billing address), or do Acme have other people that can be billed, depending on the project?

I'd suggest you watch this video - it may not be describing your situation exactly, but the basic principle is something you should seriously consider.

P.S. "...When EXECUTION Matters Most" - for a moment I thought you've sent me to the Governor of Texas site...

[jessieleah]

Very funny, (I did have a problem with it at first, but the word execution in the tag line does grow on you after a while and not in a creepy way).

believe me - after seeing what we do, execution is what matters most. One accident and we are finished. Plus, we have the best on-time and within contract dollars record in the business. OK, enough Perkins promotion.

I will watch the video. I hope it helps, if not, I will post another reply.

Jessica

[jessieleah]

Just wanted to let you know that I figured out my problem and it is fixed. I set up the relationships wrong. They are now set correctly and look ups are functioning they way I wanted them too. I kind of figured the relationships were set wrong - but I wasn't for sure.

Thanks

Jessica