integrating a lookup into a calculation

[egulk2000]

hi there!

 

i have been using this calculation for reformatting a phone number to look like a phone number.. i got help for the gist of this from the forums here (thanks guys! smiley-smile )

 

 

Let(

phone 1 = Substitute ( Phone 1; ["-" ; ""] ; ["/" ; ""] ; [" " ; ""] ; ["(" ; ""] ; [")";""] )

Case (

Length ( Phone 1 )=10;

"(" & Left (Phone 1; 3) & ") " & Middle(Phone 1; 4; 3) & "-" & Right(Phone 1; 4);

Length ( Phone 1 )=7;

Left(Phone 1; 3) & "-" & Right(Phone 1; 4);

Left (Phone 1;3) & Middle (Phone 1; 4;3) & Right (Phone 1 ; 4)

))

 

 

but i am trying to implement a lookup so that it will auto-enter based on a relationship to a contact table.. something like this?

Phone 1 = Lookup ( Contacts 9::Phone )

 

but i cant seem to figure out how to merge these together, i am still trying to learn calculations....

 

if anyone has any ideas i would really appreciate it!!

 

thanks

[Techphan]

Check out the FILTER command. It can strip out all keystrokes but the ones you list from a string of text when used like this: Filter (Phone1;1234567890).

 

Now to your lookup...

 

What is it that you are trying to do (I assume look up a contact based upon phone number and "auto-enter" the contact's information into a form) ?

 

What if a phone number is entered that is NOT in your database?

 

I think I would use a global field into which the user enter the phone number, then script the find process to establish if there is an existing contact with that phone number or not (but it is still early and I have only had one cup of coffee, so there might be one or many better ways to go about this...)

[egulk2000]

thanks for your input

that is a cool idea, but what happens if a business has a name as a phone number, like 800-callnow, or something like that, i cant filter out just the numbers..

but what i am trying to do is input conact information (street, address, phone, email ect..) from my contact database into this current layout from a different table..

so what i did was have a little plus sign which i put over the contact id field and when the user clicks on the plus sign a drop-down list appears of the contacts available in the database, and after clicking on a contact name, all of the fields lookup the values through the contact id.. which was pretty easy to set up for everything else (contact info), becasue they didnt already have formatting calculations.

but i cant get the phone numbers to lookup through the auto-enter, so that is where i am currently stuck..

[David Head]

As per my other post, I would suggest trying the following auto-enter calculation for the field Phone 1 where Contacts::Phone is a related contacts field:

 

Let ([
phone = If ( IsEmpty ( Phone 1 ); Contacts::Phone; Phone 1)
phone = Filter ( phone; "1234567890" )
];
Case (
Length ( phone ) = 10; 
"(" & Left (phone; 3) & ") " & Middle(phone; 4; 3) & "-" & Right(phone; 4);
Length ( phone ) = 7; 
Left(phone; 3) & "-" & Right(phone; 4);
Left (phone;3) & Middle (phone; 4; 3) & Right (phone; 4)
)
)

[egulk2000]

thank you so much for your input, i really appreciate it!!

 

the trouble i am running into here is that i want this to function as a lookup would, where if i would change the contact fk to another contact, the phone number would update to the other contacts through the relationship..

 

but in this case, (because it tests if it is empty), if the field is filled with a previous contacts info, it will keep the old contact info and not update to the new contact....

 

i guess i have a catch 22 here, i want it to update when i change the relationship 'connection' ID, but i dont want it to update if i dont?

 

is there any other better way to do this?

[David Head]

OK, so lets go back a few steps here. Here's a question:

 

Why are you using a field to store a copy of the related phone number? Is there some reason that the related phone number (displayed and not editable in that location) could not be used? Is there an alternate contact number for a specific project?

 

In short, why does the phone number need to be copied from a related contact record to a project record?

[egulk2000]

i have built a database to track an event for a band.

 

so i have a contact table to track contacts for the band, people who are hiring the band for a specific occasion, a seperate event table to track the details about that specific event, and an order table to track the details of an order for a specific event (and a band members table)

 

i have associated the contacts table with the events ( multiple times for multiple contacts / and a contact line for even more contacts ) and the orders tables to automatically enter in the contact information into that table, when the foreign key of the contact is entered.

 

i would like to have the information entered and stored so that if for some reason the contact information is changed or deleted, the event /order information is safe, and if for some reason the contact information is different for a particular event / order (say shipping over billing) from the contact information, it will be saved on the event page associated with that particular event.

 

so basically, i want to be able to transfer over the information as a reference so that i dont have to re-enter in a contact information if i dont have to, but i would like the ability to change some information, especially the phone number which can be changed from event to event..

 

i hope that i explained it well enough..

 

thank you very much for all your help

[David Head]

i have associated the contacts table with the events ( multiple times for multiple contacts / and a contact line for even more contacts ) and the orders tables to automatically enter in the contact information into that table, when the foreign key of the contact is entered.

So do you have a join table between event and contact? This will enable multiple contacts per event.

 

i would like to have the information entered and stored so that if for some reason the contact information is changed or deleted, the event /order information is safe, and if for some reason the contact information is different for a particular event / order (say shipping over billing) from the contact information, it will be saved on the event page associated with that particular event.

OK to have it stored. But understand that it is stored for a reason. You don't want it updating.

 

so basically, i want to be able to transfer over the information as a reference so that i dont have to re-enter in a contact information if i dont have to, but i would like the ability to change some information, especially the phone number which can be changed from event to event..

So if you have a join table like this:

 

EVENT ------ CONTACT

 

You can have many roles for an event. These roles can have names (such as roadie, performer, groupie) and also start and end dates where people come and go. If a role changes person, then you add a new role and close off the previous role. Each role will hold a copy of contact information from the contact record and can be updated at any time (separate from the contact) for a specific event.

 

As mentioned by someone in the other thread, it is your data structure that needs to be improved to solve the problem you have.