6 Replies Latest reply on Dec 6, 2012 11:42 PM by oberrauch_bernhard

    Conditional formatting


      Hi everyone,

      Our company sometimes handles dangerous goods and I'm trying to create a flag on the purchase order which incacates when a Dangerous Goods product is being purchased.

      I created a field called Dangerous Goods in the product table and used a Value List which has only one value that is Dangerous Goods.

      I then placed the related field on the purchase order layout. I used conditional formatting - value = Dangerous Goods to turn red.

      It seemed to be working at first but alas not so.

      Does anyone have an idea how I could do this?


      Thank you.

        • 1. Re: Conditional formatting

          Nella -


          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.   





          • 2. Re: Conditional formatting

            Great reply, Mike.

            Nicely explained and works well.

            Thank you,



            Case ( Count ( Product::Dangerous Good ) ; "DANGEROUS GOOD" )

            • 3. Re: Conditional formatting

              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).

              • 4. Re: Conditional formatting

                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.







                • 5. Re: Conditional formatting

                  Thank you for your great help Mike & Michael, much appreciated.

                  • 6. Re: Conditional formatting

                    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.