[FP5] Same Values in List show as one.

[Scott Pon]

Kind of a 2 part question:

1) I have a order entry db where you select the customer. The customer list is based on a Customer Database file. However there can be more than 1 entry of a customer name but have to different bill to addresses. Such as:

Customer BillToCity

Best Buy San Fransico

Best Buy New York

If I did a field list on field Customer, Best Buy only shows up 1 time, even though there are 2 entries for it. If Order Entry selects "best Buy" OE only the related information for the first city appears, the other city does not, and is unchooseable.

Is there a way to see both "best buys" in the value list???

Part 2 of question: (assuming the first part is solved)

Now Order Entry has to pick a Ship To Address. Let me explain the Customer DB, There are 2 files related to each other. One has the bill to Addresses, and the 2nd has the Shipping Addresses for the bill to address. This way I can have multiple Ship To Addresses for one Bill To Address.

The Ship To Addresses are selected by the Zip Code. I created the list where when the Customer is selected, only the Ship to Zip Codes that are related to the customer shows in the value list. Then OE picks the zip code.

The Bug appears when there are 2 companies (like if Best Buy and Target) has the same Ship to Zip, the first company's ship to address is selected, even though that company is not in the related list. Therefore the shipping address is wrong.

Anyone have a solve for these problems? Thanks in Advance. If more explaintion is needed let me know.

Scott

FileMaker Version: 5

Platform: Windows 2000

[Ender]

For Part 1:

When you create a value list that is based on a field value, like your customer list, it filters out duplicates of the primary value. Two possible solutions: give the different billing addresses unique names, like

Best Buy - SF

Best Buy - NY

Or, use a Customer ID number as the primary value, making the Customer ID a serial # that is unique to each location. You can still sort by a second field. This solution can be slower on WANs if there are many customer records, but it is nice to have that ID in case you need to pull information later (You don't need to worry if a company changes it's name, the relations still work.)

Part 2:

Not sure if I get it, but if your relations are based on Zip code, then that would definitely be a problem. Much better to use an auto-generated serial number (Customer ID), and then have multiple shipping addresses for that Customer.

[-Queue-]

Value lists are designed to only display a single item if there are multiple records with the same value. If you want to see more than one Best Buy, you need to make a calculated field of the Customer & "_" & BillToCity, and use this as your value list field or use a portal to select them instead.

It sounds as if you are looking up the Ship To based on the selected zip. And I assume you're using a relationship to pull that data. If so, then it's going to pull the first related record's information, which in this case seems like the first record with the selected zipcode. Again, it might work easier to use a portal that's based on the id for the company and lists the related addresses.

[Ugo DI LUCA]

-Queue- said:

use a portal to select them instead.

Agreed. Specially for growing lists as Customer and Products can be.

FileMaker Version: 6

Platform: Mac OS 9

[Scott Pon]

Thanks for the quick reply.

For the first question I do have a Customer Code, I guess I have to use that.

As for the 2nd Question. I have two relationships:

1st I have a relationship between the Sales Orders::Customer Code and the Shipping Addresses::CustomerCode. This way when Order Entry selects a customer, only related records from Shipping Adderss DB show up in the value list.

The 2nd relationship joins the Sales Orders::ZipSelected with Shipping Addresses::ZipCode. A lookup fills in the Ship To Address (Street1, Street2, City, state, etc.)

Oh well. Any suggestions welcome. I will have to try new stuff.

THANKS!

[-Queue-]

If you don't have multiple address for the same customer going to the same zipcode, you could use a concatenated value for the lookup. Create calcs of CustomerCode & "_" & ZipSelected in the order entry file and CustomerCode & "_" & ZipCode in the Shipping Address file. Relate these two fields and use this as your lookup relationship.