Filtered Value list?

[bret10]

I know this topic has been covered numerous times, but none of the solutions seem to apply or even work with my problem...

I have a data base for employee information. Here are the tables (simplified for view)

Emp_Table:

emp name (*source for value list)

status

Timecard:

emp name

Value List:

status ("active" "inactive") (custom values)

emp name (* from emp name field of emp_table)

In the Emp_table I specify info like, name and status (active or not)

In the timecard table I have a field for the emp name which is a value list from the emp names in the emp_table.

In the employee table I have a value list to select the status of the employee (active or not).

I want to have the emp name value list only display the active employees from the emp name table.

Edited August 25, 2008 by Guest

[bcooney]

Your structure seems a bit off. Have a look at this demo and tell me what you think.

Post301517.zip

[comment]

How about something simple?

Post301517s.fp7.zip

[bcooney]

That is simple.

[Simon K]

I was curious about this one -

Love the simplicity...

As far as I can see this relies on the fact that you are allowing both the calc'd id and the calc'd name to be shown in the drop down (value) list...

If you just wanted to see the employee name only (ie on a pop up menu) do you have to create another stored calc field to store the name of active employee? (so that the "display values from 2nd field only" shows only active names rather than all names)?

Many thanks

Simon

[comment]

You could simply change the definition of cFullName to:

Case ( Active ; Last & " " & First )

However keep in mind that "Show values only from second field" should be used only when you are certain that the values in second field are unique.

[Simon K]

Agree with the idea - but wanted to store the ID as a foreign key and display the name (without the id being shown to the user)

Cheers anyway

Simon

Edited August 26, 2008 by Guest

[comment]

And that's exactly what will happen.

Post301517s2.fp7.zip

[bcooney]

comment, if I deactivate an employee in your demo2, then timecard records no longer display an employee name. That is why I have two relations.

[comment]

You could just place the "real" (not calculated) related name fields on the layout.

[bcooney]

which is what i did in my example. you need the second relationship that doesn't filter the active employees.

[comment]

I am somewhat confused: my first (and only) relationship doesn't filter the active employees, so why is another one needed?

[bcooney]

Yes, I was mistaken. The calc'd full name is limited to Active. So, you'd either put the fields for First and Last, or create another calc'd full name that isn't active. However, if you do not layer the fields as I did in my example, your TimeCard will look odd when an employee is deactivated.

I am curious as to why you felt that the solution I proposed wasn't simple? While yours is perhaps more straightforward (a smart value list rather than a relationship using a constant), I feel that it may be a disservice to not provide examples with anchor-buoy graphs. As the solution progresses, the "simple" is quickly outgrown.

[comment]

if you do not layer the fields as I did in my example, your TimeCard will look odd when an employee is deactivated.

Yeah, well - the native layout tools are a bit limited, and using 'second field' AND a conditional calculation might be a little too much. Perhaps a pop-up window/portal is called for in these circumsatances. In any case, I prefer solving UI issues by layout tools and scripting, rather than by adding TO's and relationships.

why you felt that the solution I proposed wasn't simple?

A matter of counting the resources used to solved the problem - and also because I couldn't understand it, at least not immediately. Which brings us to the last point:

I feel that it may be a disservice to not provide examples with anchor-buoy graphs.

I feel exactly the opposite:

http://fmforums.com/forum/showtopic.php?tid/196775/

and especially so with demos. IMHO, a demo should demonstrate one thing only, and be as simple as it possibly can with regard to everything else. A demo is not "suggested serving", it's a learning tool that purposefully shows a specific technique in isolation.

[bcooney]

OK, I'll agree to disagree. : I prefer more relationships than fields, and I think properly named key fields and TOs should always be used. Perhaps they are not as approachable, I'll give you that, but you'll need them as the complexity of the structure evolves and I think that happens quickly.

[comment]

I think properly named key fields and TOs should always be used.

I'll agree to that - but I'm afraid you'll have to extend our agreement to disagree to include WHAT are properly named fields and TOs. I, for example, prefer easily readable names like TimeCardID to codes like _kP_TC_ID.

[bcooney]

Absolutely. Whatever nomenclature works for you, but I believe that it should be consistent and readable to another developer.

[comment]

As long as "another developer" includes me! :

[bcooney]

well, you're in a category all your own, comment! :

[comment]

_fw_that's sw_what_I_was tw_afrd_of!