Keeping open key record

  I'm trying to create a invoicing solution using FM8 Pro Advanced using multiple tables (Clients, Vehicles, Invoices) on their own layout.  My problem is, on each layout, it creates a new record that is not associated with the CID (Client ID).
Who is Participating?
lesouefConnect With a Mentor Commented:
No chance, yr new vehicule script works: what is the current client when you click 'new vehicule': none.
you define the vehicule:cid as the client:cid, but this link is not active yet since the vehicule:cid is not defined yet!
you also use the 'last record value' feature, but this assumes you just browsed an existing vehicule (this feature means last record browsed on the same table), which is not necessarily what you want.
I assume you want to create a new vehicule for the current client in the other tab, so in yr script used to create a new vehicule, insert after the new record creation for instance:
- goto layout /clients
- define variable $client = clients:cid
- goto layout /original one
- define field vehicule::cid = $client
there are plenty other ways to do this, and maybe this command should be issued from the client tab. the way you did it is confusing, you can switch from a client to a vehicule which is not his, pretty dangerous that, you should switch to its related vehicules to me, but this is really a personnal feeling about ergonomy.
The easiest would probably be to have a portal for vehicules in the client tab, and that's all. and you would switch to vehicule tab only for details on the selected client vehicule using "goto related record" from the client tab.
I also noticed a few others errors, in tables like "client open", fields referenced to nothing, layout fields refering to missing fields, etc...
Also, you often use 2 auto-enter features on the same field, make sure you fully understand which one comes first or second to avoid unexpected results!
I can improve your stuff from tuesday and onwards, I am not @ home at the moment, just let me know if this is enough or not.
I assume you've got a Client ID field in each of these other tables, and that you have relationships established between the tables based on this ID?

There are a few ways to make a newly created record populate a foreign key with a value from a related table.

One of the most common ways is to use a portal to create the new records. For example, if you create a portal on the Clients table layout which is based on the relationship to the Vehicles table, then whenever you create a new record in the Vehicles portal, it will automatically have its Client ID field populated with that of the current Client record.

Another you can do this is by defining the field in the vehicles foreign table to auto-enter a value in the Client ID field when a new record is created. You can define the auto-enter calculation to retrieve the client id from the clients table based on the same relationship as above.

Yet another way you can do this is by using a script to create the new records, and defining the script to insert the value from the currently selected Client record. You could even define a value list based on the Client ID, and use a popup menu to allow the user to select the appropriate record ID.

Which of these is appropriate for your purpose depends on your specific project and what sort of users you expect to use the program.

Here's an example of a FileMaker database that uses a portal for this purpose.
lownoma925Author Commented:
It seems as if the CID from the Clients isn't copying over to the Vehicles table.   I have uploaded the file to my server if you want to take a look at it to closer understand what I'm trying to accomplish.
billmercerConnect With a Mentor Commented:
I've made some modifications to your file to show a couple of different ways of handling this.

I added a Vehicles portal to the Clients layout. You can add a new vehicle to a client's record by typing in the VIN in the portal. This will automatically create a vehicle record with the correct Client ID. You can then jump to this record by clicking the small button next to the vehicle in the portal. With a plugin and a bit more scripting, you could make this a more automatic process.

I also added a separate New Vehicle button which uses scripting to accomplish the same thing in a different way. It saves the current CID in a script variable, then creates a new vehicle record and applies that value to it.

In addition, I modified your Client and Vehicle tab buttons so that clicking them does something a bit more sophisticated. Instead of just switching layouts, it runs a script which decides how to handle the situation based on whether a record already exists. If the client doesn't already have a vehicle record, then clicking the vehicles tab switches to the vehicles layout AND creates a new record.

If the client DOES already have a vehicle, then clicking this tab simply jumps to that vehicle (or a set of vehicles if they have more than one.)

I did something similar for going from vehicles to clients, though really, the best approach would be not to allow a new vehicle to be created without already having a client record to be associated with.

I also turned on script debugging. Try clicking the buttons and watching the logic of the scripts step by step.

One other thing I noticed is that you created a template layout with a set of tabbed buttons, and grouped and locked these elements. This is fine for static elements, but you usually don't want to use grouping with buttons. The problem with this is that each of those tab buttons needs to be customized to do things specific to its particular layout, they can't be generic for all layouts (unless you do some pretty sophisticated scripting of your user interface.)  More importantly, it's possible to accidentally destroy the button functionality if you buttonize a group. So lock 'em, but don't group 'em.

There are lots of other tricks and tips for creating an interface, but here are some basic principles:

The Go To Related Record script step is your friend.  Use it whenever you want to get a record or set of records based on a relationship.

Make your buttons run actual scripts rather than simply performing a single step. This way, buttons can make decisions for you, and it's much easier to modify your user interface, and re-use existing scripts.

Try to create your interface so that it works in a way that is natural and comfortable for the employees who use it.The best way to do this is to actually watch the employee's interaction with a customer, and see what they really do. Sometimes people tell you one thing, but really do another, and they're not even aware of it.

Try to anticipate not just how the program SHOULD be used, but also how people may MISUSE it. Think about the possible mistakes someone could make, and use scripting to prevent these mistakes from being made. Use error messages that make sense to the employee, not you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.