Solved

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

Posted on 2013-06-17
9
353 Views
Last Modified: 2013-06-22
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.
0
Comment
Question by:ITMikeK
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Will Loving
ID: 39254034
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
 

Author Comment

by:ITMikeK
ID: 39254086
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
 

Author Comment

by:ITMikeK
ID: 39254142
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 39254601
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ITMikeK
ID: 39254826
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
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
ID: 39254934
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
 

Author Comment

by:ITMikeK
ID: 39255066
Checking these out now.
0
 

Author Closing Comment

by:ITMikeK
ID: 39267631
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 39268142
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now