Help please. Referential Integrity Probs

Hi All,

I could use a little help.  Migrated to Win7 and upgraded to Pdox 11.  That did not work out well.  The remaining problem in my app is the relationship between a customer.db of 10,000 recs and the transaction.db of 160,000 recs.  Customer is the master - transaction is one of the child tables.  The setup is D:\tracker\data  where Tracker holds forms, queries, scripts, library .... data holds the data tables and associated files.

Somehow a corruption showed up one evening.  I wrote this app 11 years ago in Pdox 8 and Win XP (I think) and have never had a corrupted table.  Not working in this stuff much anymore, I forgot a golden rule and moved and renamed tables using Windows Explorer.  Result is the transaction.db has referential integrity to customer, but it is pointing to customer in the Tracker directory.  I have deleted the .val file and others, done a BDE rebuild etc, but when I try to add RI back into the transaction.db the dialog only list tables in Tracker directory, not in \data where the tables really reside.

I have read the threads on deleting associated files and doing the rebuilds but I can't even get to the real master table to link it.  I opened a 3 month old backup; queried current tables for the missing data in the appropriate tables, deleted the RI links, then added to the old.  Still gave me the RI to the wrong directory. BTY, without adding the customer.db to Tracker directory, I can't even open the app or bring the main form up in design mode.

Ideas and/or solutions are MUCH appreciated.  I've been messing with this for weeks and it is our everyday program that handles all of our customer recs, their kids, and financial transactions for the parents (people).  I have a lot of data to add somehow.

First thing to consider is that RI is maintains links in both the master table and the child table.  If you delete the val file for the child table the master table will not open due to a bad or missing val file.  Not the master table val file the child table val file.  Opening the master table will result in Paradox looking in the child table for a RI link to the master table.  The opposit is also true.  This means that when you moved the files (i am assuming that you moved them from the Tracker dir to the data dir) both tables maintained the RI link to the old file location.

Have you have deleted both the master and child val files at the same time and attempted to then open the master and child tables?  If you change the working directory to the data directory does the RI dialog then show the tables in the tracker directory?

I havent heard of the issue with the RI dialog looking in a different folder than the same folder as the table for which you are setting the RI.  This actually violates one of the constraints of Paradox RI.  This leads me to feel that the Paradox install may be at fault.

You said "BTY, without adding the customer.db to Tracker directory, I can't even open the app or bring the main form up in design mode."  Any error messages?  I suspect you may have received an error indicating that the Customer table was missing.  In this case you probably have a hard coded path in your main form.  You should be given the option to replace the customer table in the datamodel.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Hi Dan,

It really pays to ask for help.  Almost all of the items you suggested I have tried, then a few, but when you asked the following:

"If you change the working directory to the data directory does the RI dialog then show the tables in the tracker directory?"   It hit me like a hammer in the face.  I became angry with myself for overlooking this obvious issue since I never really need to change the Working Dirrectory during operations.  Naturally, I can now see all of the tables required to re-establish RI. 

I have gotten all of the data back together by taking out an old "clean" backup from several months ago, queried the bad six tables essential to operations and the RI issues and used the add utility to put them in the good tables.  I was liberal with dates on the queries and the RI key violated dupes out.

There are other issues; however, that may impact your readers as it did me.  I started with Win 7 on a new machine:   Win 7  :  Pdox Version 11.0.0.411  :  BDE 5.01.  I wrote the software originally in Pdox 8.  The thing is it was locking the program when entering the $ side to the transaction form located on a tab in the main form.  Of perhaps worse consequence was trying to change a customer name or telephone number; it just lost the child table data within the system.  Each time I clicked the trans tab, it put up one of my other messages but did not get back the links.  Then it would lock up.

Also, most of the reports would not print or immediately closed the  entire program on print.  When I took the entire application and loaded it on a working Win XP machine running Pdox 8 it performed as it always had.  I did recompile the forms from Pdox 8 to 11, and reloaded the originals on the XP today.

I am going to attach the code that messes up the RI link between customers and child tables on another post.

Thanks for your help Dan. - Bob

Dan,

I have lost a lot of my coding skills over the past 11 years, but here is what I had on the Change Value event of Customer Last name and the Phone number field which are key fields in the Customer table and is tied by RI to transaction.db, and child (literally children) tables.  The only thing I can figure is that Pdox 11 looks at this a lot differently than Pdox 8 did:

_________________________________________________________________________

Customer Last name field:

method changeValue(var eventInfo ValueEvent)
var
 oldval,newval,modval    string
 tc                                              Tcursor
endvar

    
modval=gcustRec.phone
newval=eventinfo.newvalue()
oldval=self.value
if not oldval.isblank() then
if msgQuestion("Last Name Confirmation","You changed "+oldval+" to "+newval+".\n"
+"Do you wish to make the change permanent?")="Yes" then
if not oldval.isblank() and not newval.isblank() then
    tc.open(":data:progression History.db")
    tc.edit()
    lop="Y"
    while lop="Y"
      if tc.locate("phone",phone,"lastname",oldval) then
          tc.lastname=newval
      else lop="N"
    endif
    endwhile
    tc.close()
endif
    unlockrecord()
    tc.attach(gcustrec)
    tc.locate("LastName", newval,"phone", modval)
    gcustrec.resync(tc)
    tc.close()
else
    disabledefault
    eventinfo.seterrorcode(cannotDepart)
    lastname.moveto()
    lastname=oldval
    endif    endif

endMethod
_________________________________________________________________________
Customer phone number field:

method changeValue(var eventInfo ValueEvent)
var
 oldval,newval,modval         string
 tc                                                   Tcursor
endvar
  
modval=gcustRec.LastName
newval=eventinfo.newvalue()
oldval=self.value

;check to see if this phone number already exist in file
tc.open(":data:Customer.db")
if tc.locate("Phone",newval) then
   msginfo("Possible Error","'" + newval + "'  is already in the file for customer \n \n" +
               "Last Name = " + tc.lastname + " \n Parents = " + tc.Parents +
            " \n Street = " + tc.street + "\n City,St,Zip = " +
             tc.city + ", " + tc.state + ", " + tc.zip)
   eventInfo.setErrorCode(CanNotDepart)
else
   errorclear()
   tc.close()
   if not oldval.isblank() then
        if msgQuestion("Last Name Confirmation","You changed "+oldval+" to "+newval+".\n"
           +"Do you wish to make the change permanent?")="Yes" then
         
            if not oldval.isblank() and not newval.isblank() then
                tc.open(":data:progression History.db")
                tc.edit()
                lop="Y"
                while lop="Y"
                  if tc.locate("phone",oldval,"lastname",lastname) then
                      tc.phone=newval
                  else lop="N"
                 endif
                endwhile
            tc.close()
            endif

       if not oldval.isblank() and not newval.isblank() then
                tc.open(":data:swim progress history.db")
                tc.edit()
                lop="Y"
                while lop="Y"
                  if tc.locate("spphone",oldval) then
                      tc.spphone=newval
                  else lop="N"
                 endif
                endwhile
            tc.close()
            endif
              unlockrecord()
              tc.attach(gcustrec)
              tc.locate("LastName", modval,"phone", newval)
              gcustrec.resync(tc)
              tc.close()
       else
            disabledefault
            eventinfo.seterrorcode(cannotDepart)
            phone.moveto()
            phone=oldval
        endif
    endif
 endif

endMethod

Information purposes: this is a Gymnastics & Swim School and tracks the Customers and their kids for classes, tuition, progress through higher levels, and billing and payments.

If something is different in Pdox 11, I'd love to know it because I'd really like to migrate to it, but I can't even get Pdox 8 to work in Win 7.

Thanks for looking and comments offered.  Bob