Is it possible to link to entities that have a 1:n relationship and select the data in the second entity (n) so that the resultant record set is 1:1.
E.g. I have two entities names Patients and Appointments. Their relationship is 1:n because 1 patient has many appointments attached to it. What I want to accomplish is linking from the Patient only to the latest Appointment based on the AppointmentDate in the Appointments entity.
I can do this in advanced Sql but I would prefer to accomplish it in an aggregate, if possible.
You can see that the problem below is there are multiple rows per patient. I want to select only one appointment, which is the latest one. I also have an attribute named SessionNo (integer). If I could somehow select max(SessionNo), it would return only one record, but I cannot find a way to do this.
Your assistance would be appreciated.
Leon
I thought we don't have to modify the table structure. But glad you solved your problem.
Hey, there is one way to do that is, first, you will on the aggregate selected the max records to be 1. Because you want just one record, then on the sort you are going to sort by the session number and sort by the appointment date! where you are going to get the last appointment with the session number higher :)
Tell me if you have more doubts and if this is what you want
Best regards,
Márcio
Hi,
If i got it correctly, then try below
Step 1 set maxrecord=1 in aggregate.
Step2 Sort with date desc.
It will give you one record with max date.
what do you say about it :)
Amit Jain
Hi Leon Holmes,
Follow the steps:
1. Apply Group by First Name
Your Output will be like below image :
2. Now apply Max property at appointment date.
Final Output : As you can see in below image latest appointment date in output.
Hope this help,
Cheers
Thank you Marcio and Amit.
However, setting Maxrecords to 1, limits my output to only that 1 record and I need all of the Patient records and only 1 of the Appointment records.
Regards,
Thank you Rahul. You suggestions comes close to a solution, except that the aggregate now only outputs the group values, i.e. FirstName and Max(Date). I need access to other attributes as well.
Do you perhaps know of another way to accomplish this.
I am thinking of placing the LatestSessionNo on the Patient entity that I can use in the filter to reduce the Appointment records to 1. But this involves quite a lot of development work. I was hoping I can have a simpler solution.
you can do one workaround,
Select aggregate with sort with patient ID asc and Date desc and get all records. After that use for each loop and insert the first record in the same record list and avoid others if repeat again in the loop using a variable.
And after that use this new record list.
Hope it can help you to solve your problem with low changes.
AJ
Hi all,
of all above answers, only last one of @Amit Jain will work, if you want to use only aggregates.
But this might be a good example for choosing SQL widget over aggregate to avoid post processing your aggregate result. If same patients have many appointments, you do a lot of unnecessary retrieving from the database.
Dorine
I know you said you know how to do it with sql, Leon. But this is for community members that maybe don't.
See attached an oml of a possible way for doing this with a single SQL node.
Did you check the last option, it required few more codes and your issue can be resolve.
Admit,
Yes, your recommendation is a functional solution, thank you.
Dorine,
Thank you for the example. This is a good solution and I am keeping your example for future cases, which come from time-to-time.
However, I have solved this case with a work-around. I have put an attribute on the Patients entity, which holds the LatestSessionNo. I then use this in a filter to select the Appointment record with the LatestSessionNo. Although it uses up space in the database, it is a more simple solution and perhaps more efficient.
Thank you both for your input. I have learnt from it.
Yes, I needed a speedy solution so I went ahead and did it this way, which was before I got your response.