a little query help?

[DaveM]

hI AND tHANKS TO ALL!

I have a query that just does not wish to run in runtime or with runtime.

t=table.open("comp.dbf",file_ro_shared)
query.filter="lot=lotset"   'numeric field and variable  My AE stops here.
query.order="Lot" 
query.options="T" 
ix=t.query_create() 
group=t.group
comptaxrate=t.taxrate
compdtaxrate=t.dtaxrate
docrate=t.docrate
compname=t.name
compaddress=t.address
compcsz=t.cstzip
addtrade=t.addtrade
comppred=t.pred
electron=t.electron
t.close()

Lot was a character field and had the following ="lot="+quote(trim(lotset)) with character field and it worked in v7. I have gone to v10.5 and it does not work. I could not come up with any way to get it to work, so I tried numeric. still says no field.

Note** This only happens with runtime or if using either one on the runtime folder. When using developement on server, it works fine. ?????

I can make it character or numeric if that should make a diff.

[Davidk]

Re: a little query help?

Still have to try this with a runtime, but try.

query.filter="lot=VAR->lotset"

Yes... working here in Version 10.5 runtime... as long as the variable is set.

Here's the database I used to test. Set the variable to a value of 1 on startup.

I was getting errors on the query create when the variable was not set.

[DaveM]

Re: a little query help?

Thanks dave - tried it. stil gives error - no field.

currently using ( switched all back to character) Will try with runtime in the morning. Tired now.

t=table.open("comp.dbf",file_ro_shared)
query.filter="lot="+quote(trim(lotset))
query.order="" 
query.options="T" 
ix=t.query_create() 
lot=t.lot
group=t.group
comptaxrate=t.taxrate
compdtaxrate=t.dtaxrate
docrate=t.docrate
compname=t.name
compaddress=t.address
compcsz=t.cstzip
addtrade=t.addtrade
comppred=t.pred
electron=t.electron
t.close()  '.close()

[MikeC]

Re: a little query help?

Dave,
Try s_quote() instead as it was created mainly for variables which quote() was not....

[DaveM]

Re: a little query help?

Thanks David,

That did not help

I also tried S_quote to no avail.

Dave

[Tom Cone Jr]

Re: a little query help?

Dave, you could use debug(1) to see where's falling over. (FWIW, I'm suspicious of the ro_shared parameter here, and would try your script without specifying any mode for the table being opened at all).

A better solution in my book is to scrap the use of a query altogether. Open the source table, set the index, then fetch_find the desired record.

-- tom

[martinwcole]

Re: a little query help?

If both the table field "lot" and the variable "lotset" are character values, I would try this:
dim shared lotset as c
lotset="10" 'or whatever
then your existing query using "lot=var->lotset"

not sure if this will help, but I have found that I had to sometimes set variables as shared to get stuff to work

[DaveM]

Re: a little query help?

Thanks Martin,

If both the table field "lot" and the variable "lotset" are character values, I would try this:
dim shared lotset as c
lotset="10" 'or whatever
then your existing query using "lot=var->lotset"

I have tried "lot=var->lotset"
all are character and only 2 long.

dim global lotset as c = 1 is what it is and gets is value from a dialog box after the 1 is set for the d box.
Checked and it is correct.
It does assign proper value.
Thanks

.

[martinwcole]

Re: a little query help?

wouldn't "as c=1" need to be as c="1"

[Jay Talbott]

Re: a little query help?

I have always used:

query.filter="lot= '"+lotset+"'"

Does that work?
Jay

[Clunes]

Re: a little query help?

I think you problem will be a field lentgh.

try

"lot=alltrim(var->lotset)"

or

"lot=" + alltrim(var->lotset)

or
"alltrim(lot) = alltrim(var->lotset)"

or
"var->lotset$lot"

etc

[Clunes]

Re: a little query help?

One other this when you debug what does the query.filter line actually show when you step past it?

It should show the value contained lotset variable if you have it correct

[Mike Wilson]

Re: a little query help?

From first post:

t=table.open("comp.dbf",file_ro_shared)
query.filter="lot=lotset"   [COLOR="Red"]'numeric field [/COLOR]and variable  My AE stops here.

t=table.open("comp.dbf",file_ro_shared)
xo=t.order("","lot="+lotset)
lot=t.lot
group=t.group
comptaxrate=t.taxrate
compdtaxrate=t.dtaxrate
docrate=t.docrate
compname=t.name
compaddress=t.address
compcsz=t.cstzip
addtrade=t.addtrade
comppred=t.pred
electron=t.electron
t.close() '.close()

[DaveM]

Re: a little query help?

Nothing offered has helped. This may be some kind of bug.
all are now back to character fields 2 long
Made a code change to be sure what is happening
*** Note*** A5V7 runtime opens it with NO PROBLEMS v9 does not.
Something I am missing here??

lot = lookupc("F",var->lotset,"lot","comp","Lot")
group = lookupc("F",var->lotset,"group","comp","Lot")
compname = lookupc("F",var->lotset,"name","comp","Lot")
compaddress = lookupc("F",var->lotset,"address","comp","Lot")
compcsz= lookupc("F",var->lotset,"cstzip","comp","Lot")
addtrade = lookupn("F",var->lotset,"addtrade","comp","Lot")
comppred = lookupn("F",var->lotset,"pred","comp","Lot")
electron = lookupn("F",var->lotset,"electron","comp","Lot")
docrate = lookupn("F",var->lotset,"docrate","comp","Lot")
comptaxrate = lookupn("F",var->lotset,"taxrate","comp","Lot")
compdtaxrate = lookupn("F",var->lotset,"dtaxrate","comp","Lot")

popup error:
There was an error running autoexec script
error: lookupc(F,var->lotset,lot,comp,Lot) No Such File


this works from the v10 - but not from shadowed tables. It does not matter if using runtime or developer. Runtime use on the main tables gives same error.

I am going to give the whole script so we can see if I may have an error somewhere else. AND YES the comp table is shadowed.

'Date Created: 01-Sep-2006 10:51:31 AM
'Last Updated: 29-Jan-2008 04:28:06 PM
'Created By  : dave
'Updated By  : dave
'dim t as P
't = table.open("names")
't.decrypt("heidi")
't.close()
default_encryption_key_set("heidi")
dim global versionnumber as n
versionnumber = A5_GetAppVersionNumber()
dim global cpath as c
dim global cmast as c
dim global regusers as n
dim global runtimeusers as n
dim global totalusers as n
statusbar.hide()
cpath = a5.Get_Path()
cmast = a5.GET_MASTER_PATh()
regusers = a5_registration_license_inuse(cmast+"\sales.muf")
runtimeusers = a5_registration_license_inuse(cmast+"\sales.rmuf")
totalusers = runtimeusers+regusers+1
'dim t as P
't = table.open("names")
't.encrypt("eaglet")
't.close()
dim global scode as c
dim global stat as c = "A"
dim global username as c = ""
dim global salesman as c = ""
dim global cellphone as c = ""
dim global usernow as c = ""
dim global password as c = ""
dim global reverse as c = ""
dim global worknumber as n = 0
dim global secused as c = ""
dim global group as c = ""
dim global lot as c = ""
dim global nowpath as c = ""
dim global compbat as n = 0
dim global comptire as n = 0
dim global comptaxrate as n = 0
dim global comppred as n = 0
dim global complemon as n = 0
dim global comptags as n = 0
dim global comppmtterm as n = 0
dim global comptermtype as n = 0
dim global compapr as n = 0
dim global compnewwheels as n = 0
dim global compantitheft as n = 0
dim global permissions as c = ""
dim global userlot as c = ""
dim global cpath as c = ""
dim global electron as n = 0
dim global compdtaxrate as n = 0
dim global docrate as n = 0
dim global compname as c = ""
dim global gotoileads as L = .f.
dim global gotosales as L = .f.
dim global gotoother as L = .f.
dim global gotoreports as L = .f.
dim global gotocsi as L = .f.
dim global buycar as L = .f.
dim global gotoslsschedule as L = .f.
dim global gotoslslabels as L = .f.
dim global setupemail as L = .f.
dim global seedealnumbers as L = .f.
dim global reshadow as L = .f.
dim global gotocomp as L = .f.
dim global cleantbls as L = .f.
dim global compaddress as c = ""
dim global compcsz as c = ""
dim global gotoinv as L = .f.
dim global editletters as L = .f.
dim global gotoslspeople as L = .f.
dim global editownpassword as L = .f.
dim global editownuserinfo as L = .f.
dim global seeallslspeople as L = .f.
dim global editpermiss as L = .f.
dim global surl as c = "http://"
dim global gotovendors as L = .f.
dim global gotoagents as L = .f.
dim global gotorebates as L = .f.
dim global deletesales as L = .f.
dim global makebackup as L = .f.
dim global advertisers as L = .f.
dim global editcompinfo as L = .f.
dim global gotobanks as L = .f.
dim global addtrade as n = 0
dim global ulot as c = ""

dim global emok as L
emok = .f.
:a5.minimize()
'=========================
x = getstart()  'dialog to get user name and password into global vars.
'=========================
lot = lotset
t=table.open("employ",file_ro_shared) 
query.filter="username="+quote(trim(scode))
query.order="" 
query.options="T" 
ix=t.query_create() 
 if ix.records_get()=0 then 
  a5.close() 
 end if
ulot=t.lot  
username=t.username 
salesman=t.salesname 
cellphone = t.cellphone 
password = t.password 
usernow = t.usernow
permissions = t.permissions
userlot = t.lot
gotoileads = t.gotoileads
gotosales = t.gotosales
gotoother = t.gotoother
gotoreports = t.gotoreports
gotocsi = t.gotocsi
buycar = t.buycar
gotoslsschedule = t.gotoslsschedule
gotoslslabels = t.gotoslslabels
setupemail = t.setupemail
seedealnumbers = t.seedealnumbers
reshadow = t.reshadow
gotocomp = t.gotocomp
cleantbls = t.cleantbls
gotoinv=t.gotoinv
editletters=t.Editletters
gotoslspeople = t.gotoslspeople
editownpassword = t.editownpassword
editownuserinfo=t.editownuserinfo
seeallslspeople=t.seeallslspeople
editpermiss=t.editpermiss
gotovendors=t.gotovendors
gotobanks=t.gotobanks
gotoagents=t.gotoagents
gotorebates=t.gotorebates
deletesales=t.deletesales
makebackup=t.makebackup
editcompinfo=t.editcompinfo
advertisers=t.advertisers
compbat = t.bat
comptire = t.tire
complemon = t.lemon
comptags = t.transfer
comppmtterm = t.term 
comptermtype = t.termtype
compapr = t.apr
compnewwheels = t.newwheels  
compantitheft = t.antitheft
t.close()
't=table.open("comp.dbf",file_ro_shared)
'query.filter="lot="+s_quote(trim(lotset))
'query.order="" 
'query.options="T" 
'ix=t.query_create() 
'lot=t.lot
'group=t.group
'comptaxrate=t.taxrate
'compdtaxrate=t.dtaxrate
'docrate=t.docrate
'compname=t.name
'compaddress=t.address
'compcsz=t.cstzip
'addtrade=t.addtrade
'comppred=t.pred
'electron=t.electron
't.close()
lot = lookupc("F",var->lotset,"lot","comp","Lot")
group = lookupc("F",var->lotset,"group","comp","Lot")
compname = lookupc("F",var->lotset,"name","comp","Lot")
compaddress = lookupc("F",var->lotset,"address","comp","Lot")
compcsz= lookupc("F",var->lotset,"cstzip","comp","Lot")
addtrade = lookupn("F",var->lotset,"addtrade","comp","Lot")
comppred = lookupn("F",var->lotset,"pred","comp","Lot")
electron = lookupn("F",var->lotset,"electron","comp","Lot")
docrate = lookupn("F",var->lotset,"docrate","comp","Lot")
comptaxrate = lookupn("F",var->lotset,"taxrate","comp","Lot")
compdtaxrate = lookupn("F",var->lotset,"dtaxrate","comp","Lot")
 

:a5.maximize()
If PrintDailyReport = "Y" then
dim global leasemonths as n
leasemonths = 10
script_play_local("printstartreports")
end if
delete leasemonths
if alltrim(pword)<>alltrim(password) then
 UI_MSG_BOX("User","Your User name or Password is incorrect. UpsLog will now close",UI_OK)
 sleep(3)
 a5.close() 
end if
form.view("Begin")

[DaveM]

Re: a little query help?

a little more help? When I create the shadow on the main tables, I lose the indexes to the table "comp".

Something is dead wrong.

copied a working table from v7 and it had 2 indexes, worked with dev a5v10, shadowed the tables and got an error of no index. Went back to the dev a5v10 and sure enough, the indexes were gone.

This is not good. Why would making a shadowed copy wipe out the indexes in both server and shadow???

also, in the shadow, a field in the table(default browse) cannot be changed nor can you add one.


.