Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
370 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 25

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

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
 

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 25

Accepted Solution

by:
Will Loving earned 2000 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 25

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

916 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