Jump to content

combining fields (including related) for easy searching


This topic is 6538 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6538 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.