Calculate Multiple Offices

[Rickinrsm]

Answer Table: FIELD 001

COLDWELL BANKER NRT 1
FIRST TEAM
RE/MAX REAL ESTATE
FIRST TEAM BAKER
COLDWELL BANKER NRT 3
FIRST TEAM BAKER
C-21 WIDDELL
RE/MAX REAL ESTATE
C-21 WIDDELL

I have a program that selects each one of these offices ONE AT A TIME and runs calculations that are in fields 002 through 004 and stores them in a linked table placed on the last page of the report. This works fine.

After or during that what I need is a way to tell Paradox 7 to see that there are MULTIPLES of the same office name or a slight variation (as in COLDWELL BANKER 1 and 3 and FIRST TEAM BAKER) and total them together as one office.

Any ideas?

Thanks much . . . Rick

 

[TonyMcGuire]

Assign each office/company their own internal ID.

Use that instead of what comes from the system to identify the actual company/office.

Usually, and OFFICE ID is part of an MLS system; are you retrieving the info from an MLS system? Or users typing in?

(I've worked with Paradox and MLS/real estate systems for 16 years. Ask away!)

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Yep! An MLS system.

I'm connected to a RETS server where I can download lots of raw data.

How exactly would I assign an Internal ID nunmber, like a variable? CB="COLDWELL BANKER.."

Where in the present script would I accomplish this?

Rick

 

[TonyMcGuire]

"Where in the present script would I accomplish this?"

I have the code to your script?

"I'm connected to a RETS server "

My condolences.

One of the fields you should have available is an office ID. Usually a 6-10 character field, which uniquely identifies each office. For instance in Sacramento, CA, they have a 4-2 system (ie COLD02) where the first (up to) 4 characters identify a company and the last 2 identify the office. By creating an 'office' table (and RETS systems are required to have an 'Office Meta-Data' function last I checked), you can use the office code to identify back to the naming convention. I also strip the first portion of that office ID and place in its own field of the Offices.db table I create; then I can easily pull out all offices of a company with setrange instead of querying; MUCH faster.

Which MLS software are you connecting to to get the data?


Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Hi Tony . . . thanks for your inpuy. I have no experience with setRange but I'll look at it.

I use MarketLynx to access the RETS server in Orange County. I also use the WEB based application that many of the boards supply access for and download ASCII data and then import to Paradox. Few Association have RETS servers and they are restricting their access.

I have a new one that you helped me with to select the top offices 20 of a query and use them for my calculation and it works great but now I'm asked to combine multiple offices within that top 20 if there are any multiple occourences. How do I do that? I'm quite familiar with the unique office ID numbers.

Basically this is my script that runs all of my calculations;

method pushButton(var eventInfo Event)
{This code scans a table with office names and assign each
office name to the variable s and runs the code for each office
in the table. The asorted variables throughout the code are
stored in tables that are linked to reports. This code
alliveates the necessity of additional code for each office.
(c) March 21, 1997 Advanced Computer Technologies}
var;----------------Declare Variables-------------
ofc,tc,totaltc tCursor
Scmls table ;All downloaded sales
CBFull table ;Stored Table w/Office Names
CBFullLink table ;Linked table to report (Stats)
s string
q query
r report
i longint
endVar;-------------End Variable List-------------
addAlias("MYDIR","Standard","D:\\Pdoxwin7\\Working")
;----------------------------------------------------------------
If Not q.readFromFile("CBFull.qbe") then ;saved query
errorShow()
endIf
If not q.executeQbe(":WORK:CBFullAns") then
errorshow() ;specifies answer table
endif
;---------------------------------------------------------------
CBFullLink.attach("CBFullLink")
if isTable(CBFullLink) then
CBFullLink.empty() ;This table is created interavtively.
errorShow() ;Tools Copy Table etc..
endIf
;---------------------------------------------------------------
if Not tc.open(":MYDIR:CBFullAns") then
errorshow() ;Queried answer table
Return
Endif
;---------------------------------------------------------------
if Not ofc.open(":MYDIR:CBFull") then
errorshow() ;This table stores all
Return ;of the offices unique
Endif ;to the report
;---------------------------------------------------------------
scan ofc:
s = ofc."OfcName" ;gets office name from field OfcName
s=s+".."
;msginfo("Assigned?",s.isAssigned()) ;Test the variable s
{-------------------------We Sold Ours----------------------------}
q=

Query
ANSWER: RIV:ANSWER.DB

CBFullANS.DB | SALEPRICE |
| CALC SUM AS PRICE, calc count all AS recs |

CBFullANS.DB | LISTNAME | SELLNAME |
| ~s | ~s |

EndQuery

if not q.executeQbe()then
errorShow()
return
endif
If Not tc.open("RIV:Answer") then
errorShow()
Return
endIf
if tc.isEmpty() then ;if Tcursor is empty
WSO=0 ;variable is zero
WSOdv=0 ;variable is zero
Else ;else
WSO =tc.recs*2 ;variable = # of records in query times 2
WSOdv =tc.price*2 ;variable = Sum of dollars in query times 2
Endif
tc.close()
{---------------------------They Sold Ours------------------------}
q=

Query
ANSWER: RIV:ANSWER.DB

CBFullANS.DB | SALEPRICE | LISTNAME |
| CALC SUM AS PRICE, calc count all AS recs | ~s |

CBFullANS.DB | SELLNAME |
| NOT ~s |

EndQuery
if not q.executeQbe()then
errorShow()
message ("Query error TSO...")
return
endif
If Not tc.open("RIV:Answer") then
errorShow()
Message("Open error tCursor TSO...")
Return
endIf
if tc.isEmpty() then ;if Tcursor is empty
TSO=0 ;variable is zero
TSOdv=0 ;variable is zero
Else ;otherwise...
TSO =tc.recs ;variable = # of records in query
TSOdv =tc.price ;variable = Sum of dollars in query
Endif
tc.close()
{-------------------------We Sold Theirs--------------------------}
q=

Query
ANSWER: RIV:ANSWER.DB

CBFullANS.DB | SALEPRICE |
| CALC SUM AS PRICE, calc count all AS recs |

CBFullANS.DB | LISTNAME | SELLNAME |
| NOT ~s | ~s |

EndQuery
if not q.executeQbe()then
errorShow()
return
endif
If Not tc.open("RIV:Answer") then
errorShow()
Return
endIf
if tc.isEmpty() then ;if Tcursor is empty
WST=0 ;variable is zero
WSTdv=0 ;variable is zero
Else ;else
WST =tc.recs ;variable = # of records in query
WSTdv =tc.price ;variable = Sum of dollars in query
Endif
tc.close()
q=
{----------------------Total Listing Dollars----------------------}
Query
ANSWER: RIV:ANSWER.DB

CBFullANS.DB | SALEPRICE | LISTNAME |
| Calc Sum All as Listdv | ~s |

EndQuery

if not q.executeQbe()then
errorShow()
return
endif
If Not tc.open("RIV:Answer") then
errorShow()
Return
endIf
if tc.isEmpty() then ;if Tcursor is empty
listdv = 0 ;variable is zero
Else ;else
listdv =tc.Listdv ;variable = Sum of dollars in query
Endif
tc.close()
tt=WSO+TSO+WST
totaldv=WSOdv+TSOdv+WSTdv
If not totalTc.open ("CBFullLink") then
errorShow()
Return
endIf
s = ofc."OfcName"
totalTc.end() {totaltc. is for tables that store info.}
totalTc.edit()
totalTc.insertAfterRecord()
totalTc."Office" = s
;totalTc."Date" = today() - 25
totalTc."WeSoldOurs" = WSO
totalTc."TheySoldOurs" = TSO
totalTc."WeSoldTheirs" = WST
totalTc."Total Sales" = tt
totalTc."List DV" = listdv
totalTc."Total DV" = totaldv
totalTc.endedit()
totaltc.close()
endScan
Sort "CBFullLink.db"
ON "Total DV" D
EndSort
If Not exportSpreadsheet("CBFullLink.db","D:\\FLW\\Reports\\CBFullLink.xls", True)
then
errorShow()
endIf
If Not r.open("CBFull") then ;Open the Report
errorShow()
Return
Else
r.menuAction(MenuReportPageLast) ;Moveto last page of report
Endif
endMethod{--------------------------------------------------------}