List problem

[James Patrick]

Hello,

I wonder if anyone can help me with a problem with relationships.

I have 2 tables, clients and contacts, due to clients being able to have multiple contacts, I have a portal on the clients layout where you can enter the contacts. I am now trying to create a list view that shows both the client name and the names of the contacts working there. ideally the client name would show up once with the contact names listed next to it. My solution was to have a client name field on the contacts table that copies the Client name into it when a contact is added. The tables are related via a contact ID and the list layout is based on contacts table via this relationship. This works fine but the problem I have is when a client has no contacts which obviously doesn't show up in the list.

Does anybody know a work around for this? My limited skills are making me pull my hair out

Thanks

James

[LaRetta]

Hi James,

You say the tables are related via ContactID. That is incorrect. Your Contacts table should hold the ClientID and they should be related on that ID instead.

Since the tables are related, you do not need to copy also the Client Name into Contacts. If you wish to display a list from Contacts, just place the related Client fields directly onto your Contact layout.

If from Clients, you want a list of their Contacts, you can do so by creating a calculation (result is text) in Clients with:

List ( Contacts::Name )

... which will produce a multiline list.

If you want instead the list to be similar to: Bill Smith, Nancy Jones, Mary Brown ... then it would be:

Substitute ( List ( Contacts::Name ) ; ¶ ; ", " )

[James Patrick]

Hello LaRetta,

Thanks for the reply and I apologise for not explaining myself properly. When I said list what I meant was list view and you were right the 2 tables were related via the client Id.

Thanks to your reply I have solved what I was trying to do by basing the list view on the contacts layout and then placing the related client name onto that layout.

One thing this does, if you have more than one contact for a client, is it lists the client name against each contact. Is it possible to have the client name only once, so if there are more than one contact there will be a space below the client name until you get to the next client. This is a personal preference, I think it might look better.

Thanks again

James

[LaRetta]

Two ways:

1 (easiest) - add a leading sub-summary part to your layout based upon Contacts::ClientID. Move your Clients::ClientName field from the body up into the leading part. Sort by Contacts::ClientID (important).

2 Attach conditional formatting to your ClientID Client Name field with something like:

Contacts::ClientID = GetNthRecord ( Contacts::ClientID ; Get ( RecordNumber ) ; -1 )

... and below specify 'more formatting' and set the font size to custom 500. When sorted, it the text will disappear on all lines but the first of each ClientID.

Edited December 14, 2011 by LaRetta

[James Patrick]

Great, thanks LaRetta, that's exactly what I wanted to do.

Thanks again

James