7 Replies Latest reply on Jul 6, 2009 10:14 AM by philmodjunk

    Help Needed referencing from external source

    JimLewis

      Title

      Help Needed referencing from external source

      Post

      Hi, 

      I am new to FM Pro and a business owner with a little over 10 employees. I am trying to keep their schedule via time sheets and i cannot seem to be able to reference the employee ID and name to my contacts in "contact manager". this kind of reference is very important to me and it is really imperative that i figure out how to be able to use external sources or DBs for values, drop down menus and as part of calculations. Can anyone advise me on how exactly i use part of the contact manager's information in time sheets?

       

      Regards and thanks in advance,

      Jim 

        • 1. Re: Help Needed referencing from external source
          philmodjunk
            

          You can use File | Manage | Database | Relationships to add a reference to a filemaker table found in a different file.

           

          Once you've pulled up the relationships graph, click the lower left button to add a new table occurrence and use the data source drop down to select "Add filemaker data source" to find your file and table that you want to add.

           

          That's all you need to be able to set up simple value lists that draw values from the other table. For more sophisticated needs, you'll need to establish relationships that link your newly added table occurrence to your other tables.

          • 2. Re: Help Needed referencing from external source
            JimLewis
              

            Hi, 

            Your help is well appreciated. So what you described helps me pull up records from other data sources via linking one field to another, meaning if i linked Zip codes to a db of zip codes corresponding to city and state, i MUST put in the zip code to get the reference.  Now what if i want to have the zip codes as the only options in that data field and have them in a drop down menu? or rather, to be exact, i would like my interview database and my contact manager to be connected in such a way that when i select the status of an interview as hired, he is automatically added to my contact manager with all the relative fields, and a time sheet is created for that new employee? 

             

            Thanks and Regards.

            • 3. Re: Help Needed referencing from external source
              philmodjunk
                

              "Now what if i want to have the zip codes as the only options in that data field and have them in a drop down menu?"

               

              Simply add the table occurrence as I described (no need for a relationship) and you can now use manage | value lists to create a value list that specifies a field in this external table as it's source of values. Once you've defined the value list, you can use Field/Control | Set up to specify this new value list as a drop down for a field.

               

              "I would like my interview database and my contact manager to be connected in such a way that when i select the status of an interview as hired, he is automatically added to my contact manager with all the relative fields, and a time sheet is created for that new employee?"

               

              The simple answer is that you would write a script for that to put data in the relevant fields and which creates new records as needed. The detailed answer requires detailed knowledge of your tables, relationships and layouts which I don't have.

              • 4. Re: Help Needed referencing from external source
                JimLewis
                   Thanks, I guess it is time to learn how to script :-)
                • 5. Re: Help Needed referencing from external source
                  philmodjunk
                    

                  One newcomer mistake to avoid:

                   

                  Don't copy data from one table to another unless it's absolutely unavoidable. Instead use the relationships between your tables to make it possible to place fields from both tables into the same layout.

                   

                  Example:

                  Say you have a contacts table listing names and phone numbers and a calling log table recording each phone contact made to a customer.

                   

                  If you want a report listing all the contacts made to a given customer, you don't have to copy name and phone number information from the contacts table to the calling log table. Instead, simply place fields from your calling log and contacts tables into the same layout and rely on a relationship linking a Customer ID field in both tables to match the right log records with the right contact record.

                  • 6. Re: Help Needed referencing from external source
                    JimLewis
                      

                    Hi,

                    This seems to be the crucial point here which i cannot seem to avoid: Copying records. How can I logically have a list of people that have been interviewed and have a list of employees, one being a derivative of the other, without having two lists? My need for a solution stems from the fact that i need to create a db of interviewees, a db of current employees, a list of their sales, a list of their attendance, and the ability to generate reports based on the info. It would seem to me that the way to do this is to create a db called interviews, which will contain all those coming through my office. If their status field becomes "HIRED" i would like a trigger activated script to add that person to my contacts, maybe even to address book as well, and then create a db of sales and a db of attendance, both with relationships to the employee list i have populated. What do i need to understand to look at this in a totally different light and avoid duplicating info? 

                     

                    thanks 

                    • 7. Re: Help Needed referencing from external source
                      philmodjunk
                        

                      You can load both hired and prospective employees into the same table(s). In your personnel table, include a field that records their status ("Hired", "Prospective") or whatever values make the most sense to you.

                       

                      When a person comes in for an in interview, you have them entered as "Prospective". If you hire them, simply change this field to "Hired".

                       

                      If you want to see a list of data on your prospective employees, use a manual find, scripted find, or a Relationship with Go To Related Record to pull up a list of just your prospective employees.

                       

                      Use the same technique to see a list of all your current employees.