Date Calculations using Linked Forms

[Stephen Froggatt]

I am using the YYMM function to give me a Text output of a baby’s Age at Baptism using the fields “DOB” and “Baptism Date”. Straightforward – except for the fact that both of these fields are on linked forms.
I want the formula to read YYMM (DOB; Baptism Date) -> Text
However, although the fields I wish to use are available on the left, when I click them in they appear with the “COUNT” prefix, as per the Screenshot.
I have noticed that the “COUNT” function here returns the value 1.
The result is that YYMM returns 1 – 1 = 0 Years, 0 Months
Is it possible to use linked fields in this way, or does it depend on the way the forms have been linked? (They are both One-to-Many since a single Baptism Visit could potentially realise more than one baby to be baptised, and so more than one baptism service.)
Many thanks for any help.
Revd Stephen

Attachments:

You must be logged in to view attached files.

[Brendan]

You can get what you want from the other direction, but not from the parent form. That is, on your Baby form you could add a Calculation field that references the Date Visited field from your Visit form.

If you think about it, you have multiple visits, each with its own Date of Birth and Baptism Date. So which one do you want?

The Calculation field is not able to reference an individual Link to Form field’s record.

The Script field can though. To do that you would have to fetch the Link to Form field’s records, then pick out the first record and then do your calculation.

[Stephen Froggatt]

Thank you, Brendan, very much indeed.
Your reply is crystal clear and helps me understand better how the Linked Forms work.
I have moved some of the fields around a bit on my forms so that they are now referenced in the correct direction. Everything now works as intended!
You have also inspired me to learn some JavaScript programming in order to make use of “calculation fields on steroids!”
Many thanks again for your full, prompt and helpful response.
Every blessing,
Stephen

[Brendan]

Hi Stephen,

Excellent! I’m glad that has helped a bit.

Tap Forms will write a bunch of the Script code for you by using the Snippets provided and just by double-clicking on the fields in the Fields list on the Script Editor. So you can do some basic stuff quite easily there.

I know, easier said than done :) Hopefully it won’t be too difficult for you to learn a bit of JavaScript.

Thanks,

Brendan

[Author]

Posts