Fields to search in two files

[Christian Coppe]

Hello,

I have two files in FMP7v3. In file1, two fields : reference and tick, both text field.

In file2, the text fields are : reference, criteria1, criteria2 and data.

The two files are linked by the fields "reference".

The tick field in file1 is filled by a user several days after a record is created : it means that the record is released and available.

Another user perform a search in file2 : he searchs on criteria1, criteria2 and on tick in file1 (file1::tick). All is well apart from the time the first user remains on a record in file1 after filling the tick field : in this case, the record doesn't appear in the found set. The second user is of course not aware of that.

The simplest solution is to create a field "tick2" in file2, which is a calculation, saying that tick2 = file1::tick. It works, but the problem is that this new field cannot be indexed, decreasing the search performance (The reality is two databases of 50,000 records...). So, I try to find another solution where all the search fields are in file2, and I would like to have your advice.

1) I though to a lookup field, but in this case I have to re-lookup the entire file each time I peform a search. Not interesting.

2) I tried to define a text field, with an auto-calculation allowing further changes. The calculation is file1::tick. It doesn't work (why ?).

3) I tried to define two fields : the first one, "tick2", is a calculation (not indexed, of course) as file1::tick, and the second one, "tick3" is a text file, indexed in this case, with an auto-calculation, changes allowed, defined as file2::tick2. Damned! it doesn't work...

What are your suggestions ? Many thanks in advance for your help.

Christian

[-Queue-]

Auto-enter calculations will not update unless a local field (within the same table) is modified and the calculation references that field or the local key field for the referenced relationship is modified.

Are searches also performed in file1 for tick's value? If not, perhaps it would be more efficient to put tick in file2 and put the related field on file1's layout. If that is not a desirable option, then you need to add a trigger of some sort, either via 'update' button or a plug-in, such as EventTrigger, to update the related record when file1::tick is modified.

[Christian Coppe]

Thanks Queue. It was some of my ideas too. Maybe the use of EventTrigger is the best one.

Thank you for your help

Christian

[Søren Dyhr]

I have two files in FMP7v3. In file1, two fields : reference and tick, both text field.

In file2, the text fields are : reference, criteria1, criteria2 and data.

The two files are linked by the fields "reference".

The tick field in file1 is filled by a user several days after a record is created : it means that the record is released and available.

So record-locking seems to be the name of the game, why not make a multi-criteria relationship for all users to sift out the data they want's sans finds in a portal with no access to it's fields.

My hunch tells me that the desired issue might be very close to this templates functionality:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

only it utilizes a custom function (depends on Dev) which might be out of reach, unless you hack Rays file until it's your data and layouts it handles?

--sd