5 Replies Latest reply on Jan 5, 2009 9:46 AM by TSGal

    Relationship Problems



      Relationship Problems


      I have previously been using a DOS program, Nutplus, for a very basic payroll program.  I had a file for each employee, and then a Pay file that "looked up" information from each of the employee files (there are only two, and used that to calculate Fed & State witholding, SS, MED, and retirement contributions. This has worked well for us for a many of years, but we are, at long last, retiring our DOS machines all together (I know, don't laugh).


      We have converted the Nutplus files to Filemaker, and the individual files work just fine.

      What does not work, is the process of retrieving the information from the employee files from the Pay file.  From what I have read so far, I think the term "look-up" may be different in Filemaker than it was in Nutplus.  I have tried under "manage Database" to set the relationship, but I am just getting error messages.   The fields that were previously "looked-up" from the linked file now just say "linked field", and the fields that are calculations, say "table".


      Perhaps there is another, better, way to relate the information from one file to the other.

      Can anyone help?

        • 1. Re: Relationship Problems



          Thank you for your post.


          I don't know how Nutplus referred to fields, tables, files, etc., so maybe the first thing is to provide you with the FileMaker Pro terminology, just to make sure we're talking about the same thing.


          Each bit of information, Last Name, SSN, IRA amount, etc. are known as fields.  Each group of fields for one person is referred to a record.  And there are a group of records in a table.  You can also have one or more tables within a database file.  For example, suppose you have a database file named "Employees".  This database file may contain one or more tables.   For simplistic case, let's assume there is one table, and it is also called "Employees".  In the Employees table, you would define fields for Last Name, First Name, Address, City, State, Zip, SSN, and perhaps a few more fields.  Next, you would start adding records (or employees) to this table.  If you have 10 employees, you would end up with 10 records.


          With the terminology out of the way, let's move forward...


          Your "Pay" file may be another table in the Employees database file, or it may be its own separate file.  In order to link the two files/tables together, you need a common key.  Perhaps it is the SSN field, or maybe you have an Employee ID field.  In any case, you definitely need a common field/key.


          For this exercise, let's assume it is the SSN field, because it would be unique for each employee.


          Therefore, go into "Manage -> Database" like you have done previously, click on the Relationship tab, and you should see the tables for this database file.  If the "Pay" table is listed, then click on the SSN field in one table and drag it over to the SSN field in the other table.  You will see a line appear between both tables, and the SSN field will appear at the top of each of these tables.  If the "Pay" table is not listed, then click on the bottom left icon, and you will be prompted to Specify the table.  Click on the "Data source" pop-up, and select "Add FileMaker Data Source...", and select the "Pay" file.  Once in the Relationship graph, then you can link the two tables/files together using the common key field (SSN).


          Once finished, return to Browse mode to view your information.


          You won't see your "Pay" information just yet, because we need to create a portal.  A portal allows you to view data from another table.  Pull down the View menu and select "Layout Mode".  On the left side of the screen, you will see several icons.  Click on the icon just below the oval tool.  This is the portal tool, and it will allow you to draw a box on the layout to view the related data.  After you draw the box and let go, a new dialog box appears prompting to "Show related records from:".  Click on the pop-up and select the "Pay" table/file.  Click OK, and then you are prompted for the fields you want to appear in the portal.  Select the field (or fields) you want to display from the "Pay" table/file and click OK.  Your portal is now set up, and you can return to Browse Mode (View menu) to view all related data.


          This should help you get started.  If you run into any difficulty, or if you need clarification for any of the above steps, please let me know.



          FileMaker, Inc. 

          • 2. Re: Relationship Problems

            TSGal,  Thanks for your reply.  It was very informative.  I am making progress, but still have a few glitches.   Not sure if I made it clear that we had the Nutplus files converted to Filemaker and I am trying to work with my files as they were - individual employess each have a file (or database I guess) and then I have another database related to the indivudual ones.  This Common database pulls the information from the individuals and uses it to calculate the Employer contributions to SS, Med, retirement, etc.


            Here's where I am:  By making a new layout, I can now to see the information correctly from the related file so I know the relationshoip is established.   My old layouts do not work - they still display the words "Linked Field" where the info from the related file should be.     On the new layout, records that were existing when we converted the files work OK.  The information from the related file is displayed and the calculated fields have information.   I suspect the numbers New records are displaying the info from the related file, but the calculated fields are not working.  They are blank.   Anything come to mind?


            Another thing I cannot figure out: My old files had "preset" values.   Name, SS#, etc. was already filled in when I added a record.  Now I cannot seem to get this to work in the converted filemaker files.  I have tried a few things, thinking it will work, but I am missing something.


            I do appreciate any suggestions you may have.  I still have a long way to go!



            • 3. Re: Relationship Problems



              First, I'm glad I was able to help, and I'm glad you are making progress!


              It appears your old layouts may not be set up properly.  If you pull down the View menu and select "Layout Mode", you will be able to view what fields are displayed.  If you double-click a field, FileMaker will show you the table from where the field is located.  If it says "Unknown", then that is probably the reason why it isn't working.  You can select the appropriate file and field and you should be good to go.  Do this for each of the fields that say "Linked Field".


              One of the options for a field is to auto-enter information.  This would be your "preset" values.


              Pull down the File menu and select "Manage -> Database".  Click on the Fields tab (if not already selected), and find one of the fields that you want to automatically fill in when a new record is added.  For example, double-click on Name, and a dialog box will appear with Field options for Name.  Select the left tab, "Auto-Enter", and there will be a number of checkbox options.  Near the top, it will say, "Automatically enter the following data into this field".  Put a check mark in "Data", and enter the information "Carol" or whatever is the information you want to automatically enter.  Remember, this will only work for new records; not existing records.


              Please continue to keep me updated with your progress!



              FileMaker, Inc.

              • 4. Re: Relationship Problems

                More progress!   The Auto-Enter advice worked perfectly.  I was trying to accomplish this by checking "Value from last visited record" and it was not working.  


                I still have made no headway on my existing layouts.  When I double click on the fields where the Linked Field is displayed, I get nothing.  No option to change or add anything.   It displays a box and the cursor is flashing.  I can edit the text, but nothing else.  I believe this is a problem in the conversion, and that the "field" is not really there.  I think that the "LINKED FIELD" wording is just text, where the field info should be.   I am able to delete that text and drag in a new field.  I am thinking I will have to rebuild the layouts.


                In order to make these converted filemaker files work together, I had to go to the field definitions and re-enter the formulas.  As soon as I did that, all of the calculations worked.  The formulas in the converted files were different, different way of showing the related file, etc.  Once again, I am sure a conversion glitch, but I now know how to fix it.


                Something that I don't fully understand about our old Nutshell program:  I originally opened these files and defined the fields back in 1994.  Every year to prepare for the new year, I would copy the old files, naming it the next year, say 1995, and I would have to tell the Pay file to look-up files from the 95 employee files.  The formulas in the files, however, still contained the original 94 file name in the formula, and it appeared they were looking up information from the 94 file, even though they were not.   I hope that makes sense.   I think filemaker may do something similar.  My dos file used in 08 still shows 94.db in the formulas. 


                When I first starting working with these converted filemaker files, I made copies of them in case I messed them up.  If I remember correctly, even when I copy 08 file and rename it 09, the table of fields is still called 08.  Maybe similar to what my old files were doing in Nutplus.  Hopefully I will not have to redefine all of the formulas.  I haven't tried to make a copy since things have been working.  


                Unless you can suggest something else to take care of the "linked field" problem, I will proceed with redoing my layouts. 


                Thanks so much for helping a beginner filemaker!

                • 5. Re: Relationship Problems



                  I'm sorry for the late reply.  I left at noon on 12/17, and I have just returned.


                  FileMaker Pro is looking for a fixed link.  Therefore, when you change the name of a file, the reference will NOT be changed in the linked file.  Therefore, you would need to go back and redo the links.


                  Another thought would be to have a master file.  Then, at the end of the year, duplicate the file, rename the duplicate to the name of that year (08 in this case), and then delete all the records in the original file.  You'll have your yearly file, and your current file won't have to have anything changed.  Does that make sense?


                  Let me know if you need any other assistance.



                  FileMaker, Inc.