Report creating woes

[mun]

I have a rather large relational database and have to create a rather complex report to show information from records that are related by records that are related by other records (goes 3 levels deep). In order to do this, I have a few scripts checking certain criterion and copying appropriate fields and pasting them in a "report" field. this works fine - if nobody else is in the record that I need to copy data from, but if someone is, the pieces of information I need do not show up in the report. Is there a better way of doing this? I thought of creating calculated fields, but that would not go 3 levels deep appropriately.

[Fitch]

Doing it with calculated fields may be exactly what you want. It's called [color:"blue"]pipelining. If file A is related to B and B to C... make a calculated field in file B that is C::name (for example). Now, since A is related to B, you can display that related field in your report.

If you have a lot of fields to pipeline, it may be better to pipeline the key fields, and then set up the relationships you need. Remember, your pipelined field has to be on the left side of the relation -- the right side has to be indexable.

Two other approaches would be 1) a constant relation, where each file has an auto-entered or calculated field that = 1; or 2) setting a global field to the 2-deep 3rd file key field, and creating a relation to the 3-deep file based on that.

[mun]

That sounds good, but the problem I see is say that a particular record in file A relates to a record in file B and that record relates to several records in file C (through a portal). How would I pipeline all of the appropriate records in C to a report based on A?

[Keith M. Davie]

"That sounds good, but the problem I see ..."

Just a point of curiosity: Do you see this problem as a result of testing the answer provided, or do you see this problem as a result of your own understanding and speculation?

[mun]

Keith,

It is due to some previous testing of the solution. I've already tried pipelining the data to the first file. What I do not understand is (if this is the proper solution) is how to bring the data to a report if the record that another record from another file that it is referencing to references many other records in yet another file. The solution that I had tried to impliment was to create a script that would check if a record contained records from another file from a portal and then go to those records and determine if those records contained yet more records within its portal and develop a tab-separated text file to be exported to Excel - this works fine, if nobody else is in the file (I also have issues with the tab-separated file offsetting the data with certain records for some reason).

For example, say I have 3 different files: Plan.fp5, Animals.fp5, Location.fp5. Now I have some records in Plan.fp5 that I have grouped together to sell to a buyer and in each record of Plan.fp5, I have 3 Animal.fp5 records per Plan.fp5 record and I also have stored multiple locations in which I can find these animals so that I can track where to get them for the buyer. I have not found a way to pipeline all of this data back to Plan.fp5 so that if someone wants to print a report that gives ALL of the details of the Plan items, they can easily build it and not worry about others being in the records that are being reported on. Again, I've tried doing the pipelining thing and have found that it pipes back only the first referenced record. My question is: Is there a better way to pipe this back without running into what I've run into or is there another way that I am not thinking of right now or am I doing this pipelining thing all wrong?

I hope this is clear, I am trying to be as specific as possible without confusing with too many details. (it is probably too late)

[Fitch]

In your case what I'd probably do is create a global text field in Plans, call it gPlanKeys.

Make a script that loops through the records and sets gPlanKeys to the id of all the records, so when you're done the field might look like this:

Plan1

Plan2

Plan3

OK, now your script will Go To Related Records (show only related, Animals)

Now you have the related records to the whole found set in Plans. You can then run your report form the Animals file. Or, you can repeat the exercise above in the Animals file, and end up in Locations, and run the report from there. That would probably give you the most flexibility in formatting your report.