Exporting Portal Data

[kwarren]

I have a sports database with a portal of related records. I want to be able to export the data for use in a Quark file. I need to export all the data in the main table and all the related portal data as one line.

Before I started using related records, I would use a calculation field like this to get the result I wanted:

Home_Team_ID & " " & Home_Team_Final & ", " & Visiting_Team_ID & " " & Visiting_Team_Final & ¶ & Player_ID & " " & Scoring_Type & " " & Scoring_Distance

With the data in a portal, I'm only getting the first record of the portal. How do I get all of the related records in the portal to export?

[comment]

I need to export all the data in the main table and all the related portal data as one line.

Not sure what exactly you mean by that. You probably want to export from the child table - with one line for each child record (including parent data, if necessary).

[Mercury]

I've struggled with this, too. The only way I've managed to accomplish the export is to create an "export" table and populate it with a script.

1) Create a Temp window that holds the Export layout.

2) Revert to the original window and start a Loop to work through each record. Use variables to copy the data required in these records.

3) Within the Loop, create another Loop that flips from one Portal row to another (Goto Next Portal Row) and copy the data you want from the Portal row into a variable.

4) Before moving from one portal row to the next, Select the Export window, create a record and use the Set Field command to populate the new record with the data you have stored in the variables.

5) Select the original window and move onto the next Portal row.

6) Once the Portal Loop finishes...

7) Move to the next record so that the Portal loop starts all over again.

Once the Export layout has been populated, you can then execute the standard export routine to get the data out.

OK, I've been a bit brief in my summary. Hope you understand. (I guarantee that it works, as I've scripted a solution for myself.)

[comment]

What exactly is achieved here, compared to exporting from the child table?

[Mercury]

The concept of generating the records from the child relation is right, but where this example (and I assume the original poster's query) is different is that we are working with groups of people and displaying the groups works beautifully from the child.

In this example (see attached image), the core Timetable table is populated with class IDs, the teacher ID etc. By using a StudentID portal you can see the teacher and all the students in the class at any point of time (Day,TimeSlot) and where they are (Room ID).

Although I can neatly see and manipulate classes of students in this way, the data needs to be exported to a different system where each student's data for each Day and Timeslot in that day is to be a separate Tab delimited record. For example, students 1234 and 9876 are in the same class and move from Room5 to Lab2, to Room8, to Room5...

[color:blue]Day,TimeSlot,StudentID,RoomID,TeacherID

1,1,1234,R5,T1234

1,2,1234,Lab2,T4321

1,3,1234,R8,T4444

1,4,1234,R5,T1234

...

1,1,9876,R5,T1234

1,2,9876,Lab2,T4321

1,3,9876,R8,T4444

1,4,9876,R5,T1234

Exporting from the Timetable table generates a single record containing all the data, but subsequent Tab-delimited records exclude the common context fields. There's the rub.

Trying to export from the Student table doesn't work as it is a 1:M relationship. Of course, the database design can be changed to focus on individuals rather than groups of individuals and then the export is simple. This, however, makes the manipulation of groups (Classes for me) less elegant.

http://s254.photobucket.com/albums/hh112/MercuryAlbums/?action=view&current=Relationships.jpg

Edited January 13, 2010 by Guest
image not displaying

[comment]

I am afraid I didn't understand your example. Perhaps a simpler one might be more useful: say you have a Parent table with two records:

ParentID: 1

Name: Alpha

ParentID: 2

Name: Bravo

and a Child table with 5 records:

ChildID:1

ParentID: 1

Value: 101

ChildID:2

ParentID: 1

Value: 102

ChildID:3

ParentID: 1

Value: 103

ChildID:4

ParentID: 2

Value: 201

ChildID:5

ParentID: 2

Value: 202

What should your exported file look like?

[Mercury]

That's just it. It isn't simple. Believe me, I do understand that a simple 1:M relationship can be exported neatly from the Child table.

Okay, I'll play along. From your example, we need multiple entries of Alpha and Bravo in the Parent table, with of course, other pertinent fields and a suitable primary key. Something like...

[color:purple]Serial#

ParentID: 1

Name: Alpha

OtherField: Frog

Serial#

ParentID: 2

Name: Bravo

OtherField: Horse

Serial#

ParentID: 1

Name: Alpha

OtherField: Eel

Serial#

ParentID: 2

Name: Bravo

OtherField: Dog

Serial#

ParentID: 1

Name: Alpha

OtherField: Cat

Then the required export is...

[color:blue]Serial#,Alpha,Frog,101

Serial#,Alpha,Frog,102

Serial#,Alpha,Frog,103

Serial#,Alpha,Eel,101

Serial#,Alpha,Eel,102

Serial#,Alpha,Eel,103

Serial#,Alpha,Cat,101

Serial#,Alpha,Cat,102

Serial#,Alpha,Cat,103

Serial#,Bravo,Horse,104

Serial#,Bravo,Horse,105

Serial#,Bravo,Dog,104

Serial#,Bravo,Dog,105

As there are repeated ParentIDs in the Parent table, exporting from the Child table doesn't generate the results needed.

[comment]

I am sorry, I guess this is just not my day. You cannot have duplicate ParentID's in the Parent table (that IS the "serial#" field), so I find this very confusing.

It looks like you have a many-to-many relationship without a join table - so of course you cannot export ALL joins without creating them first. But that is just a guess...