5 Replies Latest reply on May 22, 2012 9:46 AM by Mike_Mitchell

    Notes Fields Missing??

    biltmorefinancial

      I recently purchased the Filemaker Pro version 12 and started from the Personnel Starter solution layout. I adjusted it quite a bit to work how we needed it. I transformed the vacation section into a notes section. Everything was working fine until last night when I uploaded 3,500 records and for some reason I cant view the notes fields anymore. At first it gave me an error " This field cannot be modified until “PERSONNEL ID MATCH FIELD” is given a valid value." But now I cant see the notes section at all. I am assuming I have a relationship set that I dont need. We arent using a Personnel ID field. We have a "Contact Name" field to distinguish between each but its text and not numbers. I am thinking I just need to adjust the table set up but I am not sure. I hope someone can help. I need to have this up and running by tomorrow afternoon.

       

      Thanks in advance!

        • 1. Re: Notes Fields Missing??
          Mike_Mitchell

          Hello, biltmorefinancial.

           

          I don't know what changes you've made, but it appears you've guessed correctly. FileMaker is attempting to use a relationship that is missing a key field. Take a look at the graph below:

           

          graph.png

           

          You said you modified the "vacation" section into a "notes" section. The Time Off section in the original solution is a related table, a one-to-many relationship (so you can have many "time off" records for a single person). In order to be able to type what are now notes for a person, FileMaker needs to be able to establish which notes belong to which person - and that means it needs the key field populated. Although it doesn't appear on the layout, it's still needed, because it's how FileMaker figures out which records match up between tables.

           

          In order to fix the problem, you'll need to get the PERSONNEL ID MATCH FIELD in the Notes table populated. To do that, you'll need to establish another relationship, one based on Contact Name (which I hope is unique). You can then use the Replace Field Contents function to replace the missing value on a layout in the now Notes table (like Time Off Report, or just build a new one), using a calculation like this:

           

          Personnel Records::PERSONNEL ID MATCH FIELD

           

          That will allow you to see the Notes records from the Personnel Records table. Once that's done, delete the other relationship (the one based on Contact Name).

           

          As an aside: It's almost never a good idea to use something like Name as a key field. The chances of it being non-unique are very high. Plus, you have to allow users to edit it, which means there's a good chance they'll type something wrong and break the relationship - resulting in the kind of hair-tearing experience you're having now.

           

          HTH

           

          Mike

          • 2. Re: Notes Fields Missing??
            biltmorefinancial

            Is there anyway to use the Personnel ID match field as the key field? I just dont want it to show up when we are looking at the database, want it to be a hidden field...

             

            Oh, and now that I have deleted the relationships it says "Missing Table" in ALL of my fields. I am so lost! I wonder if you could guide me?

            • 3. Re: Notes Fields Missing??
              Mike_Mitchell

              Sure. We can do a couple of things here.

               

              1) If all you did was delete the relationship (the little line with the box on it), then it's a simple matter to recreate it between the two PERSONNEL ID MATCH FIELDs (the one in Personnel Records and the one in Notes). Just drag the field from one table and drop it on the field in the other. Piece of cake. That should update your layouts. If it doesn't, then just double-click your fields and point them to the right table.

               

              2) If all else fails, just recreate the solution from the template and import your records back in. You can copy and paste your modified layouts into the new copy and everything will update itself. (FileMaker's pretty cool in that regard.)

               

              The match field in these starter solutions is set to a serial number. It'll update itself automatically as you create new records; just make sure you set the option to update when you import your records and every record will get a serial ID. Then you can create notes from the personnel table and they'll all be tied to the correct parent record.

               

              Clear as mud?

               

              Let me know if you still have trouble.

               

              Mike

              • 4. Re: Notes Fields Missing??
                biltmorefinancial

                Well, unfortunately I deleted the tables and the relationships from the "Relationships" Dialog box. I am afraid I am going to have to start from scratch

                 

                I have made NUMEROUS changes to the layout/templates. and I am afraid that a simple copy and paste isnt going to work. Is there anyway to save what I have done and just find the "Missing tables"

                • 5. Re: Notes Fields Missing??
                  Mike_Mitchell

                  Did you delete the tables from the Tables tab of the Manage Database dialog, or just from the Relationships tab? If it's the latter, it's still salvageable. Here's how:

                   

                  1) Click the little "Add Table" button in the lower left-hand corner of the Relationships tab:    dialog.png

                   

                  2) When prompted, add the appropriate table.

                   

                  The reason this works is because the little boxes on the Relationships Graph aren't "tables". They're "table occurrences". They represent the underlying tables. (Think of them kind of like aliases or shortcuts.) You can delete every TO on the graph and not touch any of the tables in the database. (Of course, you'll break lots of stuff in the process, but that's another discussion.)   

                   

                  If you did remove the table, then you have it a bit harder, but if you have FileMaker Advanced, you can still save it. This is because you can import a table from one database to another. Create a new database from the starter solution and import the table from there. It'll automatically be placed on the graph.

                   

                  If you don't have Advanced, well, then you'll have to start over.

                   

                  However, I'm not sure why a copy and paste won't work for you. Go into Layout Mode, press Ctrl (or Cmd) - A (to select all objects), and then Ctrl (or Cmd) - C to copy. Go into the new database, go to the layout you want, press Ctrl (Cmd) - A, and then Delete. Then press Ctrl (Cmd) - V (paste). All layout objects are pasted into the layout; all old ones are gone.

                   

                  Nuke 'em all and replace 'em.      

                   

                  But make sure you do your layout replacements after you update your table structure (and your Relationships Graph). This is because FileMaker uses the graph to determine where to put newly pasted objects. If the Graph isn't up to date - if FileMaker can't find a match - then the paste operation will leave you with a mess.

                   

                  Let me know what further difficulties you have.

                   

                  Mike