AnsweredAssumed Answered

find on one related record but replace field in another

Question asked by tmlutas on Jul 21, 2013
Latest reply on Jul 25, 2013 by philmodjunk


find on one related record but replace field in another


     I have two MySQL tables with a filemaker front end, governments and government characteristics (there are a couple of other tables that are not relevant for this issue). Governments consists of two fields (censusID and name), government characteristics has three (censusID, characteristicID, CharData). One of the things I am tracking is the address of the government. An address is stored as 4 records in the characteristics database, street address, city, state, and zip code, each of which have a different characteristicID code. There are two types of addresses, mailing and physica so there are a total of 8 address characteristic IDs. I am trying to find governments whose census records show a physical address, but are really a mailing address. This is discovered by searching for variants of the PO Box string in CharData. Then I want to flip over the relevant characteristicIDs to their mailing address equivalents. I have a filemaker script that can do it. It is very slow, about 9 seconds per record. It also semi-regularly crashes Filemaker 12v4 just like it did v3. 

     I would like to do an alternate where I can somehow get the right found sets (separately zip code, state, city, and street address of governments whose street addresses are PO Boxes) and do a replace field contents. I believe that this will take less time to run through. Am I right on the speed and what's the proper way to do such a search?