Conditional Append Help?

[Beltronics]

Conditional Append
I have set up an append operation (with conditions) that appends records from one table to another and this works fine. I can "run" it and I get the desired results. However, it does an append action on "all" records from the Transaction table; even those that have already been appended. That works because I have set up a "unique" record in the Master based on "invoice number" in the transaction but I really don't need to look at ALL the transaction records every time!

What I want to do is append only one record (the current record being worked on in the transaction table) to the Master Table. I am trying to key this action on an event (onchange). If a field called "Status" changes from whatever it is to "Gone" I want to append this record (in the transaction table) to the Master Table. Can I tie an event to the "Status" field that will execute an "append" (for only this record) only if that field changes and now contains "Gone" as a status?

My desire is something like this; if STATUS changes and now contains "Gone", append this record to the Master Table using the predefined conditions set up in the append operation description.

[JerryBrightbill]

RE: Conditional Append Help?

Jim

If you are adding only one record, append is not the best way. Take a look at the xbasic manual for tbl.record_data_set() and tbl.record_data_get(). It is on about page 247 and the docummantatioon gives some examples. You can get all of the data from the current record and save it to the other table. If some field values change, you can open the record before you pull the data from the current table.

Jerry

[Beltronics]

RE: Conditional Append Help?

Thanks for the help. I'll see what I can do with it.

Jim

[Beltronics]

RE: Conditional Append Help?

I looked up those expressions and they don't really fit my situation. They depend on the fields in both tables being the same (at least those that are in the transaction table). All I am trying to do here is a sophisticated "post" and can't be done any better than a conditional append.

I will keep trying to work around this and I am sure I will come up with an awkward but workable solution. I am trying to make this happen automatically and without operator action. This will post a sale to the salesman's commission table whenever the item has left, therefore the "Gone" status.

Thanks for trying to help.

[JerryBrightbill]

RE: Conditional Append Help?

Jim

If you need to add a single record to another table, that is quite easy with xbasic. In a sense, you need a one record append. Assuming you have a form open showing the record that is the source information,

rec_num = recno() 'get record number of current record

tbl1 = table.open("sourcetable") 'open a new instance of the same table
tbl1.fetch_goto(rec_num) ' find the current record in the new instance of the table

tbl2 = table.open("destinationtable")
tbl2.enter_begin()
tbl2.destination_field1 = tbl1.source_field1
tbl2.destination_field2 = tbl1.source_field2
tbl2.destination_field3 = tbl1.source_field3
' and so on until you have filled all of the fields in the destination with values from the current record

tbl2.enter_end(.T.)
tbl1.close()
tbl2.close()

Jerry

[Robert Adler]

RE: Conditional Append Help?

Jim:

If you test the transaction table for a particular field value you should be able to restrict the append.

Establish a logical field at the end of the transaction dbf (Is_append). Set it to .t. after the record is appended. When you add a new record to the table the field will not be .t. and only the new record should append.

I hope this helps.

bob adler

[Beltronics]

RE: Conditional Append Help?


Establish a logical field at the end of the transaction dbf (Is_append). Set it to .t. after the record is appended. When you add a new record to the table the field will not be .t. and only the new record should append.

OK, I understand that. But, that process will still check ALL records in the transaction table and append only the one. I had done that already, kind of, by instructing the append operation to ignore duplicates (defined by invoice number) and that works fine. I just thought I could simply append the one record I was making a change in without scanning the entire table.

I thank you guys for your help and will explore all suggestions.

Jim

[Beltronics]

RE: Conditional Append Help?

So, my situation is this: I have a table called "repairs" and I want to append a record to a table called "nucommsales". I want to add a new record in "nucommsales" from "repairs" if and only if the field "status" in repairs = "gone", and only if a field "dancomm" = .t., as follows. I would need an "if then" statement but the code isn't working anyway.

tbl1=repairs tbl2=nucommsales
if status="gone"
if dancomm=.t.
then
invoice invoice
datein date
Customer_name Customer
Cust_ID Cust_ID
commsale totsale
active=.t. I want to set the field called "active" to true

rec_num = recno() 'get record number of current record
tbl1 = table.open("repairs") 'open a new instance of the same table
tbl1.fetch_goto(rec_num) ' find the current record in the new instance of the table
tbl2 = table.open("nucommsales")
tbl2.enter_begin()
tbl2.invoice = tbl1.invoice
tbl2.date = tbl1.datein
tbl2.customer = tbl1.customer
tbl2.cust_id = tbl1.cust_id
tbl2.totsale = tbl1.commsale
tbl2.active=.t.
tbl2.enter_end(.T.)
tbl1.close()
tbl2.close()

1. When I run that code, one line at a time in the interactive window; I get this error message when the invoice line comes up and again when the customer line comes up: If I highlite the code and run it, it stops at the first error.

rec_num = recno() 'get record number of current record

tbl1 = table.open("repairs") 'open a new instance of the same table

tbl1.fetch_goto(rec_num) ' find the current record in the new instance of the table

tbl2 = table.open("nucommsales")

tbl2.enter_begin()

tbl2.invoice = tbl1.invoice
ERROR: tbl2.invoice subproperty cannot be assigned to

tbl2.date = tbl1.datein

tbl2.customer = tbl1.customer
ERROR: tbl1.customer subelement not found.

tbl2.cust_id = tbl1.cust_id

tbl2.totsale = tbl1.commsale

tbl2.active=.t.

tbl2.enter_end(.T.)

tbl1.close()

tbl2.close()