Creating Order Lookup Relationship for Customers

[jill]

I've been struggling trying to figure this one out and am hoping someone can help me out as I think I'm now going in circles and confusing myself!

I have an Order Tracking database which includes a tables for Customer Contacts, Orders and Customer Order Status.

The Customer Contacts are individual records for each person in a company and there are multiple companies. Therefore, I can have multiple records for the same company. All the records have a common company name to tie them together.

The Orders include the Customer Contact and all the order specifics. There are multiple orders for each company and can be multiple orders for a person within a company. Again, the company name is the common field for all orders within a company.

I'm trying to create a layout to make available through IWP for customers to view all their orders. All they will see is a line listing per order of the order number, description and total price and status of the order. I would like them to be able to see all the orders for their company, not just a single contact within the company.

Ideally, I'd like them to enter an order number and their billing zip code in order to pull up all orders for the company. (I'm looking at doing the combination so that it would be difficult for somebody from another company to pull up a list of orders that are not their own) I don't want to create a login system with separate logins for each company.

Is there a way I can create a relationship that will display all the orders for a company if any order number for a company and the corresponding zip code is entered on the status layout? I'm getting just the single order to show up, but not all the orders for the company. I've been able to figure it out by entering the company name and zip code, but that can be problematic as some of the company names are rather long, may have an extra space in them, etc. If it's not entered exact, the relationship fails.

Thanks for any help you can provide to point me in the right direction. I am using FM 11 Advanced for Mac.

[bcooney]

"Therefore, I can have multiple records for the same company. All the records have a common company name to tie them together." You should have a parent Company table. Each order would receive a _kF_CompanyID.

[comment]

Each order would receive a _kF_CompanyID.

Or each contact.

[bcooney]

Depends on what makes sense to relate to Orders.

Edit: Thought I'd expand on this a bit. Each Order should have both a _kF_CompanyID and a _kF_ContactID. If a Contact changes jobs and is associated with a new Company, their historic orders can follow with them (will still relate by ContactID), but since the Order maintains the Contact's previous CompanyID, the historic data is correct (the Order maintains the correct CompanyID).

[jill]

I did think about having a company id, but since I've got over 1500 contacts was trying to find away around coming up with codes. Haven't thought up a great/easy way to automatically generate unique codes for each company. Thought of using part of their company name, but I do have several that start with "The" (which I'd want to omit).

Would there be a fairly easy way to pull out part of the company name and add a serial number which will be the same for every contact under that company? If I run a script through all the contacts, would need the serial number to change only when the company name changes. How would I skip over "The" or "A" for those companies that start that way?

[geraldh]

Jill,

I think I'm having a similar problem. I'm trying to find a way another unique ID system that would be easier to recall for a certain company than a neutral serial numbering system (although some people say that is the best way).

I've a recent post here and I am waiting for people's responses, so I'll try to inform you also.

I've generated codes for concerts by calculating the concert's season, year, monthNumber, dayName, and concertSeries.

Each one of them is unique (since there is only one concert on any given day). But I want to the same for the 1400 Artists and Ensembles that have performed with our series. I fear though that a certain formula might not work with odd names such as your companies that start with "a" or "the"....

Let's keep each other posted?