Batch update Memo Fields

[Mark Williams]

Is it possible to do add a text string to a memo field for all records that match a query?

It doesn't appear that it can be done via an operation, but I was hoping it could be done manually some way.

Thanks,

[Ted Giles]

It should be possible.
Suggest you search on updating memo fields as I recall a member - Robin - helped me with this.
In the meantime, I will look at some legacy stuff which may have it.

[madtowng]

something along the lines of dbf.memoField = dbf.memoField + crlf() + <new data>
should work.

[Ted Giles]

Using Action Scripting you can "Add Text to Memo Field", an option in the menu. You can put the text in most places. Try it as a simple button event on a form first, I suggest.
If you look at the code created by the action script, you will be able to create a script which you can run against a selection of records if you wish.

The code is a bit long winded but does what it says it will.

If you need a working example, just post back.

'Insert text into a memo field: 'Info' in current window.
Dim TextToInsert as c
Dim InsertPos as n
TextToInsert = "whatever your new text is"
InsertPos = -1

Dim TempObj as p
dim readonly_flag as l
'Get a pointer to the Object on the Form/Browse that has the memo field.
TempObj = obj(parentform.name(16)+":"+"Info") ' my field name for the test
'Check if the RTF memo is readonly
readonly_flag = TempObj.field.readonly
if readonly_flag = .t. then
ui_msg_box("Error","Cannot insert text into Memo field '"+"Info"+"' because it is read-only",UI_STOP_SYMBOL)
end
else
Dim MemoText as c
MemoText = TempObj.text
If InsertPos = 1 then
MemoText = TextToInsert + MemoText
Else if InsertPos = -1 then
MemoText = MemoText + TextToInsert
Else
MemoText = substr(MemoText,1,InsertPos - 1 ) + TextToInsert + substr(MemoText,InsertPos)
End If

'Now set the Memo field's text property to the next text..
TempObj.text = MemoText

'Now activate the memo
tempobj.activate()
'...and set the insertion point in the memo field after the inserted text
if InsertPos = -1 then 'inserting at end of field
tempobj.topparent.cursor_set(9999999)
else
tempobj.topparent.cursor_set(InsertPos + len(TextToInsert))
end if
end if

[Tom Cone Jr]

Mark, be careful with update operations. They are powerful, fast, and relentless. They can wreck your data faster than greased lightning, and often cannot be reversed. My personal policy when running an update operation for the first time, is to be certain I have two separate good backups, on hand so I can restore if Alpha does what I told it to do, but that turned out to be something different than I intended.

[Ted Giles]

Yeah! We ALL backup don't we?
listen to Tom and build it in to the operation as standard.

[Mark Williams]

So... I was able to get the following to work by putting it in a button on a form.

Dim P as P

vMode = parentform.mode_get()

IF vMode = "CHANGE" .or. vMode = "ENTER"
    parentform.commit()
END IF

tbl = table.current()
p.text=tbl.Notes

addstring = "Text to be inserted into Notes field"
tbl.change_begin()

If alltrim(tbl.Notes) = ""
    tbl.Notes = tbl.Notes + date_format(date(),"MM-dd-yy") +": " + addstring
    ELSE
    tbl.Notes = tbl.Notes + crlf() + date_format(date(),"MM-dd-yy") +": " + addstring
    END IF

tbl.change_end(.t.)
Notes.refresh()
topparent.commit()​

Now I am attempting to add this to a function that can then be called to run on a batch of records defined by a query.

I haven't had any success getting that to work and would appreciate it if someone could point me in the right direction.

This is where I've gotten so far (but it doesn't work)

FUNCTION Memo_Sweep AS C ( )

tSummons = table.open("Summons")
query.order = ""
query.filter = "Done = .t. .and. isblank(\"Notes\")"
qdx = tsummons.query.create()
vCount=qdx.records_get()
msgbox("" + vCount)
tSummons.fetch_first()
completed_count=0
while .not. tSummons.fetch_eof()
goto next_Summons
tSummons.change_begin()
'***********************************************************************
Dim P as P

vMode = parentform.mode_get()

IF vMode = "CHANGE" .or. vMode = "ENTER"
    parentform.commit()
END IF

tbl = table.current()
p.text=tbl.Notes

addstring = "Text to be inserted into Notes field"
tbl.change_begin()

If alltrim(tbl.Notes) = ""
    tbl.Notes = tbl.Notes + date_format(date(),"MM-dd-yy") +": " + addstring
    ELSE
    tbl.Notes = tbl.Notes + crlf() + date_format(date(),"MM-dd-yy") +": " + addstring
    END IF

tbl.change_end(.t.)
Notes.refresh()
topparent.commit()
'***********************************************************************
tSummons.change_end(.t.)
completed_count = completed_count + i
next_summons:
end while
tSummons.close()
END FUNCTION​

[Ronald Anusiewicz]

Look this over carefully. I don't have a way to test this but this is the way I'd approach this.

vMode = parentform.mode_get()

IF vMode = "CHANGE" .or. vMode = "ENTER"
parentform.commit()
END IF

tSummons = table.open("Summons")
query.order = ""
query.filter = "Done = .t. .and. isblank(\"Notes\")"
qdx = tsummons.query.create()

dim vCount as N = 0
vCount=qdx.records_get()

response = ui_msg_box("Records Found",vCount + " Records to be Processed",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
if response = UI_CANCEL_SELECTED then
    end
end if​

vCnt = 0
If vCount >= 1 then
addstring = "Text to be inserted into Notes field"

tSummons.fetch_first()
while .not. tSummons.fetch_eof()
p.text=tSummons.Notes

tSummons.change_begin()

If alltrim(tSummons.Notes) = ""
tSummons.Notes = tSummons.Notes + date_format(date(),"MM-dd-yy") +": " + addstring
ELSE
tSummons.Notes = tSummons.Notes + crlf() + date_format(date(),"MM-dd-yy") +": " + addstring
END IF

tSummons.change_end(.t.)

vCnt = vCnt + 1
tSummons.fetch_next()
end while

msgbox("Success",vCnt + " Records Processed",UI_INFORMATION_SYMBOL+UI_OK)

end if

end

[Ronald Anusiewicz]

Mark,
This code works.

vMode = parentform.mode_get()

IF vMode = "CHANGE" .or. vMode = "ENTER"
    parentform.commit()
END IF

query.order = ""
query.filter = "Done = .t. .and. isblank(\"Notes\")"
dim tSummons as P
tSummons = table.open("summons")
qdx = tSummons.query_create()

dim vCount as N = 0
vCount=qdx.records_get()

response = ui_msg_box("Records Found",vCount + " Records to be Processed",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
if response = UI_CANCEL_SELECTED then
    end
end if

vCnt = 0
If vCount >= 1 then
    addstring = "Text to be inserted into Notes field"

    tSummons.fetch_first()
    while .not. tSummons.fetch_eof()
        p.text=tSummons.Notes

        tSummons.change_begin()

        If alltrim(tSummons.Notes) = ""
            tSummons.Notes = tSummons.Notes + date_format(date(),"MM-dd-yy") +": " + addstring
        ELSE
            tSummons.Notes = tSummons.Notes + crlf() + date_format(date(),"MM-dd-yy") +": " + addstring
        END IF

        tSummons.change_end(.t.)

        vCnt = vCnt + 1
        tSummons.fetch_next()
    end while

    msgbox("Success",vCnt + " Records Processed",UI_INFORMATION_SYMBOL+UI_OK)
end if

tSummons.close()

end​

[Ted Giles]

I couldn't get the original to work Ron, so see the attached simple Update Operation.

Sample_Data.zip

Construct:
New Operation
..Update Records
...CREATE(not with the Genie)
....Memo Field
......Expression = Memo Field +"new text"
.........Condition if required.

It works based on the current selection of records, which was in the brief.
This is the code block, but it's easier to use the operation builder!

if current_filter <> "" then
if eval_valid(current_filter,"memo_field") =.f. then
a5_Operation_Warning() 'Display warning if current selection is meaningless
current_filter = ".t."
end if
end if

if current_order <> "" then
if eval_valid(current_order,"memo_field") = .f. then
current_order = ""
end if
end if

a_tbl = table.open("memo_field")
ON ERROR GOTO ERROR2708202216401300


DIM a5_operation_filter as C
a5_operation_filter = current_filter
query.filter = a5_operation_filter
DIM a5_operation_order as C
a5_operation_order = current_order
query.order = a5_operation_order
query.options = "I"+current_flags
query.description = "Temporary Query"
a_tbl.query_create("N")

update.fields = 1
update.field1 = "INFO"
update.expr1 = "if(Info<>"",Info+"newstuff",INFO)"

'Prompt for confirmation before running the Operation.......
dim rec_count as n

rec_count = a5_get_records_in_query("memo_field",a5_operation_filter,-1,.f.)
message_text = rec_count + " record(s) from 'memo_field' will be updated."+crlf(2)+ "OK to proceed?"
operation_result=ui_msg_box("Update Operation",message_text,UI_OK_CANCEL+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
If operation_result <> ui_ok_selected then
end
end if



a_tbl.update()


GOTO CONTINUE2708202216401300
ERROR2708202216401300:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE2708202216401300:
a_tbl.close()
'Display a dialog box showing the results of the Operation
'(If last parameter is blank, button to show result is not displayed).
dim args as sql::arguments
if eval_valid("arguments") then
if typeof(arguments) = "P" then
args = arguments
end if
end if
a5_update_op_result(a_records_processed, a_records_violated,"memo_field",a5_operation_filter,args)

'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if

[Mark Williams]

Both Ron and Ted's recommendations work great.

Thanks for the help!

[Mike Wilson]

Glad it worked out Mark. I once read, some time ago, by a man who has wisely guided me through the years on these boards, I actually believe it might have been Tom Cone, that it can be a slippery slope mixing together both form methods and table methods in combinations within the same script sequence. I found this wise and I've held true to that wisdom and adverted from mixing form methods together with table methods. As well as using operations in a batch method, as he reiterated in this thread.