defining fields to show related records

[graemet]

Hi, hope somone can help.

I have a one-to-many relationship set up between 2 files (CONTACTS and EVENTS).

i.e. each event has one Contact but each contact can have many events.

ContactID appears as a primary key in CONTACTS and a foreign key in EVENTS.

I want to be able to enter the name of a contact in EVENTS and have the related fields (email, telephone number etc.) appear in EVENTS (possibly in a portal).

My problem is that I do not know which fields in EVENTS should be lookup fields and which should be calculation fields, if any!

Contact names in CONTACTS are in two fields (Forename and Surname) but I want to enter the full name into one field in EVENTS.

Hope this is clear. If more info is needed then please say.

Any help would be much appreciated.

Thanks.

[Ugo DI LUCA]

Hi,

I want to be able to enter the name of a contact in EVENTS and have the related fields (email, telephone number etc.) appear in EVENTS (possibly in a portal).

Well, if it is a one to many relationship, then your selected contact should be unique. Which means you don't need a portal here.

My problem is that I do not know which fields in EVENTS should be lookup fields and which should be calculation fields, if any!

A calculation vs a lookup.

If you want the data to update according to the data in the Contact File (ex : Contact email changed should reflect in ALL Event record), then use a calc. If the "history" should stay whatever changed would occur in the Contact File, then use a lookup.

Contact names in CONTACTS are in two fields (Forename and Surname) but I want to enter the full name into one field in EVENTS.

Full name would be a calc cFullName = Forename & " " & Surname

Either related or again looked up fields.

[graemet]

Thanks for the reply Ugo but i'm still stuck!

When I display the ContactID on the layout in EVENTS and enter it I get the related details appearing in the fields (Contact Name, Email etc.)

However, I don't want to have to enter the ID number and when I change it the same data remains in the fields!!!

I have defined the fields like this:

(Field Name; Type; Options;)

Contact Name; Text; Auto-enter Calc (Clients::Forename & " " & Clients::Surname);

Contact Email; Text ; Auto-enter Calc (Clients::Email);

Should any of these fields be Calculation fields with a text result rather than Text fields with a calculated value?!!

Sorry, I think i'm being brain-dead here.

Again, any help would be appreciated.

Thanks.

[graemet]

Ok, I now have the related fields changing when I change the ContactID number on the EVENTS layout. Because the Contact Name field is a calculation field it is now not modifiable.

How can I get this to work by changing the Contact Name rather than the ContactID?

[-Queue-]

Auto-enter calculations won't update once they're initially calculated. A lookup, however, will update.

What if you have two contacts with the same name? It's safer to use the ID for the relationship than the name.

[graemet]

Thanks for the reply Queue.

Yeah, the duplicates thing was an afterthought of mine also.

I am using the ID for the relationship between the tables but I don't want to show this on the EVENTS layout.

The user will not be able to remember the IDs of all the clients so I want him to be able to enter the name and their contact details will appear.

I assume this is not possible? (even with a script?)

[-Queue-]

You could do this with a portal to show all possible matches from Full Name to Full Name, if more than one exists, with extra fields such as City or something else to identify the correct one.

The process could be something like the user enters the full name into a global that is related to the full name in the Client file, then they click a button which performs a script like

If [Count(fullname_relationship::ClientID) > 1]

Go to Layout [similar layout with portal displaying all matches from fullname_relationship]

Else

Set Field [ClientID, fullname_relationship::ClientID]

End If

The duplicate layout with portal would have a transparent button over the portal row attached to a script to Set Field [ClientID, fullname_relationship::ClientID] and Go to Layout [main]. Now the ClientID will be set and you can use a ClientID to ClientID relationship to lookup the other client fields.

[graemet]

Thanks Queue,

I'll give it a try.