Can a given Contact record have more than one Invoice Number allocated? If not, I see no need for an added table.
Perform a find by putting an * in the Invoice Number field as your search criteria, then use sort records to sort them by invoice number if that is what you want for a sorted order.
But if you might need to assign more than one invoice number to a given Contact, then you'd need that added table and would set up this relationship:
Clients::__pkClientID = InvoiceJournal::_fkClientID
A portal to InvoiceJournal can be put on a Clients layout to use for assigning those multiple invoice numbers.
And an InvoiceJournal layout can then be used where you add any needed fields from Clients to that InvoiceJournal layout.
Thanks Phil for your prompt response.
Its always possible that we would have a repeat client so guess the second option you recomend would be the best.
I would be grateful if you could expand upon this a bit please.
i.e. in the contacts table, would i rename the ID field as _pkClientsID and if I wanted to show "Name", "Arrival date", "No of Nights", "Total Cost" etc, which are all fields on my "Contacts" Table, would I copy these across from one table to another or create new in the "Invoice"table which also would include _fkClientID as the relationship link.
Sorry for the short answer, but there's no point in having the same data in more than one table.
Since I did not know the name for your ID fields, I used my own names for them. You do not have to rename them. You can if you find doing so useful.
Once you have your relationship in place, you can:
Go to the InvoiceJournal layout.
Enter Layout mode.
Then either click the field picker (New in FileMaker 13) or drag and drop from the field tool in the Status area tool bar to open the specify fields dialog.
In either dialog, select your Contacts table occurrence in the drop down at the top and then use the list of fields to select a field for your layout. This adds a field from your contacts table occurrence to your InvoiceJournal layout. The relationship will enable FileMaker to display data, the "Name, "Arrival Date", etc... from the Contacts record that is linked to the current InvoiceJournal record.
You may want to use a layout based on InvoiceJournal to produce a Creating Filemaker Pro summary reports--Tutorial that includes data from contacts.
hi Phil, many thanks for advice, at the moment i am still trying to get a head around my issue (amongst other things!).
On Friday there is a webinar on Relationships which i shall listen into and maybe this will give me some guidance.
I may need to seek some extra advice if you are willing please after next Friday.
Hi Phil, having watched the webinar last Friday, there is still fog!!!
If I may reiterate, In a nutshell, I have created a table called "Contacts" which contains all contact details of family, friends, useful contacts, and those of our clients using our self-catering apartments.
With reference to our clients, I also create an excel spread sheet which duplicates some of the information I have entered into my "Contact" table, i.e invoice number for each client, date of arrival, number of people, number of nights and cost etc.
Duplicating this information to this excel spread sheet is obviously a waste of my time and I feel that I could do it more effectively by creating another table, say called "Invoice list" and sorted by invoice number which is linked to the "Contact" table but only showing the information as mentioned above and only showing those entries that have been given an invoice number. (by me)
I have created a layout based on this information but I have to change the "sort records order " depending on which layout I am looking at and it also shows all the entries from my "Contact" table which is unnecessary, so this is why I feel that a linked table would be best.
It is at this point that I cannot get my head around the solution.
If you are able to suggest how I can do this based on the information above, I would be most grateful.
You appear to be putting data all in one record that should be divided into individual records--likely in more than one table in some cases all linked in relationships.
I see what you are saying but if I create a table just for "clients", some of the entries/records are just enquiries from clients, some confirmed bookings and some cancellations.
After a client has made an enquiry, this may be later turned into a confirmed booking at which stage I give it an invoice number.
It is the "confirmed bookings" that I want to siphon out into a separate list/report table all sorted by the "invoice number". The rest of the data in this table would be the name, arrival date, no. of persons, no. of nights etc.
Hoping this makes for more clarity?
I thought I might send you an image of my typical contacts table/layout if it helps.
It is the "confirmed bookings" that I want to siphon out into a separate list/report table all sorted by the "invoice number".
You can manually or via script perform a find for records that are "confirmed bookings"--either all such records or all in a specified range of dates.
Once you have performed the find, you can use Sort Records to sort them by Invoice Number.
Here's a thread with quite a few scripted find examples: Scripted Find Examples