We have a database of medical accounts.  Often a number of accounts are related in some way, usually either they all involve the same patient or they involve a number of patients who were in an auto accident.  We currently have a QA database with a multi-line field called Status Report and a separate History field (on a different screen).  For multiple files, the person working the accounts simply manually enters See Account #234567 in the Status and History fields, and all data is entered into just one record.

I am putting together a Sesame replacement and I have added a field called Main File and a Main File pushbutton. Each record has a unique "File No". In Update mode:

* If the Main File field is empty, pressing the button pops up a dialog offering to set the Main File field to File No.
* If Main File equals File No, a dialog pops up saying so and that the user can enter a new number in the Main File text field.
* If the Main File record exists, the user should be moved to that record.
* If the Main File record does not exist, a dialog pops up saying the record may have been deleted, etc.

My question is how to handle "jump to the Main File record".  Currently I use XLookup(@Filename ....) so I do know for certain whether the record exists.  But how do I jump there? (Right now it just pops up a dialog saying jump needs to be coded.)

I've been thinking this over as I've been writing this and I'll use @SpecCommand() to search for all records with that Main File number, since the user will often want to work with the whole set.  But within that set I will need to go to the record where File No = Main File.  There is no particular sort order--Main File could be the lowest File No, highest, or anywhere in between and the File No's of the different records often would not be sequential.

I don't want to do a new search.

Right now, here's what I have:

File_No (field) = Main_File (field)    // I'll call this the Main Record.

I am in any record, whether the Main Record or some Other Record. (I'll call this the Current Record.)

When I click the Main File button it checks whether the Main_File field in the Current Record is empty.

If the Current Record's Main_File field is not empty it checks whether the Main Record exists.

If the Main Record exists, it sets a Retrieve Spec and runs a search that pulls up all records where Main_File = Main Record.

It then switches to Table View.


EXAMPLE:

Debtor Smith File No = 12345
Debtor Smith File No = 23456
Debtor Smith File No = 34567  This is Main File.  (Client Ref # for this is ab12345)
Debtor Smith File No = 76543

Debtor Jones File No = 87654
Debtor Green File No = 65432
Debtor Green File No = 98765

All 7 accounts for 3 different people have Main_File = 34567

No matter which record I am in, clicking the Main File button will pull up all 7 records in a Table View.



I am in the process of adding an automatic cross-reference--an Update Related Files button.  Clicking that will automatically add to the end of each related file's History field:

(date) See File No. 34567 / Client's Ref # ab12345 for current status. (user initials)

However:

1) After the "see File ..." entries the user needs to end up in the Main File record, in case she didn't update that file's History.

2) The user may also need to work with each entry in the set:

(a) "Let me check that the notes really did get updated 'just to be sure'."  (We're a law firm, so we do a lot of things "out of an overabundance of caution", e.g., writing that something is "null and void and of no force and effect" instead of just "void".)

(b) Although I am also changing the Last_Update field, I am not changing the Follow_Up field.  Different files may have different follow-up dates even though most of the notes are kept in the Main Record.

(c) The user may need to access various records in the result set for a variety of reasons.  I think the normal expectation is that if I just did an automatic update on 7 records by clicking a button on the form, I should be able to move back and forth among those 10 records without having to do a new search.  (The users don't deal at all with "Mass Updates", etc.)



I'm thinking there should be a way programmatically to look through the result set and set the Current Record to a particular record (in this case, the only record where File_No = Main_File) without running a search.  Realistically, we'll never have more than 15 Related Files, and we're running late-2010 hardware with Win7 / Win2008 Server, so speed isn't an issue.

take a look at FormResultSetCurrentPosition()

This will let you 'jump' to a particular record in your existing batch.

I did wind up using the loop to get to the record.

There is a problem with this specific code, however: although in theory the Main File should always have its Main_File field equal its File_No field, in practice some don't.  So instead I have a declared variable MainFile and I compare to that.  When it finds the record, if its Main_File field is empty I just set Main_File = File_No.  I haven't quite figured out what I want to do if the Main_File field contains some other value.  (Most likely it would be due to a typo, but not definitely.)