1 Reply Latest reply on Feb 19, 2013 2:13 PM by keywords

    Match fields not connecting certain records


      Well, I am stumped, and I am not sure how to describe this in order to ask for help. Since I have no idea where the problem lies, I feel I have no choice but to give as much background as I can, although a lot of it has nothing to do with the problem, at least not as far as I can tell.





      My solution tracks multiple kinds of data under a comon record and also allows back tracking from the multiple data types back to the master record through a series of scripts that open layouts and load the related record. This recquires that in addition to viewing lists of the master records, users can also view seperate lists of the seperate data types. Think of it as customer accounts, products, and invoices. Data can be viewed by 'Customer account', but customer accounts can also be found by viewing a list of invoices and then selecting the Customer account related to that invoice. That is the quickest way to explain the relationship without boring you with the exact details of our use case (We dont actually have customers or invoices). My solution also filters and sorts the data by those records assigned to the logged in user by default, and the filter is dynamically adjustable for users with administrative privilages so they can see records assigned to others. This is done through a combination of table occurances, portals, filtered portals, and layout duplicates that point to different occurences of the table data that vary depending on where you are at in the system. The solution is also split between two files. One is for the interface and contains no data. The other is a data file. This alows us to work on an offline interface file to add new features, and update all at once by replacing just the interface file without having to duplicate or import the massive amount of data in the data file. Scripts allow users with admin privilages to create new users and create login aucounts in both files automatically. The goal of the system is to provide a native application type experience. 100% of the navigation is contained within the layouts and all of the toolbars from Filemaker are turned off and users do not have access to the table layouts in the data file, only forms in the interface file. That is why there are duplicate layouts that point to different table occurences, otherwise the data wont display depending on which record you are on from which occurance at the time the related record is needed.



      Now for the problem...


      I have one of these master records that will not connect to its related record of one specific data type. all of the other data types connected to this record work fine. Also, all of the other master records connect to this data type just fine. The problem is only between these two specific records. Also, this problem record was connected to this related data just fine up until a few days ago. Now, when attempting to view the related data from within the master records table occurance, the fields are blank. There is a secondary peice of data in a third table that uses the relationship between the master record and the first data type as a filter. That distantly related data displays correctly when viewed from within the master record's table occurance. When the script is run to view the related data type in it's own table occurance in a new window, all of the fields have a question mark in them instead of the expected data, Even the fiels that were blank in the master records table occurance display as question marks in that records own table occurnace and the sub related data, now only one table occurance away does not display at all. This tells me that the relationship being viewed from the master record was showing a different record from the one we are taked two when the view related record script is run.



      What I have learned and tried so far...


      I opened up the data file and inspected the records involved. There are three match fields involved so that we can create records via this relationship without breaking the matchfields that are needed elsewhere in the system used for viewing all records by data type filtering by user. All of the matchfields for the master record looked normal. One of the match fields for the related data type was missing, the forign key for the master record. I have no idea how that is possible because users can't even view that field much less change it. I also found three other records in this table that showed question marks in all of their fields. I deleted the records with the question marks, and manually entered the forign key for the master record in the targed related record. Then I tried viewing the data again in the form layout. when I did, I saw no change. I again tried to use the script on the layout to navigate to the related record in its own table occurance. Again, no change from before. Then I opened the data file and viewed the records involved again. The master record is still fine. The related data type, still shows the corrected forign key, however, the record with the question marks has returned. Now, normally I would think that there is something wrong with the settings in the relationship. However, this problem seems to be limited to this specific set of related records. it still seems to be working fine when viewed from any other master record. I tried removing the record with the question marks again, closing the files on the server and re-opening them. Still no change.



      Please help...


      Now what? The only thing I can think of to do is to delete the master record and create a new one. However, with dozens of other related records that would need to be re-connected in multiple other data types, this would be a huge undertaking to re-build this master record.

        • 1. Re: Match fields not connecting certain records

          Two suggestions for you to be going on with:


          1.   DON"T delete the master record! As you point out yourself, that will have several flow-on complications and, although it may make the original problem go away it will do nothing at all to address the question of why it arose in the first place.


          2.   If the match fields in the problem relationship are text fields, it is possible that the issue is a trailing carriage return or space in one or other of the match fields. If you have a pkField = fkField relationship and pkField contains "thisData" while the fkField contains "thisData¶" or "thisData " then you will not get an exact match. If you are experiencing the problem in many/all of the iterations of this relationship then it is likely the dodgy content is on the pkField side; if the problem is in only some iterations, then the dodgy data is more likely to be found on the fkField side of the records which don't connect as expected.


          The above may not be the issue, but hopefully it gives you a start on your detective work. If it does turn out to be the problem then you must address the questions of how the dodgy data got there, and how to stop it happening again. "Don't just fix the mistakes -- fix whatever permitted the mistake in the first place."  (from "They Write The Right Stuff" @