I could use some assistance with calculating ...

references
(Thomas Cunningham) #1

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.

(Tony Fader) #2

@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

(Thomas Cunningham) #3

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!

(Tony Fader) #4

Here’s an example app: https://www.appsheet.com/samples/A-basic-demo-of-table-references?appGuidString=6ffad040-b04a-4325-aa73-ace5df8ba1b5 appsheet.com - Table References - A basic demo of table references Table References - A basic demo of table references appsheet.com

(Thomas Cunningham) #5

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!

(Tony Fader) #6

@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.