11 Replies Latest reply on Nov 15, 2013 12:24 PM by strngr12

    Related field is hit and miss

    strngr12

      I have two tables in a database. They are related by a single relationship, table1::setup = table2:setup.

       

      I have a calculation in the first table, description = Case ( camera = "A" ; table2::descriptionAcamera ; camera = "B" ; table2::descriptionBcamera ).

       

      The problem is that calculation sometimes works and sometimes doesn't and I can't figure why it's hit and miss. For some records, when the camera in table 1 is "A," I get the description in that record. Sometimes I do not.

       

      The descriptions in table 2 for both cameraAdescription and cameraBdescription are never blank when they shouldn't be. I have checked for any invisible characters that might be throwing it. I have changed it so that description = descriptionBcamera and I get all the descriptions populated even in the A camera records as would be expected with that calculation. Then I changed it to description = descriptionAcamera and got no descriptions on any of the fileds that matched the setup.

       

      However, with fields that have a different setup value, the description = Case ( camera = "A" ; table2::descriptionAcamera ; camera = "B" ; table2::descriptionBcamera ) works fine.

       

      I know this sounds complicated so I'll try to break it down.

       

      table1::setup = table2:setup

      description = Case ( camera = "A" ; table2::descriptionAcamera ; camera = "B" ; table2::descriptionBcamera )

       

      10 fields in table 1 have a setup value of 110. Every field has either a B or an A value for camera, never both. If the value is B, it's get a description from the B camera field of table 2. If the camera is A, it does not get a value for the description. Table 2 does have a description in its A camera field.

       

      10 fields in table 1 have a setup 122. Same as above, evey field has either an A or B camera value, never both. If the record has an A camera value it gets its description. If the camera value is B it does not. Table 2 does have a description in it's B camera field.

       

      10 fields in table 1 have a setup 63. Same as above, evey field has either an A or B camera value, never both. The calculation above works fine for all records whether A or B camera.

       

      I'm at a complete loss. Any help would be greatly appreciated.

       

      Thanks,

       

      Dan

        • 1. Re: Related field is hit and miss
          strngr12

          I've gone a long way towards tracking his down but still can't solve it  I'm pretty sure it's because I imported this from Excel and Excel sucks.  Can anyone tell me why:

           

          LeftWords ( field ; 1 ) Can come up blank when the field obviously has text in it?  I'm suspecting the fact that this is imported from Excel.

           

          I also Tried:

           

          Left ( field ; 4 ) and came up with the value "CAM" when the first four letters in the field were actually "ACAM."

           

          Lastly I did:

           

          Position ( field ; "ACAM" ; 1 ; 1 ) and came up with a 0 even though the first four letters are "ACAM."

           

          Anyone have any ideas?

          • 2. Re: Related field is hit and miss
            Stephen Huston

            Are the key fields on both ends of the relationship of the same data type (text, number, date), and are they both full indexed. (You cannot use unstored calcs for two-way relationships.) Key fields need to be indexed, and they may not be if created via imports.

            • 3. Re: Related field is hit and miss
              strngr12

              Hello Stephen,

               

              Yes, the fields I'm using to define the relationship on both sides are text string claculations.  In any case, if the relation ship was the problem, wouldn't it always fail?  Also, the relationship dosn't explain this:

               

              LeftWords ( field ; 1 ) Can come up blank when the field obviously has text in it?  I'm suspecting the fact that this is imported from Excel.

               

              I also Tried:

               

              Left ( field ; 4 ) and came up with the value "CAM" when the first four letters in the field were actually "ACAM."

               

              Lastly I did:

               

              Position ( field ; "ACAM" ; 1 ; 1 ) and came up with a 0 even though the first four letters are "ACAM."

               

              I should mention that these calculation fields are in table 1 performing on fields in table 2 and that the same calculations in table 2 on the same fields in table 2 come up with the expected results.  In other words:

               

              Performed in table 1:

               

              Left ( table2::field ; 4 ) and came up with the value "CAM"

               

              Performed in table 2:

               

              Left ( field ; 4 ) Comes up with "ACAM."

              • 4. Re: Related field is hit and miss
                Stephen Huston

                Calculations which reference related fields cannot be indexed to use as keys themselves.

                • 5. Re: Related field is hit and miss
                  strngr12

                  Sorry, I'm not clear on what you mean.  I'll try to be more clear about the relationships.

                   

                  I have a setup field in both table 1 and table 2.  They are both calculations that result in text and are basically scene numbers from a movie shoot.

                   

                  What I am trying to do is get a script supervisor's shot descriptions into table 1 by have a relationship table2::setup = table1::setup.

                   

                  Based on this relationship I then have a calculation in field in table 1 descriptionAcamera = table2::descriptionAcamera.

                   

                  This works on the vast majority of the records.  A description is populated to most of the records that have an A camera.

                   

                  I also have a descriptionBcamera, but for simplicity, let's stick with A camera.

                   

                  My problem is that sometimes it doesn't populate.  Right now I have 262 records in table 1 and eyeballing it I'd say about 200 of them have the description from table 2 as per the relationship.

                   

                  But the ones that don't are a mystery to me.  The calculations in my previosu 2 posts are also a mystery to me.  Why would Left ( table2::descriptionAcamera ; 4 ) = "CAM" when performed in table 1 but Left ( descriptionAcamera ; 4 ) = "ACAM" when perfomred in table 2?  Also, this discrepency only happens on the records that do not get the descriptions populated.  On the records that do get populated with descriptions Left (table2::descriptionsAcamera ; 4 ) = "ACAM" and Left ( descriptionAcamera ; 4 ) = "ACAM."

                   

                  Also, since the first four letters of table2::descriptionAcamera = "ACAM" - at least visually - why would Position ( table2::descriptionAcamera ; "ACAM" ; 1 ; 1 ) show up as 0?  Keep in mind, that on the vast majority of records it shows up as 1.  It's just in a few that it shows up as 0 and the text is correct.  The first four in the field that returns a 0 result for this position calculation are "ACAM."

                  • 6. Re: Related field is hit and miss
                    keywords

                    It seems to me from what you say that you are using a data field as your key field. That is bad practice. Try setting up a simple unique identifier field to use for your matching and sees if that solves the problem. You should have a unique key field on each record in each table. You then also replicate the table1_ID field in table2 (but not with any autoenter), and use those two fields for your match.

                    • 7. Re: Related field is hit and miss
                      strngr12

                      Unfortunately, the only way I can relate the two databases together are by calculations short of actualy typing in inique identifiers into each table which would completely defeat the purpose.  I could just type the descriptions if I was going to do that - or, more importantly, the editorial team taht is going to use this can just type the descriptions into the viewer they will use to watch dailies.  I'm tyring to help them not have to do that.

                       

                      Also, I've done these same kind of matches on well over 100 shows now and I've never had this happen.

                       

                      Ultimately, here's what just doesn't make sense.

                       

                      table1::scene = table2::scene

                      table1::description = case ( camera = "A" ; table2::descriptionAcamera ; camera = "B" ; table2::descriptionBcamera )

                       

                      There is always a description for both.  For most of the records in table 1, the description gets populated.  For some records, it's only one or the other.  This can't be a problem with the relationship.  If it was a relationship problem it would either never work or would only work for one or the other.  If it was a calculation problem the either Acam woudl always not work or Bcam would always not work.  Both work most of the time.  Both faile sometimes even when the other works.

                      • 8. Re: Related field is hit and miss
                        alquimby

                        Dan,

                         

                             Have you made sure that both your match fields have been stripped of any trailing or leading spaces, carriage returns, etc? "A" in one table will not equal "A[space]" in the other table. This could be the reason for the "hit and miss."

                         

                             It is seldom a good idea to have key match fields that are based on user input, rather than FileMaker input.

                         

                        Al Quimby

                        • 9. Re: Related field is hit and miss
                          keywords

                          Dan,

                           

                          Do take note of the point made by Al:  "It is seldom a good idea to have key match fields that are based on user input, rather than FileMaker input."

                           

                          It is reiterating the point I made earlier. His other point about rogue spaces and carriage returns is also well made.

                           

                          Referring to your reply to my previous post, I can understand your dilemma about adding unique ID fields to existing records, but it can be done, as follows:

                           

                          1.     Create the uniqueID field in each table. Call the, say, IDtable1 and IDtable2. Make them number fields and set them to autoenter serial number. At this stage there will be no value in any existing records, but you will fix that in a moment.

                          2.     Also create an IDtable1 field in table2, to use once this process is complete as you ongoing match field, but leave this as a simple number field (ie. no autoenter).

                          3.     In table1, find all records. Click in the newly created ID field and use Records > Replace Field Contents to insert a serial number in each existing record; make sure you also check update serial number in Entry Options, so that the next record you create will be given the next serial number. Repeat this process for table2.

                          4.     Now the fun bit. Using the existing relationship, in table2, click in the IDtable1 field, choose Replace Field Contents again, but this time use the relationship to insert the value in the table1::IDtable1 field. This will now give you a nuber in this field which, where there is a valid relationship, matches the value in table1.

                          5.     Because of the issues you have been having you may have some records in table2 which are still empty; this will be because the the existing relationship is broken for some reason. There shouldn't be many, and the simplest way is probably to resolve these manually.

                          6.     Now you can repoint the relationship to use table1::IDtable1 = table2::IDtable2. You should also set the option to allow creation of records using this relationship.

                           

                          You will now have a more robust and reliable relationship which will work for existing records as well as new ones you create.

                          • 10. Re: Related field is hit and miss
                            Stephen Huston

                            If you are using calcs for the relational key values, verify in the define calculation: options dialog area that field index is set to stored. Indexing is essential for reliable keys. Sometimes unstored keys work, but sometimes is exactly what you are trying to avoid.

                             

                            [The points about stripping out leading and trailing characters of also quite valid, along with the problems of relying on user-entered  data for keys.]

                             

                            If you can post your problem file(s), we would have a better chance of finding the real culprit instead of trying to list all the possble causes.

                            • 11. Re: Related field is hit and miss
                              strngr12

                              So for posterity I will post the fix here.  I still have no idea what went wrong, but when I asked the script supervisor to output her notes as a .csv and imported that instead, it worked.