I need help.

I have two FM tables/databases related by NSA

LithTable (contains many NSA values with different LITH values)

NSA LITH

100 Sand

100 Silt

100 Gravel

Etc. up to 8 lith values per NSA.

GridTable (contains 190,000 records of many NSA values with tons of GRID values per NSA)

NSA GRID

100 -50

100 100

100 36

100 45

Etc. up to 50,000 grid values per NSA.

In the LithTable database I created a portal into the GridTable database based on NSA in order to list all the GRID values for each NSA.

What I want to do is export the LITH field values and all of the related GRID values.

What I get when I export is a database that looks like this (I’m exporting into a FM database)

LITH GRID

Sand..-50

.........100

.........36

.........45

Silt....-50

.........100

.........36

Etc.

The problem is the exported table does not have the LITH field field for every value of GRID.

I want the exported table to look like this:

LITH GRID

Sand -50

Sand 100

Sand 36

Sand 45

Silt -50

Etc.

How can I achieve this? It seems elementary and I don’t understand why FM doesn’t have an option for filling in all fields in all records. The exported table is useless.

Edited February 22, 200718 yr by Guest

Looks like you need to specify grouping. When you export your records, use the sort by section to choose your sort field (the field that contains "sand" etc.)

This will fill in the fields with the data you want.

hope this helps.

Thank you for responding. I tried what you recomended but it didn't work. I may be doing the sort wrong. I went to Records-Sort and sorted by LITH. The result is similar to the result I showed above but sorted by LITH (so Gravel would end up first). The first column still only has the first row with the LITH type and the second column filled with the GRID values. Did you imply sorting the data with a different function ("sort by section")?

Export from the Grid table

I don't think there are enough records in the Grid table for this. Each Grid record needs to appear several times in the exported file. Very similar to this.

Thanks for the responces. I decided to delve into FM script and came up with this:

Go to Record/Request/Page [First]

Loop

If ["IsEmpty(LITH)"]

Insert from Last Record [select, "LITH"]

Go to Record/Request/Page [Exit after last, Next]

Else

Go to Field ["LITH"]

Go to Record/Request/Page [Exit after last, Next]

End If

End Loop

As I stated before, after I export the file into a new FM table each LITH type is in the first row with all the possible GRID values in the next rows untill the next LITH type. All I needed to do was automate the "Excel type" of "Fill Down" command but many many times down one column. That's what the above scrip does. It turns a table like this:

LITH..GRID

Sand..-50

........100

........36

........45

Silt....-50

........100

........36

........45

etc.

Into this:

LITH GRID

Sand -50

Sand 100

Sand 36

Sand 45

Silt -50

Etc.

The script takes 5sec / 2000 records and I'll have 300,000 records to fix so It'll take about 12.5 min to complete. Slow, but works.

Try exporting out of Grid. When you get to the area where you select the fields to export, select the Lith file and export the lith value from there. It will export the realated value from the Lith file.

I had a situation with company and company contacts, where when I exported the related contacts from company I couldn't get at list that had the company name filled in for each contact. When i exported from the contact field, it duplicated the company name for each.

Hope this works for you.