Not Getting Data

[wstar]

Currently I'm working on a test Invoice system. Right now I just have two tables. One is Test_Main, and the other is Test_Invoice. Just for testing, I only have 3 fields in each; CustomerName, CustomerPhone, and CustomerComments. I'm relating the tables with CustomerPhone (because their phone number is their account). When I go to Test_Invoice and type in the phone number (with the dashes and such) I have the other fields doing a Look-up to get the name of the customer. My problem comes in when I want the phone number to auto format.

For example, user types 1234567890 hits enter and it formats the field and shows 123-465-7890. Doing this I just coded the following in the Calculated Value under Auto-enter:

:

Let (

[

rawPH = Filter ( CustomerPhone ; "0123456789" )

];

Left ( rawPH ; 3 ) & "-" & Middle ( rawPH ; 4 ; 3 ) & "-" & Right ( rawPH ; 4 )

)

Now, when I type in the phone number (12346790) and hit enter it formats correctly showing 123-456-7890 but the other fields did not retrieve data, like the customer name does not appear. However, when the user types the phone in correctly (123-456-7890) the customer name and the other fields display the data.

Could the reason be the calculations are running after the other fields do their look-up? If so, how can I fix this problem? I do not want a button running a script. I want this to be as simple as possible (like hitting enter and it works).

[Lee Smith]

Using a phone number for a relationship is a BAD idea.

You should create a Unique Id for each client and use that for the relationship instead.

Lee

[comment]

I agree with Lee. However, what you describe should be working, so check your work, esp. data types.

[wstar]

Ok, now I've added one more field. After the user types in the phone number i have a 3 fields doing a look-up. One is customer name, Account Number, and Comment. When the above code formats the number in the correct way... the look-up works with text fields but not numbers. The text fields are set up the SAME way as the number but the TYPE is different. Does anyone have an idea on whats goin on? OR does anyone have a better solution in auto-formatting the phone number and having other fields doing look-ups. (Must be able to use look-up based on phone number even if its a bad idea).

And By the way... thank you for your quick replys on the past post.

Edited March 7, 2006 by Guest