How to show a field based on an ID

[Lene]

In a layout, I would like to let a user enter a number in a field. I don't want to store this number, but instead I want to store an ID, that relates to the number.

I have a database with the tables "Zip" and "Customer". In "Zip" amongst others I have the fields: ZipID, ZipCode, City. In "Customer" amongst others I have ID and ZipID. I've made an relationship between ZipID. My problem is, that in the layout of Customer, I can only enter the ZipID and then show the ZipCode and City. But to the user, the only number that make sense is the ZipCode.

I've been trying to solve this problem for at couple of days, so please help me...

[RalphL]

Are you using this table as a souce for the city name after the user enters the zip code? I know that you are not supposed to use data fields as keys but in this case I would use the ZipCode as the key and forget about the ZipID.

[Lene]

Yes, I'm using the Zip table as a source for the city name and yes, I could use ZipCode instead. But if I don't use an ID, I will get into trouble in the feature. For instance if the ZipCode change, I will have to change all the records using that number, but if I use an ID, I will only have to change the zipcode in one record in the Zip table and then every record showing the change zipcode will be right. Perhaps this change isn't likely to happen, but I can se a lot of other cases (in other tables), where is likely to change in the near feature.

That's way I find it a problem!

[RalphL]

Zip codes are funny things. A city can have many zip codes and a zip code can have many cities, but just one Post Office. Most of the time we use the Post Office as the city. You only need the first 3 digets of the zip code to idnetify the Post Office.

I usally use a lookup to get the city (Post Office) from the zip code. I also use a lookup to get the state.

[Lene]

Well, I'm from Denmark and our Zip codes are a bit different :-))

If I understand the use a lookup correctly, this will make a copy of the data, sow refering to my second mail, I'll get redundant data.

Lene

[H.P.]

There are 2 ways to solve your problem.

The first method is to use 2 fields, one on top of another. lets assume that the current layout is the customer. First, you put the zipID field from the Customer table on the layout, make that field transparent (background transparent, pen transparent, and text match the color to the background, white if the background is white). And then make that field a drop down menu which link to the zip code table. In the drop down list, set the first field to zipID and the second field to zipCode or whatever field you want the user to see. After you done that zipID field, put the zipCode field from the zip table on top of the zipID field (move it to top), and uncheck the option "Allow field to edit in browsable mode". Now, the zipID field is completely hidden and hide underneath the zipCode field. When the user clicks on the zipCode field, he will see a drop down menu of zipID from the zip table.

There is a problem with the first method though. The layout doesn't looks that nice. After selecting the ID, you will have to click outside of the field to see the zip code.

The second approach is almost similar to the first. Instead of hiding the zipID underneath zipCode, hide it under a button such as > . and put the zipCode field besides the zipID fields. Using this approach, the user won't have to click outside of the field to view the zipcode.

H.P.

[Lene]

Thanks for the solutions. I've just tried the first method and it help at bit.

Now I only have the problem, that I don't want the user to view an popup list with the ZipID, but only ZipCode and City, but I've to include the ZipID in the popuplist as the first field to be able to store the ZipID. Is there a way to make ZipID invisible?

Lene

[H.P.]

As far as I know, it's impossible I would love to see if someone able to find a way not to show the ID on the pop-up menu.

You should try the second method to make it less visible to the user.

You can still display both the zip code and city along with the zip ID though. Make a calculation field that the combination of zip cod and city and use it in the value list.

Btw, you can also make a script that opens a new window listing all the zip code and lets the user click on the zip code that correspond to the zip code he likes. Many people doesn't like pop-up windows though

[Lene]

I'm using FileMaker Pro on a Mac and perhaps it's a bit different to Windows, because the first method is not visible to the user. I've already made a calculation field to combine zip code and city, sow the solution is acceptable.

Normaly I'm using Microsoft Access on a PC and are used to work with IDs, sow it's a step back, not to be able to make a layout on top of a relational database, that looks perfect to the user.

But, thanks for the help sow fare!

Lene