Hi All,

I have a database (FM7) with a table listing clients related to a table listing contact numbers.

In order to make a quick and dirty field for searching easily I have created a calc text field which combines all fields in the client database (client name, client address, client company) etc, seperated by carriage returns.

That works perfectly, but now the problem is that I also want to add related phone numbers into that same field, and this I can't get to work properly.

I thought the way to do it would be to use the ValueListItems function, so to test it I created a new field in the client table which using ValueListItems to show all of the related phone numbers. Unfortunately this field is not searchable!

Is there a better way to do this?

Thanks in advance,

Sean.

Hi, seanc. I think it must work, why it is not work?

Can you attach part of your file here?

I want to say that your searching idea is new for me, and i liked it.

Try turn your calculation field into an auto-entry calc.

Sean

Please see my attachment - it works.

Untitled.fp7.zip

Hi Ideal,

I'm afraid your example fails after a while. Because the search field is stored, it doesn't always update when the related phone numbers change (try changing a phone number manually in the other layout, and you'll see what I mean.)

The solution then is to make the calc unstored. The only trouble then is slower searches and problems with Server 8v2.

Coconut,

I think an auto-enter calc would have the same problem as a stored calc in this case. An auto-enter calc change can only be triggered by a local field, not a related field as in valuelistitems().

I suppose another option would be to use a script to either set a search field, or tickle a local field for an auto-enter update. This would require pretty tight data-entry control with buttons and scripts, or an event script plug-in to initiate such a script when leaving the phone number field.

I suppose another option would be to use a script to either set a search field, or tickle a local field for an auto-enter update. This would require pretty tight data-entry control with buttons and scripts, or an event script plug-in to initiate such a script when leaving the phone number field.

That's true. If the idea is just to get the data into an indexable field and avoid unstored calcs, an autoentry calc is the way to do it. A subscript can be added to the navigation scripts on a layout to check for changes, and if there are any, tickle and update.

If you use something like If[Max(related:timestamp) > lastupdate] as the test, it could work. You could even use a script parameter to pass the timestamp values to the script to keep the script portable across layouts and tables.

Thanks to everyone for their replies - I appreciate the help!!

I have downloaded IdealData's file, and as Ender says, it needed to be unstored and then it works, to my surprise, exactly as I want!

Until I put it on a server - and then it fails the same as my file.

Could someone else please test and confirm this:

Open my attached file locally (slightly modified version of Ideals), and do a search on the search field for xyz. There should be 2 found records.

Now host the file on Server (in my case I am on hosting via server 7.03 on Windows 2003), do the same search and there are no results!

So it seems like a bug??

Oddly if I serve the file via the fm client it works fine...

Thanks!

Sean.

searchfieldtest.zip

I don't remember this type of Find being a problem on Server 7, but there is a documented change in behavior with Server 8v2 and client 8v2, where finds on unstored calcs fail. This has been resolved with the 8v3 versions.