Importing a single record with many line items

[MadCam]

I am importing a spreadsheet into FMPro 11 that looks like this:

HAR HA ADM TX PX

67824 ZC 04/06/2009 36854307 4452820

67824 CC 04/06/2009 36854310 4453541

67824 TC 04/06/2009 36907581 6801000

87298 TC 04/14/2009 37057448 6007600

87298 PR 04/14/2009 37057629 9012500

87298 CA 04/14/2009 37057684 9012500

I would like to create records that import like this:

67824 [Record]

....ZC 04/06/2009 36854307 4452820

....CC 04/06/2009 36854310 4453541

....TC 04/06/2009 36907581 6801000

....[these are line items within the form/layout]

87298 [Another Record]

....TC 04/14/2009 37057448 6007600

....PR 04/14/2009 37057629 9012500

....CA 04/14/2009 37057684 9012500

....[these are line items within the form/layout]

What't the best way to approach this?

[comment]

The best way, IMHO, would be to import the same spreadsheet twice: once into a parent table, importing only the HAR column, and once into a child table, importing all columns.

In the parent table, set the HAR field's validation to unique, validate always. Define a relationship between the two tables matching on HAR, abd place a portal to the child table on the parent's layout.

[MadCam]

Thanks. This is kind of what I thought I might need to do. Am I locked into using a "portal"? My layout looks like this:

HAR [single field at the top of form]

HA....ADM....TX....PX [middle of form (body)]

I was hoping that setting up HAR as the primary key (along with the "parent" / "child" table relationship), that the other fields would simply list and line up under the respective fields within the body (i.e., HA...ADM...TX...PX).

Thanks, in advance, for any thoughts that you have.

[comment]

A body part can only show a single record. In your example, there are many records to show under each HAR value, and (if I understand correctly) this number is not necessarily same for all.

Visually, a portal can provide exactly the type of view you describe - with scrolling, if necessary.

Another way you could present the data is in list view, with a sub-summary part by HAR. You wouldn't even need the parent table for this - but the records must be sorted by HAR for the sub-summary part to show.

[MadCam]

I see. That's helpful. With the sub-records imported into a portal, is it possible to add/use corresponding user-entry fields, as well? Here's what I mean:

67824 [single record at top of form]

As discussed the following lines would be imported into a portal:

....ZC 04/06/2009 36854307 4452820

....CC 04/06/2009 36854310 4453541

....TC 04/06/2009 36907581 6801000 [user-entry field]

Then, I need to add a 'user-entry' field to the end of each line in the portal (see above). This is a verification field -- perhaps a drop-down (e.g., "Verified", "Invalid", "Expired", etc.)

Is this possible within the portal?

[comment]

Why not?

har.zip

[MadCam]

Super. I'll give this a try with a drop-down field and see how that works. Many thanks for your suggestions!