Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Logging over time, calcs to get all field names?


beju0506
 Share

Recommended Posts

Ok guys, we've got another puzzle for you all. :)

First issue:

We are attempting to use a logging function we're implementing. (an audit function that requires field names to be input as parameters) We want to keep track of the changes in basically all of our fields. However, because some of our layouts are quite large, it would be a really huge project to manually enter all of the field names into the calculation function that calls the audit( ) function. Also, because we're probably going to be changing layouts, adding/removing fields, etc, we would like to be able to define them on the fly. Is there a function that anyone knows of that would allow someone to get the names of all of the fields in the table and delimit them with say a bullet or a pipe (|) or something? That way we could put them into our function on the fly...

 

Second Issue:

We would like to be able to keep "copies" of deleted records over time. For example, if we have a software item that costs 10 dollars when we first purchase it, then we delete that record, then a year down the road we put a similar item in but it costs 25 dollars, we'll be able to look back and see what the original price was a year ago...? Its not so much the comparison we're worried about, its the initial capturing/storage of the record and exporting to another table for posterity. One method that was discussed was having two scripted "delete" buttons that one is for actual deletion of the record (for actual mistakes - like an administrative delete) and one is for normal deletes (where we would need a copy of the record, or at least certain information from it)...

 

Pardon us if this is something simple we've overlooked, we're a bit new to FM, so we're still working out the kinks. :)

 

Thanks!

 

-Justin

Link to comment
Share on other sites

1St Issue

 

See Ray's SuperLog

http://www.nightwing.com.au/FileMaker/demos8/demo809.html

 

Hint look in the define custom functions.

 

2nd Issue

Do not delete the records until you have archived them to a

table that is a clone of original table. Relate it to the original table

with fields defined to lookup original records. Call the table

Completed or Obsolete. Move the record via a portal before deleting it from an original table. Now if you ever re-instate a record via

a relationship (portal) you will see the old record.

Link to comment
Share on other sites

Hi Justin,

 

Second Issue:

We would like to be able to keep "copies" of deleted records over time.

I'd do this a little differently. Instead of actually deleting those records, I'd leave them in, but mark them in such a way that they can be filtered out of Finds and relationships. Then for those processes that need to include the deactivated records, simply use the unfiltered find or relationship.

 

In my opinion, if a record may be needed in the future, it's better to keep it in the existing table, than to try to move it to another. There are too many opportunities to lose data when using a scripted operation that imports and deletes.

Link to comment
Share on other sites

Thank you both for the quick replies! :)

 

Well, regarding the first issue, we've tried the "SuperLog" and found that it doesn't consistantly show the original data that was changed. A lot of the time, it would just show the null marker "[---]" as being the original data when it clearly wasn't. This created some concern for us about validity of the logs.

 

The idea Ender presented about keeping the records in the same table and just renaming them is a good one. It seems like it will be a bit of a task resetting all of the finds/relationships in the DB, but it would most likely be so with the scripting and everything as well. So I guess we're in a catch-22.

How would we set up the relationship to filter out the "dead" records? I'm visualizing something to the effect of having a field called "DeadRecord" and having a yes/no value in it. How would we set portals/finds, etc to not show these?

 

Thanks again for the help! (also, Ender, you enjoying this fine change in Minnesota weather? ;) - We're in St Cloud)

 

-Justin

Link to comment
Share on other sites

Another issue: We found the function that gets all of the field names for the table/layout (Get (FieldNames)) but with the functions we've tried for the audit log, none of them are able to use the list when it is acquired. For example, say we don't want to manually enter all of the field names into the function when setting the log up for a table. We want to use the "Get(FieldNames)" function. But what comes out is a CR delimited list of the field names, both local and related (from this table and all related tables shown). When we try to use any of the audit-type functions, it won't take this list. For example:

 

if the function is:

 

audit(fieldnames, logfield)

 

and the instructions tell us to enter the data in this manner:

 

audit( field1 & "¶" & field2 & "¶"; logfield)

 

but if we want to be able to enter the field names on the fly, we would want to do something like this:

 

audit (Get(FieldNames); logfield)

 

See what I mean?

 

This is the audit function we're using at the moment because it seems a lot more reliable in picking up the previous data before changes:

 

http://excelisys.com/services/fmp7/tips_tricks.htm - it is the "AuditLog" function

 

Any ideas?

 

Thanks!

 

-Justin

Link to comment
Share on other sites

Ender: I'm not sure exactly how to implement what you were referring to with the active/passive setup for the history? If we had, say a field called "ActivePassive" and it had either "Active" or "Passive" as a value, how would we prevent records marked "Passive" from showing when opening a layout for the first time, between relationships, etc?

Link to comment
Share on other sites

On the parent side of the relationship, add a text field 'gActive' with global storage, then in Browse Mode insert the text string "Active" (no quotes) into the field. On the relationship graph, you can then add this field to the relationship criteria between parent and child tables, so they look like:

 

Parent Child =

Parent::ParentID = Child::ParentID

AND Parent::gActive = Child::ActivePassive

 

This relationship will then show only those child records that are marked "Active".

 

For Finds, you can simply add "Active" in the ActivePassive field for the criteria.

Link to comment
Share on other sites

Ender, thanks again for a great solution! Now i'm starting to catch on ;)

I'll give that a shot and see if I can make it work!

Link to comment
Share on other sites

Ender - I've gone back and tried to do this, but we're running into some snags. To be able to re-script everything and change the relationships we have in order to filter out active or passive records would take a monumental amount of work. We have numerous relationships that would be affected. I know it isn't the best way, but it would certainly be a lot more straightforward to be able to click a button to "delete" a record that first copies it to another "deleted record" table and then actually deletes it from the original table. I've looked around and I know the filtering concept seems to be the popular one, but is there any way to actually copy a record from one table to another?

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use