First, you can really only link tables in FileMaker not "databases" a term in "FileMaker speak" that gets a bit vague as "database" can refer to a single file or a group of files that together comprise a complete solution for what you need to accomplish with FileMaker.
When you link two tables, the two tables can be in the same file or in separate files, but FileMaker treats the link much the same once you have defined the link. Look up External Data Sources and how to use "Add FileMaker Data Source" to set up a table occurrence "box" in Manage | Database | Relationships that refers to a data-source table in another file instead of a data-source table in the same file.
When it comes to whether this is a good idea, there's no hard and fast rule that will make the decision for you. You'll need to weigh the trade-offs and decide for yourself.
"Thought to create one database for the resort, one for the restaurant and train staff how to use a simple input layout for each respective business. I assume I can lock them out of anything important on these. I would then create a 'Master Database' for me to reference the other two to generate invoices and reports."
In reality, this can all be done with a single combined file of all tables and all layouts for both businesses. You can use Manage | Security to set up accounts for each user that limit their access to specific parts of the system and even to specific records within the given tables so you don't have to separate your system into different files to accomplish this.
"My logic is that three databases are good in case the resort and restaurant parted ways."
Hmmm, that's a slightly better reason for having separate system. It's not absolutely necessary, however. Should such a split occur, you could save copies of your database and purge records from each copy as needed so that each business can be given a copy of the data specific to their needs. This would take some effort to accomplish, however, and you'd need to keep this possibility in mind while designing the system so that you can easily perform finds to locate the records you need to purge from each copy of your divided database.
Questions to ask yourself that may help you decide:
How much "overlap" is there between these two businesses, both now and how much is likely in the future?
If you have employees that work at both businesses or who might do so in the future; If you have business contacts (Customers, vendors) that may engage in business with both; if you have any common resources/expenses that are common to both...
The more such overlap, the more duplication of effort and duplication of data you can avoid by keeping your system "unified".
Do you need "overall view" reports that combine data from both businesses?
If so, these reports are often much easier to create when the same type of data can be drawn from a common table where a field in each record is used to "label" the record in order to identify the business for which it records data.
When you link two tables, the two tables can be in the same file or in separate files, but FileMaker treats the link much the same once you have defined the link.
Look up External Data Sources and how to use "Add FileMaker Data Source" to set up a table occurrence "box" in Manage | Database | Relationships that refers to a data-source table in another file instead of a data-source table in the same file.
So I'm guessing this is my solution, whatever it means exactly (smile). I'll try to figure it out better. Never realized Databases had such a complex language !
I do hve a strong desire to offer a seperate database file my housekeeper can click on and see maybe four layouts for data entry with fields like: (date, client name, room number, housekeeping charges, massage, laundry) as well as do things like check her schedule. Same for my Restaurant with fields like: (Date, Client name, room number, date and tab) as well as additional layouts for schedule, stock orders needed, etc.
My database would be the tough one, linking these files of Housekeeping with Restaurant so I could have layouts with fields like: Client Room Charge (formula), Client Restaurant Charge (formula), Stock Order Request etc. in whatever layout I decide to build for loveliness sake.
Right now (as I understand) a 'one file solution' would have 20-30 layouts or more on the pull-down menu. True ? If so I don't want my employees having to deal with that whether they were locked out of most of them or not.
Would rather have a .fm7 file renamed "Housekeeping.fm7" or "Restaurant.fm7" sitting on the iMac desktop for their data specific entries.
Am I on the righ track here ? Again, just looking for sage advice as to whether this solution would be resonable or difficult to build the architecture for.
With your advice I'll choose a road and learn where the curves and hills are.
Hey, just had another thought, hope its not frivoulus.
Can I have three or more database files and generate REPORTS using data from all three ? Seems that would be an easy way to link what I need while keeping a certain independence.
I'm in the Paper Phase of all this (I read the books) and still imagining what is possible. My head is hurting from reading manuals.
Thanks for explaining just what these programs can do and can't.
Nikki, I'm learning too and I think I just saw one of your lightbulbs go off. When Phil said it's 'tables' not databases just replace the word database in your sentence: Can I have three or more database files and generate REPORTS using data from all three?
The answer is YES you can have 3 or more TABLES ... then generate reports using data from all three.
(Phil stop me if I'm wrong). I think we tend to overcomplicate. I definitely overthink this stuff ...
I just got this from the Filemaker Help:
Accessing external data sources
You can connect to and work with data in external data sources in much the same way that you work with data in the current, active FileMaker file. For example, you can:
work with data from external files in FileMaker fields and layouts
With some effort I can make this happen, mostly I'm glad to have gotten help ferreting out what Filemaker Speak called these things. I will fasttrack my learning in the direction of the 'relationship graph'. There's so much to learn I'm trying to segment what is most important at the earliest phases of design. Right now the most basic question: One database (fm7) file or three ?
Still wondering though if I'm going down a dangerous path and is partly why I started this thread.
Just because someone can (or might) be able to do something, not certain if they should do so as far as maintenance commitment or 'bug hunting'. For example it also references OBDC or something, with troubleshooting help. Probably don't want to go there.
While you are in the planning phase, yet another "FileMakerSpeak" item you should strongly consider, is the "Separation Model". As the name suggests, this separates the data from the presentation (screen and print, etc.) By the sounds of what you have in mind, this could help. A search through the Knowledge base will turn up numerous items as will a web search.
I am using this model for the solution that I am now developing. The data (all the tables) resides in one .fp7 file and that is all that is in there (basically). I then have "separate" .fp7 files for DesktopLayouts, iPadLayouts, iPhoneLayouts, IWP(Instant Web Publishing)Layouts and Reports; each of these .fp7 files has NO TABLES; they are linked to the Data.fp7 file. Use the method described in the above posts to access "external" FMP data sources (the Data.fp7 file in my case). A bit more work to set up initially but well worth the effort down the road in maintenance, updating and modifying.
Yet more to read! :)