Conditional Append ?

[Jerry Stacey]

Is it possible to append a table based upon a conditional field?

I am trying to append a table based upon a key field already in both tables. However, I don't want the key field appended, just the conditional fields, but I don't see how I can accomplish that using the append command.

e.g. If Invoice_header->customer_id=Customer->customer-id then append Customer->last_name Invoice_header->last_name

Can this be done somehow?

Thank you.

Jerry

[forskare]

RE: Conditional Append ?

Jerry,

If you go to the Operations tab and select New, then Append and after choosing the master table, use the genine. You will be able to append whatever fields you desire.

kenn

[CALocklin]

RE: Conditional Append ?

I don't see how I can accomplish that using the append command.

In a word - you can't. It looks to me like you are not using the term Append as it would be used in an A5 append operation.

Appending adds a completely new record to the master table. It looks like you just want to update the field value for Last_name in your invoice_header table.

If you were doing this 'globally' for many records at once, you would use an Update operation.

If you want to enter the last name whenever a new invoice_header header is created, just define your lookup so that when selecting the customer ID for the header, the customer's last name is also filled in.

[Jerry Stacey]

RE: Conditional Append ?

Cal,

Thanks for the information, that was the answer I was afraid of though. However, I have looked at the Update Operation, and cant seem to figure out how to get it to do what I want it to do. It generally seems to want to replace, or fill a field, and I cannot find how to get it to do it based upon a conditional statement.

Any ideas ?

Thanks again.

Jerry.

[Marc King - A5solutions]

RE: Conditional Append ?

Hi Jerry,

Your intentions is are a bit unclear.

Generally you would have a parent customer table record which would have multiple invoices e.g. a one to many relation to an invoice child table.

You would import (append) invoice records to the invoice table. Matching ID #s would then automatically ascociate by Key to the parent Customer ID. You could then filter for orphan invoice records and process those accordingly.

Marc

[CALocklin]

RE: Conditional Append ?

- First, create a set with the Invoice_header as the parent table and the Customer table as the child linked by the Customer_ID.

Using the Genie:
- Right click on the set name and select Create Operation, Update Records, then click the Create Using Genie button.
- Select the General tab and choose Assign a Calculated Value to a Field.
- Click the Next button.
- The field to update should be the Invoice_header Last_name field. (Don't worry about which table the Last_name is from because you can't update a field in a child table.)
- The "expression for the calculated value" should be the Customer->Last_name field.
- Continue to the Select Records to Update window and choose Records Selected by a Filter Expression and enter your filter expression - probably Last_name = "" - although you should theoretically be able to update all unless your customer's last names change (marriage, for one) and you want to keep the old name in the old records.
- Finally, run the operation - probably using the default which saves it to run again in the future.

NOW, having gone through that, are you sure you really need to put the last name in the Invoice_header table?? If you were my customer, I would certainly try to discourage it unless you could provide a really good reason for it. You already have a link to the last name in the set and any report you create can be based on the set. Since the link to the customer table is one-to-one (unless you've found some way to invoice multiple customers with one invoice), you can also easily sort and filter by the Customer->Last_name if desired.