lookup field

[Bikeman17]

Hi Geeks

Forgive me for the long explanation but it is necessary. Alright, I got POS that depletes line items (one to many files). In Invoicing, I use a portal that comes from the Stats db. The Invoice Code is used as relationship. Information like pricing, qty, product description is shown in the portal. So far, so good.

I got a Customer db where I store client details. I also use a portal for information like Date, Invoice Code, Balance and Discharge. That portal is relational with the Invoicing db. It works perfect.

I got a second portal for purchasing details like Date, Qty, Description and Pricing. It displays the items bought by the client. That information comes from Stats db. You remember ... one to many files. The only way I found to make this portal works is by creating a relationship with the phone number. I cannot use the Invoice Code. It won't display new information if the client comes back to the store. I created a Phone field in Stats. I looked it up with the Phone field in Invoicing. So, when I create a new invoice, the user must enter the client details including the phone number. He selects items in the portal. That creates a record in Stats. At that time, the Phone field looks up and the phone number enters in. Then, the Customer db displays items bought.

Here's my problem: For some reasons, it doesn't work all the time. I mean the Phone field remains empty in Stats. The lookup didn't work. Some POS' work fine, some don't. I guess the problem is related to the Phone field in Stats. I can't pin point the problem. This field is Text, Storage = On, automatically turn indexing on if needed, Validation = none, Auto-Enter = looked-up value (phone field in Invoicing).

More information:

The phone field in Invoicing is looked up with the Phone field in Customer.

[LiveOak]

What is the relationship that drives the phone number lookup based upon? In general, using a phone number is (or any other field the user recognizes) to relate two files is a bad idea. If someone comes back a day later and "corrects" the phone number entry, there goes your connections. Why not use a record serial number instead?

-bd

[Bikeman17]

hi bd

Sorry the late reply. I was out during the last days.

The relationship is done with the invoice code. This relationship brings information like product code, description, qty, pricing. Each time a product code is entered in the portal in Invoicing, it creates a record in Stats. This allows me to deplete the line items in Inventory.

[LiveOak]

Phone number sounds a little dangerous to use. I can for stored several ways, as text or as a number. As text, the two text strings in the two files have to match exactly (e.g. (123) 456-7890 doesn't match (123)456-7890 or 123-456-7890. Also make sure the fields are set to index and are of the same type. You probably will have to set the type to number, unless everyone is VERY careful how the phone numbers are formatted.

I had a wierd one the other day where I had related values that didn't always appear. I finally found a relationship based upon a related field. Somehow, the order changes in the files were made to get to this state avoided all the error checks AND it worked SOME of the time!

I would probably concentrate on one related set that should match and dissect and re-enter the phone values to try and locate the problem.

-bd