# Thread: Calculation Help

1. ## Calculation Help

I'm hoping that this is simple, but I just can't seem to get past it.

We have an employee system where there is an employee table and attached to it is a position history "Child" table. We are trying to develop a report where we show the current title (that one is easy) and the first time the employee was "permanent". That is designated by a date in a field called "From" and a code of "RA" in a "status" field of the child record. Some employees have been here many years, so there are many Child records.

What I am trying (unsuccessfully) to do is to get the first non-blank calc field "PERMANENT". I'm going around in circles.

Here are the calcs:

Code:
```firstperm	if(calc->Permanent="".and.calc->Firstperm="","",calc->Permanent)
lasttitle	last(Titles->Title,GRP->Lfname)
PERMANENT	IF(POS_HIST->STATUS="RA", DTOC(POS_HIST->FROM), " ")
PROVISIONL	IF(POS_HIST->STATUS="PA", DTOC(POS_HIST->FROM), " ")
realfirsrt	if(calc->Firstperm<>"",calc->Firstperm,calc->Permanent)```
any thoughts would be appreciated.

Tom:confused:

2. ## Re: Calculation Help

Maybe

TABLEMIN("POS_HIST","STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")

Some employees have been here many years
Might have to use an earlier year than 1900.

Well, the first expression would be ok for the earliest employee. For an individual employee id.....

TABLEMIN("POS_HIST","ID = "+quote(id)+" .and. STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")

supplying the quoted id from the current record in the report.

3. ## Re: Calculation Help

What I am trying (unsuccessfully)
how was it unsuccessful? Did it error due to your setting the second (false) part of the if() to a character instead of a date-- you used " " instead of {} - curly brackets which are for a empty date value.

EDIT--which Stan's expression obviously eliminates!

4. ## Re: Calculation Help

Is the "RA" status field value appearing in multiple records for an employee, and you're trying to find the first one? Some sample data would help us offer suggestions.

In the absence of sample data I'm thinking you could build a custom UDF that will open the "child" table, indexed on the employee id number. Fetch_find the first related child table record, then step through them until you find a record that has the right status value. Return the date field (or its character equivalent) from the function.

If performance becomes an issue you could offload the desired status field values to a separate table, once, using a batch processing script, and then record first RA transactions there going forward.

Last notion would be to reverse the table relationships in the set, and filter the primary table to only show records that have the right status field values.

5. ## Re: Calculation Help

I see Stan is suggesting something similar, but with much less coding required. Would be interesting to see if the tablemin() approach was faster than the indexed find by key sequence I described. Both will require alpha to open a new instance of the child table for each employee.

6. ## Re: Calculation Help

Thanks for all your replies.

Stan,
TABLEMIN() shows the first instance of the date in the table. I just need it for the subset of records pertaining to the particular employee. I even put an additional filter in your code to make it test the Employee->empID against the POS_HIST->EMPID, to no avail.

Code:
`TABLEMIN("POS_HIST","Pos_Hist->Emp_Id=Emp_Id.and.STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")`
Mike,
The error I was getting was that in the "firstperm" calc, I was testing the firstperm calc field, and I don't think Alpha can handle that.

Tom,
you gave me an idea to build another set with just "RA" type records. I'll test that out and let you know.

To all, thanks for your suggestions.

Tom

7. ## Re: Calculation Help

It worked here with a test variable. I would explicitly pass the emp_id value into the function.

Code:
`TABLEMIN("POS_HIST","Pos_Hist->Emp_Id="+quote(Employee->empID)+" .and. STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")`

8. ## Re: Calculation Help

Stan,

I'll give it a whirl, thanks.

Tom

9. ## Re: Calculation Help

Got it working. Thanks STAN!

It turned out the the field EMP_ID is numeric. That causes all sorts of issues in a character string filter.

Here is the code that works:

Code:
`TABLEMIN("POS_HIST","Pos_Hist->Emp_Id="+val(str(Emp_Id))+".and.STATUS = 'RA' .AND. FROM > {01/01/1960}","FROM")`
The report carries on for a little bit, but since I get the results I need, I don't care.

Again, Thanks.

Tom:)

10. ## Re: Calculation Help

We should be able to speed it up using dbmin() rather than tablemin() but I'll have to work on that. Michael Humby showed me how one time but I've never utilized it.

It was dbsum() that I was remembering. Don't see how to make it work with dbmin().

The filter for the tablemin() could use isdate() to invalidate the blank "from" values rather than that fixed method I suggested.

+" .and. status='RA' .and. isdate(dtoc(from))"

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•