Slow finds

[Jon Grimshaw]

Any suggestions please! I have created a db with a field 'DateStarted' in a 'Projects' table which directly = data from a 'DateStarted' field in an 'Hours' table (logging users' work sessions) in the same file. This is an indexed calculation field determining the min date value from a range of datestamp fields. Because the former only relates to the latter, it can't be indexed itself yet it forms a part of a calculation for a 'ProjectStatus' field that is integral to many fundamental find scripts. These take on average 75 seconds - waaaay too long. How can I speed these up? Thanks in advance!

[Jon Grimshaw]

In the absence of any reply (pity me!) I've had a thought which I wondered if anyone had a view on before I tried it out, as it would involve a lot of work that I'd have to complete in a short window. If I brought the fields and data from the Hours table into the same table ('Projects') and then did a self join, would I be able to index the 'DateStarted' field or would fmp still see it as related and not index it? Thanks for any help...

[Søren Dyhr]

Because the former only relates to the latter, it can't be indexed itself yet it forms a part of a calculation for a 'ProjectStatus' field that is integral to many fundamental find scripts.

Isn't it exactly why there now exists an ability to put Lookups inside a calc'field??+

--sd

[Fenton]

A self join is a relationship, so it's not indexable, same table or not. And it would certainly screw up your relational structure. The problem as I see it is a "trigger" problem. You're expecting a change in the child table to trigger something in the parent table.

An easy solution is to have a button to create a new Hours record. Then you could easily pass the 1st date to the parent table. If the Hours entry is in a portal this is easy. Put the button at the top of the portal, sort the relationship descending by date & time.

The script creates the record; the data is current date, it set this in the Project Date1st field. Then it goes to the 1st data entry field in the portal, ready for entry.

You can also leave "allow creation of related records" on, and attach the script to the fields in the portal row. In that case you must take those 1st entry fields out of the Tab Order, or else people can bypass your script. Date & TimeStart would likely be auto-enter anyway.

If there were any field in Projects that you could say, "Yes, this will ALWAYS be edited when, or right after, a child record is created," then you could create a trigger in Projects. But it doesn't seem likely, and is a bit of a hack. Maybe someone else knows a trick, but I can't see how to trigger something in another table, without some edit in that table, or script.

You could also use a plug-in to trigger the script, if you really don't want a button. There are free ones available for 7. But, I don't mind clicking a button for this, as it allows the portal to be sorted descending, thus making it available for an infinite number of entries.

ChildLookupTrigger.zip

[Jon Grimshaw]

Can't thank you enough, Fenton. You helped me to the nub of the problem. Have used a script to sort it so that the ProjectStatus field can be indexed. Finds now done in about 3 seconds. I hope that when/if I ever get the full hang of FmP that I can also altruistically help beginners such as me. It's now 3.20 am local time and I can now finally get to bed.