Using a new table for unpaid invoices is not a good idea. That requires you to physically copy the data from one table to another everytime an invoice changes status from "unpaid" to "paid".
Instead, you can use a filtered portal (FileMaker 11 only) or filtered relationship to list only unpaid invoices in a portal. If you base your layout directly on the invoices layout, you can perform a find for all invoices where a status field stores a value that tells you the invoice is still "unpaid".
On another note, using a client name field to link your tables is not a good idea. Client names are not unique, they change their names and it's easy to incorrectly enter a name. Dealing with all of those issues can cause a lot of problems when your client's name is used in the relationship between tables. Instead, you should define a serial number field in your Contact Management table and use it to link client data to other tables.
Thank you for your quick reply.
I am currently using FM9Pro, so don't have filtered portals.
However my clients do have a unique ID. I'll base my layout on the invoices tab though the table "Job Sheet" has the record of whether I'm awaiting payment or not. So I figure there will have to be a way of sorting via that relationship.
Any ideas on the best resource for learning about filtered relationships in a portal. I've been crawling the internet and tryings stuff but it still means nothing to me.
Thank you in advance for any further assistance.
On what layout do you want to locate the portal?
When you open up Layout Setup... for this layout what table occurrence is shown in the "show records from" box?
What field in the Invoices table marks it as "paid" or "Unpaid"? You'll need a field in this table, before you can use a relationship.
We need to know that starting poing before we can set up a relationship that links to the invoices in a way such that only unpaid invoices are related.
The other alternative, performing a find for all unpaid invoices for a given client can be done without defining any new relationships--it just has to be done on a layout that lists the invoices as individual records instead of in a portal. This approach can be much more flexible than using a portal.
Okay I feel like I am getting close.
I have a "job sheet" & "invoices"
From a sort (which is scripted) I can get all of the jobs which have an unpaid invoice to appear in "Table View" as a list. Unfortunately this is regardless of which client.
How do I then perform an additional find for the specific clients?
Ideally I would like to have a layout where I place a drop down menu at the top. Select a clients name (already have a client list) and from the sorted records described above the outstanding invoices for that client appear in one list on one layout.
It easiest to find the client records then sort by paid status.
Make your drop down list attach to a global field (Select global storage in Field Options) and use this as a script:
Enter Find Mode 
Set Field [YourTable::ClientID ; YourTable::GlobalField ]
Set Error Capture [on]
Perform Find 
Sort [Restore ; no dialog ]
Thank you once again Phil.
Unfortunately I am confused to the max now. I wrote the script like you wrote and tried to perform the find but it just shows me the current record.
I may need to start again or get someone to help / do it for me. Any ideas on the best place to start for doing it myself or where to find a database programmer?
The attached image shows how the results are displayed in table view.
Ideally I would be able to sort through the results only displaying for one name. eg. will
Currently I am manually inserting the details into a spread sheet. I feel File Maker would be able to simolify this process.
Thanks for any further assistance
A summary report based on the invoices table with related fields from Job Sheet would allow you to create this report.
Here's a tutorial, on summary reports that you can look at for a step by step description on how to set them up.