I have a Clients table, a WorkOrders Table and an Invoices table in my database.

Relationship 1:

Invoices::INVCLIENTID = WorkOrders::WOClientID

Invoices::INVID "not=" WorkOrders::InvoiceID

Example of issue:

the invoice layout has two tabs..

INVOICE: allows me to choose the client and shows me an empty portal where the work orders belonging to this invoice will appear after i add them

UNBILLED WORK ORDERS: has a list of all work orders that exclude work orders that have (WORKORDERS::INVOICED = "yes")

Example:

On Invoice005 i go to the "UNBILLED WORK ORDERS" tab and click the button on the portal row with the desired work order that will belong to that invoice. that button script copied the current InvoiceID and paste's it on the WorkOrders::InvoiceID (this action allows the work order to appear on the "INVOICE" tab for calculating the total). Once that work order is added to the invoice, i see it on the invoice tab, and it is removed from the "unbilled work orders" tab.

Problem:

this relationship works fine, except that when the following month comes around and i need to invoice, work orders that where invoiced on invoice005 are visible for invoice006 which means i can make a mistake and bill a client for the same work order twice.

i know what i need to do, i just cant figure out how to tell filemaker to do it.

i want my relationship to say:

"A work order with the InvoiceID "INV005" will appear in INV005 and not be available for selection to another invoice"

which seems true but only when i have inv005 opened.

the problem is that there is nothing in the relationship that says

"if there is text inside of field WORKORDERS::INVID, then do not display this work order in any other invoices"

the field that says if it was invoiced or not is "WORKORDERS::Invoiced" which has a Yes/NO in it.

please help and im sorry if i did not communicate the issue very well

Define a calculation field in the WorkOrders table =

Case ( IsEmpty ( InvoiceID ) ; ClientID )

Use this field to match the ClientID in Invoices.