Hi -

I am back to further discuss a CD database (although I am actually applying this to something totally different).

Suppose I have a Q&A database that has two pages.

Page 1 contains all the information about the CD e.g. Title, Music Category, Date Purchased, Where Stored, General Comments, and several other fields (20 in all).

Page 2 contains separate line items for each track, eg. Track #, Song Title, Artist, Time, Comments.  It is set up something like:

TNO1:     ST1:      A1:       T1:      C1:
TNO2:     ST2:      A2:       T2:      C2:
TNO3:     ST3       A3:        T3:      C3:

and so on through 25 "line items" for each potential track.  (Some forms may have 8 tracks, some 15, some 23 and so on - up to 25).

The newly created Sesame file contains all of the information from page 1 on the main form and has a sub-form to contain all of the information from each of the tracks.

My question is how can I export the information from the Q&A file into the Sesame database, so that each track will be in a separate record on the sub-form.

It seems to me that the ASCII text file has to have all of the main form info, followed by the first track info, followed by all of the main form info, followed by the second track info, followed by all the main form info, followed by the third track info  ... and so on, and so on, and so on.

How do I accomplish this, since I cannot set up an export spec to alternately repeat the values from page 1 of my Q&A database?

Thank you!

Hi Spencer,  I have a flat file that imports into Sesame by using the technique above.  No extra programming or Mass Updates in Q&A.........BUT......one problem.

The destination Sesame parent form has three subforms on it.  I was just importing into two of them, but find that there appears to be an issue with multiple subfoms. 

Only expected one record in subform2 but got 3 more duplicate records, I suspect because 4 records were recorded in the subform1.

IS there a restriction that we can only import hierarchical records into parent that may be  limited to a single subform?

If yes, any suggestions on how to work around that?

----------------------------------------------------------------
There is also an issue with two or more records in Q&A for same parent.  They come in to Sesame as separate parent records, not surprising, but what is really needed are the subform portions, not duplicate parents. 

Any ideas on combining multiple parent Q&A records before exporting or a way to merge them in the export file before importing?  Maybe need to do some type of XLU to fill in subrecords in one of the parents, and then delete the extra parent records?

Good sugestion Erika, as usual.........I will probably get to that tonight.  Maybe Spencer will have better success before then.....

My original export files had parent LEs in middle of subform LEs.  These are things I had to do, just to get multiple records on the subforms to show up, nevermind the extra unwanted records.

Up to now, I did have to rearrange the data so that all the Parent data was grouped together by itself, I had to eliminate some data fields that I had marked as IGNORED in the Import Spec, and I had to move all of each subformx items together as a group. 

Hello Spencer,

Have you set up/or loaded an Import Spec? If you have not set any elements for the imported data to go into, you will get what you are seeing.

-Ray

Quote:



You are excused. Don't be too embarrassed, It happens to everyone from time to time.

-Ray

OK, I did an export of a Parent form with three subforms and have ended up with the following conclusions for coming up with an import file layout structure:

1.  Import file for parent and subforms will be fixed NUMBER of fields.
2.  Structure is Parent..Subform1 fields or blanks..Subform2 fields or blanks..Subform3 fields or blanks.
3.  There will be a separate line for each individual record for all subforms.  Count the total subrecords, and that is how many lines should exist.

Sample file layout for one Parent form with 4 subforms (SF1,SF2,SF3,SF4), three data fields(a,b,c) each:

Parent Fields,SF1a,SF1b,SF1c,,,,,,,,,,
Parent Fields,,,,SF2a,SF2b,SF2c,,,,,,,
Parent Fields,,,,,,,SF3a,SF3b,SF3c,,,,
Parent Fields,,,,,,,,,,SF4a,SF4b,SF4c,

------------------------------------------------
Hope this helps someone else to create an import file that includes hierarchical subform records.
And if this layout structure is not correct, Mark/Erika/Ray will correct me.

Hello Bob,

Looks right to me. Only problem I see is that there appears to be an extra comma at the end of each line. So the Last line would be
Parent Fields,,,,,,,,,,SF4a,SF4b,SF4c

-Ray

Well done Spencer....great job.

You have essentially made Q&A add the Parent info as header to each group of sub form records fields,.

And now that I understand  the desired import format for flat file to hierarchical structure, I should be able to use your technique with some modifications for multiple sub forms coming from the flat form.