Conditional Formatting based on related field

[Heres Trouble]

Hi All,

I have lists of ingredients in my recipes, and when these are special ingredients, ie relying on my seasonal garden or that need to be bought from the shop, I want their text to change colour as a warning when contemplating what to cook. My Ingredients table has both "Ingredients" and "Ingredients Source", but I have no room in the portal to show the Ingredients Source, so want to change the colour of the "Ingredient" text itself.

I've tried using the formula in the conditional formatting dialogue box by saying that IngredientsT::IngredientSource = "Seasonal Garden" but this doesn't work. Is what I want possible? Thanks heaps!

[Vaughan]

That should work...

Here's a test. Put the Ingredients Source field in the portal row and see if it displays what you think it should, which is "Seasonal Garden" for those record so marked.

[Heres Trouble]

Hi Vaughan and thanks for responding. I added "IngredientsSource" to the same portal, but it shows nothing, even though I have the ingredients and ingredient source in an Ingredients layout as related fields, ie each ingredient is shown as having only one source. Should the IngredientsSource show automatically when I add the Ingredient? Helen

[Lee Smith]

Maybe the attach simple demo file will help you

Receipt.fp7.zip

[Heres Trouble]

Thanks Lee! You've shown me that it CAN work, and I've got the right formula: I just have to figure out what I've done to stop my IngredientsSource appearing automatically in the portal now. Helen

[Heres Trouble]

Sorry, me again. Every time I add an Ingredient to a recipe, I use a pull-down value list based on the Ingredients field (ie Asparagus, Broad Beans etc). This actually creates a new record every time, so only the orginal Asparagus or whatever has the related IngredientSource attached to it. I must be adding the ingredients incorrectly to get all these "duplicate" records, right? Thanks, Helen

[comment]

It sounds like something's wrong with your basic structure, but your description is difficult to follow. I believe you need three tables here: Recipes, Products and Ingredients. You add an ingredient to a recipe by selecting a product and filling out the quantity. This should crate a new record in Ingredients, that is related to both a Recipe and a Product.

IOW, the relationships between Recipes and Products is many-to-many, and Ingredients is a join table between these two.

[Heres Trouble]

Hi "Comment" and others and thanks for trying to help. I'm still having trouble with this unwanted duplication of records. I'll try to explain, and have attached a zip file of the db.

My current structure is that 'ReadytoCookT' table brings together all other tables. The peripheral tables contain a serial number which is the join field to the ID fields in ReadytoCookT.

The relationship diagram shows one to many for each relationship.

If anyone has the patience, goodness and time to help, if you go into the db and click on "Zucchini and Marjoram Frittatas" you will see that I've added ingredients to this recipe (only). Then if you go to the Ingredients layout and scroll to the bottom (or sort), you will see that every ingredient in that recipe is duplicated.

I have to use a portal (source 'IngredientsT::Ingredient) to add Ingredients to the recipes (ReadytoCookT). When I add the ingredients, instead of using existing ingredients a new ingredient is created, evidenced by new serial number and that is is no longer linked to the IngredientSource.

How can I get the relationship to use existing ingredients?

Thanks and sorry for a convoluted question. Helen

Avanothergo.zip

[bruceR]

You seem to have gone overboard on creating data tables where they are not required. See attached for suggested changes.

Avanothergo.zip

Edited December 23, 2008 by Guest