updating a match field

[tphunt]

Know this is probably a basic question but am not sure best way to handle it. My main file references a second file of employees. The match field is employee ID. From the main file, I want my users to be able to change the name attached to a record in the main file by using the related name field on the master file layout...but what needs to be updated is the match field in the main file. I tried using a global field but realized you can't attach a value list to it. If someone could point me in the right direction, I would greatly appreciate it. Thanks in advance.

[CobaltSky]

You can attach a value list to a global field without any difficulty - I do it all the time. Select it in Layout Mode, then go to Format > Field Format... and you will be able to define your global field as a pop-up list (or whatever) based on any available value list.

What I suggest you do, however, is bypass the global field idea in this instance and instead use a different technique.

1. Create a value list which is based on the employeeID field in your employees file, and select the option to "also include values from' and select the employee name field. Check the option to sort by the second (name) field.

2. Select the employee name field on the layout in your main file, make sure it has an opaque fill then duplicate it.

3. Double click on the original copy of the employee name field and re-assign it to the EmployeeID field in your main file (not in the employee file). Then attach the new value list created in step 1 to it.

4. Select the duplicate of the employee name field (the one that is still attached to the employee name field) and turn off 'Allow entry into field'. Now position it over the top of the original copy (the one you've re-assigned).

That's it. Now you will see the front most field, but when you click on it, the pop-up list field (which is at the back) will activate and allow you to select a different employee - whereupon the employeeID for the employee you select will be placed in the match field in the main file.

When you click out of the pop-up list, it will jump back behind the employee name field and the name field will update to reflect the relationship to the new related record you've selected.

[tphunt]

It worked! If gratitude were gold, you'd be a wealthy man. Thanks for taking the time to answer.

[maxlundberg]

Cobalt Sky's recommendation works but only partly. The lookup field shows the employee id as the first field and the employee name as the second field. It is sorted by employee name, but because the employee id comes first, you can't begin typing the name and have it "look up" by name. Is there a way around this?

[CobaltSky]

If you are confident that the employee names are unique, then you might consider creating a look-up which brings the ID number ocross based on employee name - then the value list need not include ID. Alternatively, the match between files could be made on name rather than ID.

However, a more professional solution to this problem (which I've implemented for a number of clients) is to provide a pop-up window containing a portal listing all employee names, plus a 'filter' field into which you type a fie letters of the name and the portal list shrinks to show only the matching names. Once the desired name is visible, the user clicks on it and it is selected and applied to the current record (whereupon the pop-up window disappears).

The latter approach combines several advanced techniques, and works very well, but I must admit that a full description of it would be a mite long for a forum post...

[maxlundberg]

The suggestion of using a lookup to bring the employee ID number in works. It requires adding another text field to the main file that is related to the empoyee name field in the employee file. The employee ID field can then be pulled in as a lookup.

I also tried this using a global field. The problem is that each time the global field is used to look up the employee name, the employee ID in every record in the main file updates to reflect the change in the global field. That's no good.

With limited lists where the name and index fields contain unique values, using the lookup to pull in the employee id is a reasonable solution although it adds a field to to each record in the main file.

[CobaltSky]

Hello Max,

See also my reply concerning another angle on this issue, at:

Re: hiding text in value lists