Searching Records

[Charles Hoens]

I have an odd little problem and the answer to it so far has eluded me. Kind of reminds me of one of Tom Cone's old teaser puzzles. (Does he do those anymore?)
I've tried using several loops, but can't seem to find the correct answer. Maybe someone out there might have a solution.

I have two tables. One is a list of Employees. The other is a transaction table. In the Transaction table, there are three fields of importance. One is the employee id (Empid) which matches a similar field in Employees. The second field contains clients with whom the employee has worked and the third field is the date the employee worked with the clients.

There are hundreds of employees. The transaction table has several thousand records at this point with different employees, working with different clients. The question is, how do I produce a list of employees and the clients they have worked for AND the last date they worked for them?

I tried looping through the transaction table for each employee, but I keep getting doubles of the clients showing up. How can I avoid this and just get a simple list?

Thanks!

[Tim Kiebert]

Re: Searching Records

Check out the code below. Run this in AlphaSports. It will give you a list of customers with their last invoice.
The idea is to sort on the customer and the inverse of the date. As we loop through the records grab the first record for each customer then skip over the next records if the customer_id hasn't changed. As soon as a different id is found drop out of the inner loop and record the next 'first' record.

dim tbl as P
dim custid as C
dim output as C


tbl = table.open("invoice_header",FILE_RO_SHARED)
tbl.order("customer_id+invert(cdate(date))")
tbl.fetch_first()

custid = tbl.customer_id
output = ""

while .not. tbl.fetch_eof() 
	output = output + tbl.customer_id+":   "+tbl.invoice_number+" - "+tbl.date + crlf()
	while tbl.customer_id = custid .and. .not. tbl.fetch_eof() 
		tbl.fetch_next()
	end while
	custid = tbl.customer_id
end while

msgbox(output)
tbl.close()

In order to get last visit for each client per employee you would need to tweek that a bit. Combine the employee and the client in the sort order and the comparison key in the while condition. Somethig like the code below. Obviously you need to adjust the field and table names. I changed the output to a file because I figured it wouldn't fit in a message box.

dim tbl as P
dim key as C
dim output as C


tbl = table.open("Transaction",FILE_RO_SHARED)
tbl.order("Emp_id+Client+invert(cdate(date))")
tbl.fetch_first()

key = tbl.Emp_id + tbl.client
output = ""

while .not. tbl.fetch_eof() 
	output = output + tbl.Emp_id+" - "+tbl.Client+" -            "+tbl.date + crlf()
	while tbl.Emp_id + tbl.client = key .and. .not. tbl.fetch_eof() 
		tbl.fetch_next()
	end while
	key = tbl.Emp_id + tbl.client
end while

fl = file.create("C:\Emp_by_client_last.txt",FILE_RW_EXCLUSIVE)
fl.write(output)
fl.flush()
fl.close()

tbl.close()

[Tim Kiebert]

Re: Searching Records

And this version only prints the employee once for multiple clients.

dim tbl as P
dim key as C
dim emp as C
dim output as C


tbl = table.open("Transaction",FILE_RO_SHARED)
tbl.order("Emp_id+Client+invert(cdate(date))")
tbl.fetch_first()

key = tbl.Emp_id + tbl.client
emp = tbl.Emp_id 
output = ""

while .not. tbl.fetch_eof() 
	output = output + ut(tbl.Emp_id ) + crlf() + replicate("-",30) + crlf()
	while tbl.Emp_id  = emp  .and. .not. tbl.fetch_eof()
		output = output + tbl.Client + "             " + tbl.date + crlf()
		while tbl.Emp_id + tbl.client = key .and. .not. tbl.fetch_eof() 
			tbl.fetch_next()
		end while
		tbl.Emp_id + tbl.client
	end while
	output = output + replicate("=",30) + crlf() 
	emp = tbl.Emp_id
	
end while


'msgbox(output)
fl = file.create("C:\Emp_by_client_last.txt",FILE_RW_EXCLUSIVE)
fl.write(output)
fl.flush()
fl.close()

tbl.close()

Of course you could also just copy the relevant records to a temp table and then run reports from there.

PS Apologies to Prof. Pickypicky for not fully commenting the code.

[Tom Cone Jr]

Re: Searching Records

Charles, a brute force approach to get a list of all clients each employee worked with:

Sort the transaction table by employee + customer + inverted date worked
Use a script that involves three nested loops.
Loop through the employees - grab the employee name each time it changes
.. and for each employee loop through their customers - grab the customer name and date worked each time it changes
.... and skip the transactions that have the same customer name. i.e. skip til the customer name changes

[Charles Hoens]

Re: Searching Records

Thanks Tim for the time and effort on this. Makes sense to me what you are doing. I tried several things before resorting to the board. One was to get all the records then eliminate the ones I didn't need by using some form of the duplicate record concept.

Tom, thanks. I seem to do everything by brute force! I'm always looking for that clever idea I missed!

[MoGrace]

Re: Searching Records

So what happens after you get this list? Do you bill the client for the employees time? Or is this just to see who worked for whom last?

Just curious...

[Charles Hoens]

Re: Searching Records

Hi Robin!

I've created a huge employee time management system, which then turns into an invoicing system. For this client of mine, they have many employees working in home health care. Each employee works for various clients and then sends in their timeslips for the time worked. These are then keypunched and the payroll is then computed. I then "flip" that table around and view it by Client. That way the company can produce invoices for each client. Thus, if a client has one or more employees working there, it is all reflected on the invoice.

What the manager would like to see, is just who is working for what client during specific times, so he can keep track of what is going on as it is not reflected on the invoice.

I hope this all makes sense!

Charlie

[MoGrace]

Re: Searching Records

Thanks Charlie, it makes great sense.

[Charles Hoens]

Re: Searching Records

Well, if anyone needs to know about time manipulation, let me know. I learned a lot on this project.

[Charles Hoens]

Re: Searching Records

Here is my final code. Many thanks to Tim who helped get me on the right track. Sometimes just the discussion of a problem on this board spurs creative thought!

Anyway, Tim could not read my mind, but I decided after some study to write this to a table. I then created a set with Employees at the top and this new table as the one-to-many. From there, it was a cinch to create a simple report and format it as I wanted or was required.

Here is my final code:

dim t as P
dim key as C

t = table.open("Timehours",FILE_RO_SHARED)
t.order("Empno+Conid+invert(cdate(edate))")
t.fetch_first()
s=table.open("ConsumerIDs")
s.zap(.T.)
key = t.Empno + t.conid


while .not. t.fetch_eof()
	 s.enter_begin()
	 s.empno=t.empno
	 s.conid=t.conid
	 s.edate=t.edate
	 s.lastfirst=t.lastfirst
	 s.consumer=t.consumer
	 s.enter_end()
	 
	while t.Empno + t.conid = key .and. .not. t.fetch_eof() 
		t.fetch_next()
	end while
	key = t.Empno + t.conid
end while

s.close()
t.close()

As always, I hope this helps someone else!

[Tim Kiebert]

Re: Searching Records

Charles,
Your welcome, happy to help. Good to hear you got it worked out.

Cheers Tim

[Charles Hoens]

Re: Searching Records

Thanks to you for without the idea of the loop and index, I never would have gotten it done!

Thanks again.

Charlie

[Al Buchholz]

Re: Searching Records

Or you can build a summary operation that sorts on the employee and client code and gives the max date for work. ie the last date worked.

Your results are in a table.

[Ray in Capetown]

Re: Searching Records

Or you can build a summary operation that sorts on the employee and client code and gives the max date for work. ie the last date worked.

Your results are in a table.

A necessary reminder indeed.