Calculate latest record date from child record

[gzamba]

Dear Brendan,

Last year I asked you how I could calculate the latest date of a record of a related database.
At the time you said that “you did not have a function yet to grab any values from a child record and display it in a field on the parent record”
I see now that you have added this functionality.
So can you helo me out on how to do this?

I have created a patient database with a main form called “patients” with all the patients’ basic information and a second (linked) form called “visits” where each record has the details of each patient’s individual visit.
The “patients” database has a field called “first visit” – which is automatically populated with “today’s date” when the first record is created.
I want to have another field called “last visit” within the “patients” database that will display the date of the last visit from the “visits” records.
So in plain programming words it would be something like “calculation = get date of latest record from “visits” table”.
How can I achieve this – if this is even possible?

Many thanks for your reply
George

[Brendan]

Hi George,

Unfortunately for your requirement, that still won’t work. Tap Forms can get the Total, Minimum, Maximum, and Average of a related numeric type field, but not of a date type field. That would have to be an enhancement.

[Mike Schwartz]

George,

I think you can make this work.

ASSUMPTIONS: I’m assuming that your Visits form has a date field, let’s call it “Date_of_Visit”. Also, I’m assuming that your Patients form is linked to your Visits form in a manner like this: Your Patients form contains a Link-to-Form field named “Visits_Log” that links to your Visits form, and that you populate this field by linking to all the visit records for the patient.

SETUP: Create a new calculation field on your Visits form named “Date_Number” with the simple formula [Date_of_Visit] and Result Type=Number. So this field will have the same value as your date field, only represented as a big number. You don’t need to include this calculation field on any of your nice custom layouts.

Now go to your Patients form. Create a new calculation field named “Last Visit”, and click Edit Formula:
1) Type this: DATE(
2) Look at the list of available fields. It should include a section of linked fields with the heading “Visits_Log”. Find the field name “Date_Number” and double-click it.
3) In the formula box, click the little down-arrow at the end of the field tag, and select “MAX” from the pop-up menu.
4) Complete the formula by typing: ,”YYYY-MM-dd”) (or some other date format, such as “MM/dd/YYYY”
5) Your completed formula should look like this: DATE(MAX(Visits_Log::Date_Number),”YYYY-MM-dd”)
6) Set the Result Type to Text and click Save

RESULTS: This should result in your Last Visit field displaying the latest visit date for all the linked Visit records for that patient. If you neglect to link the most recent visit, then it won’t be reflected in the Last Visit field.

Hope that helps,
Mike

[Mike Schwartz]

Brendan,

I also tried a simpler version of the formula, just setting it to MAX(Visits_Log::Date_Number) and setting the Result Type to Date. It works, but for some reason the Default Layout displays that field in a greyed-out fashion. Why would that be?

Thanks,
Mike

[Brendan]

I’ll fix that greyed out issue. It was just because I was trying to distinguish an editable field from a non-editable field. Calculation fields are non-editable. Just a bug I guess that it only applies it Calc fields that returns dates.

[Mike Schwartz]

One more oddity about the calculation date field: If I add it to a custom layout and merely click in the field and then click elsewhere, then the field contents disappear. To restore the field I need to click the Refresh button.

[Brendan]

Hmm… not seeing that behaviour myself.

[Mike Schwartz]

Well, if you can’t reproduce it I guess there’s not much you can do. I see the same problem after quitting and restarting Tap Forms, both in version 5.1.3 and 5.1.4 (1658). I’ll let you know if it persists beyond my next reboot, and with the next beta. Not mission-critical in any case.

Thanks,
Mike

[gzamba]

Thanks Mike!

It works like a breaze!
This has been troubling me for years!
You are awesome.

George

[gzamba]

Hello Again Brendan and Mike,

the solution Mike suggested works fine, but for some reason Tao Forms does not automatically caldulate this “Last Date” field.
Let me explain:
I have a form called Patients with all my patients’ info and a related form called Visits with my patients’ individual visits.
According to Mike’s suggestion I have created a new calculation field on my Visits form named “Date_Number” with the simple formula [Date_of_Visit] and Result Type=Number.
I have then created a new calculation field named “Last_Visit” on my Patients form
with this calculation
DATE(MAX( Visits::Date_Number ),”dd/MM/YYYY”)
the calculation works fine and I can see the result of my Date_Number field on the Visits form, but for some reason It does not get calculated automatically in all the records of the Patients form.
I have to manually go to each record, click on the formula, pretend I am editing it and then, save it to trigger the calculation.

Why is this happening? Can I trigger the calculation for all the records at once somehow?

CORRECTION
the calculation is not automatically triggered, but when I do it manually as above, it works for all the records that have been altered so far…
SO, how can I make this work automatically?

Many thanks for any help offered
George

[Brendan]

Hi George,

There’s a refresh button at the bottom of the records list view which you can click on which will trigger a recalculation of all the fields. So give that a try to see if it works for you.

Thanks,

Brendan

[Author]

Posts