Passing a table name as a script parameter

[jim0203]

I've written a script which copies data from a field which exists in multiple tables in my database, and then pastes the data somewhere else.

Is it possible to use a script parameter so that I can have one script which will copy from different tables, depending on the parameter that is passed to it? When selecting the "Cut" option in ScriptMaker, I don't seem to be given the choice to cut from a field based on a parameter; I have to select the field from a list.

[aldipalo]

It's not considered good policy to cut and paste. You should be using Set Field and variables. Btw, do you really want to 'cut' the data from the current field, then it will no longer reside in that field?

Set Variable($Field; TableA::Field A)

Go To Layout(TableB)

Set Field(TableB::Field B; $Field)

Also, It's sounds as though you have multiple tables with the same fields. You can get around this approach completely by using relationships. Then you need only place the field from the parent table on the layout for the child table.

You can also use look ups or calculated fields once your relationships are set.

Perhaps are more complete explanation of what you are trying to accomplish will make us able to give you the best approach for you.

hth

[jim0203]

Thanks for your reply. What I'm trying to do is a bit strange, I admit!

Every table in my database has a field called "Audit_Log". Whenever another field in the database is changed, the Audit_Log field for that database is appended with details of what was changed: the field name, the old data, the new data, who changed it, and at what time.

So the Audit_Log field builds up while changes are being made to a record. Then, when the record is committed, all the data is cut from Audit_Log and pasted into a field in a table called "Log Processor". A script then transfers the data into records in the table, so that it can be read easily by reports etc.

So I do need to have the data disappear from the first table when it is transferred to the new table.

Any ideas on the best approach here?

[aldipalo]

Yes, you can still use set variable and set field.

Set Variable($Field; TableA::Field A)

Set Field(TableA::FieldA;"")

Go To Layout(TableB)

Set Field(TableB::Field B; $Field)

Set Variable($Field; "")

Go to Layout(Original Layout)

To check what layout and do an action based on that layout:

Set Variable($Layout; Get(Layout Name))

If($layout="Layout TableA")

Set Variable($Field; TableA::Field A)

Set Field(TableA::FieldA;"")

Go To Layout(TableB)

Set Field(TableB::Field B; $Field)

Set Variable($Field; "")

Go to Layout(Original Layout)

EndIf

If($layout="Layout TableB")

Set Variable($Field; TableB::Field A)

Set Field(TableB::FieldA;"")

Go To Layout(TableC)

Set Field(TableC::FieldC; $Field)

Set Variable($Field; "")

Go to Layout(Original Layout)

EndIf

Something like that should work. Of course it will need a little tweaking.

hth

Edited February 20, 2009 by Guest

[jim0203]

I see what you mean, that is much more elegant than cutting and pasting.

However, I'm still a little stuck with my initial query: namely, how to pass a script parameter (name of a field) into a script effectively?

I'm guessing that I can do it via "Set Variable", but when I use the following:

Set Variable [$$FieldToAudit; Value:Customers::Audit_Log]

I end up with the variable being set to the value of that field rather than the name of the field. How to I get the variable to set to the name of the field, in a way that I can then use that variable in my script (in place of where you have written "TableA::Field A" in the first line of your script?

Is it simply a matter of enclosing the name of the field in quote marks, like this:

Set Variable [$$FieldToAudit; Value:"Customers::Audit_Log"]

Or am I missing something?

Thanks once again for your help.

[mr_vodka]

With 9, you have to hard code the name as part of the parameter. Get(ActiveFieldName) will only give you the active field focused. Get(LayoutTableName) will return the current layout's table name so you may be able to use that possibly. Why are you doing it this way anyway? Why not use onf of the many audit trail techniques posted on these forums.

[aldipalo]

Is the user already in that field?

Get(Activefieldname)

Else

Go To Field(FieldA)

Set Variable($Fieldname;Get(Activefieldname)

Get(ActiveFieldName) function

Format

Get(ActiveFieldName)

Parameter

None

Data type returned

text

Description

Returns the name of the field currently containing the cursor.

[jim0203]

With 9, you have to hard code the name as part of the parameter. Get(ActiveFieldName) will only give you the active field focused. Get(LayoutTableName) will return the current layout's table name so you may be able to use that possibly. Why are you doing it this way anyway? Why not use onf of the many audit trail techniques posted on these forums.

Hmmm, it looks like I'm in a bit of a bind, as even if I can use Set Variable to get the audit trail stuff, I can't then use Set Field to clear the field that contained the audit trail as Set Field doesn't accept a variable as the field name. Dammit.

I'm using this method because it's the first one I worked out (I'm a total n00b). I've now searched this forum for audit trail techniques and there seem to be quite a few. Can you recommend one in particular? I've got a database with 10 tables, and every time any field in any record in any table is changed, I want an extra record to be created in the "audit" table which includes who made the change, what table and field were changed, when the change was made, what the change was from, and what it was to.

Many thanks once again.

[mr_vodka]

Audit Trail Links

[jim0203]

Thanks for that. I think the one at

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000721

will be good. However, in the description it says that

You will need to install and activate the FileMaker Example plug-in for this to work (on the install CD)

I've looked on my Install CD (I'm using a Mac) and I can't find the Example plug-in.

Once I've got an answer for this I'll leave you alone, I promise!

[mr_vodka]

I am not 100% sure of its location on a mac but it may be simliar to this.

http://www.fmforums.com/forum/showtopic.php?tid/200454/

[jim0203]

Thanks John - I didn't need it in the end as I've followed a different strategy, but thanks anyway.