Passing data between databases


#1

I am trying to migrate to Pan X - I am a long time user of Pan. I have a situation where I have a Jobs database that in its .ini file opens an Address database as secret. The Last Name field triggers a procedure that looks up the person in the Address database and transfers a bunch of the information to the Jobs database, without opening a visible window from the Address database. This is a fairly common procedure I would bet. I use these methods on several other databases in Pan 6 without fail. I can’t seem to get this to work in Pan X. I am sure it is pretty obvious what I am doing wrong, but I am stumped. I am including the ini procedure and the addlookup procedure as text files.

.ini procedure
global vdate,vtmoney,vjob,vfirst,vmiddle,vlast,vsecond,vadd,vcity,vst,vzip,vhome,vbus,vinst
global vfax,waswindow,vss,vlocal,vexpt,vstat,vbdate,vrobe,vlmod,vtmoney,vemail,vcode,vcell
global VscWage,vrot,vpot,vseason
GoForm “Data Entry”
LastRecord
OpenSecret “AddressFile”
OpenSecret “Instruments”
OpenSecret “LiveWages14”
OpenSecret “LiveWages15”
OpenSecret “LiveWages16”
OpenSecret “LiveWages17”
OpenSecret “LiveWages18”

Addlookup procedure:
global vss,vein,vrobe,vinst,vsecond,vemail,vcell,vcode,vmiddle,vcorp,vcorpname,vcaid,vw2,vw9,vi9,vbackup,vmpinst
vfirst=First
vlast=Last
vcorp=Corp
vcorpname=CorpName
«waswindow»= info(“WindowName”)
Window “AddressFile:secret”
SelectAll
Select «First Name»=vfirst AND «Last Name»=vlast
;debug
if info(“Selected”) = info(“Records”)
Message “No Records Selected”
Window «waswindow»
Stop
endif
if info(“Selected”) > 1 AND info(“Selected”) < info(“Records”)
Message “Duplicate Records Selected”
; Window «waswindow»
OpenFile “AddressFile”

Stop

endif
vfirst=«First Name»
vmiddle=«Middle Name»
vlast=«Last Name»
vsecond=«Second Name»
vcorpname=CorpName
vadd=Address
vcity=City
vst=State
vzip=Zip
vhome=Phone
vbus=Service
vcell=«CellPhone»
vemail=«E-mail»
vss=«SS#»
vein=«EIN#»
vcaid=«CAID#»
vlocal=«AFM #»
vexpt=Exemptions
vstat=Status
vbdate=BDate
vrobe=Robe
vinst=Instrument
vmpinst=Instrument
vlmod=LastMod
vcode=Code
vw2=W2
vw9=W9
vi9=I9
vbackup=Backup
Window «waswindow»
First=vfirst
Middle=vmiddle
Last=vlast
;if vsecond=""
; Last=vlast
;else
; Last=vlast+" "+vsecond
;endif
Street=vadd
City=vcity
State=vst
Zip=vzip
Home=vhome
Service=vbus
Cell=vcell
email=vemail
if vcorp=“Yes”
CorpName=vcorpname
«EIN#»=vein
«CA ID#»=vcaid
if vss > 0 «SS#»=vss endif
else
if vss > 0 «SS#»=vss endif
endif
«AFM #»=vlocal
Exemptions=vexpt
Status=vstat
BirthDate=vbdate
Robe=vrobe
Inst=vinst
GSInst=vinst
MPinst=vmpinst
Sequence=lookup(“Instruments”,“Inst”,Inst,“seqnum”,0,0)
LastMod=vlmod
Code=vcode
W2=vw2
W9=vw9
I9=vi9
Backup=vbackup
call .MPCartage
Field Job


#2

I don’t have time to go through all your code but goform should be `openform’ - check the Help wizard.

For the rest of it, stick in a few message statements to monitor where the procedure is stopping. if you have the procedure window open when you run it, it generally shows you where it encounters a problem.


#3

I spent some time looking at your procedure and do not see why it would not work. Of course, in Pan there are many ways to accomplish the same result, so I started thinking about how I would do this. Here are some comments about other ways to do some things.

You can transfer data using local variables if you do everything within the same procedure, which you are doing; global variables are not necessary. I am channeling Jim when I say it is a good practice to use a local variable rather than a global variable if possible.

To test if a selection found anything, you can use if info(“empty”), which will be true if nothing was selected. A little more economical than your approach.

You can also switch to another database without opening windows by using setactivedatabase “Addresses”. Then switch back the same way.

You could test for one and only one record in Addresses with

if aggregate("Last Name","count",{«First Name»=vfirst AND «Last Name»=vlast})=1

You could get all of the data from Addresses back to Jobs by creating a dictionary, which would have the field name and value as the pairs in the dictionary, or with a text array, which would just have the data from Jobs. First find the row that matches, then create a dictionary or text array. Here is code that would create the dictionary.

local lvarr1, lvarr2, lvdicdata,lvadd
lvarr1=info("fields")
lvarr2=replace(replace(exportline(),cr(),"; "),tab(),cr())
lvdicdata=arraymerge(lvarr1,lvarr2,cr(),",")
lvadd=dictionaryfromarray(lvdicdata,",",cr())

In either case, you have to get the data into the right field in Jobs. With a dictionary, for example, you could use, for example,

«City»=getdictionaryvalue(lvadd,"City","")

Or you could use the array function to extract the value from a text array based on its position in the array.

Anyway, there are some possibilities to consider, which you may or may not find preferable.


#4

Thanks for your response Michael. I obviously have a lot of work to do!

michael

    October 9

celloid:
GoForm “Data Entry”

I don’t have time to go through all your code but goform should be `openform’ - check the Help wizard.

For the rest of it, stick in a few message statements to monitor where the procedure is stopping. if you have the procedure window open when you run it, it generally shows you where it encounters a problem.


#5

Thanks for your time and input Cooper! Obviously, my coding skills are not that great. I have studiously avoided arrays, which needs to change. When I get some time, I will pursue your suggestions. Thanks again for your time. Dane


#6

I think you will find that arrays are not really difficult after you practice a little. There are lots of functions that help you use them in various ways. You can look at the Text Array page in the Help section, and look through all the functions that begin with ‘array’.