Jump to content
Sign in to follow this  
Matt Malyschko

Value List based on True values in checkbox set

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

No file attached.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.