Calculated field using lookup() with argument in another table

[jmatienza]

Hello! I have a question regarding the lookup() function for which I have been unable to find the answer.

Let's say I have an Age field in table "A", which I would like to be a calculated field using the Date_of_birth field in table "B". The calculated field expression would have this form:

age(lookup("B", [filterstring], "Date_of_birth"),transaction_date)

Let's say I want to use the Date_of_birth in the record in table B where Full_name_A in table A is identical with Full_name_B in table B. In such a situation, what is the correct syntax for [filterstring] please? I feel sure it must be extremely simple, yet nothing I try seems to work.

Then again, I might be extremely simple!

[G Gabriel]

Re: Calculated field using lookup() with argument in another table

age(lookup("TableB", "tableA->Full_name_A=tableB->Full_name_B", "Date_of_birth"),transaction_date)

Sometimes, you have to use a more table descriptive name, for example:
age(lookup("TableB.dbf", "tableA->Full_name_A=tableB->Full_name_B", "Date_of_birth"),transaction_date)

or even the full path.

[Tom Cone Jr]

Re: Calculated field using lookup() with argument in another table

Jose,

The lookup functions tend to be among the slowest in Alpha Five. To do their work they have to open the external table, find the desired record, return it, and then close the external table. Very handy, but slow.

In general I find the indexed lookups a bit faster. To fetch a date of birth I would probably use LOOKUPD() instead of LOOKUP().

I've attached an example I built in vers 8 for you. I expect it to work fine in vers 7.

The CurrAge field in the SalesPeople table is defined in field rules as a calculated field. It uses LookupD() to retrieve the dob from the Contacts table based on a fullname search key. It then converts the dob to an age. It returns zero if there's no match or if the dob field is empty in the lookup table.

Misgivings:

Many would advise you not to name a table field using the same name as one of Alpha's built in function. Your "Age" table field bears the same name as Alpha's AGE() function. I recommend you change your table fieldname to something else.

Many have had bitter experiences basing searches on full names. Names are not unique. Users often mistype them. People change their names. Searches fail or return the wrong records. Not a reliable approach in many cases. I would probably never base a table field rule on this kind of "match" and would look for alternatives.

Many recommend against storing a person's age in a table. The "age" for any person in the table is only valid until their next birthday. People have different birthdays. Even if you update all the field values on January 1st each year your records will be wrong on January 2nd if someone has a birthday. All the age() function can do is compute the age at a particular time. A snapshot in time, that can be wrong the next day. As a result folks usually just compute the age when it's needed by embedding a display calc field in the form or the report. They do not store it in the table.

[jmatienza]

Re: Calculated field using lookup() with argument in another table

Hello, G! Thanks for your post.

I tried following your example. Here's a screenshot of the actual expression I used. Except for the filter string, the lookup() arguments were supplied by the autocomplete feature.

I also tried "Encounters->Datestamp" for the other age() argument, as well as "Patients.dbf" as the first lookup() argument, to no avail.

What am I doing wrong?

[Tom Cone Jr]

Re: Calculated field using lookup() with argument in another table

Assuming the Encounters table is where the field rule is found, try changing

[CODE]"Encounters->Full_name = Patients->Full_name"

to

"Full_name = Patients->Full_name"

[/CODE]
This won't work for the reasons Tim points out, below.


--------------------------------------

"Datestamp" is a character string. The Age() function is looking for a date type value. If "Datestamp" is a date type table field in the Encounters table, and if the Encounters table is where the field rule is defined try

changing

"Datestamp"

to

"eval("Datestamp")"

Jose, this would be very easy to correct for you if you posted sample tables.

[Tim Kiebert]

Re: Calculated field using lookup() with argument in another table

Assuming the Encounters table is where the field rule is found, try changing

"Encounters->Full_name = Patients->Full_name"

to

"Full_name = Patients->Full_name"

However since the filter is being used to filter the patients table as part of the lookup the portion on left side of the '=' would refer to the patient table so I would try

"Full_Name = " + quote(Encounters->Full_Name)

[jmatienza]

Re: Calculated field using lookup() with argument in another table

Hello, Tom!

Thanks for your suggestion and attached example. I'll give it a whirl.

I appreciate very much the additional points you made. They are good reminders.

Well, the reason I used full names because it looks better, with proper spacing, in a single type-in field, compared to showing three separate fields. So far I've been able to avoid most of the pitfalls you mentioned by using a lookup table of full names. For the lookup table itself, the full name is calculated from the sum of the last, first, and middle names. So the user never enters the full name as such.

With regard to the age, I was calculating it as it was on the date of the transaction. However, you are indeed right in that, since the date of birth and the transaction date are already in the database, the age is better calculated only as needed on a form or report. I'd like to know, though, if the solution to my original question about calculated table fields will also work on a calculated layout field, that is, to compute the age from a date that's in a table other than the one to which the layout is based.

I really appreciate the pointers and the insights I'm picking up from this board.

Thanks again!

[Tom Cone Jr]

Re: Calculated field using lookup() with argument in another table

Good catch, Tim. You're right. Thanks. -- tom