How can I have two related Tables (Statements and Orders) so that a payment and a refund (two different records) in Statements will populate just the one record in Orders?

The Tables are related by the number generated automatically in Orders. The number is entered manually in the relevant Statement record.

The idea being that when the payment and, if applicable, refund appear in the Statement, they are numbered and will show in the Order record in order to validate the item and complete the Order.

I can make it work with a payment or refund but not both.

Can I do this?

So let me get this straight.

When you create a statement you want to automatically create an order as well.

But when you add a refund to the statement you do not want to create an order (of course)

I would create a separate table occurrence based on the statement table between your statement table and your orders table. Something like:

Orders = StatementToOrders =(greater than zero) StatementLineItems

So your StatementToOrders table occurrence has only the items that are greater than zero (sales) and nothing less than zero (refunds)

Then adjust your "allow records to be created" appropriately.

Order is created first but payment may not appear in Statements for a few weeks.

so how are you creating a statement via a script?

if so my solution still stands create a separate TO with a greater than relationship. Have your script look at these records that way it only sees the the records that are a charge not a refund.