Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Dianne Benedicto
  Where is Dianne Benedicto?
 Davao City
 Philippines
 Dianne Benedicto
 Tags
Subject: RE: set relation to tables
Thread ID: 189283 Message ID: 189344 # Views: 2 # Ratings: 0
Version: Visual FoxPro 7 Category: Databases, Tables and SQL Server
Date: Thursday, August 14, 2008 2:09:54 PM         
   


> Hi all! Could anybody from you help me find out what's wrong with my code? I have 3 tables namely personal, entrance and studentsection. I need to display output from personal table(lastname, firstname, middlename); entrance table(sy,studentid,highschool,interest,dateofexam,dat,descdat,pnlt,desc_pnlt,olmat,desc_olmat,raven,desc_raven) and lastly from student section table(sectioncode). They have primary keys: studentid.
>
> sele personal
> set order to tag id
> sele entrance
> SET orde to studentid
> SELECT studentsection
> SET ORDER TO sectioncod
> seek ALLTRIM(thisform.cSection.value)
> set relation to studentid INTO entrance
> tempfile=sys(3)+'.dbf'
> copy to (tempfile) while ALLTRIM(sectioncode)=ALLTRIM(thisform.cSection.value) fields studentsection.sectioncode,;
> entrance.sy,entrance.studentid,entrance.highschool,;
> entrance.interest,entrance.dateofexam,entrance.dat,entrance.descdat,;
> entrance.pnlt,entrance.desc_pnlt,entrance.olmat,entrance.desc_olmat,;
> entrance.raven,entrance.desc_raven,personal.religion,entrance.remark,;
> personal.lastname, personal.firstname,personal.middlename,;
>
> sele 0
> use (tempfile)alias temp
>
> sele temp
> index on lastname + firstname + middlename tag tempo
>
> REPO form c:\guidance\reports\section.frx for (temp->sy)= thisform.txtschoolyr.value and alltrim(temp->sectioncode) =thisform.cSection.value) prev
>
> use
> erase (tempfile)
> tempfile=stuff(tempfile, at('.', tempfile),4,'cdx')
> erase (tempfile)
>
> But after running my form, there's no record found. What's wrong? Please help me. Thanks.

Hi Dianne
Why are you sticking with this old-fashioned way of doing things, with lots of
SELECT alias, SET ORDER TO, SET RELATION TO, SEEK(),INDEX ON, COPY TO, ERASE
, 12 lines of code, instead of handling all of this with a single command, a single command universally accepted in all modern databases, and fully supported in VFP: the SQL SELECT command:
LOCAL lcSection
lcSection = ALLTRIM(thisform.cSection.value)+"%"
SELECT studentsection.sectioncode,entrance.sy,entrance.studentid,entrance.highschool,;
 	entrance.interest,entrance.dateofexam,entrance.dat,entrance.descdat,;
 	entrance.pnlt,entrance.desc_pnlt,entrance.olmat,entrance.desc_olmat,;
 	entrance.raven,entrance.desc_raven,personal.religion,entrance.remark,;
 	personal.lastname, personal.firstname,personal.middlename;
 FROM Studentsection ;
     JOIN Entrance ON Studentsection.sectionid=Entrance.studentid ;
     JOIN Personal ON Perdsonal.???=Entrance???.???
     WHERE sectioncode LIKE M.lcSection ;
     ORDER BY Personal.lastname,Personal.firstname,Personal.middlename ;
     INTO CURSOR Tempfile

* I don't see in your code by what fields you're joining Personal into Studentsection (or Entrance?)
* In your code there's a relation set from StudenSection.studentid to Entrance.studentid, right?
I believe this is today the way 95% of experienced VFP programmers create a temporary data set for a report.
And 100% of data programmers using any other database than VFP.

-Anders

ENTIRE THREAD

set relation to tables Posted by Dianne Benedicto @ 8/14/2008 9:13:34 AM
RE: set relation to tables Posted by Anil Sharma @ 8/14/2008 9:29:59 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/14/2008 9:49:04 AM
RE: set relation to tables Posted by Anil Sharma @ 8/14/2008 11:20:29 AM
RE: set relation to tables Posted by surinder singh @ 8/14/2008 10:05:26 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/14/2008 10:11:02 AM
RE: set relation to tables Posted by Stefan Wuebbe @ 8/14/2008 10:29:08 AM
RE: set relation to tables Posted by Anil Sharma @ 8/14/2008 11:18:33 AM
RE: set relation to tables Posted by tushar @ 8/14/2008 1:19:18 PM
RE: set relation to tables Posted by Anders Altberg @ 8/14/2008 2:09:54 PM
RE: set relation to tables Posted by Dianne Benedicto @ 8/20/2008 5:00:49 AM
RE: set relation to tables Posted by Anders Altberg @ 8/20/2008 11:23:41 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 5:43:17 AM
RE: set relation to tables Posted by Anders Altberg @ 8/26/2008 11:06:13 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/27/2008 2:17:21 AM
RE: set relation to tables Posted by Glenn Villar @ 8/15/2008 7:40:57 AM
RE: set relation to tables Posted by tushar @ 8/15/2008 7:52:31 AM
RE: set relation to tables Posted by Glenn Villar @ 8/15/2008 7:56:35 AM
RE: set relation to tables Posted by Ulhas Amrite @ 8/27/2008 10:53:24 AM
RE: set relation to tables Posted by tushar @ 8/27/2008 4:53:58 PM
RE: set relation to tables Posted by Dianne Benedicto @ 8/20/2008 4:47:13 AM
RE: set relation to tables Posted by Glenn Villar @ 8/20/2008 6:16:51 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 5:41:39 AM
RE: set relation to tables Posted by Glenn Villar @ 8/26/2008 6:02:59 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 7:40:42 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 7:44:25 AM
RE: set relation to tables Posted by Glenn Villar @ 8/26/2008 8:17:51 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 8:46:57 AM
RE: set relation to tables Posted by tushar @ 8/26/2008 9:25:42 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 9:31:54 AM
RE: set relation to tables Posted by Glenn Villar @ 8/26/2008 9:39:58 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 9:55:15 AM
RE: set relation to tables Posted by Glenn Villar @ 8/26/2008 10:00:37 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/26/2008 10:08:17 AM
RE: set relation to tables Posted by Glenn Villar @ 8/26/2008 10:34:24 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/27/2008 10:41:48 AM
RE: set relation to tables Posted by Anders Altberg @ 8/26/2008 4:35:37 PM
RE: set relation to tables Posted by Dianne Benedicto @ 8/27/2008 2:18:53 AM
RE: set relation to tables Posted by Stefan Wuebbe @ 8/27/2008 7:54:29 AM
RE: set relation to tables Posted by Dianne Benedicto @ 8/27/2008 10:43:17 AM