7 Replies Latest reply on Jul 26, 2012 10:29 AM by philmodjunk

    Finding TRULY modified records

    alc

      Title

      Finding TRULY modified records

      Post

      I need to find an EFFICIENT (fast/simple) way to isolate records that specific fields which have been modified since opening (In most cases a concantenated calc field could aid defining those fields) The catch is that this would be part one of a method to output these modified records from FMGo - FMPro, so I think looping scripts, could be prohibitive. Part 2 will involve how to best output the records in various scenarios (FMGo, FMNet, DB on Mobile Device), but I guess that handled in FMGo forum.

      I know this one should be a simple one, but all the methods I've used require too much overhead (too many fields/scripts/script triggers etc - ie too much room for error). Efficiency and simplicity has grown more important since I need to incorporate the slower/less competent FMGo (especially since scripts will also need to have several branching IFs that check for device, FMGo versions etc)

      Many thanks in advance for your advice.

      xandra

        • 1. Re: Finding TRULY modified records
          philmodjunk

          I think you should describe what you want to do in more detail--particularly by what you mean by "specific fields". A ModificationDate field can mark the date any field in the record was modified and a Modification TimeStamp field can mark the date and time.

          Depending on what you mean by "specific", it may be possible to put those fields in a related table linked via one to one relationship with the original table, then a Modification date or timestamp field in this new related table correctly flags when one of these "specific" fields have been modified.

          • 2. Re: Finding TRULY modified records
            alc

            Hi Phil:

            Was DEARLY hoping you'd pipe in.

            To be more specific. The fields in question, would only be USER modified fields (Text, numbers - dates etc. which have been manually entered by the user.) Since all my databases contain fields which change for assorted reasons (unrelated to actually modifying data), the standard record modifaction TS is inappropriate.

            To give you an idea of what I'm up to: here's a general picture of what I've done so far.. (I could upload tester if desired)

            My opening script enters an "Opening" timestamp in a Global field. lets call it [OPENING_TS]

            I create a concantenated field both  (AE or possibly scripted) [FULL_DATA] and Calc version  [FULL_DATA_calc] of all of the above. (I like this approach because it will make it easier to add additional fields if necessary with data size limits) *I'm currently not using see below.

            I then use script triggers on all desired fields which runs an onModifyField scriptTrigger that sets the current TS in another Timestamp field. let's call it [MOD_TS] .

            "Truly Modified" records  [OPENING_TS] are then identified by a Calc Field [TRULY_MOD] which gets set to 1 if [MOD_TS] I also have a scripted text field which is set to UserName/[MOD_TS] just to keep things kosher...

            Above SEEMS to work (my testing not significant) on a very simple DB. However, it's very dependant on scriptTriggers (which aren't as predictable in iOS) and which would be considerably more iffy in a real world circumstance when other triggers are desired...

            ------------

            MANY THANKs

            xandra

            ----------

            *RE [FULL_DATA] field method: I've used this in the past in FMPro,  the opening script generating the current field(fixed) to be compared with later with a calc field. This done through looping scripts which can be WAY too annoying on an iPod when you just want to get/create one record.  I've also considered just doing Script trigger which sets  [FULL_DATA] with an OnRecordLoad Script trigger which will get compared to [FULL_DATA_calc] and a 3rd boolean field to determine if different...  Thought you might have idea on how this could be used without looping.

             

            PS: Adding relations can really slow down things in iOS, simply sorting or finding a list using a related field can produce snores... 

            On a different note: Surely by now Filemaker should've given you an iPad/iPhone... for testing reasons - (heck I'd chip in) Is anyone listening?

            • 3. Re: Finding TRULY modified records
              philmodjunk

              Since all my databases contain fields which change for assorted reasons (unrelated to actually modifying data), the standard record modifaction TS is inappropriate.

              Which is why I suggested you split the table into two related tables--one for the user modifiable fields and a TS modification field and one for the rest of the fields. Then, when a script modifies one of these "other" fields, the TS Mod field does not update as those changes took place in a different table.

              The relationship would be one to one based on a serial number field in one of the two tables that serves as the parent and also as the table specified in Layout setup whild the other table becomes the "child" and is linked with the "allow creation..." relationship option so that you can put fields from both tables on the same layout and they function as though they are part of the same table.

              I don't know how much of a performance hit that will cost you in the iOS world, but I think that it'd be a lot faster than looping scripts and that careful use of finds, sorts and layout designs could minimize the impact.

              Surely by now Filemaker should've given you an iPad/iPhone... for testing reasons

              I appreciate the thought. I've lusted after them for some time, though just paying the data plan would be a challenge given my current circumstances.

              • 4. Re: Finding TRULY modified records
                alc


                Which is why I suggested you split the table into two related tables--one for the user modifiable fields and a TS modification field and one for the rest of the fields

                Phil: thanks for the suggestion. I'll give this a try on the current effort (a password DB).It has an intentionally basic structure (2 tables, one self relationship), limited fields, scripts etc. since it was designed with iOS in mind.

                To be honest, I don't have the mental accuity to do this for one's that have already have Relationship graphs which look like government flow charts on acid. The more I mess with DBs for use on iOS, the more I'm inclined to develope simple, flat (or nearly flat) files. 

                Thanks again -It's the scripting and layouts that I need to wrap my head around. I'll report back once I've got it working.

                just paying the data plan would be a challenge given my current circumstances.

                A Couple things: unless something has changed (and who knows, it may have), you don't need to commit to a cell-phone-like plans iPads. My iPad's on month to month basis - can stop/start at any time, no penalties and options that are WAY cheaper.Of course, you can save yourself some $$ by skipping the cellular, but once you've used the giant iPad screen as a GPS substitute, you'll never regret the extra $100.

                Also wasn't kidding: it would behoove all of us, if FM gave some of the more sophisticated, helpful, developers iOS devices.

                • 5. Re: Finding TRULY modified records
                  philmodjunk

                  ... that have already have Relationship graphs which look like government flow charts on acid.

                  You might find this technique for organizing table occurrences (the boxes on you relationship graph) to make them easier to work with: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                  • 6. Re: Finding TRULY modified records
                    alc

                    Thanks for the great link - i've seen this in other folks dbs, but never tried it (for fear of accidentally duplicating stuff) - as you can guess, I began with FMP before the graph (in fact Before relational dbs) so it's hard to teach this old dog new tricks.

                    Thanks - xandra

                    • 7. Re: Finding TRULY modified records
                      philmodjunk

                      Yeah, I started with flat file Pro 2.5 and daily work with some DB's that look like a spider had an LSD party. I've been slowly, carefully, introducing the Anchor Buoy format a bit at a time.