"Nested?" Value Lists

[Matt Palmgren]

I have 3 databases - Contacts, Appointments, and Photos. Each Contact can have multiple Appointments, and for each Appointment, there can be multiple Photos. In the Contacts database, there is a portal that shows all of the appointments that have been scheduled for each contact. When you click on a portal row, and enter an appointment date, a new appointment record is created in the appointments database. A contact serial number is automatically populated in the appointment record that associates it with the contact. There is a fcontact name field in the appointment database that is a lookup based on the contact serial number.

Now, in the Photos database, each photo needs to be tagged so I know during what appointment it was taken. I created a contact name field that has a value list associated to it. The value list lists all of the contents of the contact name field in the appointment database, works great. I then vreated an appointment date field in the photos database and assigned a value list to it that will show all of the appointment dates in the appointments database based on the contents of the contact name field in the photos database. That works as well.

However, since there might be more than one appointment on the same day for the same contact, I wanted to create another value list that would show all of the appointment times, based on the appointment dates, based on the contact name. I hope that was clear enough.

[CobaltSky]

Hi Matt,

It can be done...

What you need to do is create a calculating text field called 'ContactDate' in your photo database which concatenates contactID and Apppointment date. You'll then need to do the same in the Appointments database.

Once the calculating fields are in place, you should then create a relationship in the Photo database that matches the ContactDate field to the ContactDate in the Appointments database.

The you can create an Appointment time value list in the Photo database which uses values from the appointments database via the new relationship.

That should do it!