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..

 

[Olaf Doschke]

> I also tried to interchange the parent table, and noticed that there’s no error encountered
Then double check the relation you set in your form and the relation you set in the report. They must be opposite, since you get the error, and when both are established you get the error.

Look closely from where the relation starts and what the index used is.

The simplest solution might be to not set the relation in the report at all.

Also: Where do you set or see the relations? In the DE you typically see primary-foreign key relationships, but they are from froreign key to primary key, from child to parent and don't have to do with SET RELATION. In a report with 1:n relationship you make the inverse relation from primary key to foreign key and also Select the employee table and SET SKIP to the service table.

Bye, Olaf.

 

[jhed]

Then double check the relation you set in your form and the relation you set in the report. They must be opposite, since you get the error, and when both are established you get the error."

the relation i set in my form and in the report was different then i get the error but when i set the relation the same as the report and the form it wont display the data in the service table..only the employee data like the id and the name was display in my report...

i always set my table relation in data environment which i can just drag the index to the other table.i just dont know this time that i get that error and if i change the relation it wont display the result...

tried also your suggestion not to set the relation in my report but no luck, did not display my data in my child table..

thank you for the reply...

 

[Olaf Doschke]

>when i set the relation the same as the report and the form it wont display the data in the service table
Well, you can set the relations similar in TWO ways, and you obviously choose the wrong way around. You don't get the cyclic reference, but you also don't get detail records scanned, if you don't also SET SKIP. The report always needs the report main table as the currently selected table and relations have to start there. If you start from a parent and scan through it, a simple relation just relates the first child record matching, so you only get the first child per parent. Only if you also SET SKIP to the child table a skip in the parent (as the report does it scanning the report table) instead of skipping to the next parent first skips through all child record before it skips to the next record in the parent table.

Also relations via SET RELATION and the graphical relations you see in a DE are totally different things. By default, if you add two tables related by foreign key into a DE vfp automatically draws a line between them. That by itself has no effect whatsoever. Only if you go into the properties of the DE objects and set several settings you get a relation as you can also set in code via SET RELATION. And the graphical DE, even if you include the properties of the DE object, DOESN'T offer the possibility to SET SKIP.

Most probably your report works in the default datasession, so it's using the same datasession as the form, otherwise you wouldn't have had the cyclic relation problem. Don't do anything but setting SET SKIP from the parent (employee) to the child (service).

If your form has two grids, one showing the employees, one showing services, the services grid shows all services for an employee selected in the other grid. But the two grids have two recordsource, while a report only has one driving table, like a single grid. So the situation in a report compares to a single grid, even if you set some columns controlsources to fields of another table and even if a relation is set, a single grid will only have one row per employee and only show the first related service in such a column. So read about SET SKIP.

Bye, Olaf.

 

[TamarGranor]

Rather than using a relation for your report, use a query to put the data you want to report into a single cursor and then report from that. In my view, that's always the right way to do reports. That way, you can prepare exactly the data you want in exactly the order you want it, and reports are then simple.

Tamar

 

[jhed]

sir olaf, i look for the set skip function you were talking about but i dont know how to start.

sir tamar, can you sight an example for your suggestion, i am not familiar also with the query function..
thank you

 

[Olaf Doschke]

Either go with Tamars suggestion or mine.

If you read about SET SKIP and don't know how it works, what did you not understand from the help topic about SET SKIP?

I can only quote from there:

Creates a one-to-many relationship among tables.
SET SKIP TO [TableAlias1]

And what I said earlier:

SET SKIP from the parent (employee) to the child (service)

which means:

SELECT employee
SET SKIP TO service

Bye, Olaf.

 

[jhed]

i tried your suggestion sir but no luck at all.. maybe i have missed on my coding.
Can you suggest sir what should i used on my coding or idea to display the data on my second table in my report

 

[Olaf Doschke]

You need to do this additional to the relation, not instead of it.

Here's some sample code using the northwind sample database with it's employees and employeeterritories tables. I link them with a relation and set skip to the child (employeeterritories) table, then instead of a report I browse fields of both tables and though the employee table is driving the browse, the browse also shows all territories related to each employee:

Close Tables All
Close Databases All
Open Database _samples+"northwind\northwind.dbc"

* Despite of your data surely differing, this demonstrates how to relate a parent and child table and display data as if joined into one table/cursor/workarea
* This part can be made in your form instead of in the report, your report doesn't need anything in it's data environment:
Use northwind!EmployeeTerritories
Set Order To EMPLOYEEID   && EMPLOYEEID

Use northwind!Employees In 0
Select Employees
Set Relation To employeeid INTO EmployeeTerritories
Set Skip To EmployeeTerritories

* Now this "emulates a report having fields of both tables, the browse window is driven by the main,parent table employees and shows all related territories, not just the first one:
Browse Fields Employees.LastName, Employees.City, EmployeeTerritories.TerritoryID

This is essentially doing quite the same as if I'd SELECT Employees.LastName, Employees.City, EmployeeTerritories.TerritoryID FROM Employees LEFT JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INTO cursor report and BROWSE or report that cursor instead. Do the same without the SET SKIP and you'll only see each first territory ID.

If you're not the guy understanding RELATION and SKIP, then ask Tamar, I think she'll gladly take over again and explain her plan. You're perhaps even better off, as SQL is more widely used than this style of relating tables. Both ways have their pros and cons.

Bye, Olaf.

 

[jhed]

thank you for the sample sir.. i get the idea and already outputted the result but still sir the child table wont display its data's. either the browse or the report wont display the service data...what i'm missing sir..

 

[Olaf Doschke]

Well, I don't know what your missing. A checkllist perhaps?

The services table is open?
The relation is set from employees to services?
The skip is set from employees to services?
The report runs in the same datasession as the form (default)?
The report has some report controls displaying fields of the service table?

If you can answer all that with yes you'll see the services listed for each employee.

Bye, Olaf.

 

[jhed]

sir i think my answer to your question sir is yes..below are the link to the snapshot of my form,report and code you suggested..thank you sir..i appreciate your time in helping me on this problem.

Link for my report

http://i62.tinypic.com/nvr0hs.jpg

for the code

http://i61.tinypic.com/dxfsrk.jpg

for the form

 

[Olaf Doschke]

I can't judge for sure from your pictures alone, but the code is missing to set an index order on services on an index for the employeeid. First you have three records showing in the services table, just not the expected records, I assume.

You can't relate the employee id to the service id, because that would relate employee 1 to service 1, while they may or may not have anything to do with each other.

Read about the concept of primary and foreign key. A 1:n relation between a parent and child table is not made with both primary keys but with the primary key of the parent and the foreign key of the child. So your service table has to have an employeeid field, an index on that field and that has to be active. Only with that prerequisite fulfilled the relation will relate to the right service records.

The essential part in my sample code you're missing is

Use northwind!EmployeeTerritories
Set Order To EMPLOYEEID

The employeeterritories, which here is the child data instead of services, has an employeeid field, which is indexed with an index tag named employeeid, too, and that order is set. Only this way the relation is relating to the right hild records.

Your report will not work, if there is no field the service table has in common with the employees, and that's not talking about identical field names, like ID. If all table have an ID that's fine for a primary key, but it's not relating the data in the tables, just a common field name is no relation. What is relating a service to an employee is a link between them, the service has to point to the employee, for example. If there is no direct relation, you're missing an additional table in the whole outset, eg an employeeservices table having both an employeeid and serviceid so each employee can do n services and each service can be done by m employees.

I don't know your data design, but in that case there would not be a direct 1:n relation between employees and services, but you need two relations from employees to employeeservices and from employeeservices to services.

Bye, Olaf.

 

[jhed]

Use northwind!EmployeeTerritories
Set Order To EMPLOYEEID"
sir so its important that there is a database base on the code you were suggested? as i wrote to the image, i haven't created my database. it just the tables itself..

 

[Olaf Doschke]

No, it's not important. What's important is an index tag and set order. The other line is just repeated to see in which table the SET ORDER is done.

Bye, Olaf.

 

[jhed]

sir, still the same. is there any other way to display the information of my second table..

 

[Olaf Doschke]

You're so close to it.

So, how are your tables defined, what main fields are in each table? And how are they related - just by the fields?
What is the index you use in the services table? (In your code you set order to id in the employee table, that doesn't matter at all, you can remove the SET ORDER TO id for the employee table)

I used the northwind database, because it's sample data I don't need to define and everyone having VFP has it. Take a closer look at the involved tables employees and EmployeeTerritories in there. Both employees and EmployeeTerritories have an employeeid field, in the employee table it compares to your ID field, it's the primary key field of that table. In EmployeeTerritories this field is a foreign key field, it has an index tag employeeid and that is used via SET ORDER. The employee table is not sorted by any index in my sample code, that's unnecessary, for the relation to work the EmployeeTerritories table is sorted by it's employeeid tag and so all records for a certain employee are found by the combination of SET RELATION and SET SKIP.

Bye, Olaf.

 

[Olaf Doschke]

One more thing: CLOSE TABLES ALL is just used in my sample code to have a defined outset, no tables open. As you kept that line in your code, the service table is not open at all, you only have the employee table open, the code must error in several lines. Don't close all tables just because I do in the sample code, this can't be the part that's essential and needed in case all tables already are open. The essential part is the orderg, the relation, the skip, and all this has to be picked up by the FRX, so as I earlier said, remove ALL THINGs from the FRX data environment, and set the frx datasession to DEFAULT, not private datasession. Otherwise all preparation you do in the form is not seen by the FRX at all. Of course nothing changes in the report behavior.

Bye, Olaf.

 

[jhed]

i am just a newbie in this visual fox pro so apologize if i don't know the some term that u used. So maybe i just post my tables for you to suggest what should the best way around to fix this.

http://i60.tinypic.com/119tlwi.jpg

employee table

http://i60.tinypic.com/119tlwi.jpg

service table

i also search those function but its hard to understand if there's no example. i am not familiar with the northwind database until now i searched google and found an article Chapter 5 Enhancements in the Reporting System, there i found the database you were mentioning, truly it will help to fix the problem but its hard to understand at the beginning it just that i dont know how to start.

 

[Olaf Doschke]

Nobody knows your knowledge of VFP and I don't treat anybody new here in this forum as a newbie in VFP, too.
You already have a relation in your form, so we can assume you know the concepts behind it.

You have the northwind database on your harddrive. I opened and used it. Have you tried the sample code 1:1 as is at all?
After you ran the code, you can take a look at the datasession windows and see how things are set up. The 1:n relation defined by SET RELATION and SET SKIP is shown in the right hand side of the datasession window.

Also in regard to read the help on a command, simply select it in source code or in the command window and press F1, you're directed to the help topic on this command, then.

Don't post screen shots simply copy & paste code, there is no difference of the VFP editors than word or notepad or any other text editor in regard to copy and past functions. You posted the employee table twice, I don't see how the services table is defined.

Bye, Olaf.