10 Replies Latest reply on Nov 9, 2010 12:41 PM by philmodjunk

    Job Record, that contains People linked from a contact database

    ChrisCrawford

      Title

      Job Record, that contains People linked from a contact database

      Post

      Hi All

      Wonder if someone could kindly point me in the right direction

      I have a very simple database of customers. I have created a new database, each record will be a 'Photography shoot'.

      What I want to do is then add people to my record as people. Im sure It's to do with portals, I have a small understanding of how relationships work, but I just cant get my head around this one,

      Simply, I am a wedding photographer, I want to create a new record for the wedding that will hold ceremony and reception address, the package, contract sent etc etc, but I have a FM database of my client so I just want to add the "bride" * 'groom' in from my already created database!

      Thanks 

      CHRIS

        • 1. Re: Job Record, that contains People linked from a contact database
          philmodjunk

          Two approaches:

          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...

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Job Record, that contains People linked from a contact database
            ChrisCrawford

            Thanks PhilModJunk

            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

            Chris

            • 3. Re: Job Record, that contains People linked from a contact database
              philmodjunk

              I thought "christenings, birthdays, bar/bat mitzvahs" might be possible photo shoots. Wink

              • 4. Re: Job Record, that contains People linked from a contact database
                ChrisCrawford

                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!

                • 5. Re: Job Record, that contains People linked from a contact database
                  philmodjunk

                  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.

                  • 6. Re: Job Record, that contains People linked from a contact database
                    ChrisCrawford

                    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

                    or

                    if I already have the client in my database, after I create the new shoot record, how would, I add the client?

                    C

                    • 7. Re: Job Record, that contains People linked from a contact database
                      philmodjunk

                      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.

                      • 8. Re: Job Record, that contains People linked from a contact database
                        ChrisCrawford

                        Thanks!

                        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

                        C

                        • 9. Re: Job Record, that contains People linked from a contact database
                          ChrisCrawford

                          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

                          Any ideas?

                          sorry to be a pain

                          • 10. Re: Job Record, that contains People linked from a contact database
                            philmodjunk

                            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.