Searching an unstored calc field?

[sleepinggypsy]

Is there a better way than to perform a find against an unstored calculation field? The find takes forever as the unstored calculation field cannot be indexed. Here's my problem: I have a real estate database and I want the user to be able to search multiple criteria from one field (ala google toolbar style). The database fields I have are: City, State, Area, Zip Code, and Listing ID. The unstored calculation field (The field the users uses to search) concatenates all the fields, ie: City &" "& State &" ".... For example a user could perform a search by typing Chicago, IL into one field(in my case the unstored calc field) as opposed to entering "Chicago" into the City field and "IL" into the State field. Again my search works but it takes too long b/c the search field is unindexable. Is there a better way to do this? Thanks in advance!

[comment]

Why does the calc have to be unstored?

[sleepinggypsy]

Because if the values of any of the concatenated fields change (which they may), it is vital that the search field will pull correct info. Also, it seems as if the only way to pull the fields is via unstored cal field? This is the calc:

Properties::AreaList & " " &

Properties::AreaList & "," & Properties::Address_State &

Properties::AreaList & ", " & Properties::Address_State &

Properties::Address_City & " " & Properties::Address_State & " " & Properties::Address_Zip & " " &

Properties::Address_City & "," & Properties::Address_State & " " & Properties::Address_Zip & " " &

Properties::Address_City & ", " & Properties::Address_State & " " & Properties::Address_Zip& " " &

_UnitList::UnitListCode

It doesn't allow me to store.

[comment]

I am a bit confused about your structure. It seems like you ought to do your search (and the calc) in the Properties table, where most, if not all, of these attributes live.

Perhaps this thread can help clarify the issue.

[sleepinggypsy]

The calculation is performed in the LineItems table as a property listing comprises 4 related tables and the LineItems is the most specific: Owners>Properties>Units>LineItems. Thus the calculation is performed from the conext of LineItems even though most of the info resides in properties. The search itself is being performed from the customers table and a text field that stores the users input and is saved to that users's record. In sum, a record is created in the Customers table. The user enters their search criteria in a field. The find that that is performed is performed against the Unstored calculation field that concatenates the fields from the properties table. Whew... Make sense?

[comment]

No, not really. You mentioned 4 tables, but their names don't mean anything to me as such. Then you mentioned a Customers table.

Perhaps if you attached a sample file that shows the basic structure of tables, fields and relationships.

[sleepinggypsy]

I made it sound more confusing than need be. The 4 related db's represent an individual real estate listing. The customer table is just that. It is not related. The field in customers is simply a text field that stores each customer's search criteria. That criteria from customers is passed via the search script as a script parameter to the unstored calc field in UnitList. I have attached a pdf of the graph and the search script (11th line passes the script param to the unstored calc in UnitList). Hope that helps. Thanks for your help

script.pdf

graph.pdf

[comment]

I am sorry - I have no idea what you're saying. Why does an individual real estate listing need 4 db's? I am getting a vague idea that this may be about finding a real estate that fits customer's expectations, but...

[sleepinggypsy]

On an aside, a real estate listing (in my case) requires 4 related tables or you would have redundant information if it were less than 4 tables.

For example one Owner (Owner table) can own multiple buildings (Properties Table). Each Property can have multiple Units, ie: 1 brm, 2 brm (Units table) and each unit of the same type ie: 1 brm/ 1bath/ $400 can have multiple line items (UnitList Table).

Thus: Owners>Properties>Units>UnitList.

Customers is just a table that sores the customer's search results.