Cyclic Relation

[jhed]

I have two tables named Employee and service. My parent table is the employee which is the id field set to relation to the id field of the service table. I want to print out a report with the two tables data.. Example if I locate the id of employee which is 1 in the employee table then it should display all the services under employee id 1..I already display the information but the problems is in my report. Whenever I output the report I have this error “Cyclic Relation” which in my opinion the problem is in my setting the relation of my two tables in the data environment. It is the same relationship I used in my form with my report, my parent is the employee and child is the service table.. Please help me I don’t know what to do next. I also tried to interchange the parent table, and noticed that there’s no error encountered but the data from the service was not display in my report…so it means that was not the solution to interchanged the parent and the child table.. This is the only time I encountered this error which I know there’s nothing problem in my setting my table relation.

Thank you in advance..

 

[NasibKalsi]

Hi Jhed:

Olaf has given you more than enough clues to solve this problem. It is important you read each Key word as pointed out by Olaf.

I will try, it might help.

* Do this in a new prg file and experience how it works.
close databases
* Step 1
use employee in 0 alias mm
use service in 0 alias ss  

* Step 2
select ss
index on ID to t_id

* Step 3
select mm
set relation to ID into ss
set skip to ss
go top
display all field mm.ID, ss.ID, ss.FIELD1, ss.FIELD2  && Replace FIELD1, FIELD2 with the real names from your service table
* Another way to see the data
brow field mm.ID, ss.ID, ss.FIELD1, ss.FIELD2  && Replace FIELD1, FIELD2 with the real names from your service table

 

[Olaf Doschke]

Nasib, assuming employee.id and service.id are the primary keys, your code will relate the two tables 1:1, which is surely not wanted.

I still wait for jhed to show the structure of the services table, but assume there is a employeeid field in there, that is the one to index and to set order to, if you want a 1:n relation.

Otherwise I agree, enough said. I even said this already:

If all table have an ID that's fine for a primary key, but it's not relating the data in the tables

 

[NasibKalsi]

Olaf, I may have missed Jhed' question. But if the goal is to display each employee.ID (1 and the corressponding all records in 'service.ID' then the code will work. The 'set skip in ss' will set the relation from 1:1 to 1:n ?

I made a quick test and seems to work fine.

 

[jhed]

sir nasib, i tried your suggested code and still no luck, i think it just the same as the suggested of sir olaf, sir, the use command that you used is for database sir,right? coz i have no database created. if the first step is important then i will create a database for may tables? i commented the first step coz of i havent created my DB..

 

[NasibKalsi]

Jhed,

They are just standalone tables. (???) . Also you should not comment out any line of code, else, it will not give you any results. Step 1 opens the tables so that you can operate on them. If you do not open the tables you will get tons of errors. Find out where they are on your computer and then replace step 1 with the following code, ie, fully qualified table name.

* New Step 1
use c:\dir1\dir2\employee in 0 alias mm && drive c: and dir1, dir2 are the names where your tables may reside
use c:\dir1\dir2\service in 0 alias ss

You can go to the IDE command windows and try typing the code as in the example and evaluate if it works according to your requiements.

Important Note: Jhed, it is a good idea to read all the commands as are in the example code and what Olaf recommended.

 

[Olaf Doschke]

Nasib, technically a relation is 1:n with SET SKIP, but if you relate ID with ID, you relate the wrong records, also if service.id is primary key each ID will only exist once and so you stay with a 1:1 relation, even if you also SET SKIP. Tha main point is, that you have to let the relation end in the field of service, that corresponds to the employee id, not a service id. You have to make the right relation.

This can't work this way, do you see this now?

jhed, you still haven't posted the services structure. You posted the image of the employee table twice. The essential info missing is, whether it has a field for employeeids, how it's named and if it's indexed.

Bye, Olaf.

 

[Olaf Doschke]

the use command that you used is for database sir,right?

USE is the command to open a table, a database is opened with OPEN DATABASE. As said earlier you can get the explanation of any command by selecting it and typing F1, so after you copied code to code window in vfp you can select/highlight any command and press F1.

I'll go back to one thing you mentioned: You say you already have established the relation between employee and services in your form so all you need on top of that is a SET SKIP and we discuss that for 5 days now.
Aside of that, you haven't even reacted to what I said about the report. Your report can't have a private datasession, if you prepare the data and relation and skip in the form datasession. The report won't see what you do in the form having it's own private datasession. You can try whatever you want, you might already have established the right preparation, but your report won't see it. So to check the relation working, don't use the report, simply BROWSE fields of employee and services to see, whether the relation is working.

If you have a problem with the english language, there are a lot of spanish, french, german, arabic, indian, russian VFP developers and corresponding communities, forums, and I'm sure you find something in your country, too.

Bye, Olaf.

 

[NasibKalsi]

Good Morning Olaf:

Your explanation is correct. But to accomplish that in VFP, I think that is the only way. Would you be able to test my example code ?

I assume, ID is primary key in employee.id and service.id. If that is not the case, we need to wait for the structure for those tables.

Jhed: You can also get help by typing in 'vfp command window', ie,
help USE
help SET SKIP TO
help SET RELATION
and so on ...


nasib

 

[Olaf Doschke]

>But to accomplish that in VFP, I think that is the only way
I don't know, what you mean.

Your code would work technically, if I had an employee and services table structured in a the way you use it. Your code is taking into account what jged said right at the start: "I have two tables named Employee and service. My parent table is the employee which is the id field set to relation to the id field of the service table."

I hope jhed didn't mean "id field of the service table" literally, but was talking about the foreign key id for employees. Otherwise you don't get a correct relation and therefore not the expected report result.

Bye, Olaf.

 

[jhed]

your right sir olaf on what i stated "id field of the service table". my relation is set to the employee id to the id field of service..sorry i didn't notice it early what sir olaf is trying to mention.

 

[Olaf Doschke]

You first have to have data you can relate to each other, otherwise all talk about how to connect tables technically is fruitless.

If you don't have an employeeid in the services table it's not a parent/child table relationship and you can't relate them.

If you have a serviceid in the employeetable the parent/child relation exists, but is opposite of what you think.

If neither table has the id of the other table as foreign key those tables are not directly related, but a third table has to connect them.

Bye, Olaf.

 

[NasibKalsi]

Jhed: Would you be able to show some data for both tables. And then also show what you want to see as a report ? Just example data, do not show all the fields.

Nasib

 

[jhed]

hello sir nasib, sorry for the late respond..The ff. are the requested data by sir nasib.

Link for my sample data
Link for my service table
Link for my employee table

thank you for your time sir

 

[Olaf Doschke]

jhed,

the report output shows data of the first employee combined with two service records listed below. That already shows the relation is opposite of what you said. The employee table is the parent table and the services is the child table. The terms parent and child are derived from real parents and children, a parent can have 1 to many children, a child has exactly 2 parents in the real world and 1 parent in the database world, but what is common again is the parent exists before a child can exist, because the child has a field in it pointing back to the parent. In this case judging from the report the employees need to accomplish the service, that's the meaning of the relation here. So your one-to-many relation has to start in employees and ends in services, because 1 (each) employee accomplishes (potentially) many services. So employee.id is the primary key field of employee and services.id is the foreign key field of services, pointing to the employee table.

That's one thing.

Your services table id field shows repeated values, so it's not a primary key field, it seems it's really the employeeid. That's bad naming for one thing, you shouldn't call a foreign key field ID only, if you relate more tables with each other this will get confusing and can easily mix-up, besides fields need unique names in each table, so if you go about this always, you never can have more than one relation to another table. Since there seems to be no primary key in services, no other table can point there, so services can't be a parent to some other table. That doesn't matter in this case, but it's a good practice to let every table have a primary key.

Finally, all this stuff is just a bit of explanation of terms, but I don't see a problem. You're already at your goal!

The report show employee 001 (in it's header) and the first two services records, that also have ID 001, all other services are not in the list, as they are not related to employee 001. Services record 3 seems to be related to employee 002 and service records 4-6 are related to employee 003, which doesn't even exist yet. The report is complete. If you make the employee table the active workarea before calling the report you would get many pages with one employee each and the services accomplished by him. If you have a filter on employee for eg 1 id you only get that employees page.

So now that has cleared up, the question is, what is the problem and what do you want instead?

Bye, Olaf.

 

[jhed]

thanks sir olaf for your explanation. actually my sample data in the given snapshot is just an example which is requested by sir nasib. that was not exactly the report generated when i run my application.i manually inputted the values from my service table. the below link is the actual report generated when i run the program..

Link report generated

 

[Olaf Doschke]

OK, and how does the report look in the report designer?

Bye, Olaf.

 

[jhed]

what do you mean sir olaf..the design of the report or the relationship of the tables?

 

[Olaf Doschke]

First the report design. We've discussed how the relation should look like in mayn ways already. But if it doesn't work, your report design may not be, what it should be.

Bye, Olaf.

 

[jhed]

sir what would be the best suggestion you can share. i am willing to start over again my report.i already do the report builder wizard but the same output.

 

[Olaf Doschke]

Well, first let me see the design, it may already be allright, but from the output screenshot we don't see what is in which report bands. That's what's interesting.

Bye, Olaf.