1) Assume that you're only going to photograph a given bride or groom in a single wedding--dangerous assumption as people do remarry, but simplifies your structure...
Define an auto-entered serial number in your PhotographyShoot table; call it PhotoID.
Define a matching PhotoID field in your customers table and link the two tables by PhotoID.
Customers::PhotoID = PhotoShoot::PhotoID
You can format it with a value list of PhotoID's from your Photography table. You can specify a name field from PhotoShoot as a second column in this value list to make it easier to select the correct photoID.
Now a portal to customers on your photo shoot layout will list all customers that you've linked to that photo shoot.
2) assume the same customer could be part of more than one photo shoot. This is now a many to many relationship and you'll need a join table to link the two tables you've described:
Customers---<Customer_Photo>---PhotoShoot (one ---< Many )
Customers::CustomerID = Customer_Photo::CustomerID
PhotoShoot::PhotoID = Customer_Photo::PhotoID
Here's a many to many demo file you can look at: It links Contracts to Companies, but if you think of Contracts as Photo shoots and Companies as customers...
Im going to read that very slowly, Im still learning but enjoying this
The twist I should have mentioned is the couple have a kid and then book me for portraits.... so yes I could create a new 'Job' or 'shoot' that I link the same people into
Going to check out that file, thanks so much for the response
LOL Yeah its all possible... been there and done it!
Right, completely struggling to understand what your saying regarding linking tables
I don't even know where to start
The first suggestion I do understand...... but the second which I need I really need I don't, is there a step by step guide or tutorial anywhere?
It might be simpler to just re enter into new field on each record the client contact details, but I really want a seperate database of 'Contact' for my iPhone and iPad
Thanks so much!
That's one of the reasons for the demo file. You can download it, and take a look at how it's set up to support many to many relationships.
The process for both methods is much the same. It's just with many to many relaitionships you are linking three tables instead of two.
Customers::CustomerID and PhotoShoot::Photo ID would be auto-entered serial numbers. The matching fields in the Customer_Photo table will be simple number fields.
The key difference is that portals in the demo file are both portals to the Join table, to which you can add related fields from the third table.
So if you placed a portal to Customer_Photo on a customer layout, you could add the date and event name fields from Photo shoot to the portal so that you can see what photo shoot events are linked to that customer.
Conversely, if you place a portal to Customer_Photo on a Photo Shoot layout, you can add fields from customer to list the customer's name, phone number ect. to the portal.
OK thanks, Will break it down, I think I understand it, will break it down further, thank you so much for help
1 last question, what the best way to link it all,
ie If I create a new shoot, how would I 'add' a person, that when I enter the details, will add this to my 'Contact' database
if I already have the client in my database, after I create the new shoot record, how would, I add the client?
If both records exist, you can use a drop down or popup list in the portal. The demo file has this method.
If you need to both create a new shoot or customer record to link in, you'd need to first create that other record--which you can do by hand just by changing layout and starting a new record.
A script could be used to both create and link that record to make life easier. There are elaborate and simple ways to do that. I'd get the basics in place first, if I were you and then take a look at automating the "link in a new record" process.
Final FINAL question I think. Last time I used FM it was v6... my new version v11 looks like you can have 2 tables in the one database... looking, it looks like I could have 'Contacts' and 'Shoots' in the same Database, is that right? Is it worth me doing this? or should I keep things seperate like I was used to? Bare in mind I would like just my contacts available on my iPad while away from my network
Right I am really struggling with this LOL
The demo file you kindly gave me has clients (or companies) and Shoots (contracts) in the one file, I can still do this excercise but having to different files?
Ive setup exactly how the demo file is, except i've added in a source file for the relationship and linked, ie the contacts file is an external source
I create the portal, make the field pop menus and setup just like the demo file, although im not 100% im linking it all correctly, but when I create records, on the portal, I cant get the menu with my client names to pop up like I can in the demo file
sorry to be a pain
Separate files for each table should work. You'll need to look at the relationship options and the details of your value list setup to see what isn't set up right.
Once you've added a data source from another file to your relationship graph, you should be able to treat this like any other table occurrence box in your relationship graph. In the demo file, there's an option selected: Allow creation of record via this relationship that enables you to add a record to the join table by enteriing data in the bottom blank row the portal.
Another issue to check is that if you are now using a different table, a value list defined in Manage | Value Lists will also need to be updated to draw its values from this new table.