Auto-populating data between databases
How do I create relationships between two databases so that data from one database can auto-populate in another?
So the customer field that identifies with the job field should be 'boat name' as opposed to 'customer ID' as we do not want the customer information, only the boat location information.
No, this should be customerID and should be an auto-entered serial number. This value is simply used to link the curent jobs record to the correct customer record in the customer table. You can't use the name of the boat for this because boat names are not always unique and (though considered bad luck) the name might be changed.
To link tables in relationships, you open manage | Database | relationship and link them there. Since Customers is in a separate file, you'll need to click the far lower left button in this window to add a new blank table occurrence box to this map of your relationships. Then you select "Add FileMaker Data source" in the data source drop down. This opens up a dialog where you can find and open the customer file. From there you can find and select the correct table. After you have done that, you can click Ok to close the dialogs and drag from the customer ID field in Jobs to the customer ID field in the customer table.
Although the customer can have multiple boats, they are usually kept at the same location
You might want to start thinking about how you will handle the unusual customer that does not store all their boats at one location...
Can you describe what you want in more detail?
It depends on the design of your tables and how they need to link each other.
It depends on whether your "databases" represent separate files or just different tables in the same file.
It depends on whether that "auto populate" action should physically copy data from one table to the other or just should display the current values of the related record(s) in the second table...
I have a customer database and a job database, I believe them to be separate files.
The customer database holds all known locations for each boat on the second tab
What I would like to happen is when the corresponding name of the boat is entered into the job database, all the location information is automatically generated based from the customer database. So the job database should 'physically copy' data from the customer database and the trigger should be the name of the boat.
Please let me know if I can be more specific or answer any related questions and thank you for your answers!
Can't a customer have more than one boat? (Sounds like you need a separate table to list each boat so that customers can have more than one.)
I'm referring strictly to tables here as it makes no real difference whether the two tables are in the same file or in different files except for a few extra steps needed to set up the relationship if the two tables are in different files. (That's why I asked about this.)
Do you have a field in the Customer table that identifies each and every customer uniquely? You should have a serial number for each.
WIth this relationship between the Customer and Job tables:
Jobs::CustomerID = Customer::CustomerID
You can define looked up value settings in field options for each field in Jobs that you want to receive data from Customer. Jobs::CustomerID can be formatted with a value list that lists the CustomerID values from Customer in field 1 and the customer names in field 2. (If you have separate last and first name fields, define a text field with an auto-entered calculation that combines them and use it for field 2.) With this value list, you can select a customer by name, but enter their ID number into the field. WIth the looked up value settings, any such update of the Jobs::customerID field automatically looks up (copies) the data from the customer table.
Some things to think about when it comes to copying data from table to table like this:
If you need to record the data that is current at the time this record is created, then this is the way to go. This way, you can look back at older job records and see the boat location at the time that the Job Records was created--which could be different from the boat's current location.
Other data should not be copied in this fashion as changes to the original table (the customer table) will not automatically update the matching fields in the Jobs table. In those cases, you should just add the fields from the original table to your layout. That way, the data in them will always display the current values without any extra effort on your part to keep them up to date.
Although the customer can have multiple boats, they are usually kept at the same location therefore there is only one field for name/ address, etc
The only option that comes up in the 'Inspector' is the current Table as opposed to an option to include a separate database, I hope this makes sense as it is obviously not very easy to explain. It appears to me that I cannot figure out how to 'access' or 'relate' one database from another at all.
Thank you for your patience
My question has been answered and you have been a great help. Almost a bit snarky, but served the purpose.
Again, thank you!
Retrieving data ...