You need to use a separate field in the invoice table for the client name so that you are not creating a new client record by entering data into the client table each time so select a name from this list.
What you describe is very similar to the "Auto-Complete Value Lists II" example found in:
This method uses a relationship to match by name to records in the customer table. A script checks for duplicate names (you can easily have two customers of the same name and for names not found in the table. When such a new name is entered, the script displays a message offering to create a new customer record.
Thank you Phil
The issue with my problem is that I want the invoice form to add new customers if not exists in "Client Details" table too, its not just selecting name and telephone numbers from "Client Details" table.
May I share my project file??
Here are the screenshot.
I did understand that and the script that checks for duplicates and also checks for clients not found can create a new record in the clients table if the user confirms that this is what they want. (The confirmation dialog is to help avoid creating new client records when you simply entered the name incorrectly.)
Please note that you can easily get two customers with the same name and this situation is also handled by the example found in that file.
Thanks you again Phils, I have gone through but as I'm a beginner even with just layouts and tables I have not been to scripts and got puzzled when tried to copy scripts but the appropriate table names was again a mess at my end. Can I have a video or written tutorial for this "Auto-Complete Value Lists II"