Lookup Related Values with Multi Key

[Himself]

Hello All,

I am looking to add a field which concatenates the "related" values of a multi key.

I have 2 tables Appointments and Users.

(Appointments)

ID

Start

End

User ID

Function ID

Description

ShowUsers - Calc field I want to create

(Users)

ID

Name

Initials

The User ID field in the Appointment table is a check box set so multiple users can be added to an appointment.

All is splended except in one of my Layouts I want to show the user initials in one line (ie. RC,LP,DT). The problem is that I am storing the User ID for a lookup so I have this to deal with

1

3

6

I need to look up the related value of each line and concatenate them. What is the function that lets me look up a value in a related table based on another field value.

My magic function

getrelatedvalue(RelatedField,LookupField,LookupValue)

getrelatedvalue(User::Initials;User::ID;getvalue(Appointments::User ID;1))

Does someone know of this custom function?

Is there another way?

Thanks for any help

[bcooney]

You really should have a join table btw Appts and Users rather than a multikey. On the Appt form layout, you would have a portal to the join table.

[Himself]

The portal would be to cumbersom for the layout, I would still want to concatenate the values into one field. Imagine whatever calendar program you use going from Daily view to Weekly to Monthly the realistate gets real tight. I still want the users to see all people assigned to a specific appointment in one line rather than scroll a portal.

Realisticaly the percentage of appointments with more than one user will be small less than 5% probably.

Edited October 8, 2009 by Guest
Added Percent comment.

[comment]

Try:

Substitute ( List ( User::Initials ) ; ¶ ; "," )

[Himself]

Thats outstanding Comment thanks, its perfect and just in time because I get to leave for home now.