I could use some assistance with calculating a clientโs age (at arrest) based on their date of birth and the date they are arrested, these dates being in two different tables. Within the Client Table (ClientTbl) there is static information such as: ID Number, Last Name, First Name, Sex, Date of Birth, etcโฆ Within the Court Table (CourtTbl) there are fields like: ID Number, Court Date, Arrest Date, Charge, Age, etcโฆ. Within โAgeโ field in the Court Table, I would like to calculate the clients age at time of arrest based on these two dates. I have included an example below:
ClientTbl: Sample Data ID Number Last Name First Name Sex
DateOfBirth
Ter124
Doe
John
Male 12/05/1974
CourtTbl: Sample Data ID Number CourtDate ArrestDate Charge
Age
Ter124
04/13/2018 04/12/2018 DUI
???
Obviously, this client would be 43 years old, but that is the formula that I am having trouble with because the dates are in to different tables and I need to match ID Numbers. Thank you in advance for your assistance.
@Thomas_Cunningham I assume your ID Number is the primary key of your Client table. In that case, make the ID Number of your Court table a ref column. Then you can de-reference DateOfBirth column from the Court table. help.appsheet.com - References Between Tables
If you need help calculating the age in years based on the date of birth, see this thread: https://plus.google.com/+MultitechVisions/posts/QKfzMzkDWxH References Between Tables help.appsheet.com
Thank you for the information and I checked out De-reference and the video. I think I understand how it could work, but being new to this, Iโm still a bit lost. Any other suggestions or hints? Thank you!
Hereโs an example app: https://www.appsheet.com/samples/A-basic-demo-of-table-references?appGuidString=6ffad040-b04a-4325-a... appsheet.com - Table References - A basic demo of table references Table References - A basic demo of table references appsheet.com
Iโm afraid that i am not getting it. I have looked at the app that you suggested and there doesnโt seem to be similar formulas there that i could use to teach myself the steps needed.
The ID field in the ClientTbl is the primary field and the ID field in the CourtTbl is a ref.
I would think it would be something like: Any(Select(CourtTbl[ID], โArrestDateโ - ClientTbl[ID], โDateOfBirthโ)
Iโm lost, thank you in advance for your help!
@Thomas_Cunningham If you set up a reference column from your court table to your client table, then you can access the clientโs birthdate like this:
[Ref Column to Client].[DateOfBirth]
You can see a similar formula in the example app, where when creating an appointment for a client, it accesses that clientโs nickname.
User | Count |
---|---|
35 | |
31 | |
30 | |
19 | |
17 |