Value list based on another field

[posternutbag]

Hello,

I've read through posts regarding what seem like similar situations, but I am afraid I just can't wrap my brain around the examples.

I need to track errors for employees. I need to make the database as easy as possible for the managers to use, otherwise they won't use it.

The first field would be the manager selecting their own initials from a simple value list.

The 2nd & 3rd fields would allow them to select the initials of the offender, OR, the offenders name. If they picked JMS in field 2, then John M. Smith would get automatically filled out in field 3, and if they picked John M. Smith from field 3, JMS would be filled out in field 2. I assume I would have to have a separate table that contains the offender's initials and name.

The 4th field would be the category of the error (packing or typing). There will be different kinds of errors for the two different categories, so the 5th field would only show values related to that kind of error. for example, if they picked "packing", this field would only let them select 1,2, or 3, but if they picked "typing", they would select from A, B, or C. It would be fancy if they could skip field 4 and select from 1, 2, 3, A, B, C, in the 5th field and then field 4 would be automatically filled out, but that is definitely not necessary.

The final fields would just contain the auto-calculated time and date of the logging.

I would need to be able to create charts and reports later on, based on these results, but I'll tackle that later!

Sorry for the basic questions. Thanks.

[bcooney]

Something like this..

Tracking.fp7.zip

[posternutbag]

Yeah, that's what I am looking for! Thanks a lot. I wanted to put it together myself, but your example is too tempting not to use. I'll pick it apart sometime.

The list for the manager should probably be separate entries, but I can figure that out. Is there any downside to using their initials for the staffID, and is it possible that they can select from either their name or initials in the log layout?

[bcooney]

"The list for the manager should probably be separate entries"

Why? Most data models keep people in one table.

EDIT: Ah, you want to only list managers here. I'll modify the demo. You would need to flag Staff as a manager.

"The list for the manager should probably be separate entries, but I can figure that out. Is there any downside to using their initials for the staffID"

YES! Do not ever use something like that for a key. Always a meaningless auto-created number. For example, two people with the same initials...Mary gets married and takes her husband's surname (orphan all her records?).

What is wrong with full name? Notice, a popup lets you show one field but store another that is hidden (the ID). I'm showing Name_Full_c, but storing their StaffID.

Edit2: Added demo where managers popup only shows staff that have flag_mgr=1

Tracking2.fp7.zip

Edited September 9, 2010 by Guest

[posternutbag]

Great points. Thanks. I like the manager flag thing- that way we can track their errors, too.

Nothing is wrong with the full name. I just wanted it to be as thoughtless as possible. Managers will complain that they don't know everyone's initials, and that they don't know which initials equate to what name. If you worked with these people, you'd might understand. Not a big deal, though. Thanks for all your help.

[bcooney]

Yep, initials are too ambiguous. Btw, Welcome to the Forums!

[posternutbag]

Do you know why I can't use the autocomplete function on fields in the Log table?

[bcooney]

What fields would you be "auto-completing?"

[posternutbag]

I'd like them to be able to enter the field, then start typing the result they are looking for. The desired selection will then be highlighted, and they can press enter. Apparently, pop-up boxes don't give you that option (under control style in layout mode). If you change it to a drop-down box, the "auto-complete" check box appears in the field control menu, but is greyed out.

I've got them using it, but now they want radio buttons for the errors! I told them no; it would be too complicated to make a radio button list for the errors, that was based on the error category.

[bcooney]

Type ahead is only supported in single value lists that are formatted as a drop down list.

This value list has two values (ID and textField), and uses a popup menu to hide the ID.

So, no, you cannot have type ahead.

You can switch to a popup window that is in a list and has a script trigger find field. That is nice for when the popup menus become too long. (see this demo)

I don't know how many errors you have per category, but you might be able to make a radio button set.

PopupSelectNew2.fp7.zip

[fmow]

Type ahead is only supported in single value lists that are formatted as a drop down list.

This value list has two values (ID and textField), and uses a popup menu to hide the ID.

So, no, you cannot have type ahead.

You can switch to a popup window that is in a list and has a script trigger find field. That is nice for when the popup menus become too long. (see this demo)

I don't know how many errors you have per category, but you might be able to make a radio button set.

Just a minor correction point Bc, while I am looking over your implementation (which I am planning to use if I can understand it that is and figure how to incorporate it in the db): Fm does allow you autocomplete with a value list with two fields, provided you don't sort on the second one. And if the first field is not text apparently it won't autocomplete, despite the autocomplete check box being ticked and autocomplete not being greyed out. Really disappointing the way they've implemented this.