Mulitple files trouble

[Donnie]

I have 5 tables. Equipment and Who_had_last are related on Inventory in both. When equipment is checked back into the warehouse, the user currently clicks on a button which changes the "checked_out_date" to the current date, and changes the "Assigned_to" field to that warehouse.

The changes that we need, is to keep a log of who had that piece of equipment last. My thought was to create a new table called "who_had_last." After this, I would create a script to copy this info over.

Once the button is pressed, FMP goes to run an external script on the "who_had_last" file. This script 1)creates a new record, 2)inserts today's date in the transaction_date field, 3)copy inventory_no and who_had_last over to this file, 4)return to the equipment file to finish out the transaction.

I have tried multiple ways to accomplish this to no avail. I get the transaction date in the equipment file, but nothing else. I have tried it with a portal on the equipment file, but nothing there either. The user does a find on the equipment file for that particular inventory number and then completes the transaction at this point.

Is there something that I am missing because I am nearly to the end of my rope. I have worked with FMP for nearly 2 years now and haven't encountered anything of this nature. Any help or suggestions would be greatly appreciated.

Thank you.

Donnie

Version: v6.x

Platform: Windows 2000

[CyborgSam]

This sounds like a relationship problem (no, not your love life )

How does "who_had_last" relate to the files with the info you want to copy? In other words: how does it know to grab the correct record's data?

[Donnie]

Thanks for the reply. My relationship is based off of the field Inventory_No in both tables. Oddly enough, I got it working, but the script is real cludgy. Is there a way that I can clean it up to make it better? Currently it goes like:

Copy [inventory No]

[select entire contents]

Perform Script [Filename: "who_had_last.fp5", "Ext_copy_Inventory_no"]

[sub-scripts]

Open [Filename: "equipment.fp5]

Copy [Assigned To]

[select entire contents]

Perform Script [Filename: "who_had_last.fp5", "Ext_copy_Assigned_to"]

[sub-scripts]

Open [Filename: "equipment.fp5]

Copy [Check Out]

[select entire contents]

Perform Script [Filename: "who_had_last.fp5", "Ext_copy_Checked_out"]

[sub-scripts]

Open [Filename: "equipment.fp5]

Perform Script ["Assign to Warehouse"]

[sub-scripts

The external script that runs has basically one command in it. It Paste's in the respective field in the "who_had_last" table.

The final command on this scripts executes another script that copies todays date and forces a certain Warehouse name into their fields, thus symbolizing that the piece of equipment was checked into that particular warehouse.

Go ahead and pick it apart, and if there is a better way, please let me know. It looks really rough at the moment.

Thank you.

Donnie

Version: v6.x

Platform: Windows 2000

[Ender]

While this scripted approach could work, you should be able to do this in a much more elegant way with a script and a relationship:

First create two relationships, one in Equipment between Who_Had_Last and Equipment, check the box to "Allow creation of related records". Create a second relationship in Who_Had_Last between Who_Had_Last and Equipment.

The first relationship is for creating a record in Who_Had_Last through a portal in Equipment. The second relationship is for the Lookups you should add to the data fields in Who_Had_Last.

Then have your script go to a maintenance layout, copy the Inventory No from Equipment, go to the last portal row in Who_Had_Last (did I mention you have to add this portal with field "Inventory No"?), then paste into the Inventory No field in the portal.

By the way, you don't need the Open [Filename: "equipment.fp5] script step to bring control back to the current file. The script executes on the file it resides in.

--Mike