Need help Inserting a record into an audit table based on action from a form with and without ODBC

Greetings:

When a user clicks on a button on a form for the Product Line Items database, the following script is run:

Set Error Capture [ON]
If ["Status(CurrentFoundCount) > 1"]
  Exit Script
End If
Delete Record/Request [No dialog]
Show All Records

Open in new window


I want to add functionality to insert a record into the AuditTrail.fm5 database before the Delete Record/Reques [No dialog] line. I was first going to execute a SQL call, but am being asked to install the ODBC components before using FileMakers ODBC Import feature.  Assuming my client no longer has his original install disks (I'm working on a spare Macbook he provided"), how can I do this with native commands?  

This is an urgent request.  Thank you.
ITMikeKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Will LovingConnect With a Mentor PresidentCommented:
You will need to create a global and non-global field for each of the fields you wish to set in the Audit Trail file. The purpose of this is so you can set the field value for the Global, which does not require a functional relationship between the two files, and then use the value in the globals to populate the non-global values in the Audit Trail record.

This will require a relationship between ProductLineItems and AuditTrail, but you were probably already prompted to create that when you tried to set the field value in Audit Trail directly. The relationship could be from InvoiceID to InvoiceID if you want.

I have to work from an older computer so I'm not able to upload screen shots, but the attached example file demonstrate how to setup the scripts and how to call the AuditTrail script as an "External" script from within the main Delete script.

OK....for some reason Expert-Exchange is not letting me upload example files with the .fp5 extension. I'm going to report it but in the meantime, I'm simply going to remove it. After you download these files, but before you launch them, please append .fp5 to the end of each file.
AuditTrail
ProductLineItem
0
 
Will LovingPresidentCommented:
1) Just a note that the correct file extension for FileMaker Pro 6 is .FP5, not .FM5.

2) Create the script in the Audit Trail file to: Go to layout, create new record, set whatever fields you need to set in the new record, return to the default layout, and then "Hide" the file.

3) In your Product Line Items file, after step 4 you'll need to insert a few steps.

a) If you want to pass any values to the AuditTrail file, put those values into one or more Globals fields (or just use one and multiple Repetitions if you understand how to do that). You can add them to either file but if you're going to create Audit Trail records for other actions you might want to put them in the Audit Trail file.

b) Add a Perform Script Script step and select "External Script". This will prompt you to select the AuditTrail file and from there you can select the "New Record" script. If you have values in global fields that you want to pass to the New Record, then you'll need to go back and include Set Field steps after the "New Record" step that reference those global fields.

Set Error Capture [ON]
If ["Status(CurrentFoundCount) > 1"]
  Exit Script
End If
Set Field [ AuditTrail::SomeGlobalField1 ; Some ProductItem field value1 ]
Set Field [ AuditTrail::SomeGlobalField2 ; Some ProductItem field value2 ]
Set Field [ AuditTrail::SomeGlobalField3 ; Some ProductItem field value3 ]
Perform Script [ External ; "Add Audit Trail Record script" ]
Delete Record/Request [No dialog]
Show All Records

Open in new window

0
 
ITMikeKAuthor Commented:
Thanks for the quick reply.  I'll check this out in a few hours and let you know how it worked.  Looks good on paper to me!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ITMikeKAuthor Commented:
I'll have to actually try this when I'm in front of the Mac in a few hours.  I already created the AuditTrail.fp5 database.  I then went to "Script Definition" and made a new script called "NewRecord".  The first line of this script would be "New Record/Request"?
0
 
Will LovingPresidentCommented:
You can call the script whatever you want. The point is to create a record, set the values that you are wanting to preserve and then hide the file.

For what it's worth, I'd encourage you to consider switching to any version of FileMaker after 6. FileMaker 6 requires each table to be a separate file, while FM 7 and later allow you put multiple tables (along with their layouts and scripts) within the same file. This the whole development procedure much easier. Instead of having to make one script in Product Line Items that calls another (External) script in separate AuditTrail file, you can have it all in one. If you try to merge the table into a single file later, it requires care and you can "break" things that then need to be fixed.
0
 
ITMikeKAuthor Commented:
I totally understand that FM6 is way obsolete and an upgrade plan is in place (requires several new machine purchases, etc.), so this is a bandaid.  What I've done:

Created and Audit database:

Audit ID    Number  Auto-enter Serial
Audit Date  Date      Creation Date
Invoice ID   Global   Number
Product Line Number ID Global Number.

Created a script for Audit db called "NewRecord"

The only action I have now is New Record/Request

In the Product Line Items db, I edited the "DELETE this line item" script (from above)
and added a "SET Field" command.  I clicked on "Specify" and added this in the window:

[ Audit::Invoice ID ; Invoice ID]  when I click "OK" it says "This field cannot be found".  I am missing something?  Thanks!
0
 
ITMikeKAuthor Commented:
Checking these out now.
0
 
ITMikeKAuthor Commented:
Thank you!  I have a few fields from the Product Line Items that are at end of a relationship between the Product Line Items and Product Sales that aren't making it back the Audit Trail db, but this satisfied my immediate need.  Thank you!
0
 
Will LovingPresidentCommented:
If you have a relationship from the AuditTrail back to the Product Line Item file (relationships ships are NOT bi-directional in FMP 6 and earlier), the you can simply set the Auto-Enter values for those fields in the AuditTrail to use "Specify By Calculation", specifying the fields in ProductLineItems. When you populate the ProductLineItem ID field in AuditTrail, those fields should automatically populate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.