14 Replies Latest reply on Apr 1, 2011 5:25 PM by brian.curran

    How do I trace an error back?

    brian.curran

      Title

      How do I trace an error back?

      Post

      Hi,
      How do I trace an error back to the source to see what is causing it? I'm getting the following:

      Error

      I can click OK to get rid of it but then a new entry is created in the 'Site' table. I've looked in the relationships to see if another table has the right to create a new record but I can't see anything obvious.

      Any suggestions?

      Thanks
      Brian. 

        • 1. Re: How do I trace an error back?
          philmodjunk

          The error message you've posted is triggered by a validation error. _kp_site_id is a field with a required value validation rule set for it. The specific wording of this message indicates that this field is not present on the current layout.

          What ever action triggered this message is either creating a new record with this field empty or it is clearing this field. Then, when the record is committed, the validation error occurrs.

          _kp_ is a naming convention used to identify a field used as the primary key in a field. It should never be blank and is almost always defined as an auto-entered serial number field as this is the safest way to assign values to a primary key field.

          • 2. Re: How do I trace an error back?
            brian.curran

            Hi Phil,
            The "Keys" layout I was working on has a KF field with an auto-serial number, could this be causing the problem? I seem to remember you saying in another post that KF fields should never have an auto-serial number, as it inherits data from the KP...

            Sites::_kp_site_id = Keys::_kf_site_id (this relationship allows Sites to create a record in Keys)

            • 3. Re: How do I trace an error back?
              philmodjunk

              Yes, but the value won't be inherited automatically in all cases. If you are entering data into a portal with "allow creation of records..." enabled for it's relationship, then yes. If this is not what you are doing, then maybe not.

              The error is specifically referencing a field whose naming convention identifies it as a primary key. You need to figure out why that field is empty at the time this error appears.

              What are you doing just before you get the error?

              What table occurrence is listed in the Show Records From drop down of Layout Setup...?

              • 4. Re: How do I trace an error back?
                brian.curran

                Ok, I created a new record in the 'Sites' layout and each time I entered data into a field I clicked outside of the layout to commit the record. All was fine until I selected a value in a drop down box called 'Lead Source' and then the error reappeared...

                'Sites' is connected to lots of other layouts, one of which is called 'Leads'. The relationship is:
                Sites::_kp_site_id = Leads::_kf_site_id (Sites can create new records in Leads) 

                Sites::_kp_site_id is Serial number, Prohibit modification, Not empty, Unique value
                Leads::_kf_site_id is also Serial number, Prohibit modification, Not empty, Unique value 

                After clicking OK to the error, I checked the 'Leads' table and all looked fine with the new entry that Sites automatically created but when I checked the 'Sites' table, I found there were two entries, one looked fine but the other was blank! The _kp_site_id number was the same for both records even though that field is supposed to be unique.

                The TO in the Show Records box on Layout Setup is 'Addresses' which starts to mix things up quite a bit...

                • 5. Re: How do I trace an error back?
                  philmodjunk

                  Leads::_kf_site_id is also Serial number, Prohibit modification, Not empty, Unique value 

                  Definitely should not be a serial number field here, but not the cause of the validation error.

                  The TO in the Show Records box on Layout Setup is 'Addresses' which starts to mix things up quite a bit...

                  That makes no sense. If you are creating a new site record you should either be on a layout that refers to site, or you should be entering the data into a portal to sites. (Unless Addresses and Sites are table occurrences of the same data source table.)

                  • 6. Re: How do I trace an error back?
                    brian.curran

                    Does this make things any clearer? Somehow I doubt it...

                    Relationships

                    I'm nervous about making any changes now in case I lose any data but I'm guessing I've got something majorly wrong somewhere...

                    [Groups] --->[Clients] --->[Addresses] ?---?[Sites] 1---1 [Leads]

                    • 7. Re: How do I trace an error back?
                      philmodjunk

                      The problem with screen shots of Manage | Database | Relationships is that they don't tell you what data source tables are being referred to by the table occurrence boxes in the screen capture.

                      As I said before, if you are creating a new site record, it should be done on a layout that refers to sites or in a portal that refers to sites. How is your layout set up here?

                      I can't tell for sure from your screen shot which of these, if any refer to Addresses and which to sites. I can guess that your layout might be refer to _003_Addresses and you might have a portal to _004_site_details and that you are creating a new record by entering data in a blank portal row, but that's guesswork on my part.

                      • 8. Re: How do I trace an error back?
                        brian.curran

                        The layout (called 'zDashboard') is displaying records from '_003_Addresses' and the first few fields I complete are storing data in that table. However, I also have some other fields on that layout that are linked to '_0001_Leads'

                        Hang on, I think it's beginning to sink in...

                        Is entering a value in the 'Leads' field causing the 'Sites' table to demand an entry for its KP field? Maybe I'm jumping the queue by not starting off in the 'Sites' table...

                        • 9. Re: How do I trace an error back?
                          philmodjunk

                          You are close to figuring it out.

                          If you have fields from _001_Leades placed directly on your _003_Addresses layout, you will not be able to enter data into those fields unless a related record exists both in Leads and also one in sites that links both to the current record in addresses and also in leads. That won't happen unless "allow creation of records..." is enabled from addresses to sites and then from sites to leads.

                          However, this relationship cannot work correctly:

                          _003_Addresses::_kp_Address_id = _004_Site_Details::_kp_Site_id

                          You should have:

                          _003_Addresses::_kp_Address_id = _004_Site_Details::_kf_Address_id

                          Where _004_Site_Details::_kf_Address_id is a plain number field.

                          You need _004_Site_Details::_kp_Site_id and it should be an auto-entered serial number field, but it can't be part of this relationship.

                          • 10. Re: How do I trace an error back?
                            brian.curran

                            Thanks Phil, I've added the KF field to 'Sites' and amended the relationship accordingly. However, data from other tables has disappeared off the Dashboard as the original link(s) have been broken. (I made a back up file earlier)

                            Whilst doing the above, I also unticked the Serial number in a couple of KF fields, Leads, Accounts, Version etc. although I don't think this has made any difference...

                            • 11. Re: How do I trace an error back?
                              brian.curran

                              Think I've got it...

                              I've deleted the original link between 'Sites' and 'Leads'. I've changed the name of the KF field in 'Leads' to _kf_address_id and created a new relationship:

                              Addresses::_kp_address_id = Leads::_kf_address_id (also ticked the create records in Leads and made the KF field a normal number field)

                              It appears to work, only 12 more to fix! ;)

                              • 12. Re: How do I trace an error back?
                                philmodjunk

                                You do have a job here as you have confused primary and foreign keys in a way that means you may have many records with the wrong value in either a primary or foreign key field.

                                A primary key should uniquely identify each record in the parent table. In FileMaker that's almost always an auto-entered serial number. A foreign key field is the matching field defined in the child table it should be a simple data field that receives a value from it's parent record.

                                You may need to keep your original relationships, then use the button with two green plus signs in Manage | Database | Relationships to make new table occurrences with the new relationships. Then, on a record by record basis, you may need to assign values to the new foreign key fields until all the new relationships work before discarding the old relationships. This requires two sets of layouts, one for the old relationships and one for the new in many cases.

                                You might want to read this thread on table occurrences before you start such a task: 

                                Tutorial: What are Table Occurrences?

                                Given how "tangled" your database graph has become, you might find this article useful as it describes a way, (called Anchor Buoy), to organize your table occurrences to make them easier to work with:

                                http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                                • 13. Re: How do I trace an error back?
                                  brian.curran

                                  I may have been quite lucky :)
                                  11 of the relationships were simple to do, as I just followed the same process I used for 'Leads' as detailed above.

                                  Changing the relationship between 'Addresses' and 'Sites' proved a bit trickier because of links from 'Sites' to other tables but I managed it all reasonably well.

                                  I'll do some more testing tomorrow but it looks like all the data is where it should be and there are zero errors!

                                  Thanks again Phil, your help on here really is greatly appreciated...

                                  • 14. Re: How do I trace an error back?
                                    brian.curran

                                    I'll also take a more detailed look at the 'Anchor Buoy' link too, thanks again...