Related Audit

[Genx]

Hi, before anyone goes yelling at me to search, hear me out.

I have 5 tables, one main table, and four sub category type tables. I need to be able to monitor for value changes in the sub tables ... from the main table. Keep in mind that all tables have only a one-one relationship with the main table. I was thinking of using something along the lines of the CF provided by nightwing, but the issue with that is it can't monitor related values... then i started thinking that i could have 5 different fields (one for each table) with each one monitoring individual changes... but i need to compound these into one sorted main calc field ... and to be perfectly honest we all know i'm not that great at optimising calculations. Could anyone please help me out?

The format would always contain a timestamp as the first... i cant count but i assume 20 characters in a line.

Cheers,

~Genx

[comment]

the issue with that is it can't monitor related values

The issue is not with the custom function, but with Filemaker - Filemaker does not monitor related values, so your calculation/s in the main table need to be unstored.

I am not sure what "compound these into one sorted main calc field" means.

[Brian C]

When you say you are trying to track changes - I assume you have a mechanism to know if something has changed... If this is the case you could use a binary result 0 or 1 in your related file to track if something has changed. i.e. if a change has occurred it would be a 1, otherwise it would be a zero.

In your main table you could then create a calculation field that adds these fields all together, so that at any time you could then check to see if your main calculation is greater than 0.

This may not be what you are looking for - but perhaps it may point you in a useful direction...

[Genx]

comment - I realize the issue isnt with the CF.

What i wanted was the following. Lets say i'm monitoring in 5 different tables and i want to compound all these fields in to one main field using an unstored calc e.g. with 2 tables see below:

Changes In table 1

18-03-06T14:30 FieldChanged: xxField1xx

17-03-06T13:30 FieldChanged: xxFieldxx

Changes In table 2

18-03-06T13:30 FieldChanged: yyField2yy

17-03-06T13:31 FieldChanged: yyField1yy

17-03-06T13:28 FieldChanged: yyFieldyy

Gives me combined changes somehow in unstored calc:

18-03-06T14:30 FieldChanged: xxField1xx

18-03-06T13:30 FieldChanged: yyField2yy

17-03-06T13:31 FieldChanged: yyField1yy

17-03-06T13:30 FieldChanged: xxFieldxx

17-03-06T13:28 FieldChanged: yyFieldyy

Like i said, i was thinking of somehow re-arranging them using somesort of timestamp filter... i know the format at the begining isn't the real format but still.

Cheers for any help,

~Genx

[LaRetta]

So you want an audit trail of the file rather than each individual table? Expensive proposition. It's a bit more work to set up in each table but it would be faster. Whatever you do, don't display that calc on a table! It might make more sense to write them to records.

Edited June 20, 2006 by Guest

[comment]

There are several CFs that can sort a list - none of them very fast. At the rate indicated by example, I suppose it could hold out for a week or two.

Of course, you could script turning the list/s into records in a new table, but - why aren't they all in the same table to begin with?

[Genx]

Well no, just 5 particular tables out of 50. I don't really care about most changes, these tables sort of just contain critical info that needs to be audited.

Just to clarify again what i was suggesting was 5 seperated audit fields in each of the tables and one unstored calc to bring them all together and sort in the main table. I'm really no good at the optomizing so i was really hoping one of you guys might be able to help.

Why do you say not to display the calc on a table..? I don't get it?

Cheers,

~Genx

[Genx]

They aren't all in the same table to begin with for the following reason:

Basically, its four types of a certain thing. Each type has certain characteristics however they also have a set of common characteristics

Main Table (90 Common Fields)

- Type 1 (50 not common)

- Type 2 (50 not common)

- Type 3 (50 not common)

- Type 4 (50 not common)

I.e. one thing will only have 130 fields (a one-one relationship with one of the sub tables and the main table).

I purely didn't want 290 fields in one table, or 4 seperate tables because my scripts work on the sets "things" in general.

Cheers,

~Genx

[LaRetta]

"Why do you say not to display the calc on a table..? I don't get it?"

Well I pictured 50 tables with average 50 field fields each, all tracking every change every second (as your example indicated) ... can you envision the string which would be produced? And it won't be produced until the moment it is displayed. And it will be drawn for EVERY record in your table list (and Lord forbid you aren't displaying all 500,000 of your lineitems in the moment). Not only that, you want it to sort as well...

If you want to see what ugly looks like, try it some time. I have but not on purpose!

Oh - and then the topper is to sort your total record set. It's like watching a run-amok CF.

Edited June 20, 2006 by Guest

[Genx]

I'm sorry, still not making myself 100% clear, theres only around 200 items in the main table at the one time and therefore less in the sub tables... The audit trail is only viewable in one section and only there for the purpose for management to check that details have been changed and at what time and date - i.e. the access to it is minimal but required.

Then again, i could throw this all to nothing and just use event script to generate a related record each time there is a change to any of the fields.

EDIT: Oh and it's not viewable in list view... or table view

EDIT2: The audit is on a per record pasis i.e. the audit calc for record one only records / shows the audit for the current maintable record and its sub table trail.

~Genx

Edited June 20, 2006 by Guest

[LaRetta]

Yes, you made yourself clear AFTER my post, Genx.

And yes, records would certainly be more logical in my opinion. But, since each audit trail affects each PART of your structure, why would you need them together? Are you wanting to track all changes to a customer (for example) and want to include changes to their Addresses, Numbers, People, etc? ADDED: If so and it's a one-to-one, plop the child audit on the parent layout.

So yes, I'm clear now that you are talking a minimal number of tables/fields. :wink2:

Edited June 20, 2006 by Guest

[Genx]

Yeh thats a perfect example a customer with addresses, numbers and people - all in a sort of related structure where i want to bring all changes to related tables back to the customer.

EDIT: Making it easier still we're only using one-one relationships so each customer only has one address, one number sort of thing (ignore the illogical...ness of this fact).

~Genx

[Genx]

Now that we've worked out the scenario, can someone actually recommend a custom function for this?... please?

~Genx

[Razumovsky]

Just posted this for a different thread but should work here:

Custom Function: Concatenate

Parameters: field, start, number

Calc:

Case(start < number; getNthRecord(field; start)&¶ &concatenate(field; start + 1; number);

start = number; getNthRecord(field; start)

)

Then your calc field skeleton would be:

concatenate(Related1::AuditField; 1; count(related1::ID)) & ¶&

concatenate(Related2::AuditField; 1; count(related2::ID)) & ¶&...

Ah, forgot about the sort... Check out this one here. Use concatenate function as the list parameter, and you should be able to modify this to suit your needs.

Actually, if your audit occurences will ALWAYS be only one line, you could just use value lists to sort them.

-Raz

Edited June 21, 2006 by Guest
sort

[Genx]

I'm trying the value list, but to be honest, it seems easier to just use event script to generate related audit records at the moment now that i've set it up... Plus i don't want to use the real field names anyway.

Thanks for the help anyway,

~Genx