I'm just guessing here, but your difficulty is most likely coming from a glitch in the relationship between Purchase Order and Product. If you post a screen shot of your relationships graph, we can confirm, but I'm guessing you have a relationship between the line items on the purchase order and the products, right? Here's what I think is probably happening:
You have more than one line item on a purchase order. Each line item points to a specific product. If that product is a "dangerous good", then you mark the block, right? Then you have a field on your purchase order that points to Products. It's pointing to the Products table. But let's take a look at a (primitive) relationship diagram:
Purchase Order ------< Line Item -------- Product
So, one PO relates to many Line Items, and each Line Item relates to a single Product, right?
Here's the kicker: If I put a related field (not a portal, but just a single related field) on a layout, it will always point to the first related record in the related table. So, in this case, it'll point to the first Line Item in the Line Item table. If there's no sort on the relationship, then it'll point to the first record that was created.
Why does that matter? Because that Line Item points to a specific Product record. What happens if the "dangerous good" record isn't the first related record, but the second, or third, or fourth? Uh oh. Our related field is empty, because the first related record isn't checked off.
Assuming I'm right about this, how do we fix it? There are a few ways:
1) You can create a new relationship between Purchase Order and Line Item and show only the items where "dangerous good" is checked. Use that new relationship to pop up your warning.
2) You can create a calculation field in Purchase Order that looks something like this: Case ( Count ( Product::Dangerous Good ) ; "DANGEROUS GOOD" ) and put that on your layout.
3) You can put your conditional formatting on each Line Item record in a portal. That way, it will show up properly on each line item whose related Product item is marked "dangerous".
Now, if my assumption is wrong ... well, straighten me out.
Great reply, Mike.
Nicely explained and works well.
Case ( Count ( Product::Dangerous Good ) ; "DANGEROUS GOOD" )
If this is for display only (as it seems), you can do without the extra field. Just type the text "DANGEROUS GOOD" directly on the layout and apply conditional formatting using the same condition.
BTW, for Boolean fields, such as the Dangerous field in your Products table, it's best to use a value list of 1 and 0 (or just 1 vs. empty).
Good point, Michael.
Nella - Michael's suggestion will avoid putting another field in your database schema, which is a good thing.
Another refinement: When you do your conditional formatting, put the text on your layout in the color you want the warning to appear. Then reverse the logic of the calculation:
Case ( not Count ( Product::Dangerous Good ))
and make the color of the conditional format the same as the background color. It'll disappear when there's no "dangerous good" in the related table, and show up when there is. Difference is, when you go into Layout Mode, you'll be able to see what you're doing.
Thank you for your great help Mike & Michael, much appreciated.
do youn know how I can format the line weight of a line-diagram? is this possible in filemaker 12 or even 11?
Can I also determine the line color of the line-diagram? I found only "line-styles", without any possibility to modifiy that.