Solved

Need some help with changing color of field's text based on certain criteria...???

Posted on 2007-04-09
17
407 Views
Last Modified: 2010-04-27
We have an Inventory table that, of course, holds our current inventory.  We also have an Auctions table that stores all of our eBay auction data for inventory that we have posted.  

My goal here is to highlight the ItemNumber of any item in Inventory (list view) that needs an auction posted.

Any item could have a bunch of auction records created for it.  Auction records have a status field with values: Current, Winner, Loser.

I know how to use the TextColor function to change the color of a field's text based on an If/Then, for instance.  But what I need is a calc. field that will be a 1 or 0 based on whether or not there are any current auctions for that item.  If there are current records set NeedsAuctionPosted to a 0.  If there are not any current auctions set NeedsAuctionPosted to a 1.  Then my text color could look at NeedsAuctionPosted to decide whether to change the font color not.

Any information I can get on how to create a field that can search auctions records with the item's ItemNumber and set another field to 1 or 0 based on whether or not any of the current auction records were Current or not would be greatly appreciated.  

Of course, if there's a better way to do this all together I'd love to hear that as well.  Thanks!
0
Comment
Question by:Andrew Angell
  • 7
  • 7
  • 3
17 Comments
 
LVL 9

Expert Comment

by:jvaldes
ID: 18880200
Set NeedsAuctionPosted to Calculation :

If (Status="Current";1;0)

Then create a summary field : SummaryNeedsAuction Posted to Maximum of NeedsAuctionPosted select the analyze individually


Set the Item Fields to the following calculation

If(auction data::Summary NeedsAuctionPosted=0;TextColor ( Item ; RGB ( 999;0;0 ) );TextColor ( Item ; RGB ( 0 ;0 ; 0 ) ))

That should change the text color to RED when there is no related item in the auction database that contains a record with the status set to Current

I have a database named auction.fp7 that you can download from
 
http://homepage.mac.com/beckyv/

That has a working example, Good luck
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18880400
I actually made a little bit of progress on my own and was coming back to update.  I've still got problems, though. I did very close to what you said, but not the same.

I created a field in Auctions called AuctionIsCurrent and set it to a calc with exactly what you have:

If (AuctionStatus = "Current" ; 1 ; 0 )

Then, in Inventory I created a calc. field that used Sum...

If ( Sum ( Auctions::Auction_IsCurrent ) > 0 or Qty_Available = 0 ; Ref_Number ; TextColor ( Ref_Number ; RGB ( 255 ; 0 ; 0 ) ) )

So that actually checks to see if any of the auction records for the item are current and also checks the qty to see if we have any.  If none are current and we do have some in stock then it changes it to red.

I'm not quite sure what you were saying with the Summary stuff there.  Is there advantage to do this differently than I have?

I'm trying to do something similar and I'm having a heck of a time getting it to work.  One problem with this method of highlighting the text in these fields is I have to display that calc. field on the layout.  However, this makes that field un-editable.

To try and get around that I decided that instead of highlighted the text in the field a different color I could highlight the field background instead.  Here's what I've done with no luck.

I created a container field called bgGreen.  This field is simply an edit box wit a background filled to green.  It's on a separate layout.  I then created a field called UnitNumberBG and set it as a calc. with the following:

If ( ScanLabelPrinted = 1 ; bgGreen ; "" )

Thinking that I would end up with a green box where I place that field on the layouts we'll be using.  It does not ever change, though, no matter what the value of ScanLabelPrinted is.

Could you possibly a sample for that into your FM file here..??  It'd be GREATLY appreciated.  I cannot figure out what I'm doing wrong.  Thanks!

0
 
LVL 9

Expert Comment

by:jvaldes
ID: 18880608
The only reason I could think of your routine not working is that when you summarized that field you didn't select the individually selection at the bottom of the summary calculation dialog.

I suggest that to change the background color you use a different approach that is not as limited as the one you are choosing,

First of all create a Container field with two repetitions, then save an image of the color that you want tyhe background to change to in each of the repetitions. Next, set the value of the container to either 0 or 1 and the repetition will display (the color or image you selected) what is nice about this approach is that you can put any graphic in the background this way, by just arranging them in the proper order....

If you post your database where I can get it, I can look at it and hopefully I can help. But I think if you follow my notes it will also work...
0
 
LVL 28

Expert Comment

by:lesouef
ID: 18880707
if you'd like a field to be a calc. and still be editable, make it a an auto-enter field based on a calc, and uncheck "do not replace existing value..."
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18883720
lesouef:

That seems to work on the front end of things, but not the back.  What I mean is say I have a record where ScanLabelPrinted = 0 so the text is currently black.  If I set it to 1 the text does turn green based on being validated by the calc.  However, if I set that back to 0 again it stays green.  The actual calc. field will change back and forth based on the value of ScanLabelPrinted.  Is there a way to do that with validation so I can leave it as an editable field?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 18884117
is it an auto-enter field or a calc. field?
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18884976
I had it as an actual calculation field type before, but that's when I couldn't edit the field then....at least not without making some workaround that actually takes you to the text field that the calc. field is based on.

After reading your response went from using the calc. field to simply adding the calculation into the Auto-enter and the Validate using calculation portions of the original text field.

With that, it changes to green upon the ScanLabelPrinted field being set to 1, but will not change back when ScanLabelPrinted is set back to 0.

Another problem I have is that with the calc. validation on this text field I can't leave it blank without getting a message telling me the field only accepts certain values and do I want to accept it or not.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18885003
I tried fixing the blank value problem by adding a check for that into the validation calc.

If (UnitNumber = "" ; UnitNumber ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

and I also tried

If (UnitNumber = "" ; "" ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

Still getting the message, though, when I try and set the value to blank.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

by:lesouef
ID: 18885456
1
you must have "do not replace existing value" unchecked for the auto-entered field.
2
also, once a text has been coloured, it stays coloured if you enter something else. to remove the colour, you must specify textcolorremove() or textformatremove() or set another colour like black or whatever.
your line becomes:
If (UnitNumber = "" ; "" ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; TextColorRemove(UnitNumber) ) )
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18885768
Ok, that makes sense with the TextColorRemote thing, thanks!  I checked the box for "do not replace existing value" in Auto Enter but when I do that it doesn't turn green like it should.  :(
0
 
LVL 28

Expert Comment

by:lesouef
ID: 18886204
it should be unchecked! read what I wrote!
0
 
LVL 9

Accepted Solution

by:
jvaldes earned 300 total points
ID: 18886522
I made the modification to the auction database that I created for you before to include changing the color of a block that you can place behind a field that is set to transparent (to give the imprerssion that the field color is changing), with this approach you can set a preferences file where you can update the colors that you get , and if you have many different fields you want to do this with it will reduce the amount of editing you have to do.

It's open so you can see how I did it.

download from the same location I provided before:

http://homepage.mac.com/beckyv/
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18887023
lesouef:

It was unchecked before.  Sorry, I read that as if you were saying, oh, it must be this...that's why it's not working.  I should have read it as it must be checked...like check it cuz you have to.

Anyway, it was like that before.

jvaldes:

thanks, i'm gonna play with this.  be back in awhile.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 18887856
check/unchek: I'll use on/off next time!
ok, so you're only left with the textcolorremove() then
0
 
LVL 28

Expert Comment

by:lesouef
ID: 18887878
just checked: what I said is true for manual entry, not for computed data.
just using
If (flag=1; TextColor ( flag ; RGB (0;0;255 ))  ; flag)
is enough to make it blue if flag=1
any other value leaves it with the default layout colour used for this field text. what is the text colour used in yr layout for this field?
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18891948
The default color in the layout is black, and the text in that field is indeed black until flag=1.  Once flag=1 it changes to green, but it stays green from this point on no matter what flag is set to.

Also, if the field that has the validation calc. on it is empty then I get a popup message asking if i want to accept the value and I have to say yes.  I'd like to make it accept blank values without that popup.  As I said before, I tried adding another If/Then check to see if the field is empty prior to checking the flat value and changing the text color:

If (UnitNumber = "" ; UnitNumber ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

but that's not working, I still get that popup.
0
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 200 total points
ID: 18892253
ok, dooky, you are using the validation calc!! it should be the auto-enter calc. lave the validation stuff blank
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now