1 2 Previous Next 20 Replies Latest reply on Feb 16, 2017 4:39 PM by DavidJondreau

    Auto-enter lookup

    tommykeith

      FileMaker Client and Filemaker Advanced 15.0.3.305. on MacBookPro11,2 and Macmini6,2 running Mac OS 10.12.3

      FileMaker Server 15.0.3.308 on a MacPro5,1 running Mac OS 10.12.3 with Apple Server 5.2

       

      Problem

       

      Database1 has a field called HABCOMMTYPE which is an Indexed Text Field that is the key.  (This is a field that has 38,000+ entries that are not unique)

      Database2 has a field called HTCTCode which is an Indexed Text Field that is also a key.  (This is a field that has 522 Unique entries)

      Database1 is joined to Database2 via the key fields with neither database having the ability to create or delete data.

       

      Database1 has several fields that Auto-enter via a lookup when HABCOMMTYPE matches HTCTCode.  The problem is that sometimes the lookuped data does not get updated.  For example.  In Database2 we change the data in HTCTCode from ARTSVC/JUNSCO DT to ARTSVC/JUNSCO HT as well as changing data in other fields referenced by lookups.  Then we go back into Database1, find all occurrences in HABCOMMTYPE from ARTSVC/JUNSCO DT and try to change them to ARTSVC/JUNSCO HT.  The fields containing the lookups may or may not change the data.  We then go to another machine and for awhile we will be able to change the data and the lookups will work, then this machine will quit doing lookups.  Unable to find any rhyme or reason to this.

        • 1. Re: Auto-enter lookup
          DavidJondreau

          Are you using "lookups" or "auto enter calculations"? Instead of using the HTCTCode and HABCOMMTYPE as match fields, you should be using unique IDs that wouldn't change.

           

          Instead of lookups, you could use unstored calculation fields. Then the data would always be current.

          • 2. Re: Auto-enter lookup
            tommykeith

            David, HABCOMMTYPE and HTCode are both Text Fields that normally don't change.  I went back and checked the FileMaker definition for match fields and FileMaker even allows calculations to be used as match fields.

             

            The fields that are lookups are Text fields with auto-enter being set as a lookup.  HTCode data changes maybe every 5 years or so and HABCOMMTYPE only changes are normally to the new data entered for the year.  Once the data is in and cleaned, these fields do not change.

             

            This is a server based system and using calculations does slow the system down and this has only showed up in FMP client 15.  Once the data is in and cleaned, these fields do not change and our reports and searches are much faster.  We have two different databases that that have this type of lookup.  One has 36,000+ records and the other has 300,000+ records.  Both have several fields that are lookups and changing them to unstored calulations would be a troublesome and the search time on these would be unacceptably long (that is why we went with lookups to begin with). 

             

            Thanks for your response.

            • 3. Re: Auto-enter lookup
              philmodjunk

              For example.  In Database2 we change the data in HTCTCode from ARTSVC/JUNSCO DT to ARTSVC/JUNSCO HT

              That's a serious problem right there.

               

              As I understand it, you have this relationship:

              Database 2::HTCTCode = Database1::HABCOMMTYPE

               

              Then changing the value of HTCTCode will disconnect that record from records in Database2 and might even connect it to a different set of records in Database1.

               

              That seems like a very bad idea....

              • 4. Re: Auto-enter lookup
                tommykeith

                The relationship is Database1::HABCOMMTYPE = Database2::HTCTCode.  There is not a relationship between Database2 to Database1.

                 

                Again, this has worked forever, until we updated the entire system to FMP 15 client and server.

                • 5. Re: Auto-enter lookup
                  DavidJondreau

                  It sounds like you've got a lot going on here. You are upgrading to FileMaker 15. What version are you upgrading from? Lookups, separate files, and "one way relationships" sound like old old versions (pre-7). If it's older, have you studied this: http://help.filemaker.com/ci/fattach/get/9222/0/filename/techbrief_fm8_migrtn_found.pdf

                   

                  A couple things come to mind that could be going on without actually looking at the file:

                  1-Broken file references. Upgrading and moving files can break file references. If Database1 and Database2 are separate files (rather than separate tables in one file), you may need to recreate the references.

                  2-Empty values are a non-match. Is the only relationship criteria between HABCOMMTYPE and HTCTCode? I ask because in older version of FM, two fields would match if they were both empty. In current versions they don't match. If there's a second criteria and you're relying on a "blank match" that would cause the problem.

                   

                  In the end, this is tough to diagnose without getting hands on.

                   

                   

                  Notes

                  In modern Filemaker, relationships are bidirectional.  Database1::HABCOMMTYPE = Database2::HTCTCode is a relationship between two databases. There is a relationship between Database2 to Database1.

                   

                  Just because you can use a certain string of characters as a match field doesn't mean you should.

                   

                  If you ever change the data in DB2 and want it reflected in DB1, you'll need to trigger a change in all those fields. You can also search on related data directly without ever storing the information in DB1.

                  • 6. Re: Auto-enter lookup
                    tommykeith

                    <It sounds like you've got a lot going on here. You are upgrading to FileMaker 15. What version are you upgrading from? Lookups, separate files, and "one way relationships" sound like old old versions (pre-7). If it's older, have you studied this: http://help.filemaker.com/ci/fattach/get/9222/0/filename/techbrief_fm8_migrtn_found.pdf >

                     

                    We updated from FileMaker 14 when FileMaker 15 came out.  This solution started back in FileMaker 2 days.

                     

                    < A couple things come to mind that could be going on without actually looking at the file:

                    1-Broken file references. Upgrading and moving files can break file references. If Database1 and Database2 are separate files (rather than separate tables in one file), you may need to recreate the references.

                    2-Empty values are a non-match. Is the only relationship criteria between HABCOMMTYPE and HTCTCode? I ask because in older version of FM, two fields would match if they were both empty. In current versions they don't match. If there's a second criteria and you're relying on a "blank match" that would cause the problem.> 

                     

                    No broken file references.  As I stated earlier, only sometimes does the lookup not work.  It is inconsistent in FileMaker 15, but has worked properly since Lookup has been available.  The lookups are currently set to show "No Match" when the match fields are not the same.

                     

                    <In modern Filemaker, relationships are bidirectional.  Database1::HABCOMMTYPE = Database2::HTCTCode is a relationship between two databases. There is a relationship between Database2 to Database1.>

                     

                    I disagree.  If I define a relationship between Database1 and Database2, at least where they are different files, Database2 is not related to Database1.

                     

                    <Just because you can use a certain string of characters as a match field doesn't mean you should.>

                     

                    I don't understand what you are suggesting.  A number field should only be used?  In the FileMaker documentation, they use text fields as match fields.  Are you saying FileMaker was wrong?  I don't see the difference between 1 = 1 and abc = abc as far as match fields go.

                     

                    <If you ever change the data in DB2 and want it reflected in DB1, you'll need to trigger a change in all those fields.>

                     

                    Agreed and that is what we do, by doing a relookup as outlined in FileMaker Documentation.

                     

                    <You can also search on related data directly without ever storing the information in DB1.>

                     

                    Agreed as well.  It is a speed issue and has worked for a very long time.  We only use the lookups once a year in the fall (they may get relookedup many times in that period) and then is not used again until the next fall.

                     

                    • 7. Re: Auto-enter lookup
                      DavidJondreau

                      There's a limit to how much help a person can be without actually looking at the files. I've reached that limit.

                       

                      Best of luck.

                      David

                      • 8. Re: Auto-enter lookup
                        philmodjunk

                        The relationship is Database1::HABCOMMTYPE = Database2::HTCTCode.  There is not a relationship between Database2 to Database1.

                        Sorry, but you are mistaken. Since FileMaker 7, if you have a relationship from table A to Table B, you have also set up a relationship from Table B to Table A. Just like commutative properties in math, you can write the relationship in either order and it's still the same. In any case my comments about changes to either one of these two match fields disconnecting records is still accurate.

                        • 9. Re: Auto-enter lookup
                          tommykeith

                          When you are talking about 2 Tables in one File you are correct.  When you are talking about 2 separate Files, that is not correct.  Try this.  On File 1:Table 1 create a relationship to File 2:Table 1.  Now go to file 2 and try to get any field from File 1 show up on the layout.  It will not.  My data is in two different Files, not tables.  It would take me forever to move that second file into the first file.  Too many other files also take advantage of data in File 2.

                           

                          Sorry for any confusion I may have caused by refering to them as Database1 and Database2.

                          • 10. Re: Auto-enter lookup
                            Philip_Jaffe

                            While you are IN file 1 and file 1 has a relationship to file 2, if you go to a file 1 layout that is based on a file 2::table 1 TO, the relationship will, in fact, be bi-directional.  You will be able to see related data from file 1:table 1 from the context of file 2:table 1.

                            • 11. Re: Auto-enter lookup
                              tommykeith

                              But, when you are in file 2, you will NOT be able to see data, or fields, from file 1 if there is no relationship in file 2 pointing to file 1.  I think we are having a semantic argument here that is not related to my problem.  Lets try this:

                               

                              Launch FileMaker 14 client.  Go to file1 and show all records.  Go to my error field and find for No Matchs.  I have 642 No Matchs.  Click into HABCOMMTYPE and do a relookup and I will have 71 No Matchs which is what I expect.  Quit FileMaker 14 client.

                               

                              Relaunch FileMaker 15 client.  Go to file 1 and find for No Matchs.  I have 71 No Matchs which is what I expect.  Show all records, click into HABCOMMTYPE and do a relookup and I now have 642 No Matchs.

                               

                              I must now go back to FileMaker 14 client and redo a relookup to get back to the 71 No Matchs.  Is this a DBcache error or what?

                              • 12. Re: Auto-enter lookup
                                Philip_Jaffe

                                tommykeith wrote:

                                 

                                But, when you are in file 2, you will NOT be able to see data, or fields, from file 1 if there is no relationship in file 2 pointing to file 1.

                                Correct.

                                 

                                tommykeith wrote:

                                 

                                 

                                Launch FileMaker 14 client. Go to file1 and show all records. Go to my error field and find for No Matchs. I have 642 No Matchs. Click into HABCOMMTYPE and do a relookup and I will have 71 No Matchs which is what I expect. Quit FileMaker 14 client.

                                 

                                Relaunch FileMaker 15 client. Go to file 1 and find for No Matchs. I have 71 No Matchs which is what I expect. Show all records, click into HABCOMMTYPE and do a relookup and I now have 642 No Matchs.

                                Can you please describe exactly the field type/options for the two match fields and if possible, take a screen shot of the edit relationships dialog window in the relationships graph?

                                • 13. Re: Auto-enter lookup
                                  tommykeith

                                  In file 1 HABCOMMTYPE is a Text field that is indexed.  No other anything.

                                  In file 2 HTCTCode is a Text field that is indexed.  No other anything.

                                   

                                  And remember, with no changes works as it should in FileMaker 14 client, not in FileMaker 15 Client.

                                  • 14. Re: Auto-enter lookup
                                    Philip_Jaffe

                                    I believe, if I understand you correctly, that you are attempting to re-lookup field contents on the same field that is the lookup key in the relationship.  This is not an issue between 14 and 15 in my opinion.  Rather it is caused by replacing contents in the same field that is the key.

                                     

                                    I suspect that if you had started in 15, done your re-lookup, then opened in 14, you would get the same result.

                                     

                                    Or do I misunderstand?

                                    1 2 Previous Next