5 Replies Latest reply on Jan 29, 2010 12:42 PM by ninja

    Comparing Index Values of Two Tables

    CountryBoy1

      Title

      Comparing Index Values of Two Tables

      Post

        If two tables are related by their Index field, is there an
      efficient way to make sure that the Index values of the two
      tables are still identical (and therefore in the same order) ?
      For instance, one table may been sorted or had records deleted

      and had new records added.

       

        Thanks for your help.  CountryBoy1

       


        • 1. Re: Comparing Index Values of Two Tables
          ninja
            

          Howdy CountryBoy1,

           

          I do this on a regular basis with Dbases that weren't built with good orphan control.  (While I try to get permission to put in more control).


          CountryBoy1 wrote:

            If two tables are related by their Index field, is there an
          efficient way to make sure that the Index values of the two
          tables are still identical  I Assume this is a one to one relationship but I don't think it matters.

           

          Yes.  make a list layout based on Table1, put ID fileds on it from Table1 & Table2 .

          Sort the list by the Table2 ID field.  all of the blanks will rise to the top (ascending).  This means there is no related record in Table2 to this record in Table1. (Table1 record is a widow)

           

          Do the same in reverse to find your orphans from Table2. 

           

          You can also perform a find for blanks rather than sorting for them. 

           

          (and therefore in the same order) ?  Not sure where order comes into play here...
          For instance, one table may been sorted or had records deleted

          and had new records added.  Sorting the records in one table should not affect your relationship.  What are you trying to do here?

           

            Thanks for your help.  CountryBoy1

           


           

           


          • 2. Re: Comparing Index Values of Two Tables
            CountryBoy1
              

            Dear Ninja:

                     I will have to try your idea.

             Suppose I give the user the ability to sort all tables by a

             Date field in the main table.  It would be nice to leave the

             data in this state and reset the Index values sequentially

             in all the tables. To do this, I should first make sure that
             the Index values in all tables actually agree in value
             (and therefore, in their order also).  (I have software right

             now that can set the Index values sequentially in both

             tables, but I'd like to be able to verify that the records

             are aligned correctly by Index values before it is executed.)

             I should mention that I only look at my data in Table view.

                    Sincerely, CountryBoy1

             

             

             

             

             

             

            • 3. Re: Comparing Index Values of Two Tables
              ninja
                

              Howdy CountryBoy1,

               

              First a note about IDfields.  They should not be pertinent to the data you actually "use".  They are simply ways for the database to correctly and accurately link information from one table to "matching" data in one or more other tables.  If your ID field was "bippity bappity boo", it would be just fine...as long as only records with related data also had that in the ID field.

               

              That said:


              CountryBoy1 wrote:

               Suppose I give the user the ability to sort all tables by a

               Date field in the main table. 

              No problem, sounds like a good plan.  My users typically view transactions sorted by date.  The ID field is uninvolved in this and doesn't even need to show up on the layout.

               

              It would be nice to leave the

               data in this state and reset the Index values sequentially

               in all the tables.

              Why in the world would you need to change the index fields? Leave them alone.  You're asking for big trouble here.

               

              To do this, I should first make sure that
               the Index values in all tables actually agree in value
               (and therefore, in their order also).  (I have software right

               now that can set the Index values sequentially in both

               tables, but I'd like to be able to verify that the records

               are aligned correctly by Index values before it is executed.)

              To avoid this, simply do nothing. Is there something wrong with the ID fields currently?

               

               I should mention that I only look at my data in Table view.

              But you're the dbase designer...look at it in any way that helps you achieve what you are trying to do.  Let your users live in Table View. 

               


              You have re-ID-ing in mind for some reason...what is the reason for doing this potentially dangerous thing?  There ARE reasons why you might want to consider this...but they are very very few.  What is the end goal you are trying to achieve?


              • 4. Re: Comparing Index Values of Two Tables
                CountryBoy1
                  

                Dear Ninja:

                         I guess I would like to do it for preference.  If you want to resort everything

                 every time you bring your program up, then that's what you'll have to do.  To me,

                 that gets a little old.  There's other issues like deleting records.  Do you leave

                 gaps in the Index values ?

                          I've found there's more than one way that Index values can be reset.

                 Yes, you have to be careful.  Would appreciate more thoughts.  Thanks for

                 your help.  CountryBoy1

                 

                 

                 

                • 5. Re: Comparing Index Values of Two Tables
                  ninja
                    
                  CountryBoy1 wrote:

                           I guess I would like to do it for preference.

                  Understandable.  There have been a few threads previously asking much the same thing.  If I recall correctly, most ended up with a recommendation to have two ID's (keys).  One that does the work, and another that is NOT used for linking that you can have set to a preference.  "One for show, one for Go." 

                   

                  If you want to resort everything every time you bring your program up, then that's what you'll have to do.

                  Records come up in the order created unless the order is otherwise modified.  If you want records to come up in a particular order 'automatically', you can write a startup script for the Dbase, or use OnLayoutLoad triggers to sort to your preferred order when the layout opens.  (I might have the trigger title wrong...I'm not on FMP10)

                   

                    To me,

                   that gets a little old.  There's other issues like deleting records.  Do you leave

                   gaps in the Index values ?

                  Since the ID is a meaningless value...yes.  If there's a gap between two meaningless things, who cares?

                  If you want to reorder the "show" index field to remove gaps, and are OK with the fact that the showing ID# for a given record may change from time to time...go for it.  My point is simply that it shouldn't be the ID field you are using for your links.

                   

                  I actually use the existence of gaps in the serial sequence to rate the effectiveness of some operations.  If there are ID# gaps between each material request, I can get a good idea that the interface for making material requests may have some room for training or interface improvement. (Folks get confused and cancel the request to start over)

                   

                            I've found there's more than one way that Index values can be reset.

                   Yes, you have to be careful.

                  One of those ways is the Replace Field Contents script step through which you can enter serial numbers.  No external software needed.  This is a good tool if you realize that you should have had an ID field all along and just realized it now.  You can backfill ID#s into a new field...and agreed, you have to be careful. 

                   

                  Would appreciate more thoughts.  Thanks for

                   your help.  CountryBoy1