Relationship to a calculation field

[fleecy]

Hi!

I'm new to this board... I looked everywhere, but I can't seem to find the answer to my problem.

I'm working on my first FileMaker 7 project. It's got 12 tables already. My main table keeps appointments that need to go through 4 steps of approval, and I need to keep track of who changed a record's status fields and when. FM7 will only remember a record's creation/modification dates, not a single field's, so I created 4 tables (one for each step of approval). Each table is linked to the main one through a sequential number field and has fields for the status parameter, modification date & user. I finally created a calculation field in the main table, using if() to make it show the current status of the record.

Here's my problem : I'm trying to establish a simple user interface to display a list of records according to their status. I've already made one that can display a list of records within a date range and for a particular person; a small table with those fields, a 3-criteria relationship and a portal worked great. I want to do the same this time around, but I don't get any results. I created an "=" relationship between my calculation field and my 2nd small table's status field (and I set that field up with a value list that shows each possible result from the calculation field). No go.

What am I doing wrong?

Any help would be really appreciated!

[CobaltSky]

Hi fleecy,

What you are wanting to do can be done, but not the way you are going about it.

But first, your whole problem arises from a starting assumption that is not correct. You have said " [color:"666666"]FM7 will only remember a record's creation/modification dates, not a single field's". However there are various ways to do this and much more - they just require a little setting up. If you would like to check out an example of a method by which a detailed history of changes to one or more specific fields can be captured, you might like to take a look at the free download file at:

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

Using techniques such as the one mentioned above, it should be possible to do what you want to do with ease and without artificially spreading your appointment and appointment status data across five or more tables. Your present problems arise directly from the fact that the data structure you've adopted reflects a specific (perceived) functional objective rather than properly representing the properties of the data.

Notwithstanding that, the reason that your present attempts to relate the table to itself based on the results of your status calculation are not working is because the calculation in question references data from outside the appointments table and therefore it cannot be stored or indexed. Relationships depend upon the target field being indexed.

If you wanted to persist with your present structure (though I woudn't advise it...), you would need to flip it around by using the unstored calc as the *primary* key value for one or more relationships to your status tables, then add table occurrence(s) of the appointments table on the *other side* of the status table(s), and base your portal(s) of appointments by status on these remote table occurrences. Workable perhaps, but not ideal - especially since there are some rather more elegant solutions to your orignal problem which, if used, will allow you to embrace a considerably simpler structure.

[RalphL]

Take a look at Audit Log

http://elcellisys.com/services/fmp7/tips_tricks.hml

[bruceR]

"so I created 4 tables (one for each step of approval)"

Why not ONE table with separate records for each approval state; or with a single record per parent record but separate fields for each approval state.

[CobaltSky]

BruceR said:

"so I created 4 tables (one for each step of approval)"

Why not ONE table with separate records for each approval state; or with a single record per parent record but separate fields for each approval state.

Indeed. But why not a single status field within the parent record, with appropriate tracking of its modification history??!

[bruceR]

Yes, the tracking you mentioned would be better but I also think fleecy needs to work on some relational concepts judging from his approach to this problem.

[fleecy]

Thanks for all the help, everyone.

I used CobaltSky's technique and all is well now.

I am a beginner in relational databases, I'll give you that. FileMaker used to be pretty bad at handling those anyway, so I never concentrated much on the subject. I have been using FileMaker since version 2, so most FM concepts aren't new to me.

FM7 is a whole other story, however, and I'd love to get to learn more about those relational concepts BruceR is mentioning. If you have links or other good references on the subject, I'll put them to good use.

I don't think I'm alone in this position, actually; FileMaker _is_ an entry-level product, after all. It's aimed at first-time users too! I'm sure your references will help them too.

[fleecy]

About those 4 tables :

I wanted FM to remember the creation date/time/user specific to each state (proposed, hold, approved, canceled). In other words, as soon as a user would click "hold", FM would display who did it and when and keep that information available as information that can be used in reports later. I could not find a formula or script that would help me do this without isolating each state in a table, so I ended up with 4 "micro-tables".

CobaltSky was kind enough to supply me with a more elegant solution that won't let me use the log information in future reports (since I won't be able to sort log entries, use them as separate records or anything like that), but that makes my initial problem go away (and that's much more important). Thank you, CobaltSky!

[bruceR]

The point is that if it were to be a separate table, make it ONE table because it is about one kind of data, and approvalState is an attribute of the record. In a contact management system you wouldn't make a separate table for Jones, and another for Smith, and so on. A very good book on database concepts and Filemaker concepts is Filemaker 5.5 Advanced Techniques by Moyer and Bowers. It has a very good section on general database concepts.

Do a Google search on normalization.

[fleecy]

I know it's all one kind of data, and I tried to make it all work as one table initially. FileMaker's creation/modification date/timestamp features are limited to records, and can't be setup to work only on specific fields in records (unless I'm mistaken), so that's why I tried a multiple table solution (to put one field in each of them and have FileMaker track the creation date/time).

Thank you for the book recommendation, BruceR; I'll check it out as soon as they release an updated FM7 version.

[CobaltSky]

Hi fleecy,

The method I suggested gives you a way (one of many possible ways) to *capture* the information about field edits.

It's then a relatively straightforward matter to have a script parse out the contents of the log field to a separate table in whatever format suits your purposes (eg one record per change, separate fields for recordID, status, date and time of change etc etc). This can be the first action of a script that constructs a report in any form you deem suitable.

If desired, having captured the data, you could construct a number of such scripts to render the data in different configurations, simply by varying the parsing procedure and/or the destination(s) for the write-out of the lines of the log field.

Also, please note that it *is* possible to set up a modification time field which watches a specific field rather than the whole record. Simply define a stored calc (or auto-enter calc in v7) with the formula:

If(IsEmpty(YourField) or 1; Get(CurrentTime))

The resulting field will grab the current time whenever the referenced 'YourField' field is modified, and will store it until the next change occurs. Simple. this technique works equally well in all recent versions of FileMaker.

The reason I suggested the more comprehensive log procedure rather than this technique is because it seems you want to make use of the data for reporting or analysis purposes and the log process can retrieve much more data in a very compact way (including a history of changes rather than just the most recent change). You can always discard data you don't need but you can't use it if it hasn't been captured in the first place.

[The Shadow]

Ray,

Just a micro-optimization, if instead the calculation used is:

If ( True or IsEmpty(YourField); Get(CurrentTime))

then Pro doesn't need to grab the contents of "YourField" when executing this, since short-circuiting boolean calculations will prevent it from being reached.

This also provides me with an opportunity to push the use of the new True, False, and

[CobaltSky]

All good stuff. Thanks.

Interesting to note that the distinction between dependency (on a field) and referencing (retrieving its value) is now more than theory...

Built-ins duly noted also!

[bruceR]

1. Previous posts showed an audit technique which does allow individual field tracking.

2. If you were going to use a related table, then you would use different RECORDS in the same table for each approval status. The records would carry the creation date/time.