English

How To: Append tables vertically [SCRIPT]

Procedure

Summary

This document demonstrates how to vertically append two or more tables into one.

Answer

This script displays a list of the tables in a project that are used to select options that are appended together. Click OK after each selection, and Cancel when you have completed your selections. The script reads the field names from the first table that was selected and appends the records from fields in the other tables that have the same names. Fields that are not in the first table will be ignored in the other tables.

'--- APPEND.AVE

'--- Table.Append
'--- This script is based on the 'MergeThemes' sample script

theTabList=List.Make
theDocList=av.GetProject.GetDocs

'-- Populate theTabList variable with all of the Table Docs
'-- in the Project

for each d in theDocList
if (d.Is(Table))
then theTabList.Add(d)
end
end

'-- Find out from the user which tables they want to append
'-- Put the resulting tables into theSelTabs variable

theSelTabs=List.Make

'Select the tables to append

tablesToMerge = List.Make

while (true)
t = MsgBox.Choice(theTabList,
"Choose tables to merge:"+NL+ "(Click Cancel to end):",
"Merge Tables")
if (t<>Nil) then
tablesToMerge.Add(t)
else
break
end
end

if ((tablestoMerge = Nil) or (tablesToMerge.Count <2)) then
MsgBox.error("Not enough tables to merge.","")
return nil
end

'-- Specify the output dBASE file...

outTName = av.GetProject.MakeFileName("table", "dbf")
outTName = FileDialog.Put(outTName, "*.dbf",
"Output Appended Table")
if (outTName = Nil) then
exit
end

'-- Create the list of fields used for the output table.
'-- The fields are taken from the first table only, it is
'-- assumed that the other tables have an identical set of
'-- fields. If this is not the case the tables will still
'-- be appended, however fields in the first table that are
'-- not found in the second table will be empty. Fields in
'-- the second table that are not found in the first will
'-- be ignored and dropped.

fieldList = List.Make

for each f in tablestomerge.Get(0).GetVTab.GetFields
fCopy = f.Clone
fieldList.Add(fCopy)
end

mergeVTab = VTab.MakeNew( outTName, dBASE )

if (fieldList.Count > 0) then
mergeVTab.AddFields( fieldList )
end

'-- Populate the new VTab from the VTabs of the input table...

for each t in tablestomerge

av.ShowMsg( "Merging"++t.GetName )

inVTab = t.GetVTab

if (inVTab.GetSelection.Count = 0) then
theRecordsToMerge = inVTab
numRecs = inVTab.GetNumRecords
else
theRecordsToMerge = inVTab.GetSelection
numRecs = theRecordsToMerge.Count
end

for each rec in theRecordsToMerge

av.SetStatus( (rec / numRecs) * 100 )

newRec = mergeVTab.AddRecord

if (fieldList.Count > 0) then
for each f in fieldList
fName = f.GetName
inField = inVTab.FindField( fName )

'-- Skip field if not found in inFTab...
if ( inField <> Nil ) then
outField = mergeVTab.FindField( fName )
aValue = inVTab.ReturnValue( inField, rec )
mergeVTab.SetValue( outField, newRec, aValue )
end

end '-- for each f
end '-- if count
end '-- for each rec
end '-- for each t

av.ClearMsg
av.ClearStatus

'--- End of Script