Removing a Table LookUp at Runtime??

[SercoSteve]

Hi

In Design mode, and the Table Restructure Window, I can Erase a Table LookUp by selecting 'Table LookUp' from the Table Properties Drop Down, then selecting the field of interest and clicking 'Erase'.

Does anyone know how to remove a Table LookUp at RunTime.

Thanks in advance

Steve

 

[NotAllThere]

Do an enumFieldStruct(). Open the description table, scan it and remove the entries for "_Table Lookup", "_Table Lookup Type" fields as appropriate. Use restructure() method.

 

[SercoSteve]

Cheers NotAllThere, nice one

I am using the following code to put a different lookup back in, but it doesn't work. What sort of things would cause the restructure to fail. It gets all the way to the restructure, thinks about it for a while then drops out without the call to moveTo(plist).

custTbl.attach("RIVartslist.DB&quot
if custTbl.isTable() then
custTbl.enumFieldStruct("RIV:newstructure.DB&quot
if struct_tc.open("RIV:newstructure.DB&quot then
if struct_tc.locate ("Field Name", "PART_NO&quot then
struct_tc.edit()
struct_tc."_Table Lookup" = "C:\\All_part.DB"
struct_tc."_Table Lookup Type" = 3
endIf
endIF
struct_tc.close()
endIf
custTbl.unattach()

if modifiedTbl.attach("c:\\corel\\suite8\\paradox\\private\\partslist.DB&quot then
if modifiedTbl.isTable() then
if modifiedTbl.tableRights("FULL&quot then
if modifiedTbl.dropIndex("&quot then
dynNewStru["FIELDSTRUCT"] = "RIV:newstructure.DB"
if modifiedTbl.restructure( dynNewStru ) then
moveTo(plist)
endIf
endIf
endIf
endIf
endIf
modifiedTbl.unattach()

 

[NotAllThere]

Now wandering into territory which I have not extensive experience. I take it that the specified lookup table exists and field is compatible.
Some blind shots: You could try a setExclusive() before trying the restructure. You might also try a sleep(1000) between the dropIndex and the restructure to ensure the table gets written to disk before the code advances. If you omit the dropIndex, does the restructure work?
Passing comment: nesting of if statements is getting very deep. I find the following structure easier to maintain and debug:

if not tbl.isTable() then
;errormessage and tidy up
errorShow()
return
endif

if not tbl.tableRights("Full&quot then
;errormessage and tidy up
errorShow()
return
endif

HTH

 

[SercoSteve]

Hey NotAllThere

I have been trying some of the things you suggested. The code as it stands is shown below

custTbl.attach("c:\\corel\\suite8\\paradox\\private\\partslist.DB&quot
if custTbl.isTable() then
custTbl.enumFieldStruct("c:\\corel\\suite8\\paradox\\private\\newstructure.DB&quot
if struct_tc.open("c:\\corel\\suite8\\paradox\\private\\newstructure.DB&quot then
struct_tc.edit()
scan struct_tc:
struct_tc."_Table Lookup" = ""
struct_tc."_Table Lookup Type" = ""
endScan
struct_tc.close()
endIF
endIf
custTbl.unattach()

if modifiedTbl.attach("c:\\corel\\suite8\\paradox\\private\\partslist.DB&quot then
if modifiedTbl.isTable() then
modifiedTbl.setExclusive(Yes)
if modifiedTbl.dropIndex("&quot then
sleep(1000)
dynNewStru["FIELDSTRUCT"] = "c:\\corel\\suite8\\paradox\\private\\newstructure.DB"
if modifiedTbl.restructure( dynNewStru ) then
message ("Restructure: ", "Complete&quot
endIf
endIf
endIf
endIf
modifiedTbl.setExclusive(No)
modifiedTbl.unattach()

The 'message' step now executes suggesting the 'Restructure' is working, and it is in part. The indexes are removed from partslist.DB following the dropIndex call and replaced following the Restructure, however the Table Lookup is more stubborn and will not let itself be deleted.

If I remove the dropIndex step the Restructure fails.

Thanks for your time.

Steve

 

[NotAllThere]

I'm at a bit of a loss because my experience manipulating table structures pre-dates the restructure() method. I used to use enumFieldStr(), enumIndexStruct(), enumSecStruct(), create(), and add(). These always worked for me for table structure manipulation. However, restructure should shortcut a lot of that work but I wonder if it fully supports all types of restructuring. I also wonder if several separate restructures in the correct order are be needed if multiple types of change are being attempted.

I notice you drop the primary key after the enumFieldStruct. Does this also remove the secondary indices? May be worth checking that the index files have actually disappeared (partsList.X??, partList.Y??). Your code does not remove any primary key which would remained specified in newstructure.db. (struct_tc."Key&quot
Not sure what else to suggest right now.

 

[footpad]

Steve,

Um, this is a lot of work. Why do you need to remove a table lookup at runtime? If the lookup is optional, then you're better off writing a code-based lookup for the times you need it instead of trying to add it temporarily.

The restructure methods are somewhat finicky. If you really need to do this, then you may find it easier to simply keep two copies of the table, one with and the other without the table lookup. Then, simply copy the appropriate structure when needed.

Hope this helps...

-- Lance

 

[SercoSteve]

Hey Footpad, thanks for your reply.

Unfortunately I do not have control of the DB structure before I come to use it. The tables I am attempting to modify are Manufacturing Parts Lists which sit of a Network Drive I only have Read Access to. The first thing I do is make a local copy of the Parts List DB to PRIV.

Each of the Parts List DBs already contains a hard coded lookup, without ALIAS, to another DB which defines all the different part types on site. This DB contains lookups to other DBs and so on....and so on. I think there are 6 substantial DBs connected by lookups. starting with the Parts List DB. I could copy them all to PRIV, and I'm sure all the lookups would find the DBs they are looking for but that's not really a viable solution.

It is this hard coded lookup that I either want to remove or modify to include an ALIAS. Getting someone with write access to modify the lookups at source is not an option as the are well over 1000 different Parts List DBs.

I have already tried having a different structure stored in PRIV with the lookup reference removed and restructuring using that when required, but although the Restructure returns success the lookup is never removed.

The closest I have got is to create a new DB without the lookup and scan the PartsList DB to fill the new DB. This works fine, clunk clunk, but not idea.

 

[NotAllThere]

Glad to hear confirmation that restructure can be a bit dodgy if you ask it to do too much in one go - which is what I suspected.

Just an additional cent of info for Steve: Lookup tables which are not in the same folder as the 'looking up' table always get their full path (e.g. C:\PDOXWIN\PARTS\LIST1.DB) HARDCODED into the 'looking up' table family, even if you use an alias when specifying the lookup. I only mention this because you seem to be referring to an ALIAS. If you create a lookup using an alias and subsequently change the alias to point elsewhere, the original lookup table is still used - which is why I got rid of all my lookups and replaced them with a home brew (coded) lookup.

 

[SercoSteve]

Hey NotAllThere, good call.

It seems that rather than being hard coded the lookup path in my PartsLists DBs has just been specified as a DB Name. The lookup path is updated depending on the location of the DB.

Unfortunately I still have the same problems!!

Thanks for your post.

 

[warrenw]

I use the folowing code to add an alternate lookup form for a field. You could modify it to Control+Spacebar and replace the lookup without bothering to restructure the table. The form you call has to return the value that is needed in this field. If it is just closed nothing happens.


method keyPhysical(var eventInfo KeyEvent)
var
theKeyCode SmallInt
f form
retVal anyType
endVar

disableDefault
theKeyCode = eventInfo.vCharCode()
if eventInfo.isShiftKeyDown() AND
eventInfo.isControlKeyDown() AND
theKeyCode = VK_SPACE then
if NOT f.open("AlternateLookup&quot then
errorshow()
else
hide()
retVal = f.wait()
show()
if retVal.datatype() <> &quot;Logical&quot; then
if retVal <> &quot;&quot; then
if msgQuestion(&quot;Confirm&quot;,&quot;Replace current value&quot = &quot;Yes&quot; then
Self.value = retVal
endIf
endIf
endIf
endIf
else
doDefault
endif
endMethod