Sorting Linked records within a Record

[Jeffpdoes]

I have 2 linked tables, CLIENTS and APPOINTMENTS, where I link an APPOINTMENT record to a CLIENT record, and can have multiple APPOINTMENTS linked to a single CLIENT record.

Is there a way to sort the linked appointments in the client record?
For instance, could I pull the next upcoming appointment from that linked field and put it in a separate [Next Appointment] field?
Or, could I pull all past appointments to a [Client History] field?

Am I thinking about this the wrong way, completely?

Thanks.

[kuovonne]

It sounds like you have multiple linked record fields joining two tables. Yes, it is possible to have automations move a linked record from one linked field to another, as long as both fields point to the same table.

You will need to have formula fields and rollup to identify which linked records need to be moved. Then you need the actions to make the changes.

If each appointment links to only one client, you could use the update action to remove the link from one linked record field and add the links no to another field.

If you have a more complex situation, you might be better off using a script to handle the logic regarding which linked record fields to use.

[Jeffpdoes]

Thank you.

I’m having trouble getting it to work just right. Maybe you can help me fill in the gaps.

I created a Rollup field using ArrayUnique(values) to collect the correct Appointments, but it is only collecting the date information, not collecting the linked record.

I also tried using an update record automation, but when there is a new appointment to add to the History field, it overwrites the previous entry. Is there a way to update the field without overwriting what is already there?

Each appointment links to a single client, but clients can have multiple appointments.

[Jeffpdoes · Accepted solution]

I figure it out:
I’ve got the A client record links to several appointment records. Then I have a lookup field that looks up the linked appointments and filters them “where Appointment is before Today”. Then an automation which triggers when the lookup field is updated, and copies the lookup field contents into a linked record field called “History” which turns all the lookup info into linked records.

Not sure if I explained that clearly, but it works.

[I found some advice from ScottWorld on another post that clued me in to how to automatically link records by pasting the primary field of a record into the linked record field. That was the missing link]

Thanks for the help.