7 Replies Latest reply on Jul 25, 2013 11:22 AM by philmodjunk

    find on one related record but replace field in another

    tmlutas

      Title

      find on one related record but replace field in another

      Post

           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?

        • 1. Re: find on one related record but replace field in another
          philmodjunk

               It's hard to answer this when you describe your current method only in the most general terms. The details matter. Performing a script in FileMaker should not, under any circumstances result in a crash. A crash may indicate that your file is damaged and crashes can damage your file. Any file that crashes should then be run through a Recover action for both of those reasons.

               And if you takea brand new test file, recreate the circumstances of your crash and get a crash, this should be reported as a software bug in Report an Issue to bring it to the attention of the TS personnel that work for FileMaker inc.

               That said, the ability for Replace FIeld contents to do a batch update of a specified field for all records in your found set does make it a very good option for what you are trying to do. Using a different record for each part of an address, however, seems needlessly complex. Instead of 8 records, 2 records of 4 fields each would seem a better option--but if it is set up this my in MySQL, you may not have the option to change it.

               Speaking strictly from the context of FileMaker Pro, You can perform a find on your address table to specify criteria to find PO addresses. Just from what you describe here, It seems that the update you want is in the same table so I don't see where the presence of data in related tables is a factor. However, if the address records link to other data besides governments, you can specify additional criteria--even criteria in another related table to filter down your set of addresses to get the needed found set for an update.

          • 2. Re: find on one related record but replace field in another
            tmlutas

                  

                 It is in the same table but not in the same record and no, putting it in the same record is what I'm trying to get away from. Your suggested approach has already been tried and is being discarded because of scalability problems. I actually think that I would end up really needing to know what "256 million total fields over life time of file" means in the filemaker tech specs. 

                 If you add a field every time you add a characteristic, things get very hairy. For example, there are 28 side judges in the entire USA, 2 per 14 counties in Vermont. It is a unique position as they judge and the senior side judge is also the county executive. Adding relevant side judge fields to the government record for all 89k governments doesn't work. In a 50 state federation with dual sovereignty and tens of thousands of governments, there is simply too much variation for this approach to work so I'm trying to go the other way, generic characteristics with a characteristic look up table and data. What ties all the characteristics together is the CensusID which all governments are guaranteed to have and which I can fudge in between census counts and make it right come next census when I get a correct listing of the new entities (or maybe they assign in between the counts, I haven't actually checked). 

                  

            • 3. Re: find on one related record but replace field in another
              philmodjunk
                   

                        Your suggested approach has already been tried and is being discarded because of scalability problems. I actually think that I would end up really needing to know what "256 million total fields over life time of file" means in the filemaker tech specs.

              I think you misunderstand. You would not have "256 million fields in the table", only just those needed for an address. That generally can be done with less than 6 fields total per record. Don't see how that would generate more fields than that here. I am NOT suggesting that you put the address fields in the same table as your government table. The total fields in yoru table of adresses would not except more than about 12 even with primary and foreign key fields added to implement relational links to other tables.

                   There are two extremes here that I recommend that you avoid:

                   "Flat file design" where you have one table and fields for everthing. That indeed can result in scalability issues.

                   But, except for very specialized purposes, an "atomized" data structure, with just one data field plus key fields in every record can also be very difficult to work with.

                   Good relational design usually strikes a middle ground approach where each record represents a relatively small number of characteristics all for the same basic type of entity. Thus, an address field might have 1 or 2 street name/number fields, a field for the city, one for the state or provence, one for a postal code and possibly one for the country if international addresses are possible. That's a far cry from "256 million fields in one table" even if you then add in several key fields for relationship purposes.

                   But whether or not you stick with an "atomized" design, the rest of my post still applies as a method to achieve the results that you want with your batch update.

              • 4. Re: find on one related record but replace field in another
                tmlutas

                     I think that we're going down a rabbit hole here. I have a problem. I already figured out a solution. The solution is slow and inelegant and thus unsatisfactory. 

                     I think I see a way to get a better solution. The better solution has a bug. I'm interested in solving that bug, not the inelegance of the other solution or reworking my data tables. Those are a priori off the table. I only mentioned the other solution so that if I was unclear, I could publish that to clarify what I'm trying to accomplish.  

                     Again, the bug is in finding the correct set of characteristics because I need to search on one and return another. I've been working on this and I think I've got it. 

                     1. Layout gets records from Governments table

                     2. Portal on layout gets related government characteristics, filtered to only show a single field among those that I want to replace (2, 3, 4, or 5) with new values (20, 21, 22, 23)

                     3. set up a find with search criteria in the portal with 2 as the characteristicID and PO Box as the CharDate. A second find criteria omits 20

                     4. The results should display main records that have 2 and PO Box related records with the targeted related records of (2, 3, 4, or 5) in the portal depending on how I filtered it. 

                     5. I change the 2, 3, 4, or 5 to the target value of 20, 21, 22, or 23 using replace field

                     I'm actually running the test search now as I type and...

                     ... Coming back, it works

                     I ended up making 5 portals, 1 with filters showing each of the source values and the 5th portal showing all of the target values for the main record, each value strung together using or statements

                     The only remaining question is whether there is an even more elegant solution to the problem while keeping the business rule constraints? 

                • 5. Re: find on one related record but replace field in another
                  philmodjunk
                       

                            Portal on layout gets related government characteristics, filtered to only show a single field record among those that I want to replace (2, 3, 4, or 5) with new values (20, 21, 22, 23)

                       And as I mentioned before, whether you follow my advice on the structure of your tables (good luck with it, I predict a lot of complications), or not my suggested approach still works.

                       The key difference is that I would not perform the find on the governments table. I'd perform it on a layout based on the portal's table to get the needed found set of records that need updating.

                  • 6. Re: find on one related record but replace field in another
                    tmlutas

                         I couldn't figure out how to get that done. Let's do a simplified hypothetical

                         You hav a table with govID, CharID and CharData

                         You are looking for a particular CharID/CharData combination, CharID = 2, CharData= Box. Given the found set, you'd like to change records with the identical govIDs of the found set but whose CharID = 3. I think you've just represented that this is possible. What does the search look like to find the CharID = 3 records without using a portal? 

                    • 7. Re: find on one related record but replace field in another
                      philmodjunk

                           This would require a relationship:

                           TableOccurrence1::govID = TableOccurrence2::govID

                           This can be a link between two occurrences of the same table or occurrences of different tables.

                           Go to Layout ["TableOccurrence1" (YourTable)]
                           Enter FInd Mode [] ---> clear the pause check box
                           Set FIeld [TableOccurrence1::CharData ; "Box" ]
                           Set FIeld [TableOccurrence1::CharID ; 2 ]
                           Set Error Capture [on]
                           Perform Find []
                           IF [ Get ( FoundCount ) ]
                               Go To Related Record
                                       [Show only related records; Match Found Set ; From table: TableOccurrence2; Using layout: "SomeLayoutName" (TableOccurrence2) ]
                               Enter Find Mode []
                               Set Field [TableOccurrence2::CharID ; 3 ]
                               Set Error Capture [on]
                               Constrain Found Set []
                           Else
                               Show Custom Dialog ["No Records Found"]
                           End If
                            

                           Note that if TableOccurrence2 refers to the same data source table as TableOccurrence1, you do not need to change layouts in the Go To Related Records step and the set field step that follows would then refer to TableOccurrence1 instead of TableOccurrence2.