Condtional VL not working

[4Justme2]

Hello,

I'm trying to create a conditional value list. I've read through what I can find on this topic... and there's a lot. Still not able to equate a solution to my example. Can someone advise please advise how to proceed?

_EMPLOYEES

ID_EMP.pk

Name

Status (active or inactive)

_PROJECTS

ID_PRO.pk

ID_EMP.fk

Team Leader

My link between the two tables is _EMPLOYEES::ID_EMP.pk to _PROJECT::ID_EMP.fk

To simplify this example I have just two records in the employees table:

Name Ben

Status Active

Name Sally

Status Inactive

The Team Leader value list field in the _PROJECTS table should only produce those records where the Status = "Active" in the _EMPLOYEES table.

What do I do to create a value list in the _PROJECTS table that will only show me Ben (because he's active) and not Sally?

Do I need only one table table occurance or two? Of which table do I need a table occurance (_EMPLOYEES or _PROJECTS)?

When setting up the relationship to link related records only... which table do I list on the left, on the right, and which do I indicate in the related records only section?

I've tried to do this in many different combinations and I must be doing something wrong and because there are a number of different steps involved I am unable to indentify what part I'm doing wrong. Any help would be greatly appreciated. Thank you... mp

[mr_vodka]

You need some kind of relationship between a utility field that returns "Active" and the Status field in your employees table. Personally for flag fields like this, I like to use a simple checkbox field with a value of 1 and a utility field that returns the value of 1. This way it can be used in many other ways.

[4Justme2]

Very good. I'll change the type of the Status field in the _EMPLOYEES table to a checdkbox with value of 1 = Active... Should it be a text field having the data value of 1? Then what do I do? Do I still need a table occurrance to link to that specifc relationship where Status = 1? Do I only need one table occurance and if so, which table do I create the TO from?

[mr_vodka]

Well you should probably use a number field. Yes you would create a TO from the flag field to the Status field.

Also, just to let you know there is another method you can use to achieve what you are trying to do for this specific case. You can create a calculation in the employees table that is something like:

Case ( Status = "active"; D_EMP.pk )

Now you can create your value list to be based off that calculation field.

[4Justme2]

If I go with the calcualtion... are those two different fields?

Status, text, "active:

Status.c, number type, (the case statement calculation)

[imoree]

Also, just to let you know there is another method you can use to achieve what you are trying to do for this specific case. You can create a calculation in the employees table that is something like:

Case ( Status = "active"; D_EMP.pk )

]

test this:

create a textbox in your Current table (name it Status )

now create a new Field ; call is c_checkStatus ( make it a textfield; with calculated value: Uncheck; if not replace... and enter Johns calc with your fields; Case( Name ="active"; YOURFIELD::ID )

Create value list based on this c_checkStatus.

hope that helps

Edited April 17, 2012 by imoree

[4Justme2]

I changed the names of the tables and fields to match what I have in my primary database (just to make to it easier for me when I try to recreate this in my real solution).

I must have missed something you suggested above because although I'm closer, I've still got something wrong. The value list is showing me all values.... (but only those flagged as events are actually populating the field which is good--but I want the value list to only show events. Can you tell what I've done wrong from the attached?

conditionalVL Copy.zip

[mr_vodka]

See here.

conditionalVL _MOD.zip

[4Justme2]

Beautiful! Many big thank yous... :laugh2:

[4Justme2]

I wanted to do something further with this. Upon pick from the value list I'd like to populate other fields from the ProgramsInitiatives table. I started by placing the ID_PROIN.fk field under the values list. I thought here was if I could get the ID field to change on pick from the values list I could autopopulate other fields from the ProgramInitiatives table.

When didn't automatically work I created a script trigger to copy and paste on Save of the VL field; when that didn't work I added a trigger on modification of the VL field. Still not working.

conditionalVL _MOD Copy.zip

[mr_vodka]

Well you didnt explain your set up in detail from the beginning so I havent been able to provide the best example. In the future please try to explain your issues as true as they are rather than abstract examples. It really helps us better understand what is going on.

Your file already has a relationship from ProgramInitiatives to ProgramDetails that is based on the ID fields. Do you want to keep this one?

If you are trying to view data from ProgramIntiatives table that reflect the record data of the specific event that you have chosen, then you need another table occurrence keyed from the ProgramInitiatives field to ID_PROIN.pk. Then you place the other fields on the layout from that new table occurrence.

[4Justme2]

Well you didnt explain your set up in detail from the beginning so I havent been able to provide the best example. In the future please try to explain your issues as true as they are rather than abstract examples. It really helps us better understand what is going on.

Sorry about that. My intent was not to clutter--but I see your point, now.

Your file already has a relationship from ProgramInitiatives to ProgramDetails that is based on the ID fields. Do you want to keep this one?

Not sure how to answer this question. I thought it was required in order to show the additional populated fields that I want to pull in after the vl selection has been made. Is it not required?

If you are trying to view data from ProgramIntiatives table that reflect the record data of the specific event that you have chosen,

Yes, that's what I want to do.

then you need another table occurrence keyed from the ProgramInitiatives field to ID_PROIN.pk. Then you place the other fields on the layout from that new table occurrence.

I thought that was what I already had with the relationship between the ProgramInitiatives using ID_PROIN.pk and Programdetails using ID_PROIN.fk. I must be misunderstanding something here. Are you suggesting that I create another layout from the context of ProgramInitiatives? I definitely need the value list to appear on the program details layout. I apologize for being very confused... but I am very confused and appreciate your taking the time to help me understand this.

[mr_vodka]

Layouts displays data in the context of the table occurrence.You dont need a new layout. If you want your original relationship to display the selected related information, then just put the value list on the ID_PROIN.fk field itself. Then you dont have to create a new table occurrence. That is why I asked if you actually needed it for something else earlier.

I think you need a better understanding of some key FileMaker concepts. I would highly suggest reading this whitepaper. It is pretty old but its a great read to better understanding FileMaker.

Key Concepts

[4Justme2]

Very good. That worked for me--changing the field to the ID. Thank you again... mp