Jump to content

Related Audit


Genx

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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

"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! :P

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

Edited by Guest
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
sort
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6490 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.