Query with Criteria for Letters

[lars7]

Hi Guys,

I’m new to foxpro so please forgiven my lack of understanding.

I have pieced together the following code from other processes in our system so that I can open a letter with the record set of a query and then write to a table that the letters have been sent.

It works fine if it’s run as two separate parts ie,

A sequence that runs the first process, opening the letter with a run time prompt for “Contract type”, “Contract Start Date” and “Contract End Date” and then a second process that opens the same query attached to the code for updating the fields to say that the letter has been sent.

This is working ok but I would like to join the two processes together so that I only get one run time prompt for the query.

I have tried the following process and although the letter opens fine with the required criteria the code does not update the fields (I think maybe because the query is already opened with the letters).

Does anyone know how I can do this?

Thanks in advance for any help or advice.


*!*
*!* Setup
*!*


=RunLetter("Med _Test_Query","A4","ASK")


*!*
*!* Record processing for table ACTIONS
*!*

*----+ create a blank cursor for C/S for actions as this makes it updateable
PWACreateCursor("Actions","select * from actions where 1=2")
*----+ create a blank cursor for C/S for stages as this makes it updateable
PWACreateCursor("Stages","select * from stages where 1=2")


select ACTIONS
append blank

*----+ Fill in table details
Replace Actions.action_description with "MedTest4"
Replace Actions.letter_yn with "Y"
Replace Actions.Time_Actioned with Time()
Replace Actions.Date_Actioned with Date()
Replace Actions.letter_category with "Contract"
Replace Actions.stage_code with "09"
Replace Actions.actioned_by with goVariables.cUserName
Replace Actions.stage_number with 9

replace Candidat.JnrDocContractSent with "Y"

*----+ Fill in the system fields
lnRecordID = SerialUpdate("Actions", .T.)
Replace Actions.unique_identifier with lnRecordID, ;
Actions.parent_identifier with CANDIDAT.unique_identifier, ;
Actions.datetime_created with datetime (), ;
Actions.created_by_user with goVariables.cUserName


*----+ force write back to action table
local Array laTables(4)
laTables(1)="ACTIONS"
laTables(2)="STAGES"
laTables(3)="Candidat"
laTables(4)="Vacancy"

if ! EmpowerTableUpdate(@laTables,.T.)
Warning("Action(s) could not be created")
Endif


*!*
*!* End processing for table CANDIDAT
*!*

*----+ Close Tables
PWACloseTable("Actions")
PWACloseTable("Stages")
PWACloseTable("Vacancy")

 

[JRB-Bldr]

"the code does not update the fields (I think maybe because the query is already opened with the letters)."

Have you run the code from the FP development mode using the TRACE and DEBUG windows to watch the code execute?

Your code makes use of a number of Functions/Procedures which are outside of the code above.
Such as:
=RunLetter("Med _Test_Query","A4","ASK")
PWACreateCursor("Actions","select * from actions where 1=2")
etc.
These 'external' functions/procedures most likely reside in your PROCEDURE file.

I, for one, don't have enough information to give you any answers.

If you trace the code execution, you should be able to see where it goes and what it does or does not do.

Good Luck,
JRB-Bldr

 

[lars7]

Hi JRB-Bldr,

Thanks for replying,

=RunLetter("Med _Test_Query","A4","ASK")

This opens the letter that has the same tables as the record source as query A4 which asks for the criteria's "Contract type", "Contract Start Date" and "Contract End Date". and run as a procedure on it's own works fine.

The rest of the code I modified from a batch entry procedure to append fields after a letter had been sent but I only picked out the bits I thought would work and it does as a procedure run again with query A4 attached.

As I mentioned at the outset I am not familiar with FP coding, this is by far the biggest thing I have attempted, and I don't know how to "trace the code execution" as you mentioned, could you talk me through that please.

Thanks again.

 

[Olaf Doschke]

First of all the problem is, that you don't tell us at all, what fields you want updated. Not even which table.

You give some code, but the essential parts of updating any records is not in there, but in some functions you call.

To debug your code, set a breakpoint or use the command SET STEP ON before the line you want to go into single step mode (debugging). The debugger will start and you can see what's happening, eg stepping forward line by line with F8. You'll see the debugger toolbar with several symbols, the tooltip texts should tell you what you need to use.

Bye, Olaf.

 

[JRB-Bldr]

To teach you how to use FP/VFP via a forum would be a LENGTHY task and not a very effective utilization of either of our time.

Instead you might want to look at some reference material.

One that I have often suggested is:

http://www.garfieldhudson.com/FreeVideos.aspx

these videos discuss Visual Foxpro instead of Foxpro, but there are enough commonalities that it can get you pointed in the right direction.

Just remember that within the old FP IDE your TRACE and DEBUG windows are launched separately from your Command window.

From your code above and the REPLACE commands, AFTER the execution of the RunLetter() operation you are Selecting the intended table ACTIONS and then appending a blank record after which you populate the fields with the various REPLACE commands.

If this is not occurring then you need to determine what else might be happening around that code.

You then also go to another Function SerialUpdate()
where, without the code, we cannot determine what is happening.

Good Luck,
JRB-Bldr

 

[cbgr]

lars7
It appears that you are only updating the created cursor.
You have to also update the parent table.

Check your routine EmpowerTableUpdate(@laTables,.T.)
It must contain commands to append/update/replace the parent table (where you keep letters sent records).

As suggested by others, you need to show more of your codes and tables you wanted to update.