Modify records

[flrver]

Does anyone have an example of modifying the transaction database with the AddTransaction command. Would need to select all records that are True for a field I added called Active, than add a transaction , category is Membership and Item Description is Member Dues. I need to run this once a year. I have a query that can select the records but not sure on the santex of the AddTransaction command. I would also like to add it to the report Menu to run as a script with a warning message box, but one step at a time. Have to be able to modify the transaction database.

Thank for any help
Jeff

[flrver]

I put this together. Owner run RV park with yearly dues that needed to be billed once a year for all active owners. Limited programming experience and was expecting a variant of a do while not EOF statement. Ended up controlling the flow with the customer numbers, noticed that all new customers started with 2000 or higher, so started with 2000, adding one to the variable. go to that record if null, than add another 1, deleted customers caused nulls to be found in the database so created a drop out only if the control was greater than 500 and each time a record was found, not a null record it control was reset to 1. This allow the records to be searched and when no records were found in a range of 500 records it must be at the end of the database.
The dues amount is pulled in from the transaction template table. I added a Menu in the reports drop down for the operator to run a start up script that makes a backup and verify the correct dues amount before running this processing script.

Hope that Stan is not laughing too hard when he see this bad programming script, but it does seem to work.

Below is the script

#HEADER# Scripts Scrp_Name Scrp_Description Scrp_Expressions Scrp_Notes
#HEADER# Expressions Expr_Expression
Scripts #000000056 start_dues Adds the yearly dues to all customers with a UDI number, with year update #000002078, #000002079, #000002080, #000002081, #000002082, #000002083, #000002084, #000002085, #000002086, #000002087, #000002088, #000002089, #000002090, #000002091, #000002092, #000002093, #000002094, #000002095, #000002096, #000002097, #000002098, #000002099, #000002100, #000002101, #000002102, #000002103, #000002104, #000002105, #000002106, #000002107, #000002108, #000002109, #000002110, #000002111, #000002112, #000002113, #000002114, #000002115, #000002116
Expressions #000002078 setVar("dd",DateToText( Today() + 200))
Expressions #000002079 setVar("yyyy",Right(dd, 4))
Expressions #000002080 setvar("cNumber",1)
Expressions #000002081 setVar("tNumber",TableNumRecs("Customers"))
Expressions #000002082 SetVar("CrDues",FieldText(FindRecByRecID("TranTemp lates", 000006), "Temp_Each"))
Expressions #000002083 //FindRecByRecID("Customers", 2016)
Expressions #000002084 setVar("rn",2000)
Expressions #000002085 while cNumber < 500
Expressions #000002086 SetVar("mf",yyyy+" Maintenace Fee")
Expressions #000002087 setVar("pointer2",FindRecByRecID("TranTemplates",0 00006))
Expressions #000002088 setVar("pointer",FindRecByRecID("Customers", rn))
Expressions #000002089 SetFieldText(pointer2,"Temp_Item" , mf)
Expressions #000002090 SetVar("IsChecked",FieldText(FindRecByRecID("Custo mers", rn), "Cust_User_Deed"))
Expressions #000002091 SetVar("war",FieldBool(FindRecByRecID("Customers", rn), "Cust_Warning_Flag"))
Expressions #000002092 SetVar("AT",FieldBool(FindRecByRecID("Customers", rn), "Cust_User_Active_check1"))
Expressions #000002093 if pointer<> nullrecord() and IsChecked<> "" and AT= .T. and war<>.T. and cNumber <499
Expressions #000002094 // if thisrecord()Cust:Cust_User_Deed >"" and cNumber <499
Expressions #000002095 SetVar("pTran",AddTransaction(pointer))
Expressions #000002096 SetFieldText(pTran, "Tran_Type", "Charge")
Expressions #000002097 SetFieldText(pTran, "Tran_Cat", "Maintenance Fee")
Expressions #000002098 SetFieldText(pTran, "Tran_Item", mf)
Expressions #000002099 SetFieldText(pTran, "Tran_Qty", "1")
Expressions #000002100 SetFieldText(pTran, "Tran_Each",CrDues)
Expressions #000002101 SetFieldText(pTran, "Tran_Amount",CrDues)
Expressions #000002102 SetFieldText(pointer, "Cust_User_ann_yr", right(dd,4))
Expressions #000002103 SetFieldText(pointer, "Cust_User_quarterly",".F.")
Expressions #000002104 SetFieldText(pointer, "Cust_User_Semi_Annual",".F.")
Expressions #000002105 SetFieldText(pointer, "Cust_User_Annual_checked",".T.")
Expressions #000002106 setVar("cNumber",1)
Expressions #000002107 // setVar("rn",rn+1)
Expressions #000002108 // else
Expressions #000002109 // endif
Expressions #000002110 else
Expressions #000002111 endif
Expressions #000002112 setVar("cNumber",cNumber+1)
Expressions #000002113 setVar("rn",rn+1)
Expressions #000002114 endwhile
Expressions #000002115 pointer
Expressions #000002116 MessageBox("The Owner Dues processing is completed. The logs will now be updated and this will take a while. Check message lower left corner of program window for status. When saving log files message is finished the log files have been updated. Select OK to continue.")

[sspielbusch]

No reason to laugh at it, seems like a good solution if it does what you want. There might be better ways to find which ones need dues rather that the 500-record gap, but whatever works!