Value List based on True values in checkbox set

[Matt Malyschko]

I have a table called Items. This stores information regarding different items, with fields (eg: ItemID, Name, Description, etc.) and a field called AvailableColours.

I have a table called Colours (with the single field called Colour) , in order to make a value list from the values in this field. I have it this way as it will be easier to add a new colour to this table rather than a custom value list.

I have a table called Orders with fields ( OrderID, Date, etc.) and another table called OrderLineItems with fields LineItemID, OrderID, ItemID, Qty, and also Colour. The OrderLineItems table fills each order through a portal by adding items, etc. This works fine.

My problem: In the Item table, AvailableColours is a checkbox set, using the value list made from the Colours table.In the OrderLineItems table, I want the Colour field to be a drop-down list made from all the available colours (those that are checked in the AvailableColours field on the Items table), and excluding those colours that are not checked.

Is this doable?

[eos]

Have a look at this example.

Note that the problem with conditional value lists is that if you subsequently invalidate the relationship, the built-in value lookup can't resolve the value anymore and you see just an ID; but there's a workaround for that.

ColorSelection.fp7.zip

[dansmith65]

I think this is what you are looking for: http://fmforums.com/forum/topic/85478-problems-with-conditional-value-lists/#entry393419

[Matt Malyschko]

Have a look at this example.

Note that the problem with conditional value lists is that if you subsequently invalidate the relationship, the built-in value lookup can't resolve the value anymore and you see just an ID; but there's a workaround for that.

This is close, however I want the Colour selection on Items rather than in the Order. I tried to make it work but with no success. I've attached my file.

When I make the value list of AvailableColours only use related records, it does not show anything...

I think this is what you are looking for: http://fmforums.com/...ts/#entry393419

This example would work, but how would I go about converting the checkbox data to a table (without having to do it manually)... I am familiar with scripting but can't determine how to loop through each of the checkbox values to determine if they are true or false... If i could do that I could have a script to generate that table for me!

[bruceR]

No file attached.

[Matt Malyschko]

doh! forgot to zip it!

attached now!

Test.fp7.zip

[eos]

I guess this is what you want. Make sure to understand which value list does what and how they're generated

Test_conditionalVL.fp7.zip

[dansmith65]

There were a couple things that needed adjusting...

• Colours value list needed to return ColourID and Colour, not just the Colour
  
• AvailableColours value list wasn't using fields from the AvailableColours table
  
• Orders table, OrderLineItems portal, colourID field should be formated as a pop-up menu so it shows the colour rather than the colourID
  

Modified file attached.

I guess this is what you want. Make sure to understand which value list does what and how they're generated

hehee... You're always just a little bit ahead of me - I never see your post until after I reply.

TestDS.zip

[eos]

hehee... You're always just a little bit ahead of me - I never see your post until after I reply.

Just because you spend time on education. :thumbsup:

[Matt Malyschko]

I guess this is what you want. Make sure to understand which value list does what and how they're generated

I am still having trouble making this work...

In the AvailableColours value list, is says the fiels come from colorCheck__Colours. But this does not appear in the relationships diagram. This is just another instance of the Colours table correct? What is it's relationship to everything else?

[Matt Malyschko]

I am still having trouble making this work...

In the AvailableColours value list, is says the fiels come from colorCheck__Colours. But this does not appear in the relationships diagram. This is just another instance of the Colours table correct? What is it's relationship to everything else?

actually got it working looking at the other example! thanks for your help!