Jump to content
skearton

Conditional formatting field based on another

Recommended Posts

I have an inventory database that shows when the price was updated by a user. The PriceMod field is a timestamp that enters the current date based on a script trigger from the price field being modified.

I want to be able to hide the pricing (which is a calculation field) on the count sheets for any items that have not had their pricing updated since Feb. 1, 2016. This way the data entry people know that current pricing still needs to be entered for that item.

Share this post


Link to post
Share on other sites

I've attached a sample.  

You don't need a script trigger if the PriceMod is in the same table as the Price.  In my example, I've used auto-enter (Replace) to instead only update the PriceModified timestamp if the Price changes.  

You don't need to use conditional formatting (which can't truly hide text) when you can use Hide.

If you have any questions about the sample, let me know.  :-)

 

PriceModified.fmp12

Edited by LaRetta

Share this post


Link to post
Share on other sites

That is good to know about the auto enter calc.

But I'm still stuck. Maybe more explaining will help...

I have 3 fields that can have a price entered, based on country of origin, for each inventory item.

Then there is a calculation field called "c_MtrlsUnitCost_CDN" that gives a Canadian dollar value for the net landed cost. This field is the one that shows up on the count sheets.

So basically I want to hide or highlight any inventory item that has not had it's price updated since Feb. 1, 2016 (using the PriceMod field, that is a timestamp). 

I tried doing a conditional format on the field c_MtrlsUnitCost_CDN using this: Formula is: Inventory::PriceModDate < 2016-2-1.

This does nothing to highlight the fields with old pricing.

Share this post


Link to post
Share on other sites

Inventory::PriceModDate < 2016-2-1.

 

FM does not see it as a date.  Instead, use Date ( 2 ; 1 ; 2016 ).

Oh you want to HIGHLIGHT - your original post said hide.  Either way, the answers is the same ... FileMaker sees 2016-2-1 as 2016 minus 2 minus 1 and not as a date. :-)

Share this post


Link to post
Share on other sites

Thanks LaRetta, that did the trick!

Screen Shot 2016-12-15 at 3.09.25 PM.png

Sorry I did say hide, but what I do is just make the text white if the result is true. The white does not show up when printed on white paper.

Share this post


Link to post
Share on other sites
4 hours ago, skearton said:

Sorry I did say hide, but what I do is just make the text white if the result is true. The white does not show up when printed on white paper.

Just a consideration for future ...

  1. The white text may not always disappear on some monitors if accessing remote desktop and it will display through as a ghost; I've had it fail in several different situations.  
  2. Some day you may change your mind on the background color but forget that you have that conditional format as white.  It has been known to bite folks at times; it has bitten me.
  3. Conditional formatting will not eliminate field borders (if used).I'm pleased you worked it out!  

About dates ...if in doubt of your result, enter your calculation into your data viewer and check the result which, in this case, would have been 0 no matter what PriceLastUpdated timestamp was entered.  If you enter  2016-2-1 by itself in data viewer, it evaluates to 2013.  So the true calculation was being evaluated as (based up timestamp of 2016-01-07 10:50:12 AM):

63587760612 < 2013

I just thought you might appreciate understanding the logic of it a bit more ... I know I've always appreciated it when someone explains the principles behind things.  I'm pleased you've worked it out!! :)

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

×

Important Information

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