expression question...

[fhannaford]

I am trying to use 'address' (street only, really) to link tables across multiple company files. Of course, the notion that address will be an exact match turns out to be fallacious. While the number is always the same, and, street name, the inclusion of terms such as 'st', 'st.', in an inconsistent manner makes getting exact matches problematic.

Not being an alpha programmer, I was hoping someone might suggest a way to use expressions...somehow, for example, comparing addresses based only on the numbers and letters strung together, bypassing the periods, etc.

Actually, just any 'logical' way to compare / affiliate addresses with different minor differences would be very helpful.

[Mike Wilson]

Re: expression question...

a way to use expressions...somehow, for example, comparing addresses based only on the numbers and letters strung together, bypassing the periods, etc.

dim vadd1 as C = "1234 Big st."

dim vadd2 as C = " 1234    Big-St"

? remspecial(vadd1) = remspecial(vadd2)
= .T.

This removes all the non-number and alphabet items but won't help you much with st versus street.

[MikeC]

Re: expression question...

Frank,
There most likely is a better way to link the tables (and easier). First, why are you trying to link the different companies together? Do the different companies actually have common customers? Regardless, the name might be an easier way and less fraught with error. You have to find a common, unique value that can be easily matched between the various companies which obviously you are trying to do, but it has to be one that can be depended upon to be the same between them....I do not think the address is this. Maybe a combination of parts of the name and address could be made to work.

Without knowing the names (your data) it is nigh impossible to tell you exactly how to go about this.

[Al Buchholz]

Re: expression question...

Frank

The solution to this is very data dependant.

The first consideration is to identify exactly what is needed and then devise a workable solution.

As Mike stated, you need to have some data that is common to each company file and can be matched exactly. Using an address is going to be a futile exercise.

You may want to consider using the QuickBooks custom field option to set a common value that is only used to match records together.

But with the information that you have given we are only providing some WAG. (Wild A.. Guesses)

Hope that helps.

[fhannaford]

Re: expression question...

Problem: Organization is in real estate - buying, selling, fixing, renting - and has 3 companies handling the disparate parts (buying / selling, renting, and fixing / maintaining).

They use 'address' of property as unique identifier. Works fine within each company, it seems. Issue is multi-company reporting.

I think your expression help will handle most of the issues I have seen when looking at actual data. Actual street number and name are there / same. Differences center on things like whether 'st' has a period after it or not.

Your thought about a custom field that is unique may be only other recourse.

Assistance much appreciated, as always.

[Al Buchholz]

Re: expression question...

Your thought about a custom field that is unique may be only other recourse.

Having a dual purpose field - address and link - usually causes a conflict in purpose somewhere in the future.

Someone will have a perfectly valid reason that the address needs to have a 'little tweak' for a special purpose.. and defeat the link...

It's usually difficult enough to maintain data for one purpose, let alone two purposes..

But if you're charging by the hour and the client doesn't mind......... right....