Amor,
I created a small app with a Parent form (Orders) and a child form (Results) that matched your design. The parent and child are relationally linked by the OrderNo field.
I created a separate Tools database/form with a button on it for Import Records.
The button has the following code, which works quite well. Let me know if you'd like for me to put the actual app someplace you can get to it.
NOTE: If you do not have SERVER PREINDEX RELATIONAL: OFF in your ini file, you will need to close and reopen your database to see the new child records.
NOTE: This works for relational links. If you want to do this with natural linking, you will also need to use XResultSetReparent().
#include "sbasic_include.sbas"
var vCRS as Int // Child Result Set
var vFile as Int // Results file
var vPatient as String // Current PatientNo
var vOrder as String // Current OrderNo
var vLine as String // Current Results line from file
var vVal as String // Current value from Results line
var vLen as Int // Length of Results file
var vTemp as Int
FUNCTION ProcessLine() as Int
var vRet as Int
var vCode as String
var vResult as String
vRet = 0
// Everything left in line should be code/result pairs.
// Make a subrecord for each pair.
While @Len(vLine) > 0
{
vCode = Split(vLine, Tab(1))
vResult = Split(vLine, Tab(1))
If (vCode <> "") And (vResult <> "")
{
// Make new child record
XResultSetCreateNewRecord(vCRS)
XResultSetValue(vCRS, "PatientNo", vPatient)
XResultSetValue(vCRS, "OrderNo", vOrder)
XResultSetValue(vCRS, "ItemCode", vCode)
XResultSetValue(vCRS, "UniqueCode", vOrder + vCode)
XResultSetValue(vCRS, "DateAndResults", @Str(@ServerDate()) + " " + vResult)
}
Else
{
// If info not paired, abandon the line with error
vRet = 1
}
}
Return vRet
END FUNCTION
// Create an empty child record result set by specifying crtieria that no record will match
vCRS = @XResultSetSearch(@FN, "Orders!Results", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!OrderNo==")
If vCRS > -1
{
vFile = FileOpen("amor.txt")
If vFile > -1
{
FileSeek(vFile, 0)
vLen = FileSize(vFile)
// Read and process results file line by line
While (FilePos(vFile) < vLen)
{
vOrder = ""
vPatient = ""
FileReadLn(vFile, vLine)
If @Len(vLine) > 0
{
// Throw away first two values on line
vVal = Split(vLine, Tab(1))
vVal = Split(vLine, Tab(1))
// Get OrderNo from Position 3
vVal = Split(vLine, Tab(1))
If @Len(vVal) > 0
{
vOrder = vVal
// Get matching PatientNo and confirm OrderNo exists
vPatient = @XLookup(@FN, vOrder, "Orders!OrderNo", "PatientNo")
If @Len(vPatient) > 0
{
// Process rest of line into subrecords
vTemp = ProcessLine()
If vTemp <> 0
{
WriteLn("Error while processing Order: " + vOrder)
}
}
Else
{
WriteLn("No matching Order record for: " + vOrder)
}
}
}
}
FileClose(vFile)
}
Else
{
@MsgBox("Error: Failed to open results file.", "", "")
}
XResultSetClose(vCRS)
}
Else
{
@MsgBox("Error: Failed to open child result set.", "", "")
}