Below are changes I have made to VDF11.0 Order Entry Sample to demonstrate smooth
inserting of records. The goal is to create a class or methods that can be reused
and simplify the ability to insert records in a DBGrid. I welcome your comments,
testing, and suggestions.

OrderDtl.DD
Remove: Define_Auto_Increment Orderhea.Last_Detail_Num To Orderdtl.Detail_Number
Remove: Set Field_Options Field Orderdtl.Detail_Number To DD_NOPUT

Order.vw
Added the following methods to oOrderDtl_Grid

// 11/01/05 DMM
Procedure add_or_remove_row
Set priInsertBefore of (Server(Self)) to (GetRowId(Main_File(Self)))
Forward Send add_or_remove_row
Set pbInserting of (Server(Self)) to TRUE
End_Procedure // add_or_remove_row

// 11/01/05 DMM
Procedure Define_Insert_Field
Integer iField
Field_Map (Main_File(Self)) "Detail_number" To iField
Set piOrderingField of (Server(Self)) to iField
End_Procedure // Define_Insert_Field
Send Define_Insert_Field

// 11/01/05 DMM
function Row_Changing integer from# integer to# returns integer
integer retval lim toRow fromRow rowcount
integer dynUpdt wasNew ROS NCS
integer bToMoved iOldCurrentItem iOldTo
Integer eRowStatus
Rowid riRec

get Current_item to iOldCurrentItem

get Item_Limit to lim
get row item from# to fromRow
Move (not(RowHasRecord(self,fromRow))) to wasNew

// check if change allowed....if not return from#
// this might save a new record..so we recheck the rec array
If ( Allow_Row_Change(self) ) Function_Return from#

If (not(RowHasRecord(self,fromRow))) begin
// if a new record..we have approval to remove this row..do so
get dynamic_update_State to dynUpdt
set dynamic_update_State to false
send Remove_row fromRow
set dynamic_update_State to dynUpdt
if to# ge from# Move (to#-lim) to to# // target has moved
// anytime we remove the row, we consider this a new item. We use this to
// determine if an item_entry must be forced. Sometimes the actual item
number
// will not change (so entry will not be sent normally). In such a case, we
will
// force it.
move 1 to bToMoved
end

get row item to# to toRow
set base_item to (toRow * lim)
set new_item to to#
// this handles auto add. Auto Add=T if Move 1 down and it was a new rec
//if ( (toRow - fromRow = 1) AND (wasNew) and pbInserting(Server(Self))) Begin
// 11/01/05 DMM - this adds a change to where if an insert is done, creating a
blank line is ignored
if ( (toRow - fromRow = 1) AND (wasNew) and pbInserting(Server(Self))) Begin
// 10/04/94 - Changed to make new rows get inserted instead
// of replacing current item. At this point we are in insert mode
// and we've moved to the new row. If this new row has data
// (i.e., its eRowStatus is not -1,-2, or 0) then we need to insert a row,
// else we just need to clear the row. In either case, make sure the
// current_row number does not change (insert changes current row)
If (CurrentRowHasRecord(self)) ;
Send Insert_Blank_row (Current_Row(self)) // existing data
else ;
Send Clear_Row (Current_Row(self)) // row already blank
set base_item to (toRow * lim) // The current row might
set new_item to to# // change so we reset it here.
Move (NullRowId()) to riRec
End
Else Begin
Get Record_RowId Item toRow to riRec // the new rec number
// if eRowStatus -2 or -1 then we need to go to the database to fill
// in the next record.
If not (IsNullRowId(riRec)) Begin
Send ReadByRowId riRec // read the next record
Move 1 to eRowStatus // 1 means has a record
End
else Begin
get Fill_next_row toRow to eRowStatus
If (eRowStatus>1) move 1 to eRowStatus // in case fill_next_row still
returns a record
end
//
If (eRowStatus=1) Begin
send Display_other_UI // fill out rest of UI
set base_item to (toRow * lim)
End
Else begin
// if top row couldn't add only allow an empty row on top if
// it is allowed
Get Row_Count to rowCount
// if row count is 1 then this is the only row we have and we
// will not delete it.
If (RowCount>1 and eRowStatus=rsNewAtTop AND ;
( Allow_top_add_State(self)=0 OR ;
No_Create_State(self) OR ;
Read_Only_State(self) ) ) ;
Begin
Send Delete_Row toRow
Set New_item to to# // added 01-14-1993
// added 11/07/94 - make sure we've got the right record
// in the buffer
Send ReadByRowId (CurrentRowId(self))
End
else Begin
// if bottom row couldn't add only allow an empty row on bottom if
// it is allowed
If (RowCount>1 and eRowStatus=rsNewAtBottom AND ;
( Allow_Bottom_add_State(self)=0 OR ;
No_Create_State(self) OR ;
Read_Only_State(self) ) ) ;
begin
Send Delete_Row toRow // (Current_Row(self))
// adjustment stuff since current row no longer exists..
// could probably be optimized
move from# to to#
If (top_item(self)<>0) Set Top_item to 0 // (top-1)
set new_item to to#
// added 11/07/94 - make sure we've got the right record
// in the buffer
Send ReadbyRowId (CurrentRowId(self))
end
else Send Clear_Row toRow
End
End
End

get current_item to iOldTo // keep track of current To item, it might change now
If (eRowStatus=1 and Auto_regenerate_State(self) and UnSorted_State(self)) begin
send Display
end
Else Begin
Send Trim_Page
end
get Current_Item to to#
// if trimming changed to#, we mark it (it is a move)
if (to#<>iOldTo) move 1 to bToMoved

// if we are not in column 0 we must validate up to this row
// for all to work properly.
if to# gt (toRow * lim) begin
move (validate_range(self,(toRow * lim),(to# - 1))) to retval
if retval ne -1 function_return retval //returns item# which failed
end

// This traps cases where moves occur but the final current item ends up
// being in the same position where it started. We still want to force an entry
// message. So if we know a move has occured but it does not look like the item
// actually changed, we will force the message
if (bToMoved and to#=iOldCurrentItem) ;
get exec_entry to# to retval

function_return to#
end_function

Added the following methods to oOrderDtl_DD

// 11/01/05 DMM
Property Boolean pbInserting FALSE // whether this save operation is an
insert
Property RowId priInsertBefore (NullRowId()) // record of field to insert before
Property Integer piOrderingField 0 // numeric ordering file.field

// 11/01/05 DMM
Procedure OnNewCurrentRecord RowId riOldRec RowId riNewRec
Forward Send OnNewCurrentRecord riOldRec riNewRec
Set pbInserting to FALSE
End_Procedure // OnNewCurrentRecord

// 11/01/05 DMM
Procedure Request_Save
Handle hFile hField hoArray hParentFile hParentField hoDD
Integer iFieldType iLength iDigits iIndex iField iFields
Number nInsert nOrder nHoldOrder
RowID riRowID riLastRowID
Boolean bErr
String sVal

Get Main_File to hFile
Get piOrderingField to hField
Get priInsertBefore to riRowID
Set priInsertBefore to (NullRowId())
Begin_Transaction
// Only do if operation is inserting, we know what field to do it on, and
has a record to insert before
If ((pbInserting(Self)) and (hField) and (Not(IsNullRowID(riRowID)))) Begin
// Cancel inserting so we don't end up in a recursive loop. We call this
// method again to modify the other records for inserting.
Set pbInserting to FALSE

// Make sure it is a numeric field
Get_Attribute DF_FIELD_TYPE of hFile hField to iFieldType
If (iFieldType=DF_BCD) Begin
Get_Attribute DF_FIELD_LENGTH of hFile hField to iLength
Get_Attribute DF_FIELD_PRECISION of hFile hField to iDigits
Get_Attribute DF_FIELD_INDEX of hFile hField to iIndex
// Calculate number to increment by
Move (1.0/(1^iDigits)) to nInsert

// Store new field values
Get Create U_Array to hoArray
Get_Attribute DF_FILE_NUMBER_FIELDS of hFile to iFields
For iField from 1 to iFields
Get_attribute DF_FIELD_RELATED_FILE of hFile iField to
hParentFile
If (hParentFile) Begin
Get_attribute DF_FIELD_RELATED_FIELD of hFile iField to
hParentField
Get File_Field_Current_Value hParentFile hParentField to
sVal
End
Else Begin
Get File_Field_Current_Value hFile iField to sVal
End
Set Value of hoArray item iField to sVal
Loop

// Move Records gt the record we are inserting
Send FindByRowId hFile riRowID
If (HasRecord(Self)) Begin
// remember last record
Move (GetRowId(hFile)) to riLastRowID
Get Field_Current_Value hField to nOrder
Send Request_Find GT hFile iIndex
While (Found)
// Test each detail record to see if it needs to be moved
// by adding our increment to the last ordering value. If
// it is a match, this record needs moved, too.
Get Field_Current_Value hField to nHoldOrder
If ((nOrder+nInsert)=nHoldOrder) Begin
Move (GetRowId(hFile)) to riLastRowID
Send Request_Find GT hFile iIndex
Get Field_Current_Value hField to nOrder
End
Else Indicate Found FALSE // Abort if the record we found
doesn't need to be moved
Loop

// Start from last record found and start saving backwards
// We need to move each record starting from the end.
Send FindByRowId hFile riLastRowID
// should always have a record since we are in a locked state
// but I do this just to be safe. After all, it is a different
// index we are finding by and the index _could_ be corrupt...
If (HasRecord(Self)) Begin
Move (FALSE) to bErr
While (Not (bErr))
// Get current ordering value and add our increment to
it
Get Field_Current_Value hField to nOrder
Set Field_Changed_Value hField to (nOrder+nInsert)

// test for errors before we save
Get Request_Validate to bErr
If Not (bErr) Begin
// save it
Send Request_Save
End
Else Begin
// roll back all changes
Abort_Transaction
End

// Stop when we moved the row we are inserting before
Move (IsSameRowid( GetRowId(hFile), riRowID)) to bErr
If (Not(bErr)) Send Request_Find LT hFile iIndex
Loop

// reload values for new record being inserted
Send Clear
For iField from 1 to iFields
Get Value of hoArray item iField to sVal
Set FIELD_CHANGED_VALUE iField to sVal
Set_Field_Value hFile iField to sVal
Loop
// Set ordering value to make this field inserted
Set FIELD_CHANGED_VALUE hField to nOrder
Set_Field_Value hFile hField to nOrder

// refind all parent files then update their DD's so the
// correct record is in the DD buffer
Relate hFile
For iField from 1 to iFields
Get_attribute DF_FIELD_RELATED_FILE of hFile iField to
hParentFile
If (hParentFile) Begin
// Find the DD for the filenumber we have
Get Data_Set hParentFile to hoDD
If (hoDD <> Server(Self)) Begin
Send FindByRowId of hoDD hParentFile
(GetRowID(hParentFile))
End
End
Loop

// Save new record
Get Request_Validate to bErr
If Not (bErr) Begin
// save it
Send Request_Save
End
Else Begin
// roll back all changes
Abort_Transaction
End
End
End
Else Begin
// roll back all changes
Error 999 "The record you are attempting to insert before has
been removed."
Abort_Transaction
End
End
Else If (iFieldType=DF_ASCII) Begin
// ToDo: Anyone see a need for this?
End
End
Forward Send Request_Save
End_Transaction
End_Procedure // Request_Save

// 11/01/05 DMM
Procedure Creating
Forward Send Creating
If (OrderDtl.Detail_number = 0) Begin
// Assign from parent
Increment Orderhea.Last_detail_num
Move Orderhea.Last_detail_num to OrderDtl.Detail_number
End
End_Procedure // Creating


--
David Martinko
Redeemed Software
248-535-7495
RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com
www.redeemedsoftware.com
www.redeemedhosting.com