There are probably several ways to do this. One would be via scripting, and I'm sure someone would advise on that.
The way I'd do it is to establish a relationship between the two files based on the seaman's unique ID. Then retire the "Sign Off" field in the original file (I would keep it for a while, or even permanently). Instead, define a Portal that contains the Sign Off field from the second file. That way you will be able to see the status from the second file as if it was there in the original file. And if you have other fields or scripts or calculations based on the original file's Sign Off button, you can make them dependent on the related Sign Off field.
Basically, you don't actually need to import the Sign Off status from one file to another - relationships are a more elegant solution.
I must not have explained it properly but there is only one file with two tables. The tables are related by the membership number. From this I have one form which is the entry screen and many records come from this. Every time someone logs in a new record with a lot of information on it including a sign off field which would at this stage be empty and so it goes on and on but when someone signs off a new record is made but this time the sign off field is ticked now from this I want to sign off the origional record which could be bacl 50 records or more.
I don't understand why a new record is created when the person (who already has a Sign On record) signs off? Why not have the Sign Off field in the original record, and have it updated when the person signs off? (I'm not understanding the design very well).
It is not possible as you have many records and when someone calls in you don't know until near the end of the call he is signing off he could be signing on and I have made the form full of drop down and check boxes to speed up entries so to search for the member would be difficult it he wasn't there. There is a second reason as each seperate call is recorded for statistics so someone calling in to sign on and to call in to sign off is two records for statistics.
I can achieve what I want manually by using the reports and ordering by number as the calls are adjacent so it can be done there but I want to get it a bit better.
The way the script should work is.
When the sign off is completed by a check box the script goes and finds exactly the same record by member number and as the sign off is blank it then updates the previous record to sign off.
I can get a script to work but it updates all the records irrespective of member number.
Ok, I think I understand.
Any chance you could paste your script in here?
I will have to go and make it up again as I deleted it when it didn't work.
Would it work to have a script that checks when you enter someones name whether they have an "active" record, find memberID and IsEmpty(signoff), if true it brings up that record so that you can check the sign off field, if false create a new record.
I'm not quite sure how you are defining "active record", but as for the rest of it -
Find MemberID - check
do IsEmpty(signoff) - check
(But) if True, you simply want to Set Field (signoff)
if False, then create new record - check
by "active" record I meant someone who was signed in but not signed out, assuming that once they were signed out the record becomes a history of that deployment and anyone having a record that does not have a value in the signoff field is at sea.
I would set up a layout with a global field to enter the member number when the call comes in, then have a script perform a find with 2 requests, GlobalField =memberID and IsEmpty(signoff). If there is a found record (the member was at sea and is calling to sign off) go to the layout where you can edit that record. If the find returns a "no record found" error (the member is not at sea and is calling to sign in), go to the layout where you enter data and create a new record.
Sounds good - except for "go to the layout where you can edit that record" - you can use the Script to Set Field (signoff) to whatever value you want in there.
I thought that too, but the statement "you don't know until near the end of the call he is signing off " made me think it might be better to just go to the layout first and let the user determine whether it was really a signoff call or maybe some other data needed to be modified. After re-reading that paragraph, you may also want to have the script check that the MemberID is valid - in case the call is for a new member and a new member record is needed.
As far as having 2 records to record the statistics on the # of calls - you could have a calculated field that Case(IfEmpty(signoff); 1: 2), then total that calculated field to have the total # of calls.
Another option may be to show the signon and signoff fields in a selfjoined portal (filter be decending dates so the most recent is on top). This would allow you to enter the data in the sign off field without have to search back through all of the records.