To use a calclation field--not always the best option, but easy to setup, it should be written as:
If ( JobStatus = "open" or JobStatus = "Hold" ; __pkPrimaryKeyField )
And then you can set up your value list to draw values from this calculation field instead of __pkPrimaryKeyField.
For some links and a demo file on conditional value lists:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
yep that's what i read.
trying 1. setting up with the new calc field is not working .. perhaps it's when does this value get calculated, and what happens when the status changes. some might go from open to closed to open again ...
otherwise, I was trying to follow your KB article, option 2. but am totally flummoxed about theTOs - create a duplicate of JobAction? and what that relationship is and which to use when setting up the list.
Partly it's that it's not conditional on anything already on the layout. It's a subset based on it's own properties.
yep that's what i read.
But it's not what you posted here as your sample calculation in your original post. Returning a value of 1 or 0 isn't going to do it. The calculation has to be a stored, indexed calculation field that returns an actual value you need your value list to be entered.
Why don't you describe what you want to do in more detail--including descriptions of the fields, values and tables involved?
Then I can use your actual tables fields and relationships to set up an example.
I have corrected the calculation so field <is_open> is calculated: If ( JobStatus = "open" or JobStatus = "Hold" ; pk_JobID ) Using <is open> instead of <JobID> in the value list did not drop down a list of only open/hold records. Curious if that might be a poor choice in indexing/ storage. And question : what happens in that field when status chages back and forth. And what is it's value if status= closed, for example.
Second attempt: I reread the articles you linked to, (I had read them previously) and still can't get it sorted. So ..
Relevant Tables: Action, ActionJoin, JobSheet. ActionJoin holds keys for Action and three other tables (for multi-multi relationships). In this instance Action <Act ID> Action Join <JobID> JobSheet.
Of interst here is the item on the Action layout linking to JobSheets using a portal: ActionJoin and a drop down Value List based on <JobID> / <JobRef> for a field showing <JobRef>.
* What I want to do is change that Value List so the choices are limited by activity - in this instance to show only Jobs with Status =Open or Hold.
Make sure that your field is of type calculation or an auto-entered calculation with the "do not replace existing value" check box cleared.
what is it's value if status= closed
If the value is neither "open nor "hold", the field is empty--this is what keeps the values from records of the other status values from supplying ID numbers to the value list.
If you just put this field on a layout do you see the expected value in each individual field?
yes its working as expected as a plain field <is_open> on a layout. field value is empty or =JobID as appropriate.
However, moving along, On the layout described above, Drop-Down Value List Jobs [<JobID/JobRef>] works as before and Value List JobsOpen [<is_open> /< JobRef>] shows all records as well, not the limited list expected. Tested on layout without portal, same thing: JobOpen list shows all items, not limited.
(side bar: as a calculation, I dont see a way to check "do not replace.. " And besides, the status is changing on/off posisbly on again. )
side bar: as a calculation, I dont see a way to check "do not replace.. " And besides, the status is changing on/off posisbly on again.
I'm trying to determine what field type you used. There are two possible methods that can be used--a field of type calcuation or a field of type number or text with an auto-entered calcualtion. If you set up the latter, an auto-entered calculation, there's an option titled "do not replace existing values, if any" that must be cleared. If you use a field of type calculation, this is not an option and there are no possible issues with changing the status failing to update the values in the value list.
Time to check the rest of your setup. I need to see two things:
The dialog box where you set up the value list options and the relationship graph in Manage | Database | relationships. You can capture both as screen shots and use the controls below "Post a Answer" to upload jpeg, gif or png image files (NOT PDFs). After seeing those two images, I'll either be able to tell you where you've gone wrong or be able to ask some very specific questions to further narrow the possible issues down.
Value List JobsOpen [<is_open> /< JobRef>] shows all records as well
Are you hiding or showing the is_open column of values in your value list? If you are hiding it, change this setting and tell me what you see in the value list.
If you are going to hide the first field or sort on the second field, use this calculation field for the secondary field in the value list:
If ( JobStatus = "open" or JobStatus = "Hold" ;NameFieldGoesHere )
You can use the normal primary key field, instead of the calculation field for the primary field.
1. used a calculation
2. JobList Open Value List with both columns shows the open jobs with IDs and the not open ones without.
relationship graph requested .
Please note that my response has appeared before yours even though I posted it after you did.
Time to send ModMan another Bug Report...
not at all I think you responded to a post I replaced because the image was the wrong one.
meanwhile, back at the value list, [ref_open the new field for jobname] <if_open / ref_open>... it's showing only one item. the first by id number.
That was it.
Check the return type for your if calculation. I would guess that it is set to return "number" when it should return "text". This is controlled by a drop down in the lower left corner of the Specify Calculation Dialog.