Lookup

[MdVries]

I have two tables Dossier and Rolhand

I'am trying to get the newest date from table rolhand into dossier. <-- Text in Red
I'am using virutal field, but i can't get it to work.

Can someone help me fil in the lookup for a virtual vield

Table: Dossier

*DOS_ID Rol_id Rol_date
------ -+--------------+------------
1 05dak-91 21-01-05 <- this is not working
2 02man-11 01-08-05




Table: Rolhand

tab2_id *DOS_ID Rol_date Name
-------+----------+------------+----------
1 1 21-01-05 Marc
2 1 20-01-05 Chris
3 2 25-07-05 Denis
4 2 01-08-05 Walker

 

[DSummZZZ]

I don't understand what you mean by virtual field.
Also, can you elaborate on what you mean by "isn't working"?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[TamarGranor]

It sounds like you need a query like this to collect the data you want:

SELECT DOS_ID, MAX(Rol_Date) AS Rol_Date ;
  FROM Rolhand ;
  GROUP BY 1 ;
  INTO CURSOR MaxDates

Now you can set a relation betwee MaxDates and Dossier to update:

INDEX ON DOS_ID TAG DOS_ID
SELECT Dossier
SET RELATION TO DOS_ID INTO MaxDates
REPLACE Rol_Date WITH MaxDates.Rol_Date FOR NOT EOF("MaxDates")

 

[MdVries]

I am using Microsoft Visual FoxPro 8.0, i don't know if i can use SQL/Fox pro code.

I use another virtual vield and this one is working!!
look_mgr.get_value(dossier.cost_code,'cost.cost_desc')

dossier.cost_code i use to get the vield cost_desc in table cost.

Now i want to use dossier.dos_id to get Rol_date in table rolhand

I tried to use this code
look_mgr.get_value(dossier.dos_id,'rolhand.rol_date') but i get empty result.

The reason of the is there are more then one vield in the result, that's my guess.

 

[MdVries]

When i put this in a query:

SELECT MAX(Rolhand.rol_datum) AS datum FROM trv_bv!rolhand WHERE Rolhand.dos_id = ( "0000000040" )"

I get one result "04-04-2004" it works!!!! wow!



But when i put the code in an virtual vield in Microsoft Visual FoxPro 8.0 i don't get any result.

My question for now is: can i use sql/foxpro code in an virtual vield??

 

[MdVries]

Later i tryed this one:

SELECT MAX(Rolhand.rol_datum) AS datum FROM trv_bv!dossier INNER JOIN trv_bv!rolhand ON Dossier.rec_id = Rolhand.dos_id WHERE Rolhand.dos_id = ( "0000000040" ) ORDER BY Dossier.dos_id, Rolhand.rol_datum

No results. But when i type this direct in one query it works fine.

 

[TamarGranor]

I don't know what you mean by a "virtual field." Can you explain?

Tamar

 

[TamarGranor]

Also, this section is for older versions of FoxPro. Since you're using VFP 8, you're better off asking your questions in one of the other FoxPro sections.

Ta,ar